Efficient sequences with MySQL

As you probably now if you reading this blog, MySQL does not support SQL sequences. Logically, it is very easy to implement something equivalent to a sequence using transaction with InnoDB with a simple 2 columns table, name and value. The only problem is that a sequence update is usually composed of two SQL statements, a SELECT to read the value and an UPDATE to increment it. Fairly you would say, use a transaction to wrap the 2 statements together but then, you are probably aware that you cannot manage transactions in a function with MySQL. A clean sequence function seemed out of reach up to recently, when Brian Morin showed me a trick that solves the issues. Here how to create a get_next_val function to a sequence.

delimiter //
drop function if exists true_function //
create function true_function(p_param int) returns int
  deterministic
  sql security invoker
  return true
//

drop function if exists get_next_value//
create function get_next_value(p_name varchar(30)) returns int
  deterministic
  sql security invoker
begin  
  declare current_val integer;
  
  update mysql.sequences 
  	set value = value + 1
  	where name = p_name 
  	  and true_function((@current_val := mysql.sequences.value) is not null);
  	  
  return @current_val;
end//
delimiter ;

The interesting stuff here is that the assignment of current_val is done inside the same SQL statement and uses the actual value, before the update. The true_function is a trick to make the optimizer not to bother with the variable assignment. The sequence table is very simple:

mysql> show create table mysql.sequences\G
*************************** 1. row ***************************
       Table: sequences
Create Table: CREATE TABLE `sequences` (
  `name` varchar(30) NOT NULL,
  `value` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

now, you get a sequence much like with other database and the transaction is implicit since everything is done inside a single statement.

Using the function is easy:

mysql> select mysql.get_next_value('user_id');
+---------------------------------+
| mysql.get_next_value('user_id') |
+---------------------------------+
|                              18 |
+---------------------------------+
1 row in set (0.01 sec)

For this recipe, if you are using explicit transactions, I recommend setting the isolation level not under “Repeatable read” since it could caused the same sequence value to be return twice. For the about same reason, isolation level under “Repeatable read” cannot be used for replication.

About Yves Trudeau

I work as a senior consultant in the MySQL professional services team at Sun. My main areas of expertise are DRBD/Heartbeat and NDB Cluster. I am also involved in the WaffleGrid project.
This entry was posted in mysql, yves. Bookmark the permalink.

5 Responses to Efficient sequences with MySQL

  1. Hi!

    “he true_function is a trick to make the optimizer not to bother with the variable assignment.”

    WTF? sorry for the swearing…I mean, can you elaborate on that?

    What would we lose if we’d chuck the true function and rewrite the UPDATE statement like so:

    UPDATE sequences
    SET value = (@current_val:=value) + 1
    WHERE name = p_name
    ;

  2. Antony Curtis says:

    Following up on what Roland commented, what about the following?

    UPDATE sequences SET value = LAST_INSERT_ID(value) + 1 WHERE name = p_name

    No need for the user variable, there is already a variable available for that… And as a bonus, the client libraries would return that value for ‘free’.

  3. Yves Trudeau says:

    Yes but will it work with many sequence names in the same table.

  4. Yves Trudeau says:

    Roland, the actual use case from where the true_function is coming is way more complex and it also was to get the assignment done. Anyway, you might be right that it is not needed, I will double check. I also was not aware it is possible to do the assignment the way you did it.

  5. Yves Trudeau says:

    Hi Robert,

    You mean creating a UDF for sequences… Interesting idea, I will think about this one. There will still be a need for a kind a global lock for serialization.

    Regards,

    Yves