List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 11 2006 9:50pm
Subject:Re: InnoDB and locking
View as plain text  
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

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