一、前言
在学习MySQL锁机制的过程中，遇到一例update更新被阻塞的case，百思不得其解。最终查阅大量资料之后终于弄清楚其中的原理。借此文章将这里面涉及到的知识点分享出来，希望对朋友们有所帮助~
二、插入意向锁
什么是插入意向锁？听过mysql的行锁，表锁，元数据锁…… 但插入意向锁，相信大多数人会比较陌生。这里先来介绍一下什么是插入意向锁？
Mysql官方文档对意向锁的定义：A insert intention lock is
a type of gap lock set by insert operations prior to row insertion (翻译：插入意向锁是一种间隙锁，是在执行insert操作之前事先设置的)。我们来看看插入意向锁的部分内核代码：
从type_mode可以看到插入意向锁包含了三个锁模式：
A、LOCK_X ： 排他锁
B、LOCK_GAP：间隙锁
C、LOCK_INSERT_INTENTION：插入意向锁
由此可知，插入意向锁是一种排他间隙锁，其本质上是间隙锁。这里关于插入意向锁就先简单介绍到这里，毕竟本文的主要目的不是为了要介绍插入意向锁。
三、案例分析
1）、表结构定义如下：
CREATE TABLE`t_lock` (
`id` int(11) NOT NULL,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_b` (`b`)
) ENGINE=InnoDBDEFAULT CHARSET=latin1
其中id为主键，b字段为普通非唯一索引；
事务隔离级别为可重复读隔离级别；
MySQL版本为5.7;
2）、表中的记录如下：
+----+----+----+
| id | a | b |
+----+----+----+
| 0 | 0| 0 |
| 5 | 5| 5 |
| 7 | 7| 7 |
| 9 | 9| 9 |
| 10 | 10 | 10 |
| 11 | 11 | 11 |
| 16 | 16 | 16 |
| 18 | 18 | 18 |
| 32 | 32 | 32 |
+----+----+----+
3）、会话A开启一个事务，执行如下SQL，并且暂时不提交事务：
begin;
select * fromt_lock where b=16 for update;
根据非唯一索引等值查询加锁原则：
A、当查询的记录存在时，除了会加next-key lock外，还会额外加间隙锁，也就是加两把锁。加锁的基本单位是next-key lock，故会给(11,16]加上next-key lock;
B、由于B是普通索引，因此仅访问b=16是不能马上停下来的，需要继续向右遍历，直到查找到b=18才会停下来。查找到的对象都会被加锁，故需要给(16,18]加next-key lock;
C、索引上的等值查询，向右遍历最后一个值不满足等值条件时，next-key
lock会退化成间隙锁。由于b=18不满足等值条件，故(16,18]会退化成间隙锁(16,18);
D、b是普通索引，故还会对b=16(对应id=16)对应的主键行记录加record lock；
E、总结：会话A会产生二级索引b的nex-key lock和gap lock，锁定范围为：(11,16]和(16,18)，并且会锁住id=16的主键索引；
来看看show engine innodb status输出信息：
TABLE LOCK table `test1`.`t_lock` trx id 1265199 lock mode IX ：万年不变的意向排他锁；
RECORD LOCKS space id 12964 page no 4 n bits 80 index idx_b of table `test1`.`t_lock` trx id 1265199 lock_mode X ： 这里显示的内容既没有lock rec but not gap，也没有lock gap before rec，其实这就是next-key lock；
RECORD LOCKS space id 12964 page no 3 n bits 80 index PRIMARY of table `test1`.`t_lock` trx id 1265199 lock_mode X locks rec but not gap ： 二级索引b对应的主键索引上行记录加了一个rec lock；
RECORD LOCKS space id 12964 page no 4 n bits 80 index idx_b of table `test1`.`t_lock` trx id 1265199 lock_mode X locks gap before rec：二级索引b上加了一个gap lock；
4）、会话B执行如下SQL：
update t_lock setid=12 where b=11;
执行会话B的SQL，发现会话B会被阻塞………
5）、分析：
会话A产生二级索引b的nex-key lock和gap lock，锁定范围为：(11,16]和(16,18)，并且会锁住id=16的主键索引。而b=11并不在会话A的锁定范围内(11,16]是开区间，并不包含11)，为什么会话B会被阻塞呢？
带着疑问，我们来看看show engine innodb status的加锁信息:
update t_lock setid=12 where b=11
------- TRX HASBEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12964 page no 4 n bits 80 index idx_b of table `test1`.`t_lock` trx id 1265343 lock_mode X locks gap before rec insert intention waiting ： 该信息表明，会话B执行的SQL在等待插入意向锁（insert intention waiting）
TABLE LOCK table `test1`.`t_lock` trx id 1265343 lock mode IX；
RECORD LOCKS space id 12964 page no 4 n bits 80 index idx_b of table `test1`.`t_lock` trx id 1265343 lock_mode X ：会话B的SQL给表加了IX锁，同时会加上next-key lock(10,11]；
RECORD LOCKS space id 12964 page no 3 n bits 80 index PRIMARY of table `test1`.`t_lock` trx id 1265343 lock_mode X locks rec but not gap ： 会话B会给主键索引id=11加rec lock；
RECORD LOCKS space id 12964 page no 4 n bits 80 index idx_b of table `test1`.`t_lock` trx id 1265343 lock_mode X locks gap before rec ： 会话B给表加gap锁(11,16);
RECORD LOCKS space id 12964 page no 4 n bits 80 index idx_b of table `test1`.`t_lock` trx id 1265343 lock_mode X locks gap before rec insert intention waiting ： 会话B等待插入意向锁(lock_mode X locks gap before rec insert intention waiting)
为什么会话B要等待插入意向锁？我们来看看data_locks表中的加锁信息：
mysql> select * from performance_schema.data_locks;
注:
LOCK_MODE=X时，LOCK_DATA 第一个数值是 next-key 锁和间隙锁锁住的范围的右边界值，第二个数值是主键值；
LOCK_MODE=X,GAP时，LOCK_DATA 第一个数值是 gap锁锁住的范围的右边界值，第二个数值是主键值；
从lock_mode : X,GAP,INSERT_INTENTION可知，会话B执行的SQL在等待（11,16）的插入意向锁；
由于插入意向锁和间隙锁是互斥的，事务B想要获取二级索引b的X,GAP,INSERT_INTENTION（11,16）lock，被事务A加的二级索引b 的next-key lock(11,16]阻塞；
那为什么事务B要获取X,GAP,INSERT_INTENTION（11,16）lock呢？难道说事务B想要在gap (11,16)插入记录？
事务B：update t_lock set id=12 where b=11是一个update语句，对主键进行更新，为了让聚簇索引的数据保持有序，update会转化成先delete后insert（不能就地更新，否则会导致数据无序），即该update语句实际会转化成：
delete from t_lockwhere b=11;
insert into t_lock values(12,11,11);
对于二级非唯一索引b的索引B+树而言，叶子节点存储的是二级索引值和主键值，即（11,11），update之后变成了（11,12）；
我们知道，当二级索引值相同的时候，二级索引B+树会根据主键值进行排序，也就是说（11,12）会在（11,11）和（16,16）之间（即在这两者间隙中）;
由此我们也就能理解，为什么会话B的update语句想要在gap (11,16)插入记录，而刚好该gap是被会话A加了next-key lock(11,16]，由于插入意向锁和gap锁是冲突的，所以会话B被阻塞……
网友评论