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 Fri, 25 Jun 2004 13:06:55 GMT
Excellent!! Thank you very much.. that was indeed my problem. I reran my
unit test after changing the table type to innodb and everything worked
great.

Good thing I'm not a DBA :)

Thanks again,

James

-----Original Message-----
From: river [mailto:river@site1.intertele.ch] 
Sent: Friday, June 25, 2004 5:46 AM
To: 'Jakarta Commons Users List'
Subject: RE: [DbUtils] MySQL rollback problem..

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




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