求教 MySQL 加锁的一个疑问

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 584 天前的主题，其中的信息可能已经有所发展或是发生改变。

表结构如下：

create table t
(
    id int not null
        primary key,
    c  int null,
    d  int null
);

create index c
    on t (c);
    
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

mysql 版本：8.0.32 隔离级别 RR

session A	session B
begin;
select * from t where id>=15 and id<=20 order by id desc for update ;
	insert into t values (22,22,22); //会被阻塞
commit;

请问，id 是主键，为什么 session B 会进入锁等待？按理说唯一索引不会检查区间(20, 25)吧

23 条回复 • 2023-04-18 20:06:23 +08:00

jimmzhou

2023-04-17 18:46:46 +08:00

索引 c 上面的加锁范围应该是 (5,25)

gps32251070

2023-04-17 18:55:12 +08:00

@jimmzhou 应该跟索引 c 无关吧，我删除了索引 c ，还是会阻塞

yyyfor

2023-04-17 19:24:14 +08:00

索引范围查询需要访问到不满足条件的第一个值为止，这里是等值查询，感觉这里主键 id 加锁的范围为(10,25]

phpcyy

2023-04-17 19:30:00 +08:00

@yyyfor 👍，我按照 OP 的表新插入了一条 (21,21,21)，session B 就不会阻塞了

gps32251070

2023-04-17 19:32:36 +08:00 via iPhone

@yyyfor 不是，测试的主键加锁范围是(5,25)

zhzy0077

2023-04-17 19:34:04 +08:00

TABLE,IX,GRANTED,
RECORD,"X,GAP",GRANTED,20
RECORD,X,GRANTED,10
RECORD,X,GRANTED,15

这里大概还有一个 20 的 next key lock, 虽然直觉上 id 是 PK 的话应该不用这个锁，可能是为了兼容 PK 有多个字段的情况，所以把等值的时候也 lock 起来了

不过现在 MySQL 不是也早就有 Snapshot Isolation 了吗，真有必要 for update 这样写嘛

admol

2023-04-17 19:39:29 +08:00

改成 RC 级别试试？

jaggle

2023-04-17 19:53:12 +08:00 via iPhone

这题目我会，谷歌搜索 “间隙锁”

tedzhou1221

2023-04-18 09:22:50 +08:00

@jaggle 谢谢你的抢答，直接搜索到答案了，哈哈

yc8332

2023-04-18 09:23:48 +08:00

mysql innodb 加范围锁的时候是会这样的。就算不存在的记录也会被加锁。。叫间隙锁

liudaolunhuibl

2023-04-18 09:57:54 +08:00

@yyyfor 说的对，但是原因就是因为间隙锁，上锁范围是（ 15 ，20]，(20,25]，20 之后由于有数据的就是 25 ，所以间隙锁会从 20 一直锁到 25

liudaolunhuibl

2023-04-18 10:04:54 +08:00

我理解的简单来说，间隙锁的锁定范围应该是锁定数字行到最近一行（左右均会）存在数据之间的数据都会锁，也就说，>=15 的时候锁住的是 10-20 区间，<=20 的时候锁定的是 15-25 的区间，合并一下就可以 10-25 区间都会上锁，楼主可以试试 insert into t values (11,11,11)会不会上锁

gps32251070

2023-04-18 10:07:17 +08:00

@liudaolunhuibl 嗯，我知道间隙锁，如果 id 不是唯一索引，这个间隙锁可以避免 id=20 的值插入进来，所以没问题。我想问的是 id 是唯一索引，锁这个间隙感觉毫无必要。

gps32251070

2023-04-18 10:08:38 +08:00

@liudaolunhuibl 这个 sql 的测试锁定范围是(5,25)

liudaolunhuibl

2023-04-18 10:26:33 +08:00

@gps32251070 啊？不要应该是从 10 开始吗。。不太懂

xiaohundun

2023-04-18 10:29:03 +08:00

大概、可能、也许是因为你用的 RR 隔离，更严格吧

fengpan567

2023-04-18 15:12:42 +08:00

你把 order by 去掉应该就正常了，倒叙查第一个查到的数就是 25

wtfedc

2023-04-18 16:02:36 +08:00

是 order by 引起的问题，但是没查到为什么可以导致 Next-Key Locks 范围变化

PythonYXY

2023-04-18 16:32:30 +08:00

确实好奇怪啊，我把 order by 去掉也会阻塞

initObject

2023-04-18 18:56:34 +08:00

https://www.v2ex.com/t/917504#reply19

SachinBeyond

2023-04-18 19:38:28 +08:00

mysql45 讲第 21 篇里面提到的第 5 点已经讲了
https://blog.csdn.net/bohu83/article/details/105344930

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

加锁的基本单位是 next-key lock,
(10,15],(15,20] ,(20,25] 被命中的就是 (10,15],(15,20]

因为这个 id 是主键唯一索引，这个(15,20] next-key lock 就 i 已经锁住了 id=20 的数据，且在这条数据之后不会有 id=20 数据了（主键唯一性保证），所以理论上（ 20 ，25] 可以不用加锁，但是实际上 mysql 会在（ 20 ，25]上加锁，又因为 25 这条数据本身没有被命中，所以会退化为（ 20 ，25 ）的间隙锁。

至于 mysql 为什么会加锁，林哥说这可能是一个 feature

gps32251070

2023-04-18 19:58:23 +08:00 via iPhone

@SachinBeyond 意思是这有可能是个 BUG ？还有，这个左边锁的范围是到 5 ，整体是(5,25)

SachinBeyond

2023-04-18 20:06:23 +08:00

@gps32251070 （ 5 ，10] 这个被锁住我没想到。我试了下确认是被锁住了，整体范围是(5,25)