AliSQL Valentine’s Release: Open source SequenceEngine

Introduction

Monotonically increasing unique values are a common requirement in persistent database systems, whether it is a business primary key in a single node, a globally unique value in a distributed system, or idempotent control in a multi-system. Different database systems have different implementation methods, for example, AUTO_INCREMENT provided by MySQL, Oracle, and SEQUENCE provided by SQL Server.

In MySQL database, if the business system wants to encapsulate a unique value, such as adding date, user and other information, the AUTO_INCREMENT method will bring great inconvenience. In actual system design, there are also different compromise methods, such as:

• The sequence value is generated by Application or Proxy, but the disadvantage is obvious. The state is carried to the Application, which increases the complexity of capacity expansion and reduction.
• Sequence values are generated by the database from simulated tables, but middleware is required to encapsulate and simplify the logic of obtaining unique values.

AliSQL has independently realized SEQUENCE ENGINE. Through the design method of the ENGINE, it is compatible with the use method of other databases as much as possible to simplify the complexity of obtaining SEQUENCE values.

Github open Source: github.com/alibaba/Ali…

Description

The open source SEQUENCE of AliSQL realizes the design interface of MySQL storage engine, but the underlying data still uses the existing storage engine, such as InnoDB or MyISAM, to store persistent data, so as to ensure the compatibility of existing peripheral tools, such as XtraBackup, as much as possible. So the SEQUENCE ENGINE is just a logic ENGINE.

Sequence objects are accessed through the Sequence Handler interface. The logic engine of this layer mainly implements NEXTVAL rolling and CACHE management, and finally passes through the underlying base table data engine to realize the final data access.

Let’s see the use of AliSQL SEQUENCE through syntax.

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] ;Copy the code

SEQUENCE OPTIONS:

• START Sequence START value
• The minimum value of the MINVALUE Sequence. If this round ends and is cycle, then the next round will start from MINVALUE
• MAXVALUE
  
  The maximum value of the Sequence, if it reaches the maximum value and is nocycle, will get the following error:
  
  ERROR HY000: Sequence 'db.seq' has been run out.
  
  
• INCREMENT BY Sequence Indicates the step size of the INCREMENT BY Sequence
• CACHE/NOCACHE Specifies the size of the CACHE. You can set the CACHE size to a larger size for performance purposes. However, if the instance is restarted, the value in the CACHE will be lost
• CYCLE/NOCYCLE indicates whether to restart the MINVALUE sequence after the sequence is used up

Such as:

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

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
Copy the code

Since the SEQUENCE is stored through a real engine table, SHOW COMMAND sees that it is still an Engine Table.

3. QUERY STATEMENT Syntax

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

Two types of access are supported, FROM and FOR:

• FROM clause: compatible with normal SELECT query statements, returns the base table data, does not iterate NEXTVAL.
• FOR clause: SQL Server-compatible method that returns the value of NEXTVAL after iteration.

mysql> select * from s; +---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+ | currval | nextval |  minvalue | maxvalue | start | increment | cache | cycle | round | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- + | | 30004 | | 0 1 9223372036854775807 | | 1 | 10000 | | | 0 0 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00) sec) mysql> select * for s; +---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+ | currval | nextval |  minvalue | maxvalue | start | increment | cache | cycle | round | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- + | | 20014 | | 0 1 9223372036854775807 | | 1 | 10000 | | | 0 0 +---------+---------+----------+---------------------+-------+-----------+-------+-------+-------+Copy the code

4. The compatibility

CREATE SEQUENCE by creating a SEQUENCE table and INSERT a row of initial records. 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;Copy the code

However, the native CREATE SEQUENCE method is strongly recommended.

5. Grammatical limitations

• Sequence does not support subQuery and join
• The FOR clause clause supports only sequence tables, not common engine tables
• You can use SHOW CREATE TABLE or SHOW CREATE SEQUENCE to access SEQUENCE structures, but you cannot use SHOW CREATE SEQUENCE to access regular tables
• If the CREATE TABLE is not supported, the SEQUENCE engine is specified, and the SEQUENCE TABLE can only be created using the syntax of the CREATE SEQUENCE

High level architecture

1. Sequence initialization

Sequence (CURRVAL, NEXTVAL, MINVALUE, MAXVALUE, START, INCREMENT, CACHE, CYCLE, ROUND); The CREATE SEQUENCE clause is for initializing a data object, so the SEQUENCE object is essentially a storage engine table with a record. The BINLOG for SLAVE replication uses the CREATE SEQUENCE clause. The QUERY EVENT generated by the statement.

2. Sequence interface

The SEQUENCE handler implements part of the Handler interface and defines two important attributes, SEQUENCE_SHARE and BASE_TABLE_FILE. SEQUENCE_SHARE stores the attributes of the shared sequence object and the CACHE value. The NEXTVAL value is obtained from the CACHE first. The base table is queried only after the CACHE is used up. BASE_TABLE_FILE is the BASE_TABLE_FILE handler for the base table. The BASE_TABLE_FILE handler is used to access and modify persistent data.

3. Sequence cache

The CACHE value of the Sequence object is stored in SEQUENCE_SHARE and is protected by SEQUENCE_SHARE::MUTEX. All CACHE accesses are serial. For example, if the cache size is 20, only one cache_end value is stored in SEQUENCE_SHARE. When the NEXTVAL accessed reaches cache_end, the next batch is fetched from the base table and placed in the cache. NEXTVAL iterates according to the step set BY INCREMENT BY.

4. Sequence update

When the cache is used up, the next batch is fetched from the base table, which updates the records in the base table. The query is converted into an update statement. The main update steps are as follows:

1. Upgrade the MDL_SHARE_READ METADATA LOCK of the SEQUENCE to the MDL_SHARE_WRITE level
2. The GLOBAL MDL_INTENSIVE_EXCLUSIVE METADATA LOCK is used
3. Open the AUTONOMOUS TRANSACTION
4. Update records and generate BINLOG events
5. Have a COMMIT METADATA LOCK
6. XA commits an AUTONOMOUS TRANSACTION and releases the MDL lock

5. Autonomous transaction

Because NextVal does not support ROLLBACK reuse, you must restart an autonomous transaction to get out of the transaction context, 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 register AUTONOMOUS transactions respectively
4. When the update is complete, XA commits an AUTONOMOUS TRANSACTION
5. Restores the current transaction context

6. Sequence read only

Because the SELECT statement for a SEQUENCE is converted to an UPDATE statement, So SELECT NEXTVAL FOR S statement must use MDL_SHARE_WRITE and GLOBAL MDl_EXCLUSIVE METADATA LOCK to intensive_exclusive METADATA LOCK. Blocks access to the Sequence object.

7. Skip cache

There are two types of CACHE:

• SEQUENCE CACHE can be usedSELECT NEXTVAL FORM Sequence_nameTo skip.
• The other option is QUERY CACHE. All SEQUENCE sequences are set to not support QUERY CACHE, so that the NEXTVAL does not iterate due to QUERY CACHE.

8. Sequence backup

Since sequences are stored in real engine tables, physical backups such as XtraBackup can be used directly, while logical backups such as MYSQLDUMP are backed up as a combination of CREATE SEQUENCE statements and INSERT statements.

Next Release

Some functions are open source this time, and some functions of the SEQUENCE will be open source in the next release:

• Support access to CURRVAL, which represents the value of the last NEXTVAL access of the 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);
Copy the code

Usage Scenario

1. More business-meaningful primary key design.

FOR example: [8-digit date + 4-digit USER ID + sequence_number], the design format of the stream service bill number can be realized by SELECT NEXTVAL FOR Sequence and application encapsulation. Compared with meaningless ID numbers, this format brings several advantages:

• Keep the order of time synchronization, which is conducive to data archiving. For example, you can directly use this ID to perform RANGE partition by day/month/year, and seamlessly use the partition feature of MySQL
• Adding the USER ID information can be used as a logical bit of database and table to improve the scalability of data nodes
• Maintain numeric order to ensure stable insertion performance of InnoDB’s clustered index tables

Current design approaches in the industry:

• In Booking, the AUTO_INCREMENT method is used to insert a number with no business meaning, and then 1230879 method is used to obtain the ID value. Finally, the ID value is used in the business logic. The disadvantage is that it must be inserted first, and there is no way to change the id that has no business meaning.
• Twitter uses a different format, [41 bits timestamp + 10 bits configured machine ID + 12 bits sequence number] Machine ID or MAC address managed by Zookeeper.
• The UUID method, which generates a random unique value, severely affects insert performance, increases index size, and reduces hit ratio, without any advantage.

2. Unique value design of distributed nodes

Distributed SEQUENCE generation:

• MySQL AUTO_INCREMENT can be used as a sequence for each node, for example, INCREMENT BY step for each node. Set the effect of auto_INCREment_increment and auto_increment_offset, but compare the global configuration of Auto INCREment_increment and save the method in my.cnf, SEQUENCE has the advantage of persisting these configurations as attributes of SEQUENCE objects. However, it is not recommended to use this method to design unique values, which can leave a lot of holes in operation and maintenance.
• Using a Twitter-like approach, create a sequence on each node and add node information to the sequence number to generate unique values.

Centralized SEQUENCE generation:

• For ID requirements in distributed nodes, a separate centralized sequence service is used to generate ids, but sequence services still need to be designed to be multi-node to ensure continuous availability, 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 |
+-------------------+------+
Copy the code

Use the following statement to generate an ID value:

REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT 1230879;
Copy the code

Since PHOTOS, COMMENTS, FAVORITES, and TAGS all require ids, different ticket tables will be created to do this. To maintain continuous availability, we use:

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

TicketServer2:
auto-increment-increment = 2
auto-increment-offset = 2
Copy the code

To ensure high availability. If you use sequence objects, you can greatly simplify the ID acquisition logic and be more secure.