mysql 间隙锁和临键锁原理

间隙锁产生的背景

备注: 本文使用的 MySQL 版本是: 8.0.13

隔离级别：可重复读(RR)

存储引擎：Innodb

以下面的表为例子进行说明

CREATE TABLE `tb` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=InnoDB;
insert into tb values(0,0,0),(10,10,10),(20,20,20),(30,30,30),(40,40,40),(50,50,50);

间隙锁的产生来自于 InnboDB 引擎在可重复读的级别基础上执行当前读时出现的幻读问题。下面来分析一下幻读的例子，假如没有间隙锁的话，那么会出现下面的现象：

	sessionA	sessionB
T1	begin; select * from tb where b = 10 for update; 返回:(10,10,10)
T2		insert into tb value(2,2,10);
T3	select * from tb where b = 10 for update; 返回:(10,10,10),(2,2,10)
T4	commit;

如上表如示，是基于没有间隙锁的假设，sessionA 事务内执行两次相同的当前读返回的数据不一样，出现幻读的现象。因为(2,2,10)这条记录在原本的数据并不存在，行锁就锁不住，因此诞生间隙锁。

间隙锁加锁规则

间隙锁和行锁合称 next-key lock，每一个 next-keylock 是前开后闭区间，如: (0,10]

• 原则 1：加锁的基本单位是 next-key lock。

• 原则 2：加锁是基于索引的，查找过程中访问到的对象才会加锁

• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁

• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

分析上面例子

select * from tb where b = 10 for update;

1. 加锁的基于索引的
   因为 b 不是索引，索引走的是主键索引，加锁就是加在主键索引上

2. 加锁范围

   遍历主键索引，发现 b=10 时，需要在前后记录之间加锁，所以在前一主键记录(0,0,0)和本记录之间加锁(0,10]，在后一条主键记录(20,20)和本记录之间加锁 (10,20]。然后继续向右遍历，判断 b=20，!=10，满足优化 2 规则，next-key lock 退化为间隙锁，变成(10,20)。同时 b=10 加了行锁，汇总锁范围: (0,20)，针对 id 主键

3. 间隙锁与“往这个间隙插入操作”冲突

   因为上面主键 id 锁的范围是(0,20)，因此插入(2,2,10)中 主键 2 属于锁范围，因此阻塞

   注意: 线程之间的间隙锁是不冲突的

查询条件走二级索引例子

	sessionA	sessionB
T1	begin; select id from tb where a = 20 for update;
T2		场景 1：insert into tb values(5,11,11);//阻塞 场景 2: insert into tb values(15,10,8);//阻塞 场景 3：insert into tb values(17,9,1);//成功 场景 4: insert into tb values(25,35,88);//成功
T3	commit;

首先看看sessionA的执行计划，发现用到覆盖索引

explain select id from tb where a = 20 for update;

1. 因为条件走了 a 索引，查询字段 id 在索引 a中，用到的覆盖索引，索引在搜索过程中，只用到索引 a，所以只会在索引 a 中间隙锁，同时命中条件对应的主键也要加上行锁，所以主键 id=20 被加了行锁。

2. 间隙锁加在索引 a 上的范围是（10，30），为了更加深度理解加锁范围，如下图：

image.png

场景 1：插入到索引 a 时，要插入是索引是(11,5)，属于(a=10,id=10)和(a=30,id=30)之间的锁范围，所以阻塞

场景 2、3、4 同理分析得出结论

查询条件走主键索引例子

	sessionA	sessionB
T1	begin; select * from tb where a = 10 for update;
T2		场景 1：insert into tb values(5,11,11);//阻塞 场景 2: insert into tb values(15,10,8);//阻塞 场景 3：insert into tb values(17,9,1);//阻塞(主键上的锁) 场景 4: insert into tb values(25,35,88);//成功 场景 5：insert into tb values(5,5,5);//阻塞(主键上的锁)
T3	commit;

本例子跟《查询条件走二级索引例子》区别在于 sessionA 是 select * ，因此需要回到主键索引查询所有字段，扫描了主键索引，所以也会在扫描到的索引进行加 next-key lock。该语句回表一次，扫描到是行是 id=10，所以加锁是(0,10],(10,20)，因此 sessionA 一共加了锁是索引 a 的(10,30)和主键索引的（0,20）。

• 场景 1，2 跟上一个例子一样命中索引 a 和主键索引的锁范围，阻塞
• 场景 3 因为 17 属于主键索引(10,20)之间，所以被阻塞
• 场景 4 不用索引 a 和主键索引的锁范围，所以成功。
• 场景 5，没命中索引 a 的锁，但是命中了主键上的锁范围，所以被主键索引上的锁阻塞