List:General Discussion« Previous MessageNext Message »
From:Patrick Duda Date:February 10 2006 7:00pm
Subject:Re: InnoDB and locking
View as plain text  
At 12:54 PM 2/10/2006, Mark Matthews wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>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
> >
> >
>
>Patrick,
>
>Are you sure the table is using the InnoDB storage engine? What does the
>output of "SHOW CREATE TABLE " for the table in question say?
>
>         -Mark


ysql> show create table requestid_innodb;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create 
Table 
|
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| requestid_innodb | CREATE TABLE `requestid_innodb` (
   `request_id` int(11) NOT NULL default '0',
   PRIMARY KEY  (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


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