MySQL next-key lock 请教

[silenceeeee]

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 锁呢？

[jimmzhou]

锁住的区间是(4,10)

[silenceeeee]

@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，我就是想不明白这个。

[junnplus]

查询辅助索引时，对存在的索引记录加 Record Lock，以及上一个键值和下一个键值开区间加 Gap Lock

[sudden]

哈哈，其实 mysql 文档没写错，next-key lock 就是 (4,7) ，这里 gap lock 是 7，至于为啥会锁住(7，10)，因为 mysql 并不一定一次只会锁一个 next-key 啊！就比如你的例子，mysql 是锁了两个 next-key ((4,7) (7，10)) 加一个 gap ……

[sudden]

我上面的 gap lock 定义搞错了，重新表达一下哈，就是 锁了一个 next-key (4,7] 加一个 gap (7，10)

[1424659514]

记录 7 之前的区间(4, 7] 是 next key lock, 之后的区间 [7,10) 是 gap lock.

[1424659514]

"MySQL 啥时候会使用 gap 锁，啥时候使用 next-key 锁" 取决于 MySQL 还会不会继续往下查找满足条件的数据, MySQL 锁的记录或间隙的位置取决于它查询时判断到的最后一条记录.
举个栗子, 比如楼主的这个查询语句 "SELECT * FROM tab WHERE a=7 FOR UPDATE;" where 条件后面加一个 limit 1, 那么就不会锁(7, 10) 这个间隙

[silenceeeee]

@sudden
首先非常感谢你的回复，能否告知一下你说的这个锁定 (4,10) 的规则在官网中哪里有讲细节呢？我在网上大概了解到的也是这样，但是在官网并没有找到相关细节。

[silenceeeee]

@1424659514
感谢回复，但是这些原理在哪里能找到对应的描述细节的文档呢？主要是问题总是会遇到的，我想了解下大家的学习方法。

[maoyusu]

很简单，我去公司了给你解释一下。

[silenceeeee]

@maoyusu 好的 期待

[1424659514]

@silenceeeee 跟朋友讨论的时候经过试验得出来的结果 =_=...

[maoyusu]

首先记住下面三个规则：
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 不在锁区间内。

[silenceeeee]

@1424659514 好的，老铁，网上都这么说，我试出来也是如此。

[silenceeeee]

@maoyusu 规则 2 在官网的文档中哪里有体现呢？老哥。我很疑惑这个。

[simonlu9]

看看极客时间的 mysql 锁环节，会有你想要的答案

[silenceeeee]

@simonlu9 好的，谢谢。

[titan2007]

你得看目前的隔离级别是什么 next-key locking 只在 repeatable read 情况下才有

[cs8814336]

https://www.v2ex.com/t/538916#reply26

[silenceeeee]

@cs8814336 还是没有人回答一个问题：官方说的 next-key lock 　是　 record-lock 加上　 record 　前面的 gap lock ．可是实际测试的时候 record 后面的 gap 也被锁定了！　为什么会这样？

[louettagfh]

@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

[RedBlackTree]

@simonlu9 你好，课程名什么呢？是丁奇的 mysql 实战吗？

[RedBlackTree]

@silenceeeee 主键的话锁住的是前一区间的 gap lock 加上 record lock ；普通索引的话除了这两个锁，还会锁住后一个区间的 gap lock 。你可以试试。