InnoDB存储引擎有3种行锁的算法,分别是:
- Record Lock(记录锁): 单个记录上的锁
- Gap Lock(间隙锁) : 锁定一个范围,但不包括记录本上,左开右开区间
- Next-Key Lock(临键锁): 锁定一个范围,并且锁定记录本身 ,左开右闭区间
下面我以 MySQL 8.0.25
版本为例,进行多角度验证 next-key lock 加锁范围。
环境准备
MySQL 版本:8.0.25
隔离级别:可重复读(RR)
存储引擎:InnoDB
一、主键索引
创建表
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;
id主键索引、a唯一索引,c普通索引 ,d普通列
首先来验证主键索引的 next-key lock 的范围,对主键索引来说此时数据间隙如下:
1、 主键等值查询 —— 数据存在
select * from t where id = 10 for update;
可以通过 data_locks
查看锁信息,SQL 如下:
select * from performance_schema.data_locks
咱们需要重点关注以下几个字段:
-
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 锁。
可以得出结论:
对主键等值加锁,且值存在时,会对表添加意向锁,同时会对主键索引添加记录锁。
2、主键等值查询 —— 数据不存在
select * from t where id = 11 for update;
如果是数据不存在的时候,会加什么锁呢?锁的范围又是什么?
在验证之前,分析一下数据的间隙。
-
id = 11
是肯定不存在的。但是加了for update
,这时需要加 next-key lock,id = 11
所属区间为 (10,15] 的区间; - 因为是
等值查询
,不需要锁id = 15
那条记录,next-key lock 会退化为间隙锁; - 最终区间为 (10,15) 的前开后开区间。
使用 data_locks 分析一下锁信息:
看下锁的信息 X,GAP
表示加了间隙锁,其中 LOCK_DATA = 15,表示锁的是 主键索引 id = 15 之前的间隙。
此时在另一个 Session 执行 SQL,答案显而易见,是 id = 12 不可以插入,而 id = 15 是可以更新的。
可以得出结论,在数据不存在时,主键等值查询,会锁住该主键查询条件所在的间隙。
3、主键范围查询(重点)
select * from t where id >= 10 and id < 11 for update;
分析得出下面结果:
-
id >= 10
定位到 10 所在的区间 [10,+∞); -
id < 11
限定后续范围,则根据 11 判断下一个区间为 15 的前开后闭区间; -
结合起来则是 [10,15]。(不完全正确)
先看下 data_locks
可以看到除了表锁之外,还有 id = 10 的行锁(X,REC_NOT_GAP
)以及主键索引 id = 15 之前的间隙锁(X,GAP
)。
所以实际上 id = 15 是可以进行更新的。也就是说前开后闭区间
出现了问题,个人认为应该是 id < 11
这个条件判断,导致不需要进行了锁 15 这个行锁。
结果验证也是正确的,id = 12 插入阻塞,id = 15 更新成功。
结论一
-
加锁时,会先给表添加意向锁,IX 或 IS;
-
加锁是如果是多个范围,是分开加了多个锁,每个范围都有锁;(这个可以实践下 id < 20 的情况)
-
主键等值查询,数据存在时,会对该主键索引的值加行锁
X,REC_NOT_GAP
; -
主键等值查询,数据不存在时,会对查询条件主键值所在的间隙添加间隙锁
X,GAP
;
结论二
通过使用 select * from performance_schema.data_locks;
和操作时间,可以看出 LOCK_MODE 和 LOCK_DATE 的关系:
LOCK_MODE | LOCK_DATA | 锁范围 |
---|---|---|
X,REC_NOT_GAP | 15 | 15 那条数据的行锁 |
X,GAP | 15 | 15 那条数据之前的间隙,不包含 15 |
X | 15 | 15 那条数据的间隙,包含 15 |
-
LOCK_MODE = X
是左开右闭区间; -
X,GAP
是左开右开区间(间隙锁); -
X,REC_NOT_GAP
行锁。
二、非主键唯一索引
数据库数据如下:
数据库的字段 a 是唯一索引。
1、唯一索引等值查询 —— 数据存在
select * from t where a = 100 for update;
查看 data_locks
-
表锁 IX;
-
索引 uniq_a 上面加了
X,REC_NOT_GAP
行锁,其中100, 10
表示是 a = 100 这行数据,后面的 10 是这行数据对应的主键; -
主键 id = 10 上添加了
X,REC_NOT_GAP
行锁。 -
所以锁住唯一索引及对应的主键索引。
这里执行的 SQL 都是 select *
,如果替换为 select id
呢?
select id from t where a = 100 for update;
并无什么区别。
把 for update 换成 for share,这时候区别来了:
select id from t where a = 100 for share;
只有两条锁记录:表意向锁和 uniq_a 索引的 S,REC_NOT_GAP
锁。
很明显,for share 覆盖了主键索引,只是对自己的索引加锁。
update t set c = 70 where id = 10;
update t set a = 1101 where id = 10;
update t set c = 2101 where a = 100;
执行结果很显然,第一个可以执行,而后两个是会阻塞的。
所以,非主键唯一索引等值查询,数据存在,for update 是会在主键加锁的,而 for share 只有在走覆盖索引的情况下,会仅在自己索引上加锁。
2、唯一索引等值查询 —— 数据不存在
select * from t where a = 111 for update;
分析这一条 SQL:
-
字段 a 具有唯一性,但是数据
a = 111
不存在,会一直查,查到 120 区间; -
所以会加a的间隙锁。
非主键索引等值查询,数据不存在,相当于一个范围查询,仅仅会在非主键索引上加锁,加的还是间隙锁,左开右开区间;
3、唯一索引范围查询
select * from t where a >= 110 and a < 115 for update;
-
120的临建锁(110,120] 110的临建锁(100,110] ,合并为(100,120]
-
对应的主键索引 15 也会加锁!
三、普通索引
1、普通索引等值查询 —— 数据存在
select * from t where c = 100 for update;
直接分析 data_locks
-
表意向锁IX;
-
索引 idx_c 上添加了 100 的临建锁;
-
索引 idx_c 上添加了 130 区间的间隙锁
-
主键上添加了 20 的记录锁
2、普通索引等值查询 —— 数据不存在
select * from t where c = 101 for update;
直接分析 data_locks
-
表意向锁;
-
索引 idx_c 上添加了130 的间隙锁。
分析是因为数据不存在,只需要锁住130 间隙就可以了
3、普通索引范围查询
select * from t where c > 70 and c <= 85 for update;
直接分析 data_locks
-
表意向锁;
-
索引 idx_c 上添加了80 的临建锁。
-
索引 idx_c 上添加了100 的临建锁。
-
主键15的记录锁
四、普通字段
对普通字段而言,无论是哪个查询,都需要扫描全部记录,所以这个锁直接加在了主键上,并且是锁住全部的区间。
总结
如果规律记不住,可以直接通过分析 data_locks 的信息,进行判断加锁范围。
select * from performance_schema.data_locks;
LOCK_MODE | LOCK_DATA | 锁范围 |
---|---|---|
X,REC_NOT_GAP | 15 | 15 那条数据的行锁 |
X,GAP | 15 | 15 那条数据之前的间隙,不包含 15 |
X | 15 | 15 那条数据的间隙,包含 15 |
-
LOCK_MODE = X
是前开后闭区间; -
X,GAP
是前开后开区间(间隙锁); -
X,REC_NOT_GAP
行锁。