Mysql Global ID Generation method _mysql

Production system with the business growth will always experience a business volume from small to large process, scalability is to consider the high availability of database system is an important indicator; in Tanku/database data volume is too large, updates are constantly soaring, MySQL DBA often to the business system to put forward sharding solution. 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, then how to generate a globally unique ID it, Several common scenarios are described in the DBA's perspective.

1. Using CAs thought

What is CAS protocol

Memcached in version 1.2.4 The new CAs (Check and Set) protocol is similar to a Java concurrent CAs (Compare and Swap) atomic operation to handle concurrent problems with the same item being changed by multiple threads

Basic principles of CAs

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

We can understand from the following examples:

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

• The first step, a takes out the data object X;
• Second step, b remove the data object X;
• The third step, b modify the data object X, and put it into the cache;
• Step Fourth, a modifies the data object X and puts it into the cache.

Conclusion: A data write conflict is generated in step fourth.

Using the CAS protocol is a scenario like the following.

• The first step, a takes out the data object X, and obtains to the CAS-ID1;

• The second step, B takes out the data object X, and obtains to the CAS-ID2;

• The third step, B modifies the data object X, before writing the cache, checking that 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.

• Step Fourth, a modifies the data object Y and checks to see if the Cas-id is consistent with the cas-id of the data in the cached space before writing to the cache. If the result is inconsistent, the write is rejected and the store fails.

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

In fact, this is not strictly a CAS, but using the idea of comparing the exchange of atomic operations.

Build the idea as follows: Each time a global ID is generated, the current global maximum ID is obtained from the sequence table. Then add 1 operations on the obtained global ID, add the value of 1 to the database, add 1 after the value of 203, table name is users, datasheet structure is as follows:

CREATE table ' SEQUENCE ' (
  ' name ' varchar NOT NULL COMMENT ' table name of table ',
  ' gid ' bigint ' not null COMMENT ' Maximum global ID ',
  PRIMARY KEY (' name ')

SQL statement

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

If the effect of the UPDATE statement is 0, the other processes already generate 203 of this value earlier and write to the database. You will need to repeat the previous steps to generate the new.

The code implementation is 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 lock mechanism is generally used in concurrent programming. In fact, the generation of global IDs is also a solution to concurrency problems.

Generate ideas as follows:

When you use the Redis Setnx method and the Memcace Add method, False is returned if the specified key already exists. Use this feature to implement global lock

Each time a global ID is generated, the specified key is detected and, if it does not exist, 1 is added using the Redis incr method or the memcache increment. The return value of these two methods is the value plus 1, and if so, the program goes into the loop wait state. During the cycle, the key is continuously detected and if key does not exist, perform the above operation.

The code is as follows:

Use Redis implementation//$name for the logical table name function Next_id_redis ($name) {$redis = Wk_redis_util::getredis ()//Get Redis object $seq _dao = W K_sequence_dao_sequence::getinstance ()//Get store Global ID data Table object if (!is_object ($redis)) {throw new Exception ("fail to create R
  Edis Object "); } $max _times = 10;
    Maximum execution times to avoid redis when not available into the dead loop while (1) {$i + +;
    Detecting whether the key exists is 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 is greater than 1 seconds, no longer wait.
    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_id ($n
    AME)//Get Global ID if (!is_numeric ($last _id)) from the database {throw new Exception ("fail to obtain 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 datasheet sequence $redis->delete ("sequence_{$name}_flag");//Delete key
  , which is equivalent to releasing the lock $db = null;
return $id;  }

3, Redis and DB combination

Using Redis to manipulate memory directly may be a better performance. But if Redis dies, how do you deal with it? 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 itself supports auto_increment operations, it is natural that we will think of this feature to implement this function. Flicker the MySQL self-growing ID mechanism (auto_increment + replace into + MyISAM) in the solution of global ID generation. A 64-bit ID scenario is specific to this:
First 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 ',
      PRIMARY key (ID),
      UNIQUE key stub
  

When we insert the record, execute the SELECT * from Tickets64, and the query results are as follows:

+-------------------+------+
| ID | Stub |
+-------------------+------+
|    72157623227190423 | A |
+-------------------+------+

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

REPLACE into Tickets64 (stub) VALUES (' a ');

This will enable us to get an ever-growing and not-duplicate ID.
So far, we're just generating IDs on a single database, from a highly available perspective,
The next step is to resolve the single point of failure: Flicker has two database servers enabled to generate IDs.
The ID of the odd even number is generated by distinguishing between the starting value and the step of the auto_increment.

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

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

• Advantages: Full use of the database's self-increasing ID mechanism to provide high reliability, the production of an orderly ID.

• Disadvantage: Occupy two separate MySQL instances, some waste resources, high cost.

The above content is small compiled to share the MySQL global ID generation method, I hope you like.