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

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; 时刻下为什么不会被阻塞死锁又是怎么产生的

第 1 条附言 · 269 天前

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

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

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

从而出现死锁的情况

8 条回复 • 2024-02-27 10:57:05 +08:00

peyppicp

269 天前

咨询了 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) 内，所以这条语句亦会阻塞。
最后形成了死锁，两个操作互相等待对方释放资源。