英协网MySQL InnoDB 锁介绍及不同 SQL 语句分别加什么样的锁（上）
7. 自增锁(AUTO-INC Locks)
表锁。向带有AUTO_INCREMENT列 的表时插入数据行时，事务需要首先获取到该表的AUTO-INC表级锁，以便可以生成连续的自增值。插入语句开始时请求该锁，插入语句结束后释放该锁（注意：是语句结束后，而不是事务结束后）。
你可能会想，日常开发中，我们所有表都使用AUTO_INCREMENT作主键，所以会非常频繁的使用到该锁。不过，事情可能并不像你想的那样。在介绍AUTO-INC表级锁之前，我们先来看下和它密切相关的SQL语句以及系统变量innodb_autoinc_lock_mode
INSERT-like语句
1. insert
2. insert … select
3. replace
4. replace … select
5. load data
外加，simple-inserts, bulk-inserts, mixed-mode-inserts
simple-inserts
待插入记录的条数，提前就可以确定（语句初始被处理时就可以提前确定）因此所需要的自增值的个数也就可以提前被确定。
包括：不带嵌入子查询的 单行或多行的insert, replace。不过，insert … on duplicate key update不是
bulk-inserts
待插入记录的条数，不能提前确定，因此所需要的自增值的个数 也就无法提前确定
包括：insert … select, replace … select, load data
在这种情况下，InnoDB只能每次一行的分配自增值。每当一个数据行被处理时，InnoDB为该行AUTO_INCREMENT列分配一个自增值
mixed-mode-inserts
也是simple-inserts语句，但是指定了某些（非全部）自增列的值。也就是说，待插入记录的条数提前能知道，但，指定了部分的自增列的值。
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
INSERT … ON DUPLICATE KEY UPDATE也是mixed-mode，最坏情况下，它就是INSERT紧跟着一个UPDATE，此时，为AUTO_INCREMENT列所分配的值在UPDATE阶段可能用到，也可能用不到。
再看一下系统变量innodb_autoinc_lock_mode，它有三个候选值0，1，和2
8.0.3之前，默认值是1，即“连续性的锁定模式(consecutive lock mode)”；8.0.3及之后默认值是2，即“交织性锁定模式(interleaved lock mode)”
a. 当innodb_autoinc_lock_mode=0时，INSERT-like语句都需要获取到AUTO-INC表级锁；
b. 当innodb_autoinc_lock_mode=1时，如果插入行的条数可以提前确定，则无需获得AUTO-INC表级锁；如果插入行的条数无法提前确定，则就需要获取AUTO-INC表级锁。因此，simple-inserts和mixed-mode inserts都无需AUTO-INC表级锁，此时，使用轻量级的mutex来互斥获得自增值；bulk-inserts需要获取到AUTO-INC表级锁；
c. 当innodb_autoinc_lock_mode=2时，完全不再使用AUTO-INC表级锁；
我们生产数据库版本是5.6.23-72.1，innodb_autoinc_lock_mode=1，而且，我们日常开发中用到大都是simple-inserts，此时根本就不使用AUTO-INC表级锁，所以，AUTO-INC表级锁用到的并不多哦。
LOCK_MODE：AUTO-INC表级锁用到的并不多，且，AUTO-INC锁是在语句结束后被释放，较难在performance_schema.data_locks中查看到，因此，没有进行捕获。感兴趣的同学可以使用INSERT … SELECT捕获试试。
8. 空间索引(Predicate Locks for Spatial Indexes)
我们平时很少用到MySQL的空间索引。所以，本文忽略此类型的锁
到此为止，MySQL InnoDB 8种类型的锁我们就介绍完了。我们以一个例子结束8种类型的介绍。
T1先执行，事务ID是8428；T2后执行，事务ID是8429
上图演示了：
1. 任何事务，在锁定行之前，都需要先加表级锁intention lock，即：第三行的IX和第一行的IX。
2. idx_c是辅助索引，InnoDB扫描idx_c时遇到了c=222，于是，在idx_c上加了next-key lock，即：第四行的X。next-key lock就是 index record lock+gap lock，于是此next-key lock锁定了idx_c上值为222的索引记录，以及222前面的间隙，也就是间隙(22, 222)。
3. idx_c是辅助索引，在主键索引之外的任何索引上加index record lock时，都需要在该行的主键索引上再加index record lock，于是，又在PRIMARY上添加了index record lock，即：第五行的X,REC_NOT_GAP。
4. InnoDB扫描完c=222后，又扫描到了c=2222，这是idx_c上，第一个不满足索引扫描条件的索引记录，于是InnoDB在c=2222上加gap lock，c=2222上的gap lock锁定的范围是“idx_c上2222前面的间隙”，这本应该是(222, 2222)，但，T1即将在idx_c上插入c=224，于是，c=2222上的gap lock锁定的范围是(224, 2222)。即：第六行的X,GAP。
5. InnoDB即将在idx_c上插入c=224，224也是不满足c=222的，于是InnoDB在c=224上加gap lock，该gap lock锁定了224前面的间隙，也就是(222, 224)，即，第七行的X,GAP。
6. T2执行INSERT成功后，会在新插入行的加index record lock，但，T2在插入之前，首先要作的是得到表级锁intention lock以及设置表的每个索引的insert intention lock，该锁的范围是(插入值, 向下的一个索引值)，于是，在设置idx_c上的insert intention lock范围就是(226, 2222)，这个范围和事务T1第六行gap lock范围(224, 2222)重叠。于是，事务T2被阻塞了，T2必须等待，直到T1释放第六行的gap lock。
performance_schema.data_locks表中并不能看到T2的全部锁，比如，T2也得在iux_b上设置insert intention lock，但，performance_schema.data_locks中并没有这个锁。关于performance_schema.data_locks中显示了哪些锁，请见本文最后一段。
把这些锁及其范围列出来如下图所示
四、不同的SQL加了什么样的锁？
OK，我们已经了解了InnoDB各种不同类型的锁，那么，不同SQL语句各加了什么样的锁呢
我们用最朴素的想法来思考一下，用锁作什么呢？锁要作的就是达到事务隔离的目的，即：两个并发执行的事务T1和T2，如果T1正在修改某些行，那么，T2要并发 读取/修改/插入 满足T1查询条件的行时，T2就必须被阻塞，这是锁存在的根本原因。index record lock, gap lock, next-key lock都是实现手段，这些手段使得锁既能达到目的，还能实现最大的并发性。所以，当我们考虑事务T1中的SQL上加了什么锁时，就想一下，当T1执行时，如果并发的事务 T2不会触及到T1的行，则T2无需被阻塞，如果T2的要 读取/修改/插入 满足T1条件的行时，T2就得被T1阻塞。而T1阻塞T2的具体实现就是：T1在已存在的行上加index record lock使得T2无法触碰已存在的行，以及，T1在不存在的行上加gap lock使得T2无法插入新的满足条件的行。
前面我们说过“加什么样的锁”与以下因素相关
1. 当前事务的隔离级别
2. SQL是一致性非锁定读(consistent nonlocking read)还是DML或锁定读(locking read)
3. SQL执行时是否使用了索引，所使用索引的类型（主键索引，辅助索引、唯一索引）
我们来看一下，不同的隔离级别下，使用不同的索引时，分别加什么锁。在讨论之前，我们先剔除无需讨论的情况
首先，普通SELECT 使用一致性非锁定读，因此根本不存在锁。无需讨论；
再者，作为开发者，我们几乎从来不会使用到隔离级别RU和Serializable。这两个隔离级别无需讨论。
于是，剩下的就是 给定锁定读SELECT或DML(INSERT/UPDATE/DELETE)语句，在不同隔离级别下，使用不同类型的索引时，分别会加什么样的锁？直接给出答案，其加锁原则如下
一、RR时，如果使用非唯一索引进行搜索或扫描，则在所扫描的每一个索引记录上都设置next-key lock。
这里“所扫描的每一个索引记录”是指当扫描执行计划中所使用的索引时，搜索遇到的每一条记录。WHERE条件是否排除掉某个数据行并没有关系，InnoDB并不记得确切的WHERE条件，InnoDB倔强的只认其扫描的索引范围(index range) 。
你可能觉得InnoDB在设置锁时蛮不讲理，竟然不管WHERE条件排除掉的某些行，这不是大大增加了锁的范围了嘛。不过，等我们了解了MySQL执行SQL时的流程，这就好理解了。MySQL的执行计划只会选择一个索引，使用一个索引来进行扫描，MySQL执行SQL语句的流程是，先由InnoDB引擎执行索引扫描，然后，把结果返回给MySQL服务器，MySQL服务器会再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集，而加锁时只考虑InnoDB扫描的索引，由MySQL服务器求值的其他WHERE条件并不考虑。当然，MySQL使用index_merge优化时会同时使用多个索引的，不过，这个时候设置锁时也并不特殊，同样，对于所用到的每一个索引，InnoDB在所扫描的每一个索引记录上都设置next-key lock。
加的锁一般是next-key lock，这种锁住了索引记录本身，还锁住了每一条索引记录前面的间隙，从而阻止其他事务 向 索引记录前面紧接着的间隙中插入记录。
如果在搜索中使用了辅助索引(secondary index)，并且在辅助索引上设置了行锁，则，InnoDB还会在 相应的 聚集索引 上设置锁；表未定义聚集索引时，InnoDB自动创建隐藏的聚集索引（索引名字是GEN_CLUST_INDEX），当需要在聚集索引上设置锁时，就设置到此自动创建的索引上。
二、RR时，如果使用了唯一索引的唯一搜索条件，InnoDB只在满足条件的索引记录上设置index record lock，不锁定索引记录前面的间隙；如果用唯一索引作范围搜索，依然会锁定每一条被扫描的索引记录前面的间隙，并且再在聚集索引上设置锁。
三、RR时，在第一个不满足搜索条件的索引记录上设置gap lock或next-key lock。
一般，等值条件时设置gap lock，范围条件时设置next-key lock。此gap lock或next-key lock锁住第一个不满足搜索条件的记录前面的间隙。
四、RR时，INSERT在插入新行之前，必须首先为表上的每个索引设置insert intention lock。
每个insert intention lock的范围都是(待插入行的某索引列的值, 此索引上从待插入行给定的值向下的第一个索引值)。只有当insert intention lock与某个gap lock或next-key lock冲突时，才能在performance_schema.data_locks看到insert intention lock。
五、RC时，InnoDB只在完全满足WHERE条件的行上设置index record lock。
六、RC时，禁用了gap lock。
正因为此，RC时不存在gap lock或next-key lock。这是为什么呢？我们想一想啊，gap lock是用来解决phantom row问题的，gap lock封锁的区间内不能插入新的行，因为插入时的insert intention lock会和gap lock冲突，从而阻止了新行的插入。但，隔离级别RC是允许phantom row的，因此RC时gap lock是被禁用的。
七、RR或RC时，对于主键或唯一索引，当有重复键错误(duplicate-key error)时，会在 重复的索引记录上 设置 shared next-key lock或shared index record lock。这可能会导致死锁。
假设T1, T2, T3三个事务，T1已经持有了X锁，T2和T3发生了重复键错误，因此T2和T3都在等待获取S锁，这个时候，当T1回滚或提交释放掉了X锁，则T2和T3就都获取到了S锁，并且，T2和T3都请求X锁，“T2和T3同时持有S锁，且都在请求X锁”，于是死锁就产生了。
好了，规则都列出来了，是时候实践一把了。下面在展示锁时，我们同时指出了当前所使用的隔离级别，表上的索引以及事务的SQL语句。
实践一：搜索时无法使用索引，即全表扫描时，InnoDB在表的全部行上都加锁
上图演示了：搜索条件无法使用索引时，InnoDB不得不在表的全部行上都加锁。所以，索引实在太重要了，查询时，它能加快查询速度；更新时，除了快速找到指定行，它还能减少被锁定行的范围，提高插入时的并发性。
实践二：唯一索引和非唯一索引、等值查询和范围查询加锁的不同
搜索时使用 唯一索引 作等值查询时，InnoDB只需要加index record lock；搜索时使用 唯一索引作范围查询时 或 使用非唯一索引作任何查询时 ，InnoDB需要加next-key lock或gap lock。
示例1演示了：使用非唯一索引 idx_c 搜索或扫描时，InnoDB要锁住索引本身，还要锁住索引记录前面的间隙，即next-key lock: X 和 gap lock: X,GAP。next-key lock既锁住索引记录本身，还锁住该索引记录前面的间隙，gap lock只锁住索引记录前面的间隙。等值条件时，在最后一个不满足条件的索引记录上设置gap lock。
示例2演示了：使用唯一索引 iux_b 的唯一搜索条件，即，使用唯一索引执行等值查找时，InnoDB只需锁住索引本身，即index record lock: X, REC_NOT_GAP，并不锁索引前面的间隙。
示例3演示了：使用唯一索引 iux_b 进行范围扫描时，依然需要锁定扫描过的每一个索引记录，并且锁住每一条索引记录前面的间隙，即next-key lock: X。范围条件时，在最后一个不满足条件的索引记录上设置next-key lock。
实践三：不同隔离级别加锁的不同
无论何种隔离级别，SQL语句执行时，都是先由InnoDB执行索引扫描，然后，返回结果集给MySQL服务器，MySQL服务器再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集。
上图中，在不同的隔离级别下，执行了相同的SQL。无论何种隔离级别，PRIMARY上的index record lock总是会加的，我们不讨论它。在idx_b上，隔离级别为RC时，InnoDB加了index record lock，即：X,REC_NOT_GAP，隔离级别为RR时，InnoDB加了next-key lock，即X。注意：RC时没有gap lock或next-key lock哦。
上图演示了：事务的隔离级别也会影响到设置哪种锁。如我们前面所说，gap lock是用来阻止phantom row的，而RC时是允许phantom row，所以，RC时禁用了gap lock。因此，上图中，RC时没有在索引上设置gap lock或next-key lock。
实践四：操作不存在的索引记录时，也需要加锁
上图中，idx_b上并不存在b=266的索引记录，那么，当更新b=266的记录时，是否需要加锁呢？是的，也需要加锁
无论b=266是否存在，RR时，InnoDB在第一个不满足搜索条件的索引记录上设置gap lock或next-key lock。一般，等值条件时设置gap lock，范围条件时设置next-key lock。上图中是等值条件，于是InnoDB设置gap lock，即上图的X,GAP，其范围是(226, 2222)，正是此gap lock使得并发的事务无法插入b列大于等于266的值，RC时，由于gap lock是被禁止的，因此，并不会加gap lock，并发的事务可以插入b列大于等于266的值。
上图演示了：操作不存在的索引记录时，也需要加锁。
实践五：重复键错误(duplicate-key error)时，会加共享锁。这可能会导致死锁。
对于主键或唯一索引，当有重复键错误(duplicate-key error)时，会在 重复的索引记录上 设置 shared next-key lock或shared index record lock。这可能会导致死锁。
上图演示了：T1在主键1上设置exclusive index record lock。T2和T3插入时，会产生重复键错误，于是T2和T3都在主键1上设置了shared next-key lock。如上图所示
如果此时，T1 rollback释放掉其所持有的index record lock，则T2和T3等待获取的shared next-key lock都成功了，然后，T2和T3争夺主键1上的index record lock，于是T2和T3就死锁了，因为它俩都持有shard next-key lock，双方谁都不会放弃已经得到的shared next-key lock，于是，谁都无法得到主键1的index record lock。
需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。死锁并不可怕，MySQL会选择一个牺牲者，然后，在系统变量innodb_lock_wait_timeout指定的秒数达到后，自动回滚牺牲者事务；从MySQL5.7开始，新加入了系统变量innodb_deadlock_detect（默认ON），如果开启此变量，则MySQL不会再等待，一旦探测到死锁，就立即回滚牺牲者事务。
上图演示了：在上图的状态下，当T1 commit时，T1释放了主键1上的index record lock，于是T2和T3等待获取的shared next-key lock都成功了，然后，T2和T3争夺主键1上的index record lock，于是T2和T3死锁了，因为它俩都持有shard next-key lock，双方谁都不会放弃已经得到的shared next-key lock，于是，谁都无法得到主键1的index record lock。
五、performance_schema.data_locks中能看到全部的锁吗？
显而易见，performance_schema.data_locks并未显示全部的锁，那么，它显示了哪些锁呢？很不幸，我并未找到文档说这事，尽管文档(https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html)说：“事务持有的每一个锁 以及 事务被阻塞的每一个锁请求，都在该表中占据一行”，但，我们很多例子都表明，它并未显示全部的锁。根据我的试验，我猜测performance_schema.data_locks显示的是WHERE条件所触碰到的索引上的锁，“WHERE条件所触碰到的索引”是指SQL实际执行时所使用的索引，也就是SQL执行计划的key列所显示的索引，正因为此，INSERT时看不到任何锁，update g set a=a+1 where b=22时只看到idx_b上的锁。需要强调的是，这是我自己试验并猜测的，我并未在文档中看到这种说法。
假设T1和T2两个事务操作同一个表，先执行T1，此时尽管performance_schema.data_locks中只显示T1的WHERE条件所触碰到的索引上的锁，但是，事实上在T1的WHERE条件触碰不到的索引上，也是会设置锁的。尽管表的索引idx并未被T1所触碰到，即performance_schema.data_locks显示T1在索引idx并没有设置任何锁，但，当T2执行 锁定读/插入/更新/删除 时触碰到了索引idx，T2才恍然发现，原来T1已经在索引idx上加锁了。
我们来看下面的三个例子
“performance_schema.data_locks无法看到全部锁”示例一
上图演示了：T1执行时，只触碰到了索引idx_b，T1执行完后，在performance_schema.data_locks中只能看到idx_b上的锁，看起来T1并未在idx_a上设置任何锁；但，当T2执行触碰到了索引idx_a时，T2才恍然发现，原来T1已经在idx_a上设置了index record lock啦。
“performance_schema.data_locks无法看到全部锁”示例二
插入新行时，会先设置insert intention lock，插入成功后再在插入完成的行上设置index record lock。
上图演示了：T1插入了新行，但，在performance_schema.data_locks中，我们既看不到T1设置的insert intention lock，也看不到T1设置的index record lock。这是因为T1的WHERE条件并未触碰到任何索引（T1根本不存在WHERE条件），因此我们看不到T1的这两个锁；但，当T2要删除T1新插入的行时，T2才恍然发现，原来T1已经在索引c2上设置了index record lock啦。
“performance_schema.data_locks无法看到全部锁”示例三
插入新行时，本来是不会在performance_schema.data_locks中显示insert intention lock的，因为插入时WHERE条件并未触碰到任何索引（插入时根本不存在WHERE条件）。
上图演示了：T2插入新行时的insert intention lock 和 T1的gap lock冲突了，于是，我们得以在performance_schema.data_locks中观察到T2插入新行时需要请求insert intentin lock。
