The reason I did not do things the way you describe is because I wanted to make sure I rollback in the case of run time exceptions as well. Other then that, I believe the two examples would behave the same.
-----Original Message----- From: David Graham [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 3:35 PM To: Jakarta Commons Users List Subject: Re: [DbUtils] MySQL rollback problem.. 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
