关于next-key范围查询加锁规则的实验

本实验基于MySQL8.0.18版本

在《MySQL45讲》定义了如下加锁规则：

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

初始化表：

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:

select * from t where id>10 and id<=15 for update;

思路：先找到id>10的第一条记录，即（15，15，15），加上next-key (10,15], 这时发现15已经达到右边界值，由于id是唯一索引，不用再往后找了（上面提到的bug在该种情况下已经解决），最终加锁范围为(10,15]。

验证结果表明确实如此，根 id>10 and id<=15的字面意思相符

例2:

select * from t where id>=10 and id<15 for update;

思路：先找到id>=10的第一条记录，即（10，10，10），加上next-key （5,10], 在这种情况下类似于优化1，退化成行锁10。然后再接着搜索下一行（15，15，15），加上next-key(10,15]，此时已不满足id<15的条件，所以最终的加锁范围为[10，15]

验证结果发现加锁范围为[10,15)，即在15这一行上没加行锁，上面的规则已经出现了问题，说明最后一个next-key退化成了间隙锁。 在这种情况下加锁范围还是与id>=10 and id<15 字面意思相符

例3:

select * from t where id>10 and id<=15 order by id desc for update;

思路：先找到id<=15的第一条记录，即(15,15,15)，加上next-key（10，15]，这时如果按照优化1规则，则退化成行锁15，接着往左搜索下一行（10，10，10），并加上next-key（5，10],所以最终的加锁范围为(5,10]行锁15;

验证结果表明最终的加锁范围为(5,10],(10,15],(16,20)，这说明了什么？
首先在倒序情况下优化1已经失效了，还是加的next-key（5，10],并且此时在已经搜索到id=15的情况下，还是继续搜到了下一行（20，20，20），说明那个bug在倒序的情况下依然存在。另外最后一个next-key也退化成了间隙锁。

例4:

select * from t where id>=10 and id<15 order by id desc for update;

验证结果表明最终的加锁范围为(0,5],(5,10],(10,15),这同样表明上面的bug还是存在的，在查到（10，10，10）后，还是会搜索下一行和上一行，但是15上依旧不会加行锁

例5：

select * from t where c>10 and c<=15 for update;

思路：首先找到c>10的第一条记录（15，15，15），加上next-key(10,15]，由于c非唯一索引，还要继续往下找，搜到下一行（20，20，20），加上next-key(15,20]，所以最终加锁结果为（10，15]（15，20]

验证结果表明最终加锁范围为（10，15],(15,20)，即在20上也没加行锁，并且发现此时加锁范围也与字面意思不同了

例6

select * from t where c>=10 and c<15 for update;

思路：首先找到c>=10的第一条记录（10，10，10），加上next-key(5,10]（由于c不是唯一索引，这里不能退化成行锁），接着搜到下一行（15，15，15），加上next-key（10,15]，最终加锁范围为(5,10]，（10,15]

验证结果表明最终的加锁范围为(5,10]，（10,15），在15这一行也没有加锁，跟例2出现了类似的问题。

例7

select * from t where c>10 and c<=15 order by c desc for update;

同例3，验证结果表明最终的加锁范围为(5,10],(10,15],(16,20)

例8

select * from t where c>=10 and c<15 order by c desc for update;

同例4，验证结果表明最终的加锁范围为(0,5],(5,10],(10,15)

综上所述，在范围查询时，在原来的基础上再添加以下原则：

1. 在正序查询时，如果查询条件满足优化1，依旧会退化成行锁；但在倒序查询时，不会采用优化1；
2. 在范围查询时，如果有不涉及的行，那么最后一个next-key会退化成间隙锁（这是一个比较好的优化，不会锁住表中已经存在的行，提高了并发度）
3. 在正序查询时，bug已经修复，如果发现唯一索引满足等值条件，不会再往下找；倒序查询时，bug依旧存在，在唯一索引满足等值条件时，依旧会往前或往后搜索。