Self-Increasing ID example of concurrency security using MYSQL transaction characteristics _mysql

You will often use your own ID in your project, such as UID, the simplest way is to use the auto_increment directly with the database, but if the user is very large, tens of millions of, hundreds of millions of and then need to be stored in the table, this scenario is not possible, so it is best to have a global self-increasing ID of the generator , regardless of whether it is a table or not, you can get a global, self-growing ID from the builder.

There should be a lot of implementation, but all solutions need to solve a problem, is to ensure that in the context of high concurrency, data acquisition is still correct, each get the ID will not repeat.

Here I share two scenarios that take advantage of the InnoDB transaction features of MySQL, one that has been implemented, the other that has not been tested, but which should be able to go through.

First of all, in the database set up a separate table to store IDs, the table has two fields, one is a kind of bar, one is the ID:

Copy Code code as follows:

CREATE TABLE auto_id (
Idname varchar not NULL DEFAULT ',
The ID bigint not NULL DEFAULT 0 COMMENT ',
Primary KEY (Idname)
) Engine=innodb DEFAULT Charset=utf8;

Next is a stored procedure:

Copy Code code as follows:

Delimiter//
drop procedure if exists get_increment_id;
CREATE PROCEDURE get_increment_id (in idname_in varchar (m), in small_in bigint, out id_out bigint)
Begin
declare oldid bigint;
Start transaction;
The select ID into the oldid from maibo_auto_id where idname=idname_in for update;
If oldid is NULL then
Insert into maibo_auto_id (idname,id) value (idname_in, small_in);
Set id_out=small_in;
Else
Update maibo_auto_id set id=id+1 where idname=idname_in;
Set id_out=oldid+1;
End If;
Commit
End
//

The point is that the select ID into the oldid from maibo_auto_id where idname=idname_in for update adds an exclusive lock to the related data, which is when the other process reads the record and then enters the wait. Wait until the process commits, then continue, so that the different processes do not fetch the same value in concurrent situations.

If your front-end is implemented in PHP.

Just execute the following two SQL and you can get that this small parameter is defined by how much you start from the

Copy Code code as follows:

$sql = "Call get_increment_id (' {$key} ', {$small}, @id)";
$ret = $db->getdata ("select @id");

There is another way to take advantage of MySQL's auto_increment.

To create a table, there is only one self-added field on the interior and exterior side:

Copy Code code as follows:

CREATE TABLE Test (
' id ' int (one) not NULL auto_increment COMMENT ' id ',
Primary KEY (ID)
) Engine=myisam auto_increment=1 DEFAULT Charset=utf8;

Through the following two sql:

Copy Code code as follows:

UPDATE Test SET id = last_insert_id (id + 1);
SELECT last_insert_id ();

can also solve the problem, last_insert_id is not to check the table, and only for the current connection, that is, other connection updates will not affect the value of the current connection.

This may have to be a table for each ID to maintain, which is also a disadvantage.

The specific use of how to deal with, it depends on their choice.