MySQLauto_increment gap question _ MySQL

We often use the auto_increment field in MySQL, especially the innodb engine. I also often use it. I only know that mysql can ensure the atomicity of this field during multi-process operations. the specific principle is not that we often use the auto_increment field in MySQL, especially the innodb engine. I also often use it. I only know that mysql can ensure the atomicity of this field during multi-process operations. the specific principle is not very good, and I went through the MySQL manual and related materials, I learned about it. This article only explores the problem of innodb engine auto_increment in mysql5.5. the myisam engine is not tested. I will add it later if I have time.

1. traditional auto_increment principle

Traditional auto_increment implementation mechanism: the auto_increment field in the mysql innodb engine table is assigned by maintaining an auto-increment counter in the memory, note that the auto-increment field must be the index and the first column of the index, not necessarily the primary key. For example, if I create a table t in my database test, the statement is as follows:

CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB;

Field a is of the auto_increment type. when the mysql server is started and data is inserted to table t for the first time, the InnoDB engine executes the statement equivalent to the following:

SELECT MAX(a) FROM t FOR UPDATE;

Innodb obtains the maximum value of field a in the current table and increases by 1 (increase by 1 by default. to increase the number of other values, you can set the auto_increment_increment configuration) then assign a value to the column and the counter corresponding to the table in the memory.

If the table t is empty, InnoDB sets the value to 1. of course, the default value night can be modified using the auto_increment_offset configuration item.

After the auto-increment counter is initialized, if the inserted data does not specify the value of the auto_increment column, Innodb will directly add the value of the auto-increment counter and assign the added value to the new column. If the value of the auto_increment column is specified for data insertion and the value is greater than the current counter value of the table, the counter value of the table is set to this value.

When inserting data, if the value of the specified auto_increment column is NULL or 0, mysql will allocate a value to the column from the counter, just like the value of this column that you did not specify. if the value of the specified auto_increment column is a negative number or exceeds the maximum value that the column can store, this behavior is not defined in mysql and may cause problems. according to my tests, there is a warning when inserting negative values, but the stored data is still correct. if it exceeds the maximum value of the bigint type of table t defined above, a warning is also given, and the inserted value is 18446744073709551615 of the maximum value that can be stored by the bigint type.

In the traditional auto_increment settings, each time you access the auto-increment counter, INNODB adds an AUTO-INC lock until the statement ends (Note that the lock is held only until the end of the statement, not the end of the transaction.). The AUTO-INC lock is a special table-level lock used to improve the concurrent insertion performance of columns containing auto_increment. therefore, two transactions cannot obtain the AUTO-INC lock on the same table at the same time. if the AUTO-INC lock is held for too long, the database performance may be affected (such as insert into t1... SELECT... FROM t2 ).

2. improved auto_increment

Because the traditional auto_increment mechanism requires a special table-level lock such as AUTO-INC, the performance is still too poor, so at mysql5.1, a new configuration item innodb_autoinc_lock_mode was added to set the auto_increment mode. the value can be set to 0, 1, 2. 0 is the traditional auto_increment mechanism described in section 1, while 1 and 2 are the newly added mode. the default value is 1, which can be modified in the mysql configuration file. here we mainly look at the differences between the two new methods. before describing the differences, we need to specify several insert types:

1）simple inserts

Simple inserts refers to the statement that can determine the number of inserted rows in advance, such as INSERT/replace into and other statements that INSERT single or multiple rows. The statement does not include nested subqueries. In addition, insert... On duplicate key update statements are also excluded.

2）bulk inserts

Bulk inserts refers to statements that cannot determine the number of inserted rows in advance, such as INSERT/replace... SELECT, load data, etc.

3）mixed-mode inserts

In simple inserts, some rows specify the value of the auto_increment column, for example:
Insert into t1 (c1, c2) VALUES (1, 'A'), (NULL, 'B'), (5, 'C'), (NULL, 'd ');
Another mixed-mode inserts is INSERT... On duplicate key update statements may cause the assigned auto_increment value to be unavailable.

The following describes how to set innodb_autoinc_lock_mode to different values:

innodb_autoinc_lock_mode=0（traditional lock mode）

For more information about the traditional auto_increment mechanism, see 1. in this mode, the AUTO-INC lock is applied to all Insert operations targeting the auto_increment column, and the assigned values are also allocated one by one, which is continuous, under normal circumstances, there will be no gap (of course, if the transaction rollback has this auto_increment value, it will be wasted, resulting in a gap ).

innodb_autoinc_lock_mode=1（consecutive lock mode）

In this case, the AUTO-INC lock method is used for bulk inserts. for simple inserts, a new lightweight mutex lock is used to assign the value of the auto_increment column. Of course, if other transactions already hold the AUTO-INC lock, simple inserts needs to wait.

Note that when innodb_autoinc_lock_mode = 1, there may be an interval between auto_increment values. For example, mixed-mode inserts and bulk inserts may cause some allocated auto_increment values to be wasted, resulting in gaps. An example is provided later.

innodb_autoinc_lock_mode=2（interleaved lock mode）

In this mode, the AUTO-INC lock is not used for any type of inserts, which has the best performance, but the auto_increment value gap is generated within the same statement. In addition, this mode is not secure for statement-based replication.

3. summary of possible causes of Gap

After analysis in the above document, we will summarize the possible causes of gaps in various types of inserts statements for the auto_increment field:

simple inserts

For innodb_autoinc_lock_mode = 0, 1, 2, the assigned auto_increment value is discarded when a transaction with an auto_increment column operation is rolled back, resulting in a gap.

Bulk inserts (transaction rollback is not considered here, and transaction rollback will cause gaps)

Innodb_autoinc_lock_mode = 0. because the AUTO-INC lock is held until the statement ends, the generated values are continuous and no gaps are generated.
Innodb_autoinc_lock_mode = 1. at this time, there is no gap in a statement, but there may be a gap between statements. Examples will be provided later.
Innodb_autoinc_lock_mode = 2. if concurrent insert operations exist, gaps may occur in the same statement.

mixed-mode inserts

In this mode, the values of innodb_autoinc_lock_mode are configured differently and the results are different. of course, innodb_autoinc_lock_mode = 0 will not generate gaps at all times, while rows = 1 and innodb_autoinc_lock_mode = 2 will generate gaps. The following is an example.

In the master-slave architecture, if statement-based replication is used for replication, innodb_autoinc_lock_mode = 0 or 1 is safe. If row-based replication or mixed-based replication is used, innodb_autoinc_lock_mode = 0, 1, and 2 are safe.

4. instance

The two tables in the test are t and t1, and the definitions are as follows:

CREATE TABLE `t` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`)) ENGINE=InnoDB;CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`)) ENGINE=InnoDB

Insert a total of 10 million pieces of data from 1 to records in table t for subsequent testing convenience. Enable session1 and execute the following statement:

insert into t1(c2) select * from t;

Enable session2 and insert data in t1:

insert into t1(c2) values(400);

When innodb_autoinc_lock_mode is different, the c1 value of the newly inserted data is also different.

When innodb_autoinc_lock_mode is set to 0, the AUTO-INC lock is applied to all session1 statements. Therefore, if session1 starts, the values in column c1 are 1-consecutive values. due to the traditional mechanism, auto_increment values are allocated one by one. Therefore, the c1 value of session2 is 10000001. The final result is that there are two such data records (400,400) and (10000001,400 ).

When innodb_autoinc_lock_mode = 1, the AUTO-INC lock is also applied to session1, but because auto_increment is pre-assigned in this mode, so we can see that the c1 value of the inserted data in session2 is not 10000001, but it is not a number in 1-0000000, because session1 has an AUTO-INC lock. The final data will be as follows: (400,400), (10026856,400 ).

When innodb_autoinc_lock_mode = 2, session1 does not apply the AUTO-INC lock. Therefore, although session2 is executed later, it does not affect the auto_increment value allocation. The final value is related to the time when we execute session2, the final value may be as follows: (400,400), (1235603,400), which occupies a value between 1.

5. other points

1) when innodb_autoinc_lock_mode = 1, the auto_increment pre-allocation policy can be referred to reference 2. assume that table t has an initial record 1, then in table t, we use 'Insert into t select NULL from t to execute four times. we can see that the final record in table t is,, of which, are wasted. The pre-allocation issue is also described in the later part of reference item 1.

2) insert into t1... SELECT... FROM t statements add record lock to table t1. if the isolation level is read committed, or innodb_locks_unsafe_for_binlog is set and the isolation level is not serialize, t is not locked, otherwise, t is added with shared next-key lock.