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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
