RE: question about locking

[Dan Burke]

I've done similar things with sequences.  This method is borrowed from
the DBIx::MySQLSequence perl module (the _sequences table can contain
many sequences, named in the "sequence_name" field):


update _sequences set 
	sequence_value = LAST_INSERT_ID(sequence_value + 1)
where sequence_name = ?

Then to get the id you will be using...

select LAST_INSERT_ID()


I'm not sure how you would make this database independent because of the
LAST_INSERT_ID() requirement.  My work is restricted to mysql and
oracle, and we use oracle's sequences to handle that.

Dan.


-----Original Message-----
From: Patrick Duda [mailto:pduda@stripped] 
Sent: Wednesday, February 08, 2006 10:20 AM
To: mysql@stripped
Subject: question about locking

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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1


----------------------------------------------------------------------
This message has been scanned for viruses by TechTeam's email gateway.


--------------------------------------------------------------------------
This e-mail transmission is strictly confidential and intended solely
for the person or organization to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this e-mail in error, please notify the sender
as soon as possible and delete the e-mail message and any attachment(s).

This message has been scanned for viruses by TechTeam's email gateway.