Emulating a transaction-safe SEQUENCE in MySQL

Question

We're using MySQL with InnoDB storage engine and transactions a lot, and we've run into a problem: we need a nice way to emulate Oracle's SEQUENCEs in MySQL. The requirements are: - concurrency support - transaction safety - max performance (meaning minimizing locks and deadlocks)

We don't care if some of the values won't be used, i.e. gaps in sequence are ok. There is an easy way to archieve that by creating a separate InnoDB table with a counter, however this means it will take part in transaction and will introduce locks and waiting. I am thinking to try a MyISAM table with manual locks, any other ideas or best practices?

Answer 1

If auto-increment isn't good enough for your needs, you can create a atomic sequence mechanism with n named sequences like this:

Create a table to store your sequences:

CREATE TABLE sequence (
  seq_name varchar(20) unique not null,
  seq_current unsigned int not null
);

Assuming you have a row for 'foo' in the table you can atomically get the next sequence id like this:

UPDATE sequence SET seq_current = (@next := seq_current + 1) WHERE seq_name = 'foo';
SELECT @next;

No locks required. Both statements need to be executed in the same session, so that the local variable @next is actually defined when the select happens.