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" <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
