关于mysql:看来MySQL-nextkey-lock-的-bug-并没有被修复

前言

在上一篇文章《MySQL 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。

这篇文章会对非主键惟一索引进行操作实际。

数据库表数据

CREATE TABLE `t` (
  `id` int NOT NULL COMMENT '主键',
  `a` int DEFAULT NULL COMMENT '惟一索引',
  `c` int DEFAULT NULL COMMENT '一般索引',
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_a` (`a`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

数据库数据如下：

数据库的字段 a 是惟一索引。

非主键惟一索引

非主键惟一索引等值查问 —— 数据存在

mysql> begin; select * from t where a = 110 for update;

剖析一下这条 SQL：

1. select * 查问条件必定会回表；
2. 惟一索引，所以定位到数据后不须要持续查问；
3. 猜想是锁住惟一索引及对应的主键索引。

查看 data_locks

1. 表锁 IX；
2. 索引 uniq_a 下面加了 X,REC_NOT_GAP 行锁，其中 110, 10 示意是 a = 110 这行数据，前面的 10 是这行数据对应的主键；
3. 主键 id = 10 上增加了 X,REC_NOT_GAP 行锁。

所有和剖析的一样。

如果把 for update 换成 for share，其实也是雷同，在主键和惟一索引上都加了锁。

这里执行的 SQL 都是 select *，如果替换为 select id 呢？

mysql> begin; select id from t where a = 110 for update;

剖析一下这条 SQL：

1. select id 查问，满足索引笼罩，不会回表；
2. 惟一索引，所以定位到数据后不须要持续查问；
3. 那这里是锁主键索引还是两个都锁？

所以看出并无什么区别。

把 for update 换成 for share，这时候区别来了：

只有两条锁记录：表意向锁和 uniq_a 索引的 S,REC_NOT_GAP 锁。

很显著，for share 笼罩索引时，只是对本人的索引加锁。

update t set c = 2101 where id = 10;

这时候应用主键更新 c 是否能更新？那上面两个 SQL 呢？

update t set a = 1101 where id = 10;

update t set c = 2101 where a = 110;

执行后果很显然，第一个能够执行，而后两个是会阻塞的。

所以，非主键惟一索引等值查问，数据存在，for update 是会在主键加锁的，而 for share 只有在走笼罩索引的状况下，会仅在本人索引上加锁。

非主键惟一索引等值查问 —— 数据不存在

mysql> begin; select * from t where a = 111 for update;

剖析这一条 SQL：

1. 首先加了 for update，必定会在 索引 uniq_a 和 主键索引上都加上锁；
2. 字段 a 具备唯一性，然而数据 a = 111 不存在，会始终查，查到 115 区间；
3. 所以会加索引 uniq_a 和 主键索引的间隙锁。（并不对）

事实证明，剖析后果不正确。

并且我执行 update t set c = 2101 where id = 15; 也过了。

所以是不是能够了解为，非主键索引等值查问，数据不存在，相当于一个范畴查问，仅仅会在非主键索引上加锁，加的还是间隙锁，前开后开区间；

如果此时走索引笼罩呢？其实后果也是雷同的。

非主键惟一索引范畴查问

mysql> begin; select * from t where a >= 110 and a < 115 for update;

剖析 SQL

1. a >= 110 and a < 115，非主键惟一索引 [110,115)，必定是要加锁的；
2. 对应的主键索引 10 应该也会加锁！

事实证明，又一次是谬误的！

剖析 data_locks:

1. 怎么会对非主键惟一索引的 110 加了锁？LOCK_MODE 还是 X，如果加了行锁我还能了解。
2. 怎么会对非主键惟一索引的 115 加了锁？

很显著 110 和 115 之前的间隙以及它们本身的记录都被锁住了。

通过一番剖析，难道是因为 前开后闭。

脑袋炸裂呀，齐全和主键索引的 next-key lock 加锁范畴不同，人家 sql 是什么就锁什么。

有小伙伴晓得起因能够通知我。

如果我把 sql 改成上面的这种呢？

mysql> begin; select *  from t where a > 110 and a < 114 for update;

诶？？？

奇了怪了！

我惟一能想到的起因就是前开后闭了。因为 a >= 10 中的等于是属于上一个区间的，所以须要锁住上一个区间。

我只能说懵逼三连了！！！

其实还是有论断的：

在非主键惟一索引范畴查问时，会对相应的范畴加前开后闭区间，并且如果存在数据，会对对应的主键加行锁。

这时候如果走笼罩索引呢？？

mysql> begin; select id from t where a >= 110 and a < 115 for update;

依照方才的思路，前开后闭：

1. 锁住主键索引 110 的区间，115 的区间
2. 锁住主键 10 的行锁

事实又错了！

还锁住了主键 15 的行锁。

把等号去掉 15 是锁住的。

感觉脑袋齐全不够用啊。重点是我没有了解怎么主键还是前开后开，这里就前开后闭了？

难道我在这里试试那个 bug？

啪啪打脸啊！

之前还说这个 bug 在 8.0.18 被修复了，并优化成了前开后开区间，这间接打脸，明摆着没有修复。

我只是操作 a > 100 and a <= 115 for update; 居然把 120 给我锁住了，不就是 next-key 的 bug。

尝试一下 sql

很显著~ 这个 bug 在非主键惟一索引上，并没有修复！！！

总结

在非主键惟一索引状况下：

1. 非主键惟一索引等值查问，数据存在，for update 是会在主键加锁的，而 for share 只有在走笼罩索引的状况下，会仅在本人索引上加锁；
2. 非主键索引等值查问，数据不存在，无论是否索引笼罩，相当于一个范畴查问，仅仅会在非主键索引上加锁，加的还是间隙锁，前开后开区间；
3. 在非主键惟一索引范畴查问时，不是笼罩索引的时候，会对相应的范畴加前开后闭区间，并且如果存在数据，会对对应的主键加行锁；
4. 在非主键惟一索引范畴查问时，如果是笼罩索引时，会对所有的后闭区间对应的主键，加行锁。

实际完本文的所有操作，集体处于有些懵逼的状态。我应用的版本是 8.0.25

1. 主键不是前开后闭，而非主键惟一索引看样子又很遵循前开后闭准则；
2. next key 的 bug 在非主键惟一索引上，并没有被修复！

认真一想，仿佛又能够了解。

因为主键上的 next-key 的 bug 被修复了，同时优化了前开后闭区间为前开后开区间，而非主键惟一索引上这个 bug 没有被修复，所以没有优化。

嗯~ 大略就是这样吧！

相干举荐

• MySQL next-key lock 加锁范畴是什么？
• 应用 Docker 装置并连贯 MySQL
• Spring 是如何解决循环依赖的