21为什么我只改一行的语句，锁这么多笔记

例证分析next-key lock加锁规则

author：陈镇坤27

创建时间：2021年11月24日17:51:23

编辑时间：2021年11月27日17:49:06、2021年12月1日14:16:12、2021年12月16日

转载请注明出处

————————————————————————————————

两个原则、两个优化、一个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锁住行数据。

问：普通的update语句，如果是使用的是普通索引，那么扫描时，对应普通索引的Id索引会加锁吗？加的话，加什么锁？

答：事务A：BEGIN; update t set c = 4 where c = 5;

事务B：update t set id = 4 where id = 5;

事务C：update t set id = 4 where id = 3;

结果，事务B阻塞，C成功。

根据是，事务A更新，扫描C索引树，也会扫描id索引树（为了更新数据），扫描到id索引树，仅对相关数据加行锁

3、主键索引范围锁

流程：

事务A的查询语句，首先根据id=10查找对应的数据，此时加next-key lock (5,10]

根据优化原则，next-key lock退化为行锁，为10；

此后范围查询id<11，扫描的范围加next-key lock，则扫描到索引id=15，加next-key（10,15]。

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会检测完毕后报错。

个人补充

问：下列事务A的语句执行后，事务B的语句能执行成功吗，为什么？

事务A

BEGIN;
update t set d = d + 1 where id = 10;

事务B

update t set c = c + 1 where c = 10;

事务B’

select c from t where c = 10 lock in share mode;

事务B’’

select d from t where c = 10 lock in share mode;

答：

结果:事务B阻塞，事务B’成功，事务C阻塞。

原理：事务A查询时，仅访问主键索引树，因为id是主键，所以加锁范围为10

事务B阻塞，是因为更新数据需要访问主键索引树，要访问id为10的节点，所以锁住了；

事务B’成功，是因为只需要查询普通索引树即可，不访问主键索引树；

事务B’'阻塞，是因为需要回表查询主键索引树，要访问id为10的节点，所以锁住了

实战

问：下面一个带排序的查询例子，为什么事务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：读提交的锁范围更小，锁时间更短。