MySQL Sequence Generator

September 15, 2010

Uncategorized

First we want to make MySQL tables retrieve PKs values from one global sequence. The second needle is to make this sequence values not consistent, it is to disallow visitors track your ids order.

h2. Theory of operation

Lets combine ideas together. One sequence for all tables + Pseudo random sequence values. My friend, the developer and mathematician, had suggested me the following formula:
@(`currval` * `factor`) % `module`@
_`currval`_ — The current sequence values, which will be incremented by 1 on each nextval operation.
_`factor`_ — Multiplier which should be equals to 282 475 249.
_`module`_ — Divisor which should be equals to 1 000 000 000 (one billion).
The algorithm guarantees than with the following formula we will always get the unique value starting from `currval` = 1 and so on.
@(`currval` * 282475249) % 1000000000@

h2. Solution

The sequence configuration will be stored into the database table `_sequence`. The `name` field is for sequence name, it should be unique. Columns `currval`, `factor` and `module` are from the formula described above. It is important to make this table not transaction aware, because transactions will damage our sequence on rollback. We use MyISAM engine for this purpose.

pre. create table `_sequence` (
`name` varchar(32) not null primary key,
`currval` bigint(20) unsigned not null,
`factor` bigint(20) unsigned not null,
`module` bigint(20) unsigned not null
) engine=MyISAM default charset=utf8;
insert into `_sequence` values (‘id’, 1, 282475249, 1000000000);

Next, we need _`nextval`_ and _`currval`_ function to deal with sequence. See their definition in my “MySQL Sequence Generator”:http://webcraft-dev.blogspot.com/2010/09/mysql-sequence-generator.html blog post.

h2. Integration

“Zend Framework supports sequences”:http://framework.zend.com/manual/en/zend.db.table.html#zend.db.table.insert.key-sequence (for PostgreSQL, Oracle, etc.), and we have to make it support our MySQL sequences. It is easy. First we should extend MySQL adapter and override _lastSequenceId()_ and _nextSequenceId()_ to call _`currval`_ and _`nextval`_. Get the “Db_Adapter_Mysql”:http://github.com/satyrius/Webcraft/blob/e73f3125594cb9e22a0373510c3ebfdb5e930b6a/classes/Db/Adapter/Mysql.php from my repository.

That’s all. Our application is ready. To make your table use this sequence do the following:

pre. create table `account` (
`id` int unsigned not null primary key comment ‘pk will be retrieved from sequence’,
`email` varchar(64) not null unique key comment ‘primary user identificator’,
`password` varchar(32) not null comment ‘user password md5 hash’
) engine=innodb default charset=utf8;

pre. // Use MySQL adapter with sequence support
$db = new Db_Adapter_MySQL($connectionOptions);
// Create account table instance, pass adapter and sequence name
$account = new Account(array('db' => $db, ‘sequence’ => ‘id’));
// Create new account and save it. PK will be generated automatically by sequence
$account->findNew()
->setFromArray(array(‘email’ = ‘sequensor@example.com’, ‘qwerty’))
->save();

h2. Conclusion

In spite of MySQL does not support sequences we can emulate this id generation method by creating _`nextval`_ and _`currval`_ mysql function. We use MyISAM engine for `_sequence` to use it outside of transactions. To itegrate this solution to our Zend Framework application we should extend _Zend_Db_Adapter_Pdo_Mysql_ by overriding _lastSequenceId()_ and _nextSequenceId()_ methods.

h2. Links

* Original “MySQL Sequence Generator”:http://webcraft-dev.blogspot.com/2010/09/mysql-sequence-generator.html article;
* This technique in action in “Webcraft CMF”:http://github.com/satyrius/Webcraft
* SQL script to create “`_sequence` table and `currval`, `nextval` functions”:http://github.com/satyrius/Webcraft/blob/4996c971fb500520b73fe68a88ea0d680b9d7ab5/scripts/schema/02_routines.sql
* Extended “MySQL adapter with sequence support”:http://github.com/satyrius/Webcraft/blob/e73f3125594cb9e22a0373510c3ebfdb5e930b6a/classes/Db/Adapter/Mysql.php

5 Responses to “MySQL Sequence Generator”

  1. brunomagalhaes Says:

    @satyrius we have 13 applications in production, all of then use transactions heavily and never had problem.

    Many times we have things like this:

    INSERT INTO `tb_accounts` VALUES
    (sp_generate_sequence(‘tb_accounts.account_id’),sp_generate_sequence(‘tb_accounts.account_code’),’User one’, …),
    (sp_generate_sequence(‘tb_accounts.account_id’),sp_generate_sequence(‘tb_accounts.account_code’),’User two’, …),
    (sp_generate_sequence(‘tb_accounts.account_id’),sp_generate_sequence(‘tb_accounts.account_code’),’User three’, …),
    (sp_generate_sequence(‘tb_accounts.account_id’),sp_generate_sequence(‘tb_accounts.account_code’),’User four’, …);

    And when we have any problems all the sequences values rolls back normally.

  2. satyrius Says:

    @brunomagalhaes Are you ok with InnoDB for your sequences table? Transaction rollback will crash you sequence on parallel inserting. The sequence value should be incremented whatever will happen.

  3. brunomagalhaes Says:

    All our applications have a few tables in common, one of these is a sequence table, and it very similar to yours but it does generate linear sequences, but it's also very flexible as we can configure prefixes, suffixes, and also we can have many sequences per table.

    CREATE TABLE `tb_sequences` (
    `sequence_name` VARCHAR(40) NOT NULL DEFAULT '',
    `sequence_prefix` VARCHAR(20) NOT NULL DEFAULT '',
    `sequence_sufix` VARCHAR(20) NOT NULL DEFAULT '',
    `sequence_value` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `sequence_increment` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `sequence_digits` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`sequence_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    DELIMITER $$

    CREATE FUNCTION `sp_generate_sequence`(`tp_sequence_name` varchar(40))
    RETURNS varchar(200) CHARSET utf8
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    BEGIN
    DECLARE tp_sequence_prefix VARCHAR(20) DEFAULT '';
    DECLARE tp_sequence_sufix VARCHAR(20) DEFAULT '';
    DECLARE tp_sequence_value BIGINT(20) DEFAULT 0;
    DECLARE tp_sequence_digits BIGINT(20) DEFAULT 0;

    SELECT sequence_prefix,
    sequence_sufix,
    sequence_value + sequence_increment AS sequence_value,
    sequence_digits
    INTO tp_sequence_prefix,
    tp_sequence_sufix,
    tp_sequence_value,
    tp_sequence_digits
    FROM tb_sequences
    WHERE sequence_name = tp_sequence_name
    LIMIT 1
    FOR UPDATE;

    UPDATE tb_sequences
    SET sequence_value = tp_sequence_value
    WHERE sequence_name = tp_sequence_name;

    RETURN CONCAT(tp_sequence_prefix, LPAD(tp_sequence_value, tp_sequence_digits, '0'), tp_sequence_sufix);
    END
    $$

    With that for example we can generate for example a RFQ-003214-2010, and the next year RFQ-005211-2011, etc. Would it be great to make a merge of our two options, and we could configure in the table if we want a linear sequence or a random sequence… :)

    Regards,
    Bruno Magalhães

  4. satyrius Says:

    @olechevalier, it is misunderstanding. I means that `_qequence` table must be outside of transaction to do not crash sequence order on roll rollbacks. I will corrent this thought in the article.

  5. olechevalier Says:

    Does MyISAM is *not* transaction aware ?