An example of self increasing ID using mysql transaction features for concurrency security

Projects are often used on the id, uid, for example, the simplest method is to use with the database directly provide AUTO_INCREMENT, but if users are very large, tens of millions, hundreds of millions of then need table storage, this scheme to make uncertain, so it's best to have 1 global since the increase ID generator, regardless of whether the table, can be obtained from the generator to the global ID from increasing.

There should be many implementations, but all of them need to solve one problem, which is to ensure that the data is retrieved correctly and that ID is not repeated each time in a highly concurrent situation.

Here I share two scenarios that take advantage of mysql's transactional features, one that is implemented and one that is untried but should work.

First, set a separate table in the database to store ID. The table has two fields, one is the type, and the other is 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; 
// 

select id into oldid maibo_auto_id where idname=idname_in for update =idname_in for update = idname. If another process reads the record, it will enter a wait, wait for the process commit, and then continue, thus ensuring that in the case of concurrency, different processes will not get the same value.

If your front end is implemented using php.

This is obtained by performing the following two small arguments, which define how much to increment from

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

Another option is to use auto_increment of mysql.

First, create 1 table, in which there is only 1 self-increment field:

 
create table test( 
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', 
primary key (id) 
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

By the following two sql:

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

LAST_INSERT_ID does not need to look up tables and is only for the current connection, meaning that updates to other connections do not affect the value of the current connection.

This may require one table per ID to maintain, which is also a drawback.

How to deal with in specific use, saw oneself choice.