Detailed introduction to MySQL innodb_autoinc_lock_mode

The following editor will bring you an introduction to MySQL innodb_autoinc_lock_mode. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

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

By setting it, you can achieve performance and Balance of security (master-slave data consistency)

[0] Let’s classify insert first

First of all, insert can generally 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 means tradition

2, 1 means coherent

3, 2 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 is a statement. Level rather than transaction level, a transaction may contain one or more statements.

3. It can ensure the predictability, continuity and repeatability of value distribution. This also ensures that the insert statement can be generated the same as the master when copied to the slave. value (which ensures the safety of statement-based replication).

4. Since the auto_inc lock is maintained until the end of the statement in this mode, this affects concurrent insertion.

【1.2】consecutive(innodb_autoinc_lock_mode=1) Mode:

1. In this mode, simple insert has been optimized. Since simple insert The number of values ​​inserted at one time can be determined immediately, so 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, so in this mode The performance is the best; but it also has a problem, that is, the auto_incremant values ​​it obtains for the same statement may not be continuous.

[2] If your binary file format is mixed | row, then any of these three values ​​​​is copy-safe for you.

Since mysql now recommends setting the binary format to row, it is best to use innodb_autoinc_lock_mode=2 when binlog_

format is not a statement. This may be known. Better performance.

Finally end with an example about auto_increment

Example:

Don’t worry about itUpdateThe value of an auto_increment column

Step 1: Reproduce 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 |
+---+

Third Step: Reproduce the usual form of expression

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

Step 4: Summary of the problem

Mysql knows the next step after executing the first step An auto_increment value is 4.

After executing the second step, mysql did not know that 4 had been artificially occupied, so an error occurred when executing the third step.

The above is the detailed content of Detailed introduction to MySQL innodb_autoinc_lock_mode. For more information, please follow other related articles on the PHP Chinese website!