InnoDB some problems with self-increment keys vs MySQL get the next value from the Increment field

Today found in bulk INSERT, the self-increment primary key is not continuous ....

InnoDB auto_increment Lock Modes

This section describes AUTO_INCREMENT the behavior of lock modes used to generate auto-increment values, and how each lock mode aff ECTS replication. Auto-increment lock modes is configured at startup using the innodb_autoinc_lock_mode configuration

From

Https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

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) 

InnoDB some problems with self-increment keys vs MySQL get the next value from the Increment field