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:38:53 GMT
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:grahamdavid1980@yahoo.com] 
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 <JBlashill@rim.com> 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: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
> 
> 



		
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
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