List:General Discussion« Previous MessageNext Message »
From:Patrick Duda Date:February 8 2006 3:19pm
Subject:question about locking
View as plain text  
Hi,

I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems 
trying to figure out why I am not getting the results I am expecting.

I have a table that is used for generating primary keys.  It only has one 
item, an int that is incremented each time a key is needed.  This is not my 
code or my design so using something like auto_incrament is not an option.

The code runs under a container and our desire is to have several different 
containers running at the same time, all accessing the same database.  Each 
container is independent so the controls need to be on the database side.

The solution also needs to be portable to other databases so I am trying to 
stay with standard JDBC or SQL options.

The code for generating a new key is this:

         try {
             c = DatabaseSetup.getDBConnection();

c.setAutoCommit(false);

             statement = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);

rs = statement.executeQuery("select transfer_id from transferid for update");

             if (!rs.next())  {
                 nextTransferId = nextTransferId + 1;
                 StringBuffer query = new StringBuffer();
                 query.append("insert into transferid(transfer_id) values (");
                 query.append(nextTransferId);
                 query.append(")");
                 tempStatement = c.createStatement();
                 // Now Update the old value with new value
                 tempStatement.executeUpdate(query.toString());
             } else {
                 rs.previous();
                 while( rs != null && rs.next() ) {
                     nextTransferId = rs.getInt(1);
                     // Get the transfer Id and increment it instead of using
                     // Db Specific sequence
                     nextTransferId = nextTransferId + 1;
                     // Now Update the old value with new value
                     tempStatement = c.createStatement();
                     tempStatement.executeUpdate("update transferid set "+
                             "transfer_id= "+ nextTransferId);
                 }
             }
         } catch (SQLException e) {

if( c != null )
{
try
{
c.rollback();
c.setAutoCommit(true);
}
catch( SQLException ex )
{
}
}
             throw new DBException(i18n.getMessage("dbInsertErr"),
                     e);
         } finally {
             try {
c.commit();
c.setAutoCommit(true);
                 if (statement != null) {
                     statement.close();
                 }
                 if (tempStatement != null) {
                     tempStatement.close();
                 }
                 if (rs != null) {
                     rs.close();
                 }
                 if (c != null) {
                     DatabaseSetup.returnDBConnection(c);
                 }
             } catch (SQLException sql) {
                 logger.warn(i18n.getMessage("dbStatementErr"), sql);
             }
         }
         return nextTransferId;
     }

I thought, that if I turned off autocommit I would enter a 
transaction.  Then, by using the "select...for update", that I would take 
and hole a lock on the table.  That no other transaction would be able to 
read the table until I released the lock.  However, this is not what I am 
seeing when I run some tests.  I start up a number of containers and then 
fire off a bunch of jobs to each.  Each of these jobs will hit the above 
code.  The problem is that every so often I see the following error message.

  Error inserting records into database [Caused by: Duplicate entry '131' 
for key 1]

What am I doing wrong?

How am I suppose to be doing this via JDBC?  I know it should work...

Thanks

Thread
question about lockingPatrick Duda8 Feb
  • Re: question about lockingGleb Paharenko8 Feb
  • Re: question about lockingPeter Brawley10 Feb
    • MySQL 5.0.x and DelphiISC Edwin Cruz10 Feb
      • Re: MySQL 5.0.x and DelphiMartijn Tonies10 Feb
        • RE: MySQL 5.0.x and DelphiISC Edwin Cruz15 Feb
          • Re: MySQL 5.0.x and DelphiMartijn Tonies15 Feb
RE: question about lockingDan Burke8 Feb