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]
