Loading...

commons-user@jakarta.apache.org

[Prev] Thread [Next]  |  [Prev] Date [Next]

RE: [DbUtils] MySQL rollback problem.. James Blashill Wed Jun 23 13:48:02 2004

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]