Example of auto-increment ID for concurrent security using mysql transaction feature _ MySQL

Using the mysql transaction feature to implement concurrent and secure auto-increment ID example bitsCN.com projects often use auto-increment IDs, such as uid. The simplest way is to directly use the AUTO_INCREMENT provided by the database, however, if the number of users is very large, tens of millions, hundreds of millions, and then needs to be stored in sub-tables, this solution is not fixed, so it is best to have a global auto-incremental ID generator, the global auto-increment ID can be obtained from the generator regardless of whether the table is sharded.

There should be many implementation methods, but all solutions need to solve one problem, that is, to ensure that the data is still obtained correctly in high concurrency scenarios, and the IDs obtained each time will not be repeated.

Here I will share two solutions that are implemented using the innodb transaction features of mysql. one is implemented, and the other is not tested, but it should also be accessible.

First, we will introduce the first method. in the database, we will set a separate table to store the ID. The table has two fields: one is the type, and the other is the ID:

Create table auto_id (
Idname varchar (20) not null default '',
Id bigint (20) not null default 0 COMMENT '',
Primary key (idname)
) ENGINE = Innodb default charset = utf8;

Next is a stored procedure:

Delimiter //
Drop procedure if exists get_increment_id;
Create procedure get_increment_id (in idname_in varchar (20), in small_in bigint, out id_out bigint)
Begin
Declare oldid bigint;
Start transaction;
Select id into 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 select id into oldid from maibo_auto_id where idname = idname_in for update adds an exclusive lock to the relevant data. if other processes read this record, wait until the process is committed. This ensures that different processes do not get the same value in the case of concurrency.

If your front-end is implemented using php.

You only need to execute the following two SQL statements to get the result. the small parameter defines the auto-increment starting from the number.

$ SQL = "call get_increment_id ('{$ key}', {$ small}, @ id )";
$ Ret = $ db-> getData ("select @ id ");


Another method is to use mysql auto_increment.

Create a table with only one auto-increment field in it:

Create table test (
'Id' int (11) not null AUTO_INCREMENT COMMENT 'id ',
Primary key (id)
) ENGINE = MyISAM AUTO_INCREMENT = 1 default charset = utf8;

Use the following two SQL statements:

UPDATE test SET id = LAST_INSERT_ID (id + 1 );
SELECT LAST_INSERT_ID ();

The problem can also be solved. LAST_INSERT_ID does not need to be queried and only applies to the current connection. that is to say, the update of other connections does not affect the value of the current connection.

In this way, each ID may have to get a table for maintenance, which is also a disadvantage.

How to deal with it in use depends on your choice. BitsCN.com