一则MySQL插入意向锁(Insert Intention Lo

一、前言

在学习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被阻塞……