MySQL Innodb_autoinc_lock_mode Detailed

Innodb_autoinc_lock_mode This parameter controls the behavior of the associated lock when inserting data into a table with auto_increment columns;

It can be set to achieve performance and security (master-slave data consistency) balance

"0" Let's sort the insert first.

First, the insert can be broadly divided into three categories:
1, simple insert such as INSERT into T (name) VALUES (' Test ')
2. Bulk INSERT as load Data | Insert INTO ... select .....
3. Mixed insert such as INSERT into T (id,name) VALUES (1, ' a '), (null, ' B '), (5, ' C ');

Description of the "1" innodb_autoinc_lock_mode

Innodb_auto_lockmode has three values:
1, 0 this represents tradition tradition
2, 1 This indicates consecutive continuous
3, 2 This means interleaved staggered

"1.1" Tradition (innodb_autoinc_lock_mode=0) mode:

1, it provides a backward compatibility of the ability
2, in this mode, all INSERT statements ("Insert like") will be at the beginning of the statement to get a
Table-level Auto_inc lock, release this lock at the end of the statement, note that this is the statement-level rather than the transaction-level,
A transaction may contain one or more statements.
3, it can guarantee the predictability of the value allocation, and continuity, repeatability, this also ensures that the INSERT statement is copied to the slave
can generate the same value as the master (which guarantees the security of statement-based replication).
4, because in this mode auto_inc lock is kept to the end of the statement, so this affects the concurrent insertion.

"1.2" consecutive (innodb_autoinc_lock_mode=1) mode:

1, this mode down simple insert is optimized, because simple insert one-time insertion of the number of values can be immediately
OK, so MySQL can generate several consecutive values at a time for this insert statement; In general, this is safe for replication.
(It guarantees the security of statement-based replication)
2, this mode is also the default mode of MySQL, the advantage of this mode is that the Auto_inc lock does not remain until the end of the statement, as long as
After the statement has a corresponding value, the lock can be released prematurely

"1.3" Interleaved (innodb_autoinc_lock_mode=2) mode
1, because this mode has no auto_inc lock, so the performance of this mode is the best, but it also has a problem, is
The auto_incremant value it obtains for the same statement may not be contiguous.

"2" if your binary file format is mixed | Row then any one of these three values is copy-safe for you.

Since MySQL is now recommended to set the binary format to row, the Binlog_format is not statement in the case of the most

Good is innodb_autoinc_lock_mode=2 so may know better performance.

End With an example of auto_increment.

Example: don't worry about it. To update the value of a auto_increment column

First step: Reproduce the scene

Create TableT (xintAuto_increment not NULL Primary Key);Insert  intoT (x)Values(0),(NULL),(3);Select *  fromT;+---+|X|+---+| 1 || 2 || 3 |+---+

Step two: Reproduce the SQL that caused the problem

UpdateTSetX=4 whereX=1;Select *  fromT;+---+|X|+---+| 2 || 3 || 4 |+---+

Step three: Reproduce the form of the expression that is always

Insert  into Values (01062 (23000'4'forkey ' PRIMARY '

Fourth step: A summary of the problem

When you finish the first step, MySQL knows that the next auto_increment value is 4.

After performing the second step, MySQL did not know that 4 has been occupied, so the third step of the implementation of the error.

---

MySQL Innodb_autoinc_lock_mode Detailed