求教 MySQL 加锁的一个疑问

[gps32251070]

表结构如下：

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)吧

[jimmzhou]

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

[gps32251070]

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

[yyyfor]

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

[phpcyy]

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

[gps32251070]

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

[zhzy0077]

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]

改成 RC 级别试试？

[jaggle]

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

[tedzhou1221]

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

[yc8332]

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

[liudaolunhuibl]

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

[liudaolunhuibl]

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

[gps32251070]

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

[gps32251070]

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

[liudaolunhuibl]

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

[xiaohundun]

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

[fengpan567]

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

[wtfedc]

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

[PythonYXY]

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

[initObject]

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

[SachinBeyond]

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]

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

[SachinBeyond]

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