List:MySQL and PHP« Previous MessageNext Message »
From:Ulf Wendel Date:August 6 2007 12:44pm
Subject:Re: Prevent Race Condition
View as plain text  
Kevin Waterson schrieb:
> I wish to get the next id from a field.
> Currently I can
> SELECT MAX(id) FROM table
> 
> but of course, this leads to a race condition with many users.
> 
> How can I get around this?
> Do I need to lock the table?

What are you trying to do?

Are you trying to get sequence numbers? Unfortunately MySQL does not 
offer SQL sequences. However, you can use AUTO_INCREMENT [1] to create 
serial numbers for a table. If that's not enough you might try using one 
or several tables to hold "sequence numbers" as you sketch it above. If 
you do so, you may use SELECT MAX() or AUTO_INCREMENT and either 
transactions or manual locking to synchronize the accesses to the 
"sequence number" table.

Here's an example with transactions and AUTO_INCREMENT. Create a table 
to hold the sequence numbers:

CREATE TABLE sequence_name(next_id INT AUTO_INCREMENT PRIMARY KEY) 
ENGINE = InnoDB

And whenever you need a new sequence number (autocommit off!):

START TRANSACTION
INSERT INTO sequence_name() VALUES ()
SELECT LAST_INSERT_ID AS _next_id
COMMIT

The same could be achieved using LOCK TABLE and MAX().

Depending on your application, you might be better off if you implement 
optimistic locking. Note that this is something you would need to 
implement on the level of your application.

Ulf

[1] http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
[2] http://en.wikipedia.org/wiki/Optimistic_concurrency_control
Thread
Prevent Race ConditionKevin Waterson6 Aug
  • Re: Prevent Race ConditionUlf Wendel6 Aug