MySQL transaction attribute implementation concurrency Security self-Increasing ID example

  Projects often use the ID, such as UID, below for the use of MySQL transaction characteristics to achieve concurrent security of the ID, interested friends can refer to the following

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, set up a separate table in the database to store IDs, the table has two fields, one is the kind, one is id:  code as follows: CREATE TABLE auto_id (  idname varchar ( Not NULL default ',  ID bigint (a) NOT null default 0 COMMENT ',  primary KEY (Idname)  ) Engine=innodb DEFAULT charset=utf8;    Next is a stored procedure:  code as follows: Delimiter//  drop procedure if exists get_increment_id;& nbsp CREATE PROCEDURE get_increment_id (in idname_in varchar (m), in small_in bigint, out id_out bigint)   begin  DECLA Re oldid bigint;  start transaction;  select id into oldid from maibo_auto_id where idname=idname_in for update;   If oldid is NULL then  inserts into maibo_auto_id (idname,id) value (idname_in, small_in); &nBsp 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; //    Focus is this sentence, select ID into the oldid from maibo_auto_id where idname=i dname_in for update, which adds an exclusive lock to the relevant data, will allow the other process to read the record and wait until the process is committed before continuing, thus ensuring 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, you can get to, this small parameter is defined from how much the start of the   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.     Create a table with only one self-add field:  code as follows: CREATE TABLE test (  ' id ' int (one) not NULL auto_increment COMMENT ' id ', & nbsp Primary key (ID)  ) engine=myisam auto_increment=1 DEFAULT charset=utf8;    passed the following two sql:  code as follows: UPDATE t EST 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, but also This means that updates to the other connections do not affect the value of the current connection. &nbsP   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.