Skip to content

AliSQL Sequence Doc_E

Jump to bottom
AliSQL edited this page Feb 14, 2017 · 1 revision

AliSQL open Sequence Engine

Introduction

The unique value of monotonically increasing is a common requirement in a persistent database system, whether it is a business primary key in a single node or a globally unique value for a distributed system, or a idempotent control in a multi-system.
Currently Database systems have different approaches to implement this, such as MySQL provides AUTO_INCREMENT, Oracle, SQL Server provides SEQUENCE and so on.

In the MySQL database, if the business system wants to encapsulate unique values, such as adding dates, users and other information, AUTO_INCREMENT method will bring a lot of inconvenience.

In the actual system design, there are different compromise methods, such as:

  • Sequence values are generated by the Application or Proxy, but the drawbacks are obvious, the state brought to the application side, an increase of expansion and contraction complexity.
  • Sequence values are generated by the database through simulated tables, but required middleware to encapsulate and simplify the logic to obtain unique values.

AliSQL autonomously implemented SEQUENCE, through the engine design methods, as much as possible compatible with the use of other databases.

Github open source address: https://github.com/alibaba/AliSQL

Description

The SEQUENCE engine implemented the handler interface, but the underlying data is still using the existing storage engine, such as InnoDB or MyISAM to save persistent data in order to ensure that as much as possible the existing external tools such as XtraBackup and other are compatible, So SEQUENCE ENGINE is just a logic engine.

Access to the SEQUENCE through the SEQUENCE HANDLER, this logic engine layer mainly achieve NEXTVAL rolling, cache management, the final data access is still through the base table data engine.

Syntax

1. CREATE SEQUENCE Syntax

CREATE SEQUENCE [IF NOT EXISTS] schema.sequence_name
   [START WITH <constant>]
   [MINVALUE <constant>]
   [MAXVALUE <constant>]
   [INCREMENT BY <constant>]
   [CACHE <constant> | NOCACHE]
   [CYCLE | NOCYCLE]
  ;

SEQUENCE OPTIONS:

  • START

    The start value of the sequence

  • MINVALUE

    The minimum value of the sequence, if the round ends and is cycle,
    the next round will start at MINVALUE

  • MAXVALUE

Sequence maximum, if the maximum and nocycle, then you will get the following error:
ERROR HY000: Sequence 'db.seq' has been run out.

  • INCREMENT BY

    The step size of the sequence

  • CACHE/NOCACHE

    Cache size, for the performance considerations, you can set the cache size relatively large,
    but if you encounter MySQL instance restart, the cached values will be lost. 　

  • CYCLE/NOCYCLE

    Indicates whether the sequence will be allowed to resume from MINVALUE if it has been run out.

For example:

 create sequence s
      start with 1
      minvalue 1
      maxvalue 9999999
      increment by 1
      cache 20
      cycle;

2. SHOW SEQUENCE Syntax

SHOW CREATE [TABLE|SEQUENCE] schema.sequence_name;

CREATE SEQUENCE schema.sequence_name (
  `currval` bigint(21) NOT NULL COMMENT 'current value',
  `nextval` bigint(21) NOT NULL COMMENT 'next value',
  `minvalue` bigint(21) NOT NULL COMMENT 'min value',
  `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
  `start` bigint(21) NOT NULL COMMENT 'start value',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache` bigint(21) NOT NULL COMMENT 'cache size',
  `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
  `round` bigint(21) NOT NULL COMMENT 'already how many round'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Because SEQUENCE is stored through the real engine table, so SHOW COMMAND will see engine table.

3. QUERY STATEMENT Syntax

  SELECT [NEXTVAL | CURRVAL | *] FROM schema.sequence_name;
  SELECT [NEXTVAL | CURRVAL | *] FOR schema.sequence_name;

Here support two access methods, FROM and FOR:

  • FROM clause: Compatible with the normal SELECT query, return the base table record, do not iterate NEXTVAL.
  • FOR clause： SQL Server-compatible method returns the value of the NEXTVAL after iterating.
mysql> select * from s;
+---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+
| currval | nextval | minvalue | maxvalue            | start | increment | cache | cycle | round |
+---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+
|       0 |   30004 |        1 | 9223372036854775807 |     1 |         1 | 10000 |     0 |     0 |
+---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+
1 row in set (0.00 sec)

mysql> select * for s;
+---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+
| currval | nextval | minvalue | maxvalue            | start | increment | cache | cycle | round |
+---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+
|       0 |   20014 |        1 | 9223372036854775807 |     1 |         1 | 10000 |     0 |     0 |
+---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+

4. Compatibility

It is compatible with mysqldump backup, so support another CREATE SEQUENCE method: through the creation of sequence table and insert a row of the initial record, for example:

  CREATE SEQUENCE schema.sequence_name (
  `currval` bigint(21) NOT NULL COMMENT 'current value',
  `nextval` bigint(21) NOT NULL COMMENT 'next value',
  `minvalue` bigint(21) NOT NULL COMMENT 'min value',
  `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
  `start` bigint(21) NOT NULL COMMENT 'start value',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache` bigint(21) NOT NULL COMMENT 'cache size',
  `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
  `round` bigint(21) NOT NULL COMMENT 'already how many round'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO schema.sequence_name VALUES(0,0,1,9223372036854775807,1,1,10000,1,0);
COMMIT;

However, it is strongly recommended to use the native CREATE SEQUENCE method.

5. Syntax Restrict

  • Sequence does not support subquery and join
  • FOR clause only supports the SEQUENCE table
  • You can use SHOW CREATE TABLE or SHOW CREATE SEQUENCE to access the sequence structure, but you can not use the SHOW CREATE SEQUENCE to access the normal table
  • Does not support CREATE TABLE with specified SEQUENCE engine

High level architecture

1. Sequence Initialization

The CREATION of the SEQUENCE will be converted into a fixed [CURRVAL, NEXTVAL, MINVALUE, MAXVALUE, START, INCREMENT, CACHE, CYCLE, ROUND] the nine fields of the engine table, and initialize a record according to the definition. So essentially the sequence object is a storage engine table with a record. the replication BINLOG will uses the CREATE SEQUENCE ... QUERY EVENT directly.

2. Sequence Interface

SEQUENCE engine has implemented a part of the handler interface, and defined two important attributes, SEQUENCE_SHARE and BASE_TABLE_FILE, the SEQUENCE_SHARE preserved shared sequence attributes and cached value.
The NEXTVAL value first obtained from the cache, only if the cache is run out, The base table is queried.
BASE_TABLE_FILE is the handler object of the base table, the persistence of data access and modification, are accessed through the BASE_TABLE_FILE handler actually.

3. Sequence Cache

The cached value is stored in the SEQUENCE_SHARE object, protected by SEQUENCE_SHARE::MUTEX, so all accesses to the cache are serial.
For example, if cache size is 20, then the SEQUENCE_SHARE just keep a cache_end value, when the NEXTVAL reached cache_end, it will retrieve the next batch into the cache from base table. The NEXTVAL is iterated according to the INCREMENT BY step size.

4. Sequence Update

When the cache runs out, it will update the base table record to get next batch value, so the query will be converted into UPDATE statement.

The main steps for updating are as follows:

  1. Upgrade the SEQUENCE MDL_SHARE_READ METADATA LOCK to the MDL_SHARE_WRITE level
  2. Hold the GLOBAL MDL_INTENSIVE_EXCLUSIVE METADATA LOCK
  3. Open the autonomous transaction
  4. Update the record and generate the BINLOG ROW EVENT
  5. Hold the COMMIT METADATA LOCK
  6. XA submits the autonomous transaction and releases the MDL

5. Autonomous Transaction

Since NEXTVAL does not support ROLLBACK and reuse, it is necessary to restart an autonomous transaction to break away from the transaction context,

The steps are as follows:

  1. Back up the transaction context of the current base table engine
  2. Back up the context of the current BINLOG engine
  3. SEQUENCE and BINLOG were registered autonomous transaction
  4. When the update is complete, XA submits an autonomous transaction
  5. Restore the current transaction context

6. Sequence Readonly

Because the SELECT statement on SEQUENCE may be converted to an UPDATE statement, the SELECT NEXTVAL FOR s statement must hold the MDL_SHARE_WRITE and GLOBAL MDL_INTENSIVE_EXCLUSIVE METADATA LOCKS so that accesses to the SEQUENCE can be blocked when read only.

7. Skip Cache

Here refers to two kinds of cache:
One is the SEQUENCE cache, you can use SELECT NEXTVAL FORM sequence to skip. Another is the QUERY CACHE, all of the SEQUENCE are not set up to support QUERY CACHE, so as to avoid to cache NEXTVAL result.

8. Sequence Backup

Because SEQUENCE is saved through a real engine table, a physical backup like XtraBackup can be used directly, and a logical backup such as MYSQLDUMP will be backed up as a combination of a CREATE SEQUENCE statement and an INSERT statement.

Next Release

The next release will continue to open SEQUENCE other features:

  • Support for CURRVAL access, CURRVAL means that the last NEXTVAL within current session.
  • Compatible with more database access methods, such as:
Oracle Syntax:
  SELECT sequence_name.nextval FROM DUAL;  

PostgreSQL Syntax:
  nextval(regclass);
  currval(regclass);
  setval(regclass, bigint);

Usage Scenario

1. Primary design included more business meaning

For example: [DATE(8) + USER_ID(4) + SEQUENCE_NUMBER(11)] This design of business trade number can be achieved through the SELECT NEXTVAL FOR Sequence and the application encapsulation，compared meaningless number, this format will bring several advantages:

  • To maintain the order with time synchronization. For the data archiving, ID can be partitioned by the DATA/MONTH/YEAR
  • Supplement the USER information, it can be used as a natural sharding dimension to enhance data node scalability
  • To maintain the order of the number, to ensure InnoDB clustered index table insertion performance and stability

The current design approach:

  • Booking uses the AUTO_INCREMENT method, first INSERT one record, and then use last_insert_id () method to get the ID value. The disadvantage is that you must first insert, and has no way to modify ID again.
  • Twitter used another format, [41 bits timestamp +10 bits configured machine ID +12 bits sequence number], sequence number generation mechanism is not disclosed. machine ID design use Zookeeper to manage the machine ID or the MAC address.
  • UUID, this method generates a random unique value, a serious impact on the performance of the insert, and increased the size of the index, reducing the hit rate.

2. Unique value design for distributed system

Distributed sequence generation:

  • For each individual node, sequence that defined different step size can implement the same functionality with MySQL AUTO_CREMENT which set the auto_increment_increment and auto_increment_offset, but compared to global configuration, and stored in my.cnf file, the sequence object can be configured as attributes persisted down.
  • Using a Twitter-like method, create a sequence on each node, and then add the node information on the sequence number to generate a unique value.

Centralized sequence generation:

  • ID for distributed nodes are generated using a separate centralized sequence service, but sequence services need to be designed to be multi-node if they are to be available continuously.

Such as Flickr's Ticket Servers design, Create the Ticket table on the sequence service node:

CREATE TABLE `Tickets64` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `stub` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM

+-------------------+------+
| id                | stub |
+-------------------+------+
| 72157623227190423 |    a |
+-------------------+------+

Use the following statement to generate the ID value:

REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

Because photos, comments, favorites, tags all need ID, it will build a different ticket table to complete. In order to maintain continuous availability, using:

TicketServer1:
auto-increment-increment = 2
auto-increment-offset = 1

TicketServer2:
auto-increment-increment = 2
auto-increment-offset = 2

to ensure high availability.
If you use the sequence object, you can greatly simplify the ID access logic, and more secure.

Clone this wiki locally