看来,MySQL next-key lock 的 bug 并没有被修复!
共 4117字,需浏览 9分钟
·
2021-06-06 14:55
前言
在上一篇文章《MySQL next-key lock 加锁范围是什么?》中已经介绍了主键索引的加锁范围,现在来回顾一下:
加锁时,会先给表添加意向锁,IX 或 IS; 加锁是如果是多个范围,是分开加了多个锁,每个范围都有锁;(这个可以实践下 id < 20 的情况) 主键等值查询,数据存在时,会对该主键索引的值加行锁 X,REC_NOT_GAP
;主键等值查询,数据不存在时,会对查询条件主键值所在的间隙添加间隙锁 X,GAP
;主键等值查询,范围查询时情况则比较复杂: 8.0.17 版本是前开后闭,而 8.0.18 版本及以后,修改为了 前开后开
区间;临界 <=
查询时,8.0.17 会锁住下一个 next-key 的前开后闭区间,而 8.0.18 及以后版本,修复了这个 bug。
这篇文章会对非主键唯一索引进行操作实践。
1
数据库表数据
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 是唯一索引。
2
非主键唯一索引
非主键唯一索引等值查询 —— 数据存在
mysql> begin; select * from t where a = 110 for update;
分析一下这条 SQL:
select * 查询条件肯定会回表; 唯一索引,所以定位到数据后不需要继续查询; 猜测是锁住唯一索引及对应的主键索引。
查看 data_locks
表锁 IX; 索引 uniq_a 上面加了 X,REC_NOT_GAP
行锁,其中110, 10
表示是 a = 110 这行数据,后面的 10 是这行数据对应的主键;主键 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:
select id 查询,满足索引覆盖,不会回表; 唯一索引,所以定位到数据后不需要继续查询; 那这里是锁主键索引还是两个都锁?
所以看出并无什么区别。
把 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:
首先加了 for update,肯定会在 索引 uniq_a 和 主键索引上都加上锁; 字段 a 具有唯一性,但是数据 a = 111
不存在,会一直查,查到 115 区间;所以会加索引 uniq_a 和 主键索引的间隙锁。(并不对 )
事实证明,分析结果不正确。
并且我执行 update t set c = 2101 where id = 15;
也过了。
所以是不是可以理解为,非主键索引等值查询,数据不存在,相当于一个范围查询,仅仅会在非主键索引上加锁,加的还是间隙锁,前开后开区间;
如果此时走索引覆盖呢?其实结果也是相同的。
非主键唯一索引范围查询
mysql> begin; select * from t where a >= 110 and a < 115 for update;
分析 SQL
a >= 110 and a < 115,非主键唯一索引 [110,115),肯定是要加锁的; 对应的主键索引 10 应该也会加锁!
事实证明,又一次是错误的!
分析 data_locks:
怎么会对非主键唯一索引的 110 加了锁?LOCK_MODE 还是 X,如果加了行锁我还能理解。 怎么会对非主键唯一索引的 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;
按照刚才的思路,前开后闭:
锁住主键索引 110 的区间,115 的区间 锁住主键 10 的行锁
事实又错了!
还锁住了主键 15 的行锁。
把等号去掉 15 是锁住的。
感觉脑袋完全不够用啊。重点是我没有理解怎么主键还是前开后开,这里就前开后闭了?
难道我在这里试试那个 bug?
啪啪打脸啊!
之前还说这个 bug 在 8.0.18 被修复了,并优化成了前开后开区间,这直接打脸,明摆着没有修复。
我只是操作 a > 100 and a <= 115 for update;
竟然把 120 给我锁住了,不就是 next-key 的 bug。
尝试一下 sql
很明显~ 这个 bug 在非主键唯一索引上,并没有修复!!!
3
总结
在非主键唯一索引情况下:
非主键唯一索引等值查询,数据存在,for update 是会在主键加锁的,而 for share 只有在走覆盖索引的情况下,会仅在自己索引上加锁; 非主键索引等值查询,数据不存在,无论是否索引覆盖,相当于一个范围查询,仅仅会在非主键索引上加锁,加的还是间隙锁,前开后开区间; 在非主键唯一索引范围查询时,不是覆盖索引的时候,会对相应的范围加前开后闭区间,并且如果存在数据,会对对应的主键加行锁; 在非主键唯一索引范围查询时,如果是覆盖索引时,会对所有的后闭区间对应的主键,加行锁。
实践完本文的所有操作,个人处于有些懵逼的状态。我使用的版本是 8.0.25
主键不是前开后闭,而非主键唯一索引看样子又很遵循前开后闭原则; next key 的 bug 在非主键唯一索引上,并没有被修复!
仔细一想,似乎又可以理解。
因为主键上的 next-key 的 bug 被修复了,同时优化了前开后闭区间为前开后开区间,而非主键唯一索引上这个 bug 没有被修复,所以没有优化。
嗯~ 大概就是这样吧!
- <End /> -
历史文章 | 相关推荐