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  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!):
INSERT INTO sequence_name() VALUES ()
SELECT LAST_INSERT_ID AS _next_id
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.