commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Blashill" <JBlash...@rim.com>
Subject RE: [DbUtils] MySQL rollback problem..
Date Wed, 23 Jun 2004 20:07:53 GMT
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:grahamdavid1980@yahoo.com] 
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 <JBlashill@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




---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message