"PHP" MySQL Global ID generation scheme

Production system with the growth of business will always go through a business volume from small to large process, scalability is considered a database system high availability of an important indicator; When the single table/database data volume is too large and the amount of updates is rising, MySQL DBAs tend to propose sharding solutions to the business system. Since to sharding, it is inevitable to discuss the sharding key problem, in some business systems, must ensure that sharding key globally unique, such as the storage of goods database, etc., then how to generate a globally unique ID? A few common scenarios are described below from the perspective of a DBA.

1. Using CAS idea

What is the CAS protocol

Memcached in 1.2.4 new CAs (Check and Set) protocol similar to Java concurrent CAS (Compare and Swap) atomic operations, processing the same item by multiple thread change process concurrency problem

The fundamentals of CAs

The basic principle is very simple, word, is the "version number", each stored data object, there is a version number.

We can understand from the following examples:

If you do not use CAs, you have the following scenarios:

• The first step, a takes out the data object X;
• In the second step, B takes out the data object X;
• In the third step, B modifies the data object X and puts it into the cache;
• Fourth, a modifies the data object X and puts it into the cache.

Conclusion: The fourth step will result in a data write conflict.

Using the CAS protocol is the following scenario.

• The first step, a takes out the data object X, and obtains to the CAS-ID1;
• In the second step, B takes out the data object X and gets to the CAS-ID2;
• In the third step, B modifies the data object X, before writing the cache, to check whether the Cas-id is consistent with the cas-id of the data in the cache space. The result is "consistent", which writes the modified X with Cas-id2 to the cache.
• Fourth, a modifies the data object Y, before writing the cache, to check whether the Cas-id is consistent with the cas-id of the data in the cache space. The result is "inconsistent", the write is rejected, and the storage failure is returned.

So the CAS protocol uses the idea of "version number" to solve the conflict problem. (Optimistic lock concept)

In fact, this is not a strict CAs, but the use of a comparison of the idea of exchanging atomic operations.

Generate the following: Each time you generate a global ID, get the current global maximum ID from the sequence table first. Then add 1 on the obtained global ID, add 1 after the value update to the database, such as 1 after the value of 203, the table name is users, the data table structure is as follows:

CREATE table ' SEQUENCE ' (    ' name ' varchar (') ' is not null COMMENT ' tables name ',    ' gid ' bigint () ' NOT null COMMENT ' Maximum global ID ',    PRIMARY KEY (' name ')) Engine=innodb

SQL statements

Update sequence Set gid = 203 where name = ' users ' and GID < 203;

The and GID < 203 of the SQL statement is to ensure that the value of GID in the concurrency environment only increases.

If the UPDATE statement has an impact record number of 0, there are already other processes that generated the value of 203 in advance and written to the database. You need to repeat the above steps to build again.

The code is implemented as follows:

$name Table name function next_id_db ($name) {    //Get Database Global Sequence object    $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);//Get Global ID from database        $id = $last _id +1;        $ret = $seq _dao->set_seq_id ($name, $id);        if ($ret) {            return $id;            break;        }    }    return false;}

2. Use global lock

The locking mechanism is generally used when concurrent programming is performed. In fact, the generation of global IDs also solves concurrency problems.

Generate ideas as follows:

When using the Setnx method of Redis and the Add method of Memcace, returns False if the specified key already exists. Use this feature to implement a global lock

Each time a global ID is generated, it detects whether the specified key exists, and if it does not exist, use the Redis incr method or the increment of the memcache to add 1. The return value of these two methods is the value after 1 plus, and if present, the program enters a circular wait state. The loop process constantly detects if key is still present and performs the above operation if key does not exist.

The code is as follows:

Use the Redis implementation//$NAME as the logical table name function Next_id_redis ($name) {$redis = Wk_redis_util::getredis ();//Get Redis object $seq _dao = Wk _sequence_dao_sequence::getinstance ();//Gets the store global ID data Table object if (!is_object ($redis)) {throw new Exception ("fail to creat    E Redis object "); } $max _times = 10;        Maximum number of executions prevent Redis from being unavailable when entering the Dead loop while (1) {$i + +;        Detects if key exists, equivalent to detecting 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 loop wait time is greater than 1 seconds, no longer waits.        Break    }} $id = $redis->incr ("sequence_{$name}"); If the operation fails, the global ID is obtained from the sequence table and loaded into Redis if (intval ($id) = = = 1 or $id = = = False) {$last _id = $seq _dao->get_seq_i      D ($name);//Get Global ID if (!is_numeric ($last _id)) from database {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);//writes the generated global ID to the data table sequence $redis->delete ("sequence_{$name}_flag");//delete ke    Y, equivalent to release lock $db = null; return $id;}

3. Redis and DB combination

Using Redis to manipulate memory directly may have better performance. But what happens if Redis dies? Combine the above two schemes 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, the Flicker solution

Because MySQL natively supports auto_increment operations, it is natural to think of this feature as a way to achieve this. Flicker uses the MySQL self-growth ID mechanism in resolving the global ID Generation scheme (auto_increment + replace into + MyISAM). A scenario for generating a 64-bit ID is exactly this:
Create a separate database (Eg:ticket), and then create a table:

CREATE TABLE Tickets64 (            ID bigint () unsigned not null auto_increment,            stub char (1) is not null default ' ',            PRI MARY key  (ID),            UNIQUE KEY stub    ) Engine=myisam

When we insert a record, SELECT * from Tickets64 it executes, and the query results are this:

+-------------------+------+| ID                | stub |+-------------------+------+| 72157623227190423 |    A |+-------------------+------+

The following two actions need to be done on our application side to commit in a transactional session:

REPLACE into Tickets64 VALUES (' a '); SELECT last_insert_id ();

So we can get an ever-growing and non-repeating ID.
So far, we're just generating IDs on a single database, from a high-availability perspective,
The next step is to solve the single point of failure problem: Flicker enables two database servers to generate IDs,
The ID of the odd even number is generated by distinguishing the starting value and the step size of the auto_increment.

Ticketserver1:auto-increment-increment = 2auto-increment-offset = 1ticketserver2:auto-increment-increment = 2auto-increment-offset = 2

Finally, the client only needs to take the ID by polling.

• Advantages: Full use of the database's self-increment ID mechanism to provide high reliability, the generated ID is ordered.
• Cons: Occupy two separate MySQL instances, some waste resources, high cost.

Reference:

http://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/

http://segmentfault.com/a/1190000004090537

"PHP" MySQL Global ID generation scheme