Excellent!! Thank you very much.. that was indeed my problem. I reran my unit test after changing the table type to innodb and everything worked great.
Good thing I'm not a DBA :) Thanks again, James -----Original Message----- From: river [mailto:[EMAIL PROTECTED] Sent: Friday, June 25, 2004 5:46 AM To: 'Jakarta Commons Users List' Subject: RE: [DbUtils] MySQL rollback problem.. Are you using InnoDB or BDB tables on mysql. If you are using default table type for mysql (MyISAM) than you don't have transactions. MyISAM don't support transactions. > -----Original Message----- > From: James Blashill [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 23, 2004 10:39 PM > To: Jakarta Commons Users List > Subject: RE: [DbUtils] MySQL rollback problem.. > > > If I weren't using connection pooling I wouldn't be concerned at all > really.. > > I've often wondered about what happens to uncommitted or rolled back > data when using connection pooling (DBCP).. I browsed through the DBCP > code a little and it looked to me as though if you didn't rollback the > connection, DBCP wouldn't do it for you. And so when the connection > was reused there was potential for the next transaction to commit old > data? > I have never gotten around to really testing this.. Has anyone else? > > Thanks, > > James > > -----Original Message----- > From: David Graham [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 23, 2004 4:19 PM > To: Jakarta Commons Users List > Subject: RE: [DbUtils] MySQL rollback problem.. > > Then just replace the "catch (SQLException e)" with "catch (Exception > e)". > However, the rollback shouldn't even be needed because you would > never commit if there was an exception; it would be rolled back > automatically when the Connection was closed. > > David > > --- James Blashill <[EMAIL PROTECTED]> wrote: > > 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] > > > > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We finish. > 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] > --------------------------------------------------------------------- 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]
