About MySQL innodb_autoinc_lock_mode

The innodb_autoinc_lock_mode parameter controls the behavior of related locks when inserting data into a table with an auto_increment column;

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

【0】Let’s first classify insert

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 This means tradition

2. 1 This means consecutive

3.2 This means interleaved

【1.1】tradition(innodb_autoinc_lock_mode=0) mode:

1. It provides a backward compatibility capability

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

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

4. Since the auto_inc lock 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. In this mode, simple insert is optimized. Since the number of values ​​to be inserted at one time can be determined immediately, MySQL can generate several consecutive values ​​at a time for this insert statement. In general, this is also safe for replication (it ensures the safety 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 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; but it also has a problem, that is, the auto_incremant values ​​obtained for the same statement may not be continuous.

【2】If your binary file format is mixed | row, then any of these three values ​​is safe for you to copy.

Since MySQL now recommends setting the binary format to row, it is best to set innodb_autoinc_lock_mode=2 when binlog_format is not statement, which may give better performance.

Finally, let's end with an example about auto_increment

Example: Don't update the value of an auto_increment column for no reason

Step 1: Recreate the 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

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

Step 3: Reproduce the usual performance

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

Step 4: Summary of the problem

After executing the first step, MySQL knows that the next auto_increment value is 4.

When executing the second step, MySQL does not know that 4 has been occupied manually, so an error occurs when executing the third step.

The above introduction to MySQL innodb_autoinc_lock_mode is all I have to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.