Stored Function to generate Sequences

Today a customer asked me to help them to convert their sequence generation process to the stored procedure and even though I have already seen it somewhere I did not find it with two minutes of googling so I wrote a simple one myself and posting it here for public benefit or my later use 🙂

This implementation uses single table to maintain multiple sequences which application can use in desired way, though you of course could have them using different tables.

I use MyISAM tables here which allows to use such sequences in transactions without serializing transactions which require access to same sequence, though it is not as safe as if you use Innodb table in this case.

Even though implementation is just 2 lines of code it seems to confuse a lot of people because last_insert_id() is used rather unusual way – with argument. This way of using this function allows you to “inject” the value to be returned next time this function is called.

Sometimes people wonder why you would like to use sequences instead of MySQL auto_increment columns ? Leaving aside more exotic ways of sequences even pure sequential value as in the case above can be quite helpful – in MySQL 5.0 you may with to use them instead of auto_increment with Innodb tables to avoid short term “table level locks” which innodb sets when Insert is happening in the table with auto_increment values. It is also helpful if you need to decouple ID generation from storing the data – for example IDs are generated on central server and when stored on number of servers or to number of individual tables.
Finally your own sequences allow you to generate multiple sequence values on demand with single statement for one or more sequences, which also can be helpful:

I should note this sequence generation requires serialization, though it is short term but it may still become the bottleneck for application with high sequence use rate. For such heavy duty apps I would use another approaches – in particular allocating “ranges” of sequences and caching them in applications, using UUID_SHORT() and other methods which do not require global lock for each time new sequence value needs to be retrieved.

Share this post

Comments (21)

  • kL

    If you change sequence name from varchar to char you’ll get fixed-width table (slightly faster).

    It’s probably important that sequences are in MyISAM table that doesn’t support transactions – otherwise different threads would be isolated (I in ACID) and could come up with the same sequence value!

    April 3, 2008 at 2:49 am
  • Toby

    This technique is perfectly safe with InnoDB tables.

    April 3, 2008 at 7:19 am
  • peter


    Yes you can use char here or even better ENUM or INT if you’re hunting for last 0.01% of performance.

    Regarding Innodb Tables it still works because updates to the same rows will be serialized and so you can’t get same values in different threads.

    April 3, 2008 at 8:17 am
  • Arjen Lentz

    Here was mine (been there for a few years 😉
    Also incorporates a trigger so the app can be oblivious it’s not an AUTO_INCREMENT column.

    April 3, 2008 at 6:34 pm
  • Arjen Lentz

    kL, even with a sequence generator stored in MyISAM you would not see the same number in different threads.

    April 3, 2008 at 6:36 pm
  • peter


    Indeed insert on dup key updates saves you from initialization though I’m wondering what way would be the faster.
    Though I guess there would not be any significant difference here.

    April 3, 2008 at 6:41 pm
  • Justin Huff

    I typically see the update step written like:
    UPDATE sequence SET nValue=last_insert_id(nValue+1) WHERE strName=?

    I’d like to allow servers the option of asking for more than one id per call by moving the increment step outside the last_insert_id:
    UPDATE sequence SET nValue=last_insert_id(nValue)+? WHERE strName=?

    The value in the table shifts from being the ‘last id handed out’ to ‘the next id to hand out’. That way requesters don’t need to know how big the last block was.

    Am I missing something here?

    July 30, 2008 at 6:22 pm
  • saroj

    thanks peter code worked

    June 1, 2009 at 10:15 pm
  • Bruno Braga

    This code does not handle concurrency/locking. Has anyone tested this against overloaded systems? I have the feeling it will not work properly.

    October 22, 2009 at 12:06 am
  • Baron Schwartz

    MyISAM has table-level locking. It works.

    October 23, 2009 at 5:23 am
  • Bruno Braga

    Yeah, I did a stress testing on this, and worked fine (on InnoDB as well).

    October 23, 2009 at 5:15 pm
  • David Smith

    Well, I see I’m a little late to the party…

    I’m wondering if I can use something like this for two user classes sharing the same int table row, by auto-incrementing in ranges, e.g. user class one has user_id in the 0 – 1000 range, and user class two in the > 1000 range.

    The reason I would need to do this instead of just cresting another table for the new user class, is because I am attempting to modify an existing, and rather complicated (to me) php app.

    What sort of syntax would I need to use, given that I want to modify transactions to/from an existing row rather than CREATE TABLE (as your example does)? I just want to write and filter on queries for user_id’s from the two seperate ranges, and auto-increment new users of the two clases in their respective ranges.


    October 7, 2010 at 2:04 pm
  • Silvio Delgado

    Very good! It works fine!

    Thanks a lot.

    October 14, 2010 at 8:47 am
  • Federico Razzoli

    I wrote a library which is a bit more complex – it implements all of the features of PostgreSQL’s sequences. You can set min_value, max_value, increment, start value, if the value can rotate…
    If you just need come standard sequences, your solution is the best (fast, simple, customizable).
    However, here’s the repository:
    If someone uses that lib, all feedbacks are welcome.

    Best regards,
    Federico Razzoli

    November 4, 2012 at 5:37 pm
  • suresh

    mysql present version

    December 14, 2012 at 4:18 am
  • Neil

    I’m guessing this doesn’t play nicely with replication?

    January 11, 2014 at 12:36 pm
    • Balázs Csaba

      Generated value (seq value) inserted in an other table, that replicated by insert. The seq table changes replicated by the update. Both binary or command based replication. I think…

      January 12, 2017 at 1:30 pm
  • Joe

    Indeed, MySQL will puke an error as this function is non-deterministic !

    March 14, 2015 at 8:31 pm
  • Balázs Csaba

    How can I change this to handle not existing rows in seq table?

    Is this safe with InnoDB?

    insert into seq (name, val) VALUES (seq_name, 1) ON DUPLICATE KEY update seq set val=last_insert_id(val+1);
    return last_insert_id();

    January 12, 2017 at 1:28 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.