You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by James Blashill <JB...@rim.com> on 2004/06/23 21:03:35 UTC
[DbUtils] MySQL rollback problem..
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DbUtils] MySQL rollback problem..
Posted by David Graham <gr...@yahoo.com>.
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 <JB...@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