List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:February 11 2006 9:17am
Subject:Re: InnoDB and locking
View as plain text  
Patrick,

it should work. You have only shown a fragment of the application code. 
Maybe there is a bug somewhere else. If you write a very simple test program 
to test this, do you still get the duplicate values?

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php


----- Original Message ----- 
From: "Patrick Duda" <pduda@stripped>
Newsgroups: mailing.database.myodbc
Sent: Friday, February 10, 2006 9:01 PM
Subject: Re: InnoDB and locking


> 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)
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
> 

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