背景

MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列<=5.7.24，8.0 系列 <=8.0.1，实验发现不同版本的差异主要是：索引范围查询的临界值的取舍有所不同。8.0范围查询加锁不包含临界值，7.0范围查询加锁包含临界值。

Next-Key Lock的规则

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。

原则 2：只有访问到的对象才会加锁。

优化 1：索引上的等值查询，

    命中唯一索引，退化为行锁。

    命中普通索引，左右两边的GAP Lock + Record Lock。

优化 2：

    索引上的等值查询，未命中，所在的Next-Key Lock，退化为GAP Lock 。

索引在范围查询： 

    1.等值和范围分开判断。

    2.索引在范围查询的时候 都会访问到所在区间不满足条件的第一个值为止。

    3.如果使用了倒序排序，按照倒序排序后，检索范围的右边多加一个GAP。哪个方向还有命中的等值判断，再向同方向拓展外开里闭的区间。

构造数据

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

分析：

根据原则1，加锁都是next-key lock,所以id=7时所在的Next-Key Lock是(5,10]。

根据优化2，等值查询且未命中表中的一行数据，所以next-key lock退化成GAP lock (5,10)

所以最后锁的区间是(5,10)。

案例2

分析：

根据优化1，c普通索引，加的是锁(0,5)+5+(5,10)

根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

所以最后的锁范围是(0,10)。

案例3

分析：

索引范围查询，等值和范围分开判断。所以对10 < c < 11 和 c = 10分别判断。

范围查询 10 < c < 11，在c的范围内在的Next-Key Lock是(10,15]。拿所在区间内不满足条件的第一个值，作为加锁范围的临界值。10和15是区间中第一个不满足条件10 < c < 11的值，所以锁定的范围是(10,15]。

等值查询 c = 10，c是普通索引，所以根据优化2，锁的范围是(5,10)+10+(10,15).。

所以把上面两种情况合起来锁的范围就是(5,15]。

注意：

刚才是mysql7.0版本所以范围查询锁定的区间是(10,15]。如果是8.0范围查询锁定的是(10,15)。区别在于范围查询加锁的时候7.0版本加的是next-key lock。8.0版本范围查询加锁加的是GAP Lock。

案例4

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

分析：

倒排序后是:20 , 15 , 10 , 5 , 0

按照倒序排序后，查询范围是 10<c<15 , 所以锁的范围是(15,10]。

按照倒序排序后，检索范围的右边多加一个GAP区间。所以加一个(10,5)。

哪边还有命中的等值判断，再向同方向拓展外开里闭的区间。15在左边且有等值命中，所以再向同方向拓展一个外开里闭区间(20,15]。

唯一索引等值查询。id =15命中退化成Record Lock。

所以上面所有的范围是:(20,15] + 15 + (15,10] + (10,5)。

最终加锁范围是:(20,5)

案例5

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

分析：

倒排序后是:20 , 15 , 10 , 5 , 0

按照倒序排序后，查询范围是 10<c<15 , 所以锁的范围是(15,10]。

按照倒序排序后，检索范围的右边多加一个GAP区间。所以加一个(10,5)。

哪边还有命中的等值判断，再向同方向拓展外开里闭的区间。10在右边且有等值命中，再向同方向拓展外开里闭的区间，因为已经加了一个(10,5)，所以在原基础上再向右推展一个外开里闭区间[5,0)。

唯一索引等值查询。id =15命中退化成Record Lock。

所以上面所有的范围是:(15,10] + (10,5) + [5,0)。

最终加锁范围是:(15,0)

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

版权声明：本文为CSDN博主「大阔龙」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/wang11yangyang/article/details/118087876