例证分析next-key lock加锁规则
author：陈镇坤27
创建时间：2021年11月24日17:51:23
转载请注明出处
文章目录
————————————————————————————————
两个原则、两个优化、一个bug
默认隔离级别RR
问：简单介绍一下next-key lock的加锁规则。
答：口诀：两个原则、两个优化、一个bug
1）原则：前开后闭区间；
2）原则：对查找到的访问索引对象加锁（首先是where的索引对象，其次是select的索引对象）；
3）优化：索引上等值查询，给唯一索引加锁时，next-key lock退化为行锁；
4）优化：索引上等值查询，向右遍历时到最后一个索引不满足等值条件时，next-key lock退化为间隙锁；
5）bug：8.0以前，唯一索引范围查询会访问到不满足条件的第一个值。
不同的例子论证加锁
表结构：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
1、等值查询
事务A
begin;
update t set d = d + 1 where id = 7;
事务B
insert into t values (8,8,8);
事务C
update t set d = d + 1 where id = 15；
结果：事务B阻塞。事务C成功。
流程：
事务A，等值查询，筛选条件id为唯一索引，索引数据查询落地在id索引树的5和10之间，在此区间加next-key lock，则为（5,10]；
由于10不满足于等值条件，因此退化为间隙锁，（5,10）。
2、非唯一索引等值查询
此处特意以覆盖索引、lock in share mode举例，涵盖这两处场景。
事务A
BEGIN;
select id from t where c = 5 lock in share mode;
事务B
update t set d = d + 1 where id = 5;
事务C
insert into t values (7,7,7)
结果：事务B成功。事务C阻塞。
流程：事务A，等值查询，查出索引数据c的行数据，给对象（索引对象）加next-key lock，此时为（0,5]
由于c是普通索引，索引InnoDB会判断下一个索引10是否满足条件，此时加next-key lock，此时为（0,5]和(5,10]
由于10不满足条件，所以去该(5,10]next-key lock退化为间隙锁（5,10）
又因为此时查询的列被索引覆盖，所以锁只加在了索引c上，因此，事务B更新主键索引树成功，而事务C失败。
PS：如果行锁是for update，MySQL会默认接下来需要更新，会提前为主键索引也添加next-key lock
问：加行锁使用lock in share mode需要注意什么？
答：如果查询语句使用覆盖索引，那么行数据并不会真正被锁住，此时可以新增一个不存在的字段来打破覆盖索引，让next-key lock锁住行数据。
3、主键索引范围锁
流程：
事务A的查询语句，首先根据id=10查找对应的数据，此时加next-key lock (5,10]
根据优化原则，next-key lock退化为行锁，为10；
此后范围查询，扫描的范围加next-key lock，则（10,15]
此处因为是范围查询，第一个不满足的值理论上不应该加行锁，但8.0以前仍然有加行锁，在8.0后修复了这个bug。
4、非唯一索引范围锁
流程：事务A的c=10，因此next-key为（5,10]，不会发生退化；
继续扫描，直到扫描到15，此时判定不满足条件，不会继续扫描了，所以就只再增加一个next-key锁，为（10,15]。
5、唯一索引范围锁bug
流程：添加next-key lock为(10,15]，由于存在bug，所以会继续向后找到第一个不满足条件的值为止（8.0.21解决主键的，8.0.22解决唯一索引的），此时找到的是20，则增添一个next-key lock为(15,20]。
6、非唯一索引存在等值的例子
数据准备：
insert into t values(30,10,30);
流程：等值查询，先加锁(5,10]；
向后扫描，加锁(10,10]，继续向下判断，发现(10,15]
由于是等值查询，所以15行锁退化。
最后加锁范围如下图
7、limit语句加锁
此时c=10的数据有两条
流程：c=10，加锁，(5,10]，继续查找，(10,10],判断满足limit 2，结束扫描。
8、间隙锁导致死锁
流程：事务A加next-key lock(5,10]，而事务B想要加next-key lock(5,10]，事务A的insert要等待事务B的间隙锁释放，而事务B虽然加间隙锁成功了，但行锁要等待事务A释放，因此发生死锁。
由于有死锁检测，因此后执行的SessionB会检测完毕后报错。
实战
问：下面一个带排序的查询例子，为什么事务B会失败。
答：首先，因为排序字段是索引c，且是降序，所以优化器选择使用c<=20索引执行，扫描从右边开始向左结束。
先获取20的值，加(20,25)间隙锁，(15,20]，（10,15]，还要再扫描10才能判断其是否符合条件，所以加锁(5,10]。
此外，因为select * ，所以回表的时候主键索引对象10,15,20会上行锁。
问：读提交情况下，加锁会很快释放掉不符合条件的行锁，那下面的一个例子中，事务B为什么没有被阻塞？
数据a，b都是递增的，无索引。
session A：
begin;
update t set a=6 where b=1;
session B：
begin;
update t set a=7 where b=2;
答：RC下，update会执行semi-consistent优化读优化。如果扫描过程中，遇到被锁住的行数据，则会读取该数据数据最新版本，如果满足查询条件，则等待，否则直接跳过。
这也很符合读提交很快释放掉不符合条件的行锁的规则。上面例子中，两个事务查询的条件都不一致。彼此不会有相互等待的时机。
PS：读提交的锁范围更小，锁时间更短。