Take the 2-minute tour ×

I am recently in the process of moving from oracle to mysql and would like some advice if how i am implementing something similar to sequences in mysql is a good way.

Essentially how i am currently going to implement it is by having a separate table in mysql for each sequence in oracle and have a single column which represents the last_number and increment this column when ever i insert a new row, that's one way another way i could go about doing it is by creating a single table with several rows representing each sequence and increment each row separately whenever i do an insert.

Another simpler way of doing it i could just do a select max()+1 on the relevant column when inserting data.

I'm basically thinking of switching to the select max()+1 option as it seems simpler to implement, but i would like to get some advice on what you think would be the best way of doing it out of these options, and if there is any pitfalls that i am currently not aware of when using select max()+1.

Also the reason im am not using auto_increment and the function last_insert_id() is i want to follow the ansi standard.


share|improve this question
Does auto_increment do what you want? –  Barmar Dec 20 '12 at 9:43
Should have mentioned, i want to follow ansi standards. –  JCS Dec 20 '12 at 9:44
AFAIK there is no ANSI standard for sequences (only for generated "identity" columns). If you need sequences that much, why not migrate to PostgreSQL (and profit from many other advanced features). Especially when migrating from Oracle PostgreSQL is probably the better match than MySQL. –  a_horse_with_no_name Dec 20 '12 at 9:55
@a_horse_with_no_name Would creating a function which performs a select (column name) for update; than an update set x=y; would be ansi standard right? –  JCS Dec 20 '12 at 10:05
I'll second horsey's comment -- I've found the move from Oracle to Postgres to be pretty painless. –  David Aldridge Dec 20 '12 at 10:17

2 Answers 2

up vote 1 down vote accepted

There is a read consistency problem, in that two sessions both running ...

insert into ... select max(..)+1 from ...

... at the same time both see the same value of max(...), hence they both try to insert the same new value.

You have the same problem with your table of maxima method, and you have to use a locking mechanism to avoid multiple session reading the same value. This leads to a concurrency problem where inserts to the table are serialised.

share|improve this answer
Thanks for the reply, essentially how i am currently updating the table is first getting the last_number using the select...for update so it locks the row until the transaction is finished, then i increment the value and perform an update on the sequence table. –  JCS Dec 20 '12 at 9:50
SELECT ... FOR UPDATE will lock the sequence table for the duration of the transaction. This implies, that you cannot have 2 transaction both needing the sequence run concurrently - this might or might not be acceptable –  Eugen Rieck Dec 20 '12 at 9:53

First of all: The max()+1 version is NOT guaranteed to give you a sequence, if you use transactions in a high isolation level.

The way we typically use sequences (if we can't avoid them) is to create a table with an AUTO_INCREMENT value, INSERT INTO it, SELECT last_insert_id(), DELETE FROM table WHERE field<$LASTINSERTID. This is ofcourse done in a stored procedure.

share|improve this answer
max()+1 will not even work with read committed isolation level. –  a_horse_with_no_name Dec 20 '12 at 9:51
The important part is: It is NOT guaranteed to give you a sequence –  Eugen Rieck Dec 20 '12 at 9:52
Yes I know, but your statement implies that there are isolation levels where it might work. –  a_horse_with_no_name Dec 20 '12 at 9:53

Your Answer


