小知识之 InnoDB自增锁

一叶而不知秋

向管中窥豹寻知外，坐井观天又出来。

在设计数据库表时，InnoDB存储引擎建议将主键设置为自增键，当表中存在自增键时，插入数据操作的同时InnoDB会加自增锁，自增锁是表锁。当一条数据插入语句持有自增锁时，其他插入语句将会被阻塞。但InnoDB为提高数据插入语句的并发性能，针对不同的插入语句采取了不同的策略。

首先，数据插入语句可分为以下几种：

Simple inserts ：在插入前，插入数据的数量是可知的，普通的插入语句（没有子查询）属于此类：

Bulk inserts ：在插入前，插入数据的数量是不可知的

Mixed-mode insert

其次，InnoDB通过innodb_autoinc_lock_mode这个参数控制着不同插入语句对自增键行为的影响，其有三个取值

1.Tradition，传统模式（innodb_autoinc_lock_mode=0）

在这种模式下，以上三类数据插入语句都需要申请并持有自增锁，即会锁表，其他的插入语句申请自增表锁会阻塞，但这个锁的作用范围是语句级，而非事务级。

该模式提供了向后的兼容性，能保证单挑数据插入语句中值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值，即保证了基于语句复制的安全性。

2.consecutive，连续模式（innodb_autoinc_lock_mode=1）

该模式是InnoDB的默认模式（MySQL5.7版本），这一模式下，InnoDB对Simple inserts类语句做了优化，由于simple insert一次性插入值的个数是可以确定的，所以MySQL可以一次生成几个连续的值，用于这个insert语句，不需要等待这个insert语句执行结束。对于其他类型的数据插入语句，仍需要申请并持有自增锁，且当一个非Simple inserts类的插入语句持有自增锁时，Simple inserts语句也需要等待。

3.interleaved，交错模式，（innodb_autoinc_lock_mode=2）

该模式下，所有数据插入语句均不使用自增锁，所以这个模式下的并发性能最好，但他的问题就是对于同一个语句

最后举一个Mixed-mode insert语句在不同模式下执行的结果，MySQL 5.7版本：

表结构：

插入语句：

tradition lock mode下结果：

下一个自增值为103，因为其是按行一次分一个，只有两个NULL的插入触发了分配。

consecutive lock mode下结果：

下一个自增值为105，因为其是一次性分配了4个。

interleaved lock mode下结果：

x/y具体值取决于其他并发插入操作。

你如果想学技术 | 屯干货 | 聊职场