At 10:52 AM 2/10/2006, [EMAIL PROTECTED] wrote:
Patrick Duda <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
