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 int unsigned 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.

Answer 2

The right way to do this is given in the MySQL manual:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

Answer 3

We are a high transaction gaming company and need these sort of solutions for our needs. One of the features of Oracle sequences was also the increment value that could also be set.

The solution uses DUPLICATE KEY.

CREATE TABLE sequences (
  id BIGINT DEFAULT 1,
  name CHAR(20),
  increment TINYINT,
  UNIQUE KEY(name)
);

To get the next index:

Abstract the following with a stored procedure or a function sp_seq_next_val(VARCHAR):

INSERT INTO sequences (name) VALUES ("user_id") ON DUPLICATE KEY UPDATE id = id + increment;<br/>
SELECT id FROM sequences WHERE name = "user_id";

Answer 4

Won't the MySQL Identity column on the table handle this?

CREATE TABLE table_name ( id INTEGER AUTO_INCREMENT PRIMARY KEY )

Or are you looking to use it for something other than just inserting into another table?

If you're writing using a procedural language as well (instead of just SQL) then the other option would be to create a table containing a single integer (or long integer) value and a stored procedure which locked it, selected from it, incremented it and unlocked it before returning the value.

(Note - always increment before you return the value - it maximise the chance of not getting duplicates if there are errors - or wrap the whole thing in a transaction.)

You would then call this independently of your main insert / update (so it doesn't get caught in any transactions automatically created by the calling mechanism) and then pass it as a parameter to wherever you want to use it.

Because it's independent of the rest of the stuff you're doing it should be quick and avoid locking issues. Even if you did see an error caused by locking (unlikely unless you're overloading the database) you could just call it a second / third time.