Some problems of innodb self-increment primary key

Background:

Self-growth is a very common data attribute, and in MySQL everyone is willing to let the field of the self-growth attribute be a primary key. In particular InnoDB, because of the characteristics of the InnoDB clustered index, using fields from the growth property when the primary key performs better, here are a few things to note about the self-increment primary key.

Issue One: Table lock

Before MySQL5.1.22, the InnoDB self-increment is obtained by its own self-growth counter, which is accomplished through the table lock mechanism (Auto-inc LOCKING). The lock is not released after each transaction completes, but is released after the SQL statement that is inserted into the self-growth value is completed, and waits for its release for subsequent operations. For example, when the table has a auto_increment field, InnoDB will save a counter in memory to record the value of auto_increment, when inserting a new row of data, a table lock will be used to lock the counter until the end of the insertion. If a large number of concurrent insertions, table locks can cause SQL congestion.

After 5.1.22, InnoDB introduced the parameter Innodb_autoinc_lock_mode in order to solve the problem of self-increment primary key lock table, which is accomplished by the growth mechanism of lightweight mutex. It is specifically designed to adjust the lock strategy in the case of auto_increment, there are currently three options:

Insert Type Description:

Insert-like: Refers to all INSERT statements, such as INSERT, REPLACE, insert ... SELECT, REPLACE ... Simple inserts SELECT,LOAD data: A statement that determines the number of rows inserted before insertion, including insert, REPLACE, not insert ... On DUPLICATE KEY Update this type of statement. Bulk inserts: The statement that the inserted row cannot be determined before insertion. such as insert ... Select,replace ... Select,load DATA. Mixed-mode inserts: A part of it is self-growing, and part of it is certain.

0: by the way of table lock, that is, all types of insert are used auto-inc locking.

1: default value, for simple insert self-growth value generation uses mutexes to accumulate in-memory counters, and for bulk INSERT, use table locks in the same way.

2: for all insert-like self-growth value generation using mutex mechanism to complete, the highest performance, concurrent insertion may lead to self-increment discontinuity, may lead to statement Replication inconsistent, using this mode, you need to use the Row The replication mode.

Before mysql5.1.22, the MySQL insert-like statement locks the table with a auto-inc lock during execution of the entire statement until the end of the entire statement (not the end of the transaction). So when using Insert ... SELECT, Insert...values (...), values (...) , the time-consuming operation, such as LOAD data, locks the entire table and blocks other statements such as Insert-like,update. It is recommended to use the program to divide these statements into multiple statements, one at a time, to reduce the lock table time.

Solve:

Solved by parameter Innodb_autoinc_lock_mode =1/2 and inserted with simple inserts mode.

Problem two: self-increment primary key discontinuous

5.1.22 after default: Innodb_autoinc_lock_mode = 1
By parsing the statement directly, obtaining the quantity to be inserted, and assigning enough auto_increment ID at once, only the entire assigned process is locked.

[email protected]: Test 04:23:28>show variables like ' innodb_autoinc_lock_mode '; +-------------------------- +-------+| variable_name | Value |+--------------------------+-------+| Innodb_autoinc_lock_mode | 1 |+--------------------------+-------+1 row in Set (0.00 sec) [email protected]: Test 04:23:31>create table T Mp_auto_inc (id int auto_increment primary key,talkid int) engine = InnoDB default CharSet GBK; Query OK, 0 rows affected (0.16 sec) [email protected]: Test 04:23:35>insert into Tmp_auto_inc (talkid) Select Talk Id from Talk_dialog limit 10;  Query OK, Rows Affected (0.00 sec) records:10 duplicates:0 warnings:0[email protected]: Test 04:23:39>show CREATE TABLE tmp_auto_inc\g;*************************** 1. Row *************************** table:tmp_auto_inccreate table:create Table ' tmp_auto_inc ' (' id ' int ') not NUL L auto_increment, ' talkid ' int (one) DEFAULT NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=16 DEfault CHARSET=GBK1 Row in Set (0.00 sec) 

Insert 10 records, but the table's auto_increment=16, and then insert a bar, the table's self-increment ID is already discontinuous.

Reason:

Parameter Innodb_autoinc_lock_mode = 1 o'clock, each time a "pre-request" extra ID(handler.cc:compute_next_insert_id) is applied, and when insert execution is complete, These reserved IDs are specifically vacated to write the current maximum ID back to the table (Dict0dict.c:dict_table_autoinc_update_if_greater) after the pre-request.

This reservation strategy is "not enough time to apply a few more", the actual implementation is a step-up application. As for the number of applications, it was decided by the time "several data n" had been inserted. When Auto_increment_offset=1, the number of pre-application is N-1.

So you will find that when you insert only 1 lines, you do not see this phenomenon and do not pre-apply. And when there are n>1 rows, it is required. The number of applications is N-1, so the self-increment after execution is: 1+n+ (N-1). 10 rows in the test: 1+10+9 = 20, and 16 inconsistent? The reason is: when inserting 8 rows, the table's auto_increment is already 16, so when inserting 10 lines, the ID has been reserved in the 8th row, so directly use, self-increment is still 16. So when inserting 8 lines, I applied for 7 IDs, namely: 9,10,11,12,13,14,15. Insert the 8~15 line by the method in the example, the auto_increment of the table is always 16

Verify:

Insert Line 16: Guess id:1+16+ (16-1) = 32, i.e.: auto_increment=32

[Email protected]: Test 04:55:45>create table tmp_auto_inc (id int auto_increment primary key,talkid int) engine = Innod b default CharSet GBK; Query OK, 0 rows affected (0.17 sec) [email protected]: test 04:55:48>insert into Tmp_auto_inc (talkid) Select Talkid fr Om Sns_talk_dialog limit 16; Query OK, Rows Affected (0.00 sec) records:16  duplicates:0  warnings:0[email protected]: Test 04:55:50>sho W CREATE table tmp_auto_inc\g;*************************** 1. Row ***************************       table:tmp_auto_inccreate table:create Table ' tmp_auto_inc ' (  ' id ' int ') not Null auto_increment,  ' talkid ' int (one) default NULL,  PRIMARY KEY (' id ')) engine=innodb auto_increment=32 Default CHARSET=GBK1 row in Set (0.00 sec)

As with speculation, the self-increment ID is up to 32. So when inserting 16 lines, I applied for 17,18,19...,31 more.

Therefore, the cause of the ID discontinuity is that because Innodb_autoinc_lock_mode = 1 o'clock, the ID will be applied more. The advantage is that allocating enough auto_increment IDs at once will only lock the entire assigned process.

5.1.22 before default: Innodb_autoinc_lock_mode = 0

[email protected]: Test 04:25:12>show variables like ' innodb_autoinc_lock_mode '; +-------------------------- +-------+| variable_name | Value |+--------------------------+-------+| Innodb_autoinc_lock_mode | 0 |+--------------------------+-------+1 row in Set (0.00 sec) [email protected]: Test 04:25:15>create table T Mp_auto_inc (id int auto_increment primary key,talkid int) engine = InnoDB default CharSet GBK; Query OK, 0 rows affected (0.17 sec) [email protected]: Test 04:25:17>insert into Tmp_auto_inc (talkid) Select Talk Id from Talk_dialog limit 10;  Query OK, Rows Affected (0.00 sec) records:10 duplicates:0 warnings:0[email protected]: Test 04:25:21>show CREATE TABLE tmp_auto_inc\g;*************************** 1. Row *************************** table:tmp_auto_inccreate table:create Table ' tmp_auto_inc ' (' id ' int ') not NUL L auto_increment, ' talkid ' int (one) DEFAULT NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=11 DEfault CHARSET=GBK1 Row in Set (0.00 sec) 

Insert 10 records, but the table's auto_increment=11, and then insert a bar, the table's self-increment ID is still continuous.

Innodb_autoinc_lock_mode = 2 and Innodb_autoinc_lock_mode = 1 are the same test cases. But the pattern is to assign one, without locking the table, only locks the allocation ID of the process, and the 1 difference is that it does not pre-allocate more than one, this way is the highest concurrency. But there is a problem when Binlog_format is statement-based in replication.

Solve:

Try to make the primary key ID no business sense, or use simple inserts mode to insert.

Conclusion:

When the Innodb_autoinc_lock_mode is 0, the self-increment ID will be continuous, but the table lock will appear, the problem can be fixed to 1, or even 2. will improve performance, but will cause the self-increment ID to be discontinuous under certain conditions.

Summarize:

From the above 2 questions, the self-increment primary key will produce a table lock, which raises the problem, the self-increment primary key has the business meaning, the discontinuous primary key causes the master-slave key to be inconsistent to the problem. For simple inserts insert type, none of the above problems will occur. The above problem occurs with the insertion type of the bulk inserts.

Some problems of innodb self-increment primary key