求助 MySQL 5.7 默认隔离级别下死锁问题

[misoomang]

表结构：

CREATE TABLE `student` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`score` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

已经有如下数据 (1, 'Alice', 20, 85),

(8, 'Bob', 22, 92),

(15, 'Charlie', 21, 78),

(25, 'David', 23, 95), (33, 'Eva', 20, 88),

(44, 'Frank', 22, 79),

(50, 'Grace', 21, 94),

(55, 'Henry', 23, 87);

事务 A 执行： time1: update student set score=88 where id=22;

time3: insert into student(id, name, age, score) value(22, 'John', 28, 88);

事务 B 执行： time2：update student set score=99 where id=21;

time4: insert into student(id, name, age, score) value(21, 'John', 28, 99);

在 time1 时刻事务 A 会加上 next-key lock (15, 25)

time2：update student set score=99 where id=21; 时刻下为什么不会被阻塞 死锁又是怎么产生的

[peyppicp]

咨询了 gpt：结论如下：

对于示例中的数据库事务：
初步执行 time1: update student set score=88 where id=22; 时，因为 id=22 并不存在，所以它会添加一个 Next-key lock ，范围在项目 (15, 25) 上。
然后 time2: update student set score=99 where id=21; 可以被成功执行，而不会被阻塞，这是因为 InnoDB 使用了 Next-key Locks （即记录锁和间隙锁的组合）。执行这条语句时，id=21 并不存在，不会触发 Next-key Locks 范围 (15, 25) 上的记录锁，并成功执行。但同时，这个语句添加了一个 Next-key lock ，范围在 (21, 25) 上。
然后在 time3: insert into student(id, name, age, score) value(22, 'John', 28, 88); 语句执行时，因为 id=22 在之前 time2 执行的 Next-key Locks 泛围 (21, 25) 内，所以这条语句会被阻塞。
同时，在 time4：insert into student(id, name, age, score) value(21, 'John', 28, 99); 中，因为 id=21 在事务 A 的 Next-key Locks 泛围 (15, 25) 内，所以这条语句亦会阻塞。
最后形成了死锁，两个操作互相等待对方释放资源。

[misoomang]

感谢回复。通过手动执行上述场景的锁持有状况来看，确实是这样的

time1 时刻，由于 id=22 在表中是没有的，此刻事务 A 会在 (15, 25) 加上间隙锁
time2 时刻，由于 id=21 在表中也是没有的，且间隙锁之间不是互斥的，所以此刻事务 B 会在 （ 15, 25 ）加上间隙锁

time3 时刻的 insert 语句因为事务 B 间隙锁的存在所以需要等待事务 B 间隙锁释放；
time4 时刻的 insert 语句因为事务 A 间隙锁的存在所以需要等待事务 A 间隙锁释放；

从而出现死锁的情况

[kousei]

mysql 的锁可以参考 https://www.yuque.com/kousei/kb/snht1u?singleDoc# 《 MySQL 文档翻译——InnoDB 锁》

[waytodelay]

是研究还是说生产环境遇到了？

[morgan1freeman]

https://blog.csdn.net/fedorafrog/article/details/104249140
参考 何登成 这个，最消息了，各种情况下 加的什么锁

[LeeReamond]

挺有意思的，因为我以前做的业务都用默认隔离级别所以还真没遇到过这种情况。所以意思是 innodb 最高隔离级别下两个线程同时进行邻近区段的 [查询->更新] 操作就大概率导致死锁？那开发要咋做，难道业务端维护锁镜像么

[kanepan19]

业务中很少碰到 update 不存在的 id ，都是先确认主键的， 所以没碰到这种间隙锁。

[changdy]

太麻烦了 . 不推荐 可重复读 ..隔离级别.建议 读已提交