MySQL 中关于gap lock（间隙锁） 、 next-key lock（间隙锁+行锁） 的一个问题

在学习 MySQL 的过程中遇到的一个关于锁的问题，包含多个 MySQL 相关的知识；相关资料在文章末尾

问题1描述

• 表初始化

CREATE TABLE z (
  id INT PRIMARY KEY AUTO_INCREMENT,
  b  INT,
  KEY b(b)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO z 
  (id, b)
VALUES 
  (1, 2),
  (3, 4),
  (5, 6),
  (7, 8),
  (9, 10);

当前表中的数据为：

表z的数据

• session A

BEGIN;
SELECT * FROM z WHERE b = 6 FOR UPDATE;

• session B

INSERT INTO z VALUES (2, 4);/*success*/
INSERT INTO z VALUES (2, 8);/*blocked*/
INSERT INTO z VALUES (4, 4);/*blocked*/
INSERT INTO z VALUES (4, 8);/*blocked*/
INSERT INTO z VALUES (8, 4);/*blocked*/
INSERT INTO z VALUES (8, 8);/*success*/
INSERT INTO z VALUES (0, 4);/*blocked*/
INSERT INTO z VALUES (-1, 4);/*success*/

分别执行 session B中的insert 会出现上述情况，为什么？

加锁过程

1. 在索引 b 上的等值查询，给索引 b 加上了 next-key lock (4, 6]；索引向右遍历，且最后一个值不满足条件时退化为间隙锁；所以会再加上间隙锁 (6,8)；所以索引 b 上的 next-key lock 的范围是(b=4,id=3)到(b=6,id=5)这个左开右闭区间和(b=6,id=5)到(b=8,id=7)这个开区间。（读起来有点绕口，看不懂的可以看下文中的图）
2. for update 会给 b = 6 这一行加上行锁；因此 (b=6,id=5) 这一行上有行锁

• 这么看来上述语句都不在锁的范围内，为什么会被锁

这个问题其实是因为没有理解索引的结构，所以认为所有值都不应该被锁

• 如图所示，此时索引 b 上的锁：

索引 b 锁范围.png

• 图中绿色部分即为被锁范围；索引会根据 b 和 id 的值进行排序，插入不同的值，锁的范围是不一样的；分别插入 (b=4,id=2) 和(b=4,id=4)，插入的位置如图所示：

(2,4)&(4,4)索引 b 锁范围.png

• 因为索引是有序的，此时，由于记录(b=4,id=3)的存在，(b=4,id=2)不在锁的范围内，可以插入，但(b=4,id=4)在锁的范围内，所以插入时需要等待锁释放，被 blocked
• 对于其他(id,b)的值(2,8),(4,8),(8,4),(8,8)也是同样的道理；因此，得出以下结论：
  • id <= 2 时，b 索引锁范围为 (4,8]
  • 2 < id < 8 时，b 索引锁范围为 [4,8]
  • a >= 8 时，b 索引锁范围为 [4,8)

(2,8),(4,8),(8,4),(8,8)索引 b 锁范围.png

• 但是，实践发现，当 id=0 时，被锁的范围为 [4,8)，这和我们得到的第一个结论(4,8]不一样；此时分析得到的示意图为：

(0,4),(0,8)索引 b 锁范围.png

• 在 session A 中尝试插入 (b=4, id=0)并查询结果：

INSERT INTO z VALUES (0, 4);

SELECT * FROM z;

• 此时，发现表中并没有发现 (b=4, id=0)这条记录，但是多了 (b=4,id=10)这条；所以插入 (b=4, id=0)的时候真正插入的是 (b=4,id=10)这个值；这是因为我们在创建表的时候指定主键 id 的值 AUTO INCREMENT，当插入的主键值为0的时候，会被替换为 AUTO_INCREMENT的值，即10

(0,4)插入后查询结果.png

• 对此，MySQL 官方文档中的解释是：在非 NO_AUTO_VALUE_ON_ZERO模式下，给自增的列赋值为 0，都会被替换为自增序列的下一个值；当该自增列值指定 NOT NULL 时赋值 NULL，也会被替换；当插入其他值时，自增序列的值会被替换为当前列中最大值的下一个值；参考 MySQL 8.0 Reference Manual 文档，Tutorial 的 Examples of Common Queries , 3.6.9 Using AUTO_INCREMENT

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.

If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.

• 如果将主键修改为不自增，插入 (b=4, id=0) 会得到这条记录

问题一的部分来自MySQL 中关于gap lock / next-key lock 的一个问题

问题2描述

问题二部分来自什么是间隙锁？到底锁了什么？

当前表中的数据为：

表z的数据

• session A

BEGIN;
SELECT * FROM z WHERE b = 6 FOR UPDATE;

• session B

UPDATE z SET b = 7 WHERE id = 7;/*blocked*/
UPDATE z SET id = 6 WHERE id = 8;/*blocked*/

分别执行 session B中的UPDATE会出现上述情况，为什么？

建表后，b字段上的2,4,6,8是以B+tree的数据结构出现，为了方便理解，这里我们不强调B+tree的结构，强调有序。索引b上的数据结图如下所示：

b索引

• 根据上文解释的上锁规则：

加锁过程

1. 在索引 b 上的等值查询，给索引 b 加上了 next-key lock (4, 6]；索引向右遍历，且最后一个值不满足条件时退化为间隙锁；所以会再加上间隙锁 (6,8)；所以索引 b 上的 next-key lock 的范围是(b=4,id=3)到(b=6,id=5)这个左开右闭区间和(b=6,id=5)到(b=8,id=7)这个开区间。（读起来有点绕口，看不懂的可以看下文中的图）
2. for update 会给 b = 6 这一行加上行锁；因此 (b=6,id=5) 这一行上有行锁

锁住的部分应该如下图所示

上锁

• 执行UPDATE z SET b = 7 WHERE id = 7;，会删掉(b=8,id=7)的索引且新增(b=7,id=7)的索引，新增部分根据索引有序的规则，将会落在锁住的部分区间，所以会被阻塞。

UPDATE z SET b = 7 WHERE id = 7;

• 执行UPDATE z SET id = 6 WHERE id = 8;，会将b索引上（b=8,id=8）叶子节点删掉，并增加(b=8,id=6)的叶子节点。可以看到(b=8,id=6)的叶子节点也落入锁住的部分区间，所以会被阻塞住。

UPDATE z SET id = 6 WHERE id = 8;

本文是作者根据日常业务场景，写出的一些解决问题或实施想法的历程。如有错误的地方，还请指出，相互学习，共同进步。