Mysql global ID generation method,

Mysql global ID generation method,

As the business grows, the production system will always go through a process in which the business volume grows from small to large. scalability is an important indicator to consider the high availability of the database system. When the data volume of a single table/database is too large and the volume of updates continues to soar, mySQL DBAs often propose sharding solutions for business systems. Since sharding is required, it is inevitable to discuss the sharding key issue. In some business systems, the sharding key must be globally unique, such as the database for storing commodities, then, how to generate a globally unique ID? The following describes several common schemes from the DBA perspective.

1. using CAS

What is CAS?

The CAS (Check and Set) protocol added in Memcached version 1.2.4 is similar to the concurrent CAS (Compare and Swap) Atomic operations in Java to handle the concurrency problem of the change process of the same item by multiple threads.

Basic Principles of CAS

The basic principle is very simple. Simply put, it is the "version number". Each stored data object has a version number.

We can understand from the following example:

If CAS is not used, there are the following scenarios:

• Step 1: A extracts data object X;
• Step 2: B retrieves the Data Object X;
• Step 3: B Modify the Data Object X and put it into the cache;
• Step 4: A modify the Data Object X and put it into the cache.

Conclusion: Data Write conflict occurs in step 4.

The CAS protocol is used in the following scenarios.

• Step 1, A retrieves the Data Object X and gets the CAS-ID1;

• Step 2, B retrieves the Data Object X and obtains the CAS-ID2;

• Step 3, B modify data object X, and check that the CAS-ID is consistent with the CAS-ID for the data in the cache space before writing to the cache. If the result is consistent, the modified X with a CAS-ID2 is written to the cache.

• Step 4, A modifies the Data Object Y, checking that the CAS-ID is consistent with the CAS-ID for the data in the cache space before writing to the cache. If the result is "inconsistent", the write is rejected and the storage fails to be returned.

In this way, the CAS protocol uses the version number to solve the conflict. (Optimistic lock concept)

In fact, here is not a strict CAS, but the idea of relatively exchanging atomic operations.

The idea is as follows: each time a global id is generated, the current global maximum id is obtained from the sequence table. Then add 1 to the obtained global id, and the value after adding 1 is updated to the database. For example, the value after adding 1 is 203, and the table name is users. The table structure is as follows:

Create table 'sequence '('name' varchar (30) not null comment' TABLE shard name', 'gid' bigint (20) not null comment 'maximum global id ', primary key ('name') ENGINE = innodb

SQL statement

update sequence set gid = 203 where name = 'users' and gid < 203; 

The and gid of the SQL statement <203 is used to ensure that the gid value is only increased and not decreased in the concurrent environment.

If the number of records affected by the update statement is 0, another process has generated the value 203 in advance and written it to the database. Repeat the preceding steps to generate a new one.

The code is implemented as follows:

// $ Name table name function next_id_db ($ name) {// obtain the global sequence object of the database $ seq_dao = Wk_Sequence_Dao_Sequence: getInstance (); $ threshold = 100; // maximum number of attempts for ($ I = 0; $ I <$ threshold; $ I ++) {$ last_id = $ seq_dao-> get_seq_id ($ name ); // obtain the global id from the database $ id = $ last_id + 1; $ ret = $ seq_dao-> set_seq_id ($ name, $ id); if ($ ret) {return $ id; break;} return false ;}

2. Use global locks

The lock mechanism is generally used for concurrent programming. In fact, the generation of global IDS also solves the concurrency problem.

The generation idea is as follows:

When using the setnx method of redis and the add method of memcace, if the specified key already exists, false is returned. Use this feature to implement global locks

Before generating a global id, check whether the specified key exists. If not, add 1 to the redis incr method or memcache increment. The return values of these two methods are values after 1. If yes, the program enters the cyclic waiting state. Check whether the key still exists during the loop. If the key does not exist, execute the above operation.

The Code is as follows:

// Use redis to implement // $ name function next_id_redis ($ name) {$ redis = Wk_Redis_Util: getRedis (); // obtain the redis object $ seq_dao = Wk_Sequence_Dao_Sequence:: getInstance (); // obtain the object if (! Is_object ($ redis) {throw new Exception ("fail to create redis object");} $ max_times = 10; // The maximum number of executions to avoid entering an endless loop when redis is unavailable while (1) {$ I ++; // check whether the key exists, it is equivalent to checking whether the lock exists $ ret = $ redis-> setnx ("sequence _ {$ name} _ flag", time (); if ($ ret) {break ;} if ($ I >$ max_times) {break;} $ time = $ redis-> get ("sequence _ {$ name} _ flag"); if (is_numeric ($ time) & time ()-$ time> 1) {// If the cyclic wait time is greater than 1 second, no longer wait. Break ;}$ id = $ redis-> incr ("sequence _ {$ name}"); // if the operation fails, obtain the global id from the sequence table and load it to redis if (intval ($ id) === 1 or $ id == false) {$ last_id = $ seq_dao-> get_seq_id ($ name); // obtain the global id from the database if (! Is_numeric ($ last_id) {throw new Exception ("fail to get id from db");} $ ret = $ redis-> set ("sequence _ {$ name }", $ last_id); if ($ ret = false) {throw new Exception ("fail to set redis key [sequence _ {$ name}]");} $ id = $ redis-> incr ("sequence _ {$ name}"); if (! Is_numeric ($ id) {throw new Exception ("fail to incr redis key [sequence _ {$ name}]") ;}}$ seq_dao-> set_seq_id ($ name, $ id); // write the generated global id to the data table sequence $ redis-> delete ("sequence _ {$ name} _ flag"); // delete the key, equivalent to releasing the lock $ db = null; return $ id ;}

3. Integration of redis and db

Using redis to directly operate the memory may improve performance. But what if redis dies? The above two solutions are combined to provide better stability.
The Code is as follows:

function next_id($name){  try{    return $this->next_id_redis($name);  }  catch(Exception $e){    return $this->next_id_db($name);  }} 

4. Solutions for Flicker

Because mysql itself supports the auto_increment operation, we naturally think of using this feature to implement this function. In the global ID generation solution, Flicker uses the MySQL auto-increment ID mechanism (auto_increment + replace into + MyISAM ). A 64-bit ID generation scheme is as follows:
First create a separate database (eg: ticket), and then create a table:

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 

After we insert a record, execute SELECT * from Tickets64. The query result is as follows:

+ ------------------- + ------ +
| Id | stub |
+ ------------------- + ------ +
| 72157623227190423 | a |
+ ------------------- + ------ +

The following two operations must be performed on our application end and committed in one transaction session:

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

In this way, we can get a growing and non-repeated ID.
So far, we only generate IDS on a single database. From the perspective of high availability,
Next, we need to solve the single point of failure (spof) Problem: Flicker enables two database servers to generate IDS,
The start value and step size of auto_increment are distinguished to generate an odd and even ID.

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

Finally, the client only needs to get the ID through polling.

• Advantage: fully utilizes the auto-incremental ID mechanism of the database to provide high reliability and ID order generation.

• Disadvantage: two independent MySQL instances are occupied, which is a waste of resources and the cost is high.

The above content is the Mysql global ID generation method shared by Alibaba Cloud.

Articles you may be interested in:

• How to solve the "unable to load mysql extension" problem that may be encountered during PHP installation
• Ext/mysql extension of PHP source code
• Php cannot load mysql extension
• How to connect to the database after mysqli extension is enabled in php
• Solution to the lack of mysqli extension in phpmyadmin
• Generate a global ID based on the MySql extension function