List:General Discussion« Previous MessageNext Message »
From:Patrick Duda Date:February 10 2006 6:40pm
Subject:Re: InnoDB and locking
View as plain text  
At 10:52 AM 2/10/2006, Alec.Cawley@stripped wrote:
>Patrick Duda <pduda@stripped> wrote on 10/02/2006 16:28:56:
>
> > I guess I don't understand this locking stuff.  I have a InnoDB table
>that
> > has one thing in it, a counter.  All I want to do is have multiple
> > instances of the code read this counter and increment it.  I want to
>make
> > sure that each one is unique.
> >
> > Here is what I am doing in java:
> >
> > c.setAutoCommit(false);
> > ...
> > rs = statement.executeQuery("select request_id from requestid_innodb for
>
> > update");
> > ...
> > String updateQuery = "update requestid_innodb set request_id=";
> >   updateQuery = updateQuery + nextRequestId;
> > tempStatement = c.createStatement();
> > tempStatement.executeUpdate(updateQuery);
> > ...
> > c.commit();
> > c.setAutoCommit(true);
> >
> > If I have multiple instances of this code running I end up with
>duplicate
> > keys.  I thought this was suppose to lock the table so that would not
>happen.
> >
> > What am I not doing right?  What am I not understanding about locking?
>
>I think this problem is explained in detail at
>http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
>
>Alec
>


Yes, I know that, I have read that and that is why I am asking what it is 
that I am not doing right.  It talks about a counter specifically:
"2) read the counter first with a lock mode FOR UPDATE, and increment after 
that. The latter approach can be implemented as follows:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A SELECT … FOR UPDATE reads the latest available data, setting exclusive 
locks on each row it reads. Thus, it sets the same locks a searched SQL 
UPDATE would set on the rows. "
Isn't that what my Java code is doing?

I start a transaction by turning off autocommit, I then do a select for 
update.  Then I do the update and I commit.  From the way I read this, no 
one else should be able to read the table until I commit.

Yet, that is not what I am seeing.  When I start several instances of the 
program running I get lots and lots of:

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

That is what has me confused.  I thought I was doing things they way the 
manual said to.

Thanks

Patrick


Thread
InnoDB and lockingPatrick Duda10 Feb
  • Re: InnoDB and lockingAlec.Cawley10 Feb
    • Re: InnoDB and lockingPatrick Duda10 Feb
  • Re: InnoDB and lockingMark Matthews10 Feb
    • Re: InnoDB and lockingPatrick Duda10 Feb
  • Re: InnoDB and lockingPeter Brawley11 Feb
Re: InnoDB and lockingHeikki Tuuri11 Feb