Introduction to MySQL innodb_autoinc_lock_mode

The innodb_autoinc_lock_mode parameter controls the behavior of the correlation lock when data is inserted into a table with an auto_increment column;

By setting it, you can achieve a balance between performance and security (master-slave data uniformity)

"0" Let's classify insert first

First of all, insert can be roughly divided into three categories:

1. simple insert such as insert into t (name) values ('test')

2. bulk insert such as load data insert into... select... from...

3. mixed insert such as insert into t (id, name) values (1, 'a'), (null, 'b'), (5, 'c');

"1" Description of innodb_autoinc_lock_mode

innodb_auto_lockmode has three values:

1, 0 indicates tradition tradition

2, 1 This indicates that consecutive is continuous

3, 2 This represents interleaved interleaving

"1.1" tradition (innodb_autoinc_lock_mode=0) Mode:

1. It provides a backward compatible capability

2. In this 1 mode, all insert statements ("insert like") must get a table-level auto_inc lock at the beginning of the statement, and release this lock at the end of the statement. Note that this is a statement level rather than a transaction level, and a transaction may contain one or more statements.

3. It can ensure the predictability, continuity and repeatability of value allocation, which ensures that insert statements can generate the same value as master when copied to slave (it ensures the security of statement-based copying).

4. Since the auto_inc lock 1 is held until the end of the statement in this mode, this affects concurrent insertions.

"1.2" consecutive (innodb_autoinc_lock_mode=1) mode:

1. simple insert is optimized in this 1 mode. Because the number of secondary insertion values of simple insert1 can be determined immediately, mysql can generate several consecutive values at one time for this insert statement; In general, this is also safe for replication (it guarantees the security of statement-based replication)

2. This 1 mode is also the default mode of mysql. The advantage of this mode is that the auto_inc lock should not be kept until the end of the statement. As long as the statement gets the corresponding value, the lock can be released in advance

"1.3" interleaved (innodb_autoinc_lock_mode=2) Mode

1. Since there is no auto_inc lock in this mode, the performance in this mode is the best; However, it also has a problem, that is, the auto_incremant values it gets may not be continuous for the same statement.

"2" If your binary file format is mixed row, then any one of these three values is copy-safe for you.

Since mysql has now recommended setting the binary format to row, it is preferable to be innodb_autoinc_lock_mode=2 if binlog_format is not statement so that better performance may be known.

I conclude with an example of auto_increment

Example: Don't have nothing to do to update the value of 1 auto_increment column

Step 1: Reproduce 1 scene

create table t(x int auto_increment not null primary key);
insert into t(x) values(0),(null),(3);
select * from t;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+

Step 2: Reproduce the SQL that caused the problem under 1

update t set x=4 where x=1;
select * from t;
+---+
| x |
+---+
| 2 |
| 3 |
| 4 |
+---+

Step 3: Reproduce the form of always under 1

insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

Step 4: Summarize the problem

At the end of step 1, mysql knows that the next auto_increment value is 4.

When mysql finished Step 2, it didn't know that 4 had been occupied artificially, so it went wrong when executing Step 3.