author：陈镇坤27
创建时间：2021年11月24日17:51:23
转载请注明出处
文章目录- 例证分析next-key lock加锁规则
- 问：简单介绍一下next-key lock的加锁规则。
- 不同的例子论证加锁
- 1、等值查询
- 2、非唯一索引等值查询
- 问：加行锁使用lock in share mode需要注意什么？
- 3、主键索引范围锁
- 4、非唯一索引范围锁
- 5、唯一索引范围锁bug
- 6、非唯一索引存在等值的例子
- 7、limit语句加锁
- 8、间隙锁导致死锁
- 个人补充
- 问：下列事务A的语句执行后，事务B的语句能执行成功吗，为什么？
- 实战
- 问：下面一个带排序的查询例子，为什么事务B会失败。
- 问：读提交情况下，加锁会很快释放掉不符合条件的行锁，那下面的一个例子中，事务B为什么没有被阻塞？
————————————————————————————————
两个原则、两个优化、一个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]，此处因为是范围查询，所以不会像唯一索引的
等值查询一样匹配优化规则——退化为间隙锁。
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：读提交的锁范围更小，锁时间更短。
