List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:February 8 2006 4:06pm
Subject:Re: question about locking
View as plain text  
Hello.

The logic of your application is clear and should work (though I haven't
been digging deeply inside the code). Check that the table type is
InnoDB. 4.0.1 version is rather old and could have lots of bugs, I
recommend you to upgrade to the latest release. Another reason, is that
your algorithm generates overlapped intervals of transerid values, but
this is not a MySQL issue. Enable the general log and check the
sequences of queries produced by your container.



Patrick Duda wrote:
> 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
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com
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