MySQL 版本为 5.6.42 。有如下表:
CREATE TABLE tab(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`a` INT,
KEY `a`(`a`)
);
INSERT INTO `tt` (`id`, `a`)
VALUES
(1, 1),
(2, 4),
(3, 7),
(4, 10),
(5, 11);
SESSION 1:
START TRANSACTION;
SELECT * FROM tab WHERE a=7 FOR UPDATE;
SESSION 2:
INSERT INTO tab(a) VALUES(4); // blocking
INSERT INTO tab(a) VALUES(5); // blocking
INSERT INTO tab(a) VALUES(6); // blocking
INSERT INTO tab(a) VALUES(7); // blocking
INSERT INTO tab(a) VALUES(8); // blocking
INSERT INTO tab(a) VALUES(9); // blocking
INSERT INTO tab(a) VALUES(10); // success
我大概知道这里用到了 next-key lock,但是我从官方了解到的是 next-key lock 是 gap lock 加上 record lock,而 gap lock 定义的范围是索引前的间隙,在这里即 (4,7) 这个区间。
问题:为啥 INSERT INTO tab(a) VALUES(8); 这里的 8>7 在 (4,7) 区间之外也 block 了
参考 MySQL 官方文档:https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-next-key-locks
其中有一段关于 next-key lock
的描述:
A next-key lock on an index record also affects the “ gap ” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record.
补充一个新的问题:MySQL 啥时候会使用 gap 锁,啥时候使用 next-key 锁呢?
1
jimmzhou 2019-08-27 21:12:33 +08:00 via iPhone
锁住的区间是(4,10)
|
2
silenceeeee OP @jimmzhou 老哥,其实我从网上很多文章中已经了解到锁住的区间好像是(4,10) 但是你看我描述的最后一段引用,明明写的是 ```That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record```
a gap lock on the gap preceding the index record,我就是想不明白这个。 |
3
junnplus 2019-08-27 21:40:02 +08:00 1
查询辅助索引时,对存在的索引记录加 Record Lock,以及上一个键值和下一个键值开区间加 Gap Lock
|
4
sudden 2019-08-28 00:23:50 +08:00
哈哈,其实 mysql 文档没写错,next-key lock 就是 (4,7) ,这里 gap lock 是 7,至于为啥会锁住(7,10),因为 mysql 并不一定一次只会锁一个 next-key 啊!就比如你的例子,mysql 是锁了两个 next-key ((4,7) (7,10)) 加一个 gap ……
|
5
sudden 2019-08-28 00:40:29 +08:00
我上面的 gap lock 定义搞错了,重新表达一下哈,就是 锁了一个 next-key (4,7] 加一个 gap (7,10)
|
6
1424659514 2019-08-28 08:37:09 +08:00
记录 7 之前的区间(4, 7] 是 next key lock, 之后的区间 [7,10) 是 gap lock.
|
7
1424659514 2019-08-28 08:42:08 +08:00
"MySQL 啥时候会使用 gap 锁,啥时候使用 next-key 锁" 取决于 MySQL 还会不会继续往下查找满足条件的数据, MySQL 锁的记录或间隙的位置取决于它查询时判断到的最后一条记录.
举个栗子, 比如楼主的这个查询语句 "SELECT * FROM tab WHERE a=7 FOR UPDATE;" where 条件后面加一个 limit 1, 那么就不会锁(7, 10) 这个间隙 |
8
silenceeeee OP @sudden
首先非常感谢你的回复,能否告知一下你说的这个锁定 (4,10) 的规则在官网中哪里有讲细节呢?我在网上大概了解到的也是这样,但是在官网并没有找到相关细节。 |
9
silenceeeee OP @1424659514
感谢回复,但是这些原理在哪里能找到对应的描述细节的文档呢?主要是问题总是会遇到的,我想了解下大家的学习方法。 |
10
maoyusu 2019-08-28 09:10:55 +08:00 via Android
很简单,我去公司了给你解释一下。
|
11
silenceeeee OP @maoyusu 好的 期待
|
12
1424659514 2019-08-28 09:38:16 +08:00
@silenceeeee 跟朋友讨论的时候经过试验得出来的结果 =_=...
|
13
maoyusu 2019-08-28 09:47:05 +08:00 2
首先记住下面三个规则:
1.当查询的索引仅含有唯一索引的时候,Next-Key Lock 会降级为 Record Lock。(联合唯一索引,每一个索引列都要查) 2.InnoDB 还会对锁住的辅助索引加 Next-Key Lock,并且会给下一个键值加 Gap Lock。 3.插入某记录时候,会检查插入记录的下一条记录是否被锁住了,如果锁住了,则不允许插入(阻塞)。 然后来分析你的 SQL: SELECT * FROM tab WHERE a=7 FOR UPDATE; 会加如下几种锁: 1. id=3 的 Record Lock (规则 1) 2. a 属于 (4,7] 的 Next-Key Lock (规则 2 ) 3. a 属于 (7,10) 的 Gap Lock (规则 2 ) 根据规则 3,INSERT INTO tab(a) VALUES(4); 会阻塞 插入 5,6,7,8,9 被阻塞是因为 (4, 10)都上锁了。 10 会成功,因为 10 不在锁区间内。 |
14
silenceeeee OP @1424659514 好的,老铁,网上都这么说,我试出来也是如此。
|
15
silenceeeee OP @maoyusu 规则 2 在官网的文档中哪里有体现呢?老哥。我很疑惑这个。
|
16
simonlu9 2019-08-28 11:31:25 +08:00
看看极客时间的 mysql 锁环节,会有你想要的答案
|
17
silenceeeee OP @simonlu9 好的,谢谢。
|
18
titan2007 2019-08-28 18:26:57 +08:00
你得看目前的隔离级别是什么 next-key locking 只在 repeatable read 情况下才有
|
19
cs8814336 2019-09-20 15:30:07 +08:00
|
20
silenceeeee OP @cs8814336 还是没有人回答一个问题:官方说的 next-key lock 是 record-lock 加上 record 前面的 gap lock .可是实际测试的时候 record 后面的 gap 也被锁定了! 为什么会这样?
|
21
louettagfh 2021-01-19 11:11:14 +08:00
@silenceeeee 你理解的没有错 next-key lock 是 record lock 加 record 前面的 GAP lock. 但为什么你这个场景后面的 GAP 也锁住了呢?
因为表中的 `a` 字段不是二级唯一索引, 所以你执行 session 1 的时候会有 3 个锁: 1. 索引 `a` 上的 '7' 的 next-key lock. 2. 主键索引上 '3' 的 record lock. 3. 索引 `a` 上的 '10' 的 GAP lock. 即 [7, 10) InnoDB 的加锁逻辑是遍历到下一个 record 然后添加 GAP LOCK |
22
RedBlackTree 2021-02-02 17:26:04 +08:00 via iPhone
@simonlu9 你好,课程名什么呢?是丁奇的 mysql 实战吗?
|
23
RedBlackTree 2021-02-17 18:36:08 +08:00
@silenceeeee 主键的话锁住的是前一区间的 gap lock 加上 record lock ;普通索引的话除了这两个锁,还会锁住后一个区间的 gap lock 。你可以试试。
|