Detailed explanation of innodb_autoinc_lock_mode parameters of MySQL self-increasing Lock Mode

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

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

[0] Let's classify insert first.

First, 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) null,'b'), (5)

[1] description of innodb_autoinc_lock_mode

There are three values for innodb_auto_lockmode:

1, 0 means tradition tradition.

2, 1 this means that consecutive is continuous.

3, 2, this means interleaved interleaving.

[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") get one at the beginning of the statement

The table-level auto_inc lock is released at the end of the statement. Note that this is at the statement level rather than the transaction level. A transaction may contain one or more statements.

3. It can guarantee the predictability, continuity and repeatability of value assignment, which ensures that insert statements are copied to slave.

It can also generate the same value as the master side (it ensures the security of statement-based replication).

4. Because 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. Simple insert is optimized in this mode, because the number of one-time inserted values of simple insert can be obtained immediately.

OK, 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 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 is not held until the end of the statement, as long as

Statement can release the lock ahead of time after getting the corresponding value

[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_ increment value it gets may not be contiguous for the same statement.

[2] if your binary format is mixed | row, then any of these three values is safe for you to copy.

Since mysql has now recommended that the binary format be set to row, it is better to use innodb_autoinc_lock_mode=2 when binlog_format is not statement so that you may know better performance.

The three modes are briefly described:

0:traditonal (table locks are generated every time)

1:consecutive (a lightweight lock will be generated, and simple insert will acquire a batch of locks to ensure continuous insertion)

2:interleaved (will not lock the table, deal with one at a time, and send it at the same time)

Summary:

When 1 innodb row is replicated, the innodb_autoinc_lock_mode can be set to 2, and the maximum concurrency of the table can be obtained in all insert cases

2 when innodb statement is replicated, innodb_autoinc_lock_mode can be set to 1 to ensure the security of replication while obtaining the maximum concurrency of simple insert statements.

3 in the case of myisam engine, no matter what kind of self-increasing id lock is a table-level lock, setting the innodb_autoinc_lock_mode parameter is invalid (test strategy)

4 in fact, the questioner mentioned that when the id value is self-incremented as the primary key under the innodb engine, the insertion speed can be mentioned compared to the uuid or custom primary key, because innodb is a primary key clustered index, and the actual primary key values must be accessed in the primary key order, so the self-increasing id itself is in ascending order, so when inserting data, the bottom layer does not have to do additional sorting operations and reduces the number of index page splits. This greatly increases the speed of insert (unless other schemes can also ensure that the primary key is fully self-increasing)

Modify the self-increment lock level method:

Edit / etc/my.cnf and add the following line:

Innodb_autoinc_lock_mode=2

An error will be reported if you modify it directly through the command:

Mysql (mdba@localhost: (none) 09:32:19) > set global innodb_autoinc_lock_mode=2

ERROR 1238 (HY000): Variable 'innodb_autoinc_lock_mode' is a read only variable