Patrick,
>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);
As you can demonstrate by running your For Update query on the same
InnoDB table row in two instances of the mysql client, a second
connection trying to execute your For Update query on a row already
locked by that query in another connection first hangs, and then times
out. Given your, that query looks like the correct behaviour. When you
try the same thing from two instances of your Connector/J code, is the
query returning instantly in the second Connector/J client? If so, you
would seem to have exposed a Connector/J problem. If not, perhaps it's
because you need some deadlock handling code?
PB
-----
Patrick Duda wrote:
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?
Thanks
Patrick
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
