commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "river" <ri...@site1.intertele.ch>
Subject RE: [DbUtils] MySQL rollback problem..
Date Fri, 25 Jun 2004 09:46:04 GMT
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:JBlashill@rim.com] 
> 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: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
> 


---------------------------------------------------------------------
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