Mailing-List: contact commons-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Jakarta Commons Users List" <commons-user@jakarta.apache.org>
Message-ID: <20040623193503.98087.qmail@web50409.mail.yahoo.com>
Date: Wed, 23 Jun 2004 12:35:03 -0700 (PDT)
From: David Graham <grahamdavid1980@yahoo.com>
Subject: Re: [DbUtils] MySQL rollback problem..
To: Jakarta Commons Users List <commons-user@jakarta.apache.org>
In-Reply-To: <3B66AF4BC3B70B4496229C5F29C577C0082509@XCH26YKF.rim.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

First, your code would be simpler if you did this:

Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
QueryRunner run = new QueryRunner();
try {
    run.update(conn, sqlStatement1);
    run.update(conn, sqlStatement2);
    run.update(conn, sqlStatement3);
    conn.commit();
} catch (SQLException e) {
    DBUtils.rollback(conn);
} finally {
    DBUtils.close(conn);
}

If you look at the code for QueryRunner.update() you will see it doesn't
call commit() or rollback() so it's completely up to your code and the
driver.  I don't use MySQL so I can't comment on any tricks for that
database.

David


--- James Blashill <JBlashill@rim.com> wrote:
> Hi,
> 
> I noticed a problem recently where autoCommit set to false did not work
> as I intended. Here is my code snippet:
> 
>         Connection connection = dataSource.getConnection();
>         connection.setAutoCommit(false);
>         boolean success = false;
> 
>         QueryRunner queryRunner = new QueryRunner();
>         try
>         {
> 		queryRunner.update(connection, sqlStatement1);
> 		queryRunner.update(connection, sqlStatement2);
> 		queryRunner.update(connection, sqlStatement3);
>             success = true;
>         }
>         finally
>         {
>             if (connection != null)
>             {
>                 if (success)
>                 {
>                     connection.commit();
>                 }
>                 else
>                 {
>                     connection.rollback();
>                 }
>                 connection.close();
>             }
>         }
> 
> What I expected from this code was that all three sql statements would
> be performed as a transaction - that is, if one fails then the other
> will no go through either. However, I have observed that even then
> connection.rollback() get called in the event of an error. The updates
> that may have already succeeded do NOT get undone. In fact, they
> appeared in the database regardless of whether connection.commit() was
> called. I have done similar things using Postgres 7.4, but now it does
> not seem to work with mySQL. Has anyone else experienced this? I am
> using MySQL 4.
> 
> Thanks for any input you may have,
> 
> James
> 
> FYI, I am using DBCP to do my connection pooling as well.


__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org