关于mysql:MySQL-nextkey-lock-加锁范围是什么

前言

某天，忽然被问到 MySQL 的 next-key lock，我霎时的反馈就是：

这都是啥啥啥？？？

这一个截图我啥也看不出来呀？

认真一看，如同似曾相识，这不是《MySQL 45 讲》外面的内容么？

什么是 next-key lock

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

官网的解释大略意思就是：next-key 锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

先给本人来一串小问号？？？

1. 在主键、惟一索引、一般索引以及一般字段上加锁，是锁住了哪些索引？
2. 不同的查问条件，别离锁住了哪些范畴的数据？
3. for share 和 for update 等值查问和范畴查问的锁范畴？
4. 当查问的等值不存在时，锁范畴是什么？
5. 当查问条件别离是主键、惟一索引、一般索引时有什么区别？

既然啥都不懂，那只好从头开始操作实际一把了！

先看看看《MySQL 45 讲》中丁奇老师的论断：

看了这论断，应该能够解答一大部分问题，不过有一句十分十分重点的话须要关注：MySQL 前面的版本可能会扭转加锁策略，所以这个规定只限于截止到当初的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13

所以，以上的规定，对当初的版本并不一定实用，上面我以 MySQL 8.0.25 版本为例，进行多角度验证 next-key lock 加锁范畴。

环境筹备

MySQL 版本：8.0.25

隔离级别：可反复读（RR）

存储引擎：InnoDB

mysql> select @@global.transaction_isolation,@@transaction_isolation\G
mysql> show create table t\G

如何应用 Docker 装置 MySQL，能够参考另一篇文章《应用 Docker 装置并连贯 MySQL》

主键索引

首先来验证主键索引的 next-key lock 的范畴

此时数据库的数据如图所示，对主键索引来说此时数据间隙如下：

主键等值查问 —— 数据存在

mysql> begin; select * from t where id = 10 for update;

这条 SQL，对 id = 10 进行加锁，能够先思考一下加了什么锁？锁住了什么数据？

能够通过 data_locks 查看锁信息，SQL 如下：

# mysql> select * from performance_schema.data_locks;
mysql> select * from performance_schema.data_locks\G

具体字段含意能够参考 官网文档

后果次要蕴含引擎、库、表等信息，咱们须要重点关注以下几个字段：

• INDEX_NAME：锁定索引的名称
• LOCK_TYPE：锁的类型，对于 InnoDB，容许的值为 RECORD 行级锁 和 TABLE 表级锁。
• LOCK_MODE：锁的类型：S, X, IS, IX, and gap locks
• LOCK_DATA：锁关联的数据，对于 InnoDB，当 LOCK_TYPE 是 RECORD（行锁），则显示值。当锁在主键索引上时，则值是锁定记录的主键值。当锁是在辅助索引上时，则显示辅助索引的值，并附加上主键值。

后果很显著，这里是对表增加了一个 IX 锁 并对主键索引 id = 10 的记录，增加了一个 X,REC_NOT_GAP 锁，示意只锁定了记录。

同样 for share 是对表增加了一个 IS 锁并对主键索引 id = 10 的记录，增加了一个 S 锁。

能够得出结论：

对主键等值加锁，且值存在时，会对表增加意向锁，同时会对主键索引增加行锁。

主键等值查问 —— 数据不存在

mysql> select * from t where id = 11 for update;

如果是数据不存在的时候，会加什么锁呢？锁的范畴又是什么？

在验证之前，剖析一下数据的间隙。

1. id = 11 是必定不存在的。然而加了 for update，这时须要加 next-key lock，id = 11 所属区间为 (10,15] 的 前开后闭 区间；
2. 因为是 等值查问，不须要锁 id = 15 那条记录，next-key lock 会进化为间隙锁；
3. 最终区间为 (10,15) 的前开后开区间。

应用 data_locks 剖析一下锁信息：

看下锁的信息 X,GAP 示意加了间隙锁，其中 LOCK_DATA = 15，示意锁的是 主键索引 id = 15 之前的间隙。

此时在另一个 Session 执行 SQL，答案不言而喻，是 id = 12 不能够插入，而 id = 15 是能够更新的。

能够得出结论，在数据不存在时，主键等值查问，会锁住该主键查问条件所在的间隙。

主键范畴查问（重点）

mysql> begin; select * from t where id >= 10 and id < 11 for update;

依据《MySQL 45 讲》剖析得出上面后果：

1. id >= 10 定位到 10 所在的区间 (10,+∞)；
2. 因为是 >= 存在等值判断，所以须要蕴含 10 这个值，变为 [10,+∞) 前闭后闭区间；
3. id < 11 限定后续范畴，则依据 11 判断下一个区间为 15 的 前开后闭 区间；
4. 联合起来则是 [10,15]。（不完全正确）

先看下 data_locks

能够看到除了表锁之外，还有 id = 10 的行锁（X,REC_NOT_GAP）以及主键索引 id = 15 之前的间隙锁（X,GAP）。

所以实际上 id = 15 是能够进行更新的。也就是说 前开后闭区间 呈现了问题，集体认为应该是 id < 11 这个条件判断，导致不须要进行了锁 15 这个行锁。

后果验证也是正确的，id = 12 插入阻塞，id = 15 更新胜利。

当范畴的右侧是蕴含等值查问呢？

mysql> begin; select * from t where id > 10 and id <= 15 for update;

来剖析一下这个 SQL：

1. id > 10 定位到 10 所在的区间 (10,+∞)；
2. id <= 15 定位是 (-∞, 15]；
3. 联合起来则是 (10,15]。

同样先看一下 data_locks

能够看出只增加了一个主键索引 id = 15 的 X 锁。

验证下 id = 15 是否能够更新？再验证 id = 16 是否能够插入？

事实证明是没有问题的！

当然，这里有小伙伴会说，在《MySQL 45 讲》外面说这里有一个 bug，会锁住下一个 next-key。

事实证明，这个 bug 曾经被修复了。修复版本为 MySQL 8.0.18。然而并没有齐全修复！！！

参考链接地址：

https://dev.mysql.com/doc/rel…

搜寻关键字：Bug #29508068)

咱们能够别离用 8.0.17 进行复现一下：

在 8.0.17 中 id <= 15 会将 id = 20 这条数据也锁着，而在 8.0.25 版本中则不会。所以这个 bug 是被修复了的。

再来看下是 前开后闭 还是 前开后开 的问题，谨严一下，应用 8.0.17 和 8.0.18 做比拟。

当初我预计大概率是在 8.0.18 版本修复 Bug #29508068 的时候，把这个 前开后闭 给优化成了 前开后开 了。

比照 data_locks 数据：

留神红色下划线局部，在 8.0.17 版本中 id < 17 时 LOCK_MODE 是 X，而在 8.0.25 版本中则是 X,GAP。

总结

本文次要通过实际操作，对主键加锁时的 next-key lock 范畴进行了验证，并查阅材料，比照版本得出不同的论断。

论断一：

1. 加锁时，会先给表增加意向锁，IX 或 IS；
2. 加锁是如果是多个范畴，是离开加了多个锁，每个范畴都有锁；（这个能够实际下 id < 20 的状况）
3. 主键等值查问，数据存在时，会对该主键索引的值加行锁 X,REC_NOT_GAP；
4. 主键等值查问，数据不存在时，会对查问条件主键值所在的间隙增加间隙锁 X,GAP；

5. 主键等值查问，范畴查问时状况则比较复杂：

   1. 8.0.17 版本是前开后闭，而 8.0.18 版本及当前，进行了优化，主键时判断不等，不会锁住后闭的区间。
   2. 临界 <= 查问时，8.0.17 会锁住下一个 next-key 的前开后闭区间，而 8.0.18 及当前版本，修复了这个 bug。

优化后，导致后开，这个不晓得是因为优化后，主键的区间会间接后开，还是因为是个 bug。具体小伙伴能够尝试一下。

论断二

通过应用 select * from performance_schema.data_locks; 和操作实际，能够看出 LOCK_MODE 和 LOCK_DATE 的关系：

1. LOCK_MODE = X 是前开后闭区间；
2. X,GAP 是前开后开区间（间隙锁）；
3. X,REC_NOT_GAP 行锁。

根本曾经摸清主键的 next-key lock 范畴，留神版本应用的是 8.0.25。

疑难

1. 那惟一索引的 next-key lock 范畴是什么?
2. 当索引笼罩时锁的范畴和加锁的索引别离是什么？
3. 我为什么说这个 bug 没有齐全修复，也是在非主键惟一索引中复现了这个 bug​。

文章篇幅无限，小伙伴能够先本人思考一下，尽量本人操作试一试，实际出真知。至于具体答案，那就须要下一篇文章进行验证并总结论断了。