OUZHANBO

对于我这种菜鸡来说，毕业等于失业

0%

MySQL 加行锁的规则

发表于 更新于 分类于 阅读次数：
本文字数： 8k 阅读时长 ≈ 7 分钟

MySQL 加行锁的规则

实验环境

MySQL 的版本是 8.0.30

这里说下这是我原来测试其它功能的表，如果觉得数据太多不好分析可以把数据弄个 4-5 条就可以了，初始化数据的 sql 如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE `student`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `age` int NULL DEFAULT NULL,
  `test1` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_name`(`name` ASC) USING BTREE COMMENT 'name的唯一索引',
  INDEX `idx_age`(`age` ASC) USING BTREE COMMENT 'age的普通索引'
) ENGINE = InnoDB AUTO_INCREMENT = 101 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student` VALUES (1, '鲁班', 21, '1');
INSERT INTO `student` VALUES (2, '诸葛亮', 71, '1');
INSERT INTO `student` VALUES (3, '荆轲', 21, '1');
INSERT INTO `student` VALUES (4, '安其拉', 24, '1');
INSERT INTO `student` VALUES (5, '橘右京', 43, '1');
INSERT INTO `student` VALUES (6, '宋江', 22, '1');
INSERT INTO `student` VALUES (7, '钟馗', 23, '1');
INSERT INTO `student` VALUES (8, '林冲', 43, '1');
INSERT INTO `student` VALUES (9, '李逵', 12, '1');
INSERT INTO `student` VALUES (10, '王莽', 43, '1');
INSERT INTO `student` VALUES (11, '李世民', 54, '1');
INSERT INTO `student` VALUES (12, '张飞', 32, '1');
INSERT INTO `student` VALUES (13, '刘备2', 11, '1');
INSERT INTO `student` VALUES (14, '关羽', 43, '1');
INSERT INTO `student` VALUES (15, '王与', 21, '1');
INSERT INTO `student` VALUES (16, '秦叔宝', 43, '1');
INSERT INTO `student` VALUES (17, '程咬金', 65, '1');
INSERT INTO `student` VALUES (18, '嬴政', 76, '1');
INSERT INTO `student` VALUES (19, '王建', 23, '1');
INSERT INTO `student` VALUES (20, '张郃', 12, '1');
INSERT INTO `student` VALUES (21, '后裔', 12, '1');
INSERT INTO `student` VALUES (22, '孙悟空', 21, '1');
INSERT INTO `student` VALUES (23, '猪八戒', 22, '1');
INSERT INTO `student` VALUES (24, '沙和尚', 25, '1');
INSERT INTO `student` VALUES (25, '乾隆', 17, '1');
INSERT INTO `student` VALUES (26, '康熙', 51, '1');
INSERT INTO `student` VALUES (27, '朱元璋', 19, '1');
INSERT INTO `student` VALUES (28, '刘永', 12, '1');
INSERT INTO `student` VALUES (29, '张伟', 26, '1');
INSERT INTO `student` VALUES (30, '吕子乔', 28, '1');
INSERT INTO `student` VALUES (33, '刘备', 100, '1');
INSERT INTO `student` VALUES (36, '刘备4', 13, '2');

主键索引等值查询存在的记录

执行SELECT * FROM student WHERE id = 36 LOCK IN SHARE MODE;，之后执行 SELECT * FROM performance_schema.data_locks; 查看加锁情况（后面不会再重复查看加锁的语句）：

图片丢失

这里得出的结论是：使用主键索引等值查询，并且这条记录存在，只会对主键索引上的数据加 record 锁

主键索引等值查询不存在的记录

执行SELECT * FROM student WHERE id = 33 LOCK IN SHARE MODE;加锁的情况如下：

图片丢失

这里得出的结论是：使用主键索引等值查询，并且这条记录不存在时，会在所在条件所在的间隙加 gap 锁（例子里面锁的区间是(33,36)）

这里有个特殊情况就是如果数据落在表中最大值和 supremum 之间，比如执行SELECT * FROM student WHERE id = 37 LOCK IN SHARE MODE;，那么加锁的范围会是(36,supremum]，个人认为这里不用 gap 锁而是用 next-key 锁的原因是加锁的基本单位是 next-key 锁，只是根据情况降级到 record 锁和 gap 锁，但是在这里给 supremum加不加 record 锁没区别，所以就不需要锁降级

图片丢失

主键索引范围查询并且范围内存在记录

执行SELECT * FROM student WHERE id > 29 AND id < 33 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE id >= 29 AND id < 33 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE id > 29 AND id <= 33 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE id >= 29 AND id <= 33 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里得出的结论是：使用主键索引范围查询，并且范围内存在记录，会对符合条件的记录加 next-key 或者 record 锁，对剩余未加锁的间隙的下一条记录加 gap 锁

不过有一种特殊情况，执行SELECT * FROM student WHERE id > 29 AND id <= 36 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

按理来说不应该给(36,supremum]加锁，

在《MySQL 实战 45 讲》中作者给行锁加锁规则总结了“两个原则”、“两个优化”和“一个 bug”（《MySQL 实战 45 讲》的作者说的是截止到当时最新的 MySQL 版本：5.x 系列<=5.7.24，8.0 系列 <=8.0.13），其中的一个 bug 就是唯一索引上的范围查询会访问到不满足条件的第一个值为止，也就是在上面的例子中虽然扫描到了id=36的索引，还要继续向后扫描，所以还要对supremum加 next-key 锁（虽然 MySQL 说在 8.0.18 版本已经修复这个bug（关键词 Bug #29508068），但是对于一部分的查询还是有这个 bug，就如上面的例子的之后的非主键唯一索引和非唯一索引范围查询）。

主键索引范围查询并且范围内不存在记录

执行SELECT * FROM student WHERE id > 33 AND id < 36 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里得出的结论是：使用主键索引范围查询，并且范围内不存在记录，会对查询条件所在范围的下一条记录加 gap 锁

有一种特殊情况，执行SELECT * FROM student WHERE id > 36 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里可以也是上面说的不需要降级所以给加的是 next-key 锁

非主键唯一索引等值查询存在的记录

执行SELECT * FROM student WHERE name = '刘备4' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里得出的结论是：使用非主键唯一索引等值查询，并且这条记录存在时，会对非主键唯一索引上查到的记录加 record 锁，还会给对应的主键索引的记录加上 record 锁

还有特殊情况，执行SELECT id, name FROM student WHERE name = '刘备4' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT id, name FROM student WHERE name = '刘备4' FOR UPDATE;，加锁情况如下：

图片丢失

发现如果只是使用共享锁并且是索引覆盖查询是不需要对主键索引对于的记录加 record 锁的，但是排它锁查询是不管是否索引覆盖都给对应的主键索引加 record 锁

非主键唯一索引等值查询不存在的记录

执行SELECT * FROM student WHERE name = '刘备3' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里得出的结论是：使用非主键唯一索引等值查询，并且这条记录不存在时，会对非主键唯一索引上查询条件所在间隙的下一条记录加 gap 锁

又是锁不需要降级所以是加 next-key 锁的特殊情况，执行SELECT * FROM student WHERE name = '鲁班1' LOCK IN SHARE MODE;

图片丢失

非主键唯一索引范围查询并且范围内存在记录

执行SELECT * FROM student WHERE name > '刘备' AND name < '刘备4' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE name >= '刘备' AND name < '刘备4' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE name > '刘备' AND name < '刘备4' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE name >= '刘备' AND name <= '刘备4' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

如果只是看非主键唯一索引加锁的情况会发现 bug

  1. name < '刘备4'原本应该锁的是(刘备2,刘备4)，但是现在多出了一个 record 锁，变成了(刘备2,刘备4]
  2. name <= '刘备4原本应该锁的是(刘备2,刘备4]，但是现在多出来一个(刘备4,刘永]
  3. name > '刘备'原本应该就只是给刘备这条记录加 record 锁，但是多出来了一个 gap 锁，变成了(关羽,刘备]

这就是前面主键索引范围查询并且范围内存在记录中说的那个 bug 导致的

然后再看主键索引上的加锁情况发现主键索引上加锁是正常的

  1. name < '刘备4'在非主键唯一索引上加锁的范围是(刘备2,刘备4]，但是主键索引上并未对刘备4对应的记录 record 锁
  2. name <= '刘备4在非主键唯一索引上加锁的范围是(刘备4,刘永]，但是主键索引上并未对刘永对应的记录 record 锁

这里得出的结论是：使用非主键唯一索引范围查询，并且范围内存在记录，会对符合条件的记录加 next-key（上面的name > '刘备'的 bug），会对不满足条件的第一个值记录加上 next-key 锁（上面的name < '刘备4'和name <= '刘备4），在主键索引上，会对在查询范围内记录对应的主键索引上加 record 锁

而且上面的这个现象还和你索引的排序有关，如果你执行把索引idx_name改成降序，再执行SELECT * FROM student WHERE name >= '刘备' AND name <= '刘备4' LOCK IN SHARE MODE;，加锁就会变成下面这种情况：

图片丢失

非主键唯一索引范围查询并且范围内不存在记录

执行SELECT * FROM student WHERE name > '刘备1' AND name < '刘备2' LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里面加锁逻辑其实和上面的非主键唯一索引范围查询并且范围内存在记录加锁的一样的，都有那个 bug 的问题，区别就是查不到记录所以不会给主键索引加锁

这里得出的结论是：使用非主键唯一索引范围查询，并且范围内不存在记录，会对查询条件所在间隙的下一条记录加 next-key 锁

非唯一索引等值查询存在的记录

执行SELECT * FROM student WHERE age = 12 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里得出的结论是：使用非唯一索引等值查询，并且这条记录存在时，在非唯一索引上，会对符合查询条件的记录加 next-key 锁，会对不满足条件的第一个值记录加上 gap 锁，在主键索引上，对符合查询条件的记录在主键索引对应的位置上加 reocrd 锁

非唯一索引等值查询不存在的记录

执行SELECT * FROM student WHERE age = 18 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里得出的结论是：使用非唯一索引等值查询，并且这条记录不存在时，在非唯一索引上，会对符合查询条件所在间隙的下一条记录加 gap 锁

这里也有那个锁没降级的情况，不在赘述了

非唯一索引范围查询并且范围内存在记录

执行SELECT * FROM student WHERE age >= 12 AND age < 13 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE age > 12 AND age <= 13 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

执行SELECT * FROM student WHERE age >= 12 AND age <= 13 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

如果只是看非唯一索引加锁的情况会发现 bug

  1. age < 13原本应该锁的是(12,13)，但是现在多出了一个 record 锁变成了(12,13]
  2. age <= 13 原本应该锁的是(12,13]，但是现在多出了一个 next-key 锁(13,17]

这就是前面主键索引范围查询并且范围内存在记录中说的那个 bug 导致的

然后再看主键索引上的加锁情况发现主键索引上加锁是正常的

  1. age < 13'在非唯一索引上加锁的范围是(12,13]，但是主键索引上并未对13对应的记录 record 锁
  2. age <= 13在非唯一索引上加锁的范围是(13,17]，但是主键索引上并未对17对应的记录 record 锁

这里得出的结论是：使用非唯一索引范围查询，并且范围内存在记录，会对符合条件的记录加 next-key，会对不满足条件的第一个值记录加上 next-key 锁（上面的age < 13'和age <= 13），在主键索引上，会对在查询范围内记录对应的主键索引上加 record 锁

不过这里要说一下age > 12的情况，因为 age 是 12 的有多条，但是这里(12,13]的 12 这条记录对应的是 id 最大的那条，这里对应的就是 id=28 的这条，原因也很简单因为我的索引是升序排序的，如果相同的情况下会根据 id 再升序排序，如果你这里把索引的排序改成降序，那么索引值相同的情况下还是会根据 id 升序排序，下面把 age 的排序改成降序，执行SELECT * FROM student WHERE age > 12 AND age <= 13 LOCK IN SHARE MODE;后加锁情况如下：

图片丢失

如果是 age 相同的情况下是按照 id 降序的话，这里应该锁显示的 LOCK_DATA 显示的是12,28

所以根据上面的情况可以发现age > 12并且索引升序的情况下可以插入 age=12 的记录只是要求 id 小于最大的值就行，在这里就是 id 小于 28，age < 13也是一样的逻辑只是变成 id 要大于最小值

非唯一索引范围查询并且范围内不存在记录

执行SELECT * FROM student WHERE age > 12 AND age < 13 LOCK IN SHARE MODE;，加锁情况如下：

图片丢失

这里面加锁逻辑其实和上面的非唯一索引范围查询并且范围内存在记录加锁的一样的，都有那个 bug 的问题，区别就是查不到记录所以不会给主键索引加锁

这里得出的结论是：使用非唯一索引范围查询，并且范围内不存在记录，会对查询条件所在间隙的下一条记录加 next-key 锁

不使用索引查询

分别执行

SELECT id,name FROM student WHERE test1 = 2 LOCK IN SHARE MODE;

SELECT id,name FROM student WHERE test1 > 2 LOCK IN SHARE MODE;

加锁情况如下：

图片丢失

这里图片截不全，表中每一条记录包括最大界限伪记录 supremum 都加了锁 next-key 锁

这里得出的结论是：不使用索引查询时，无论是等值查询还是范围查询，无论是否存在查询结果，都会对表中所有的记录加 next-key 锁，也就是我们常说的“锁表”