请教一个 MySQL 死锁问题，先 delete 再批量 insert 引发的死锁

transaction1	transaction2
BEGIN; delete from `demo` where `c_id` = 'abc'; Query OK, 0 rows affected (0.00 sec)
	BEGIN; delete from `demo` where `c_id` = 'xyz'; Query OK, 0 rows affected (0.00 sec)
insert into `demo` (`c_id`, `d_id`) values ('abc', '111'), ('abc', '222'), ('abc', '333'); WAITING
	insert into `demo` (`c_id`, `d_id`) values ('xyz', '444'), ('xyz', '555'), ('xyz', '666'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

最终事务 1 被回滚了。

死锁的日志关键字：

(1) WAITING FOR THIS LOCK TO BE GRANTED lock_mode X locks gap before rec insert intention waiting (2) TRANSACTION: TRANSACTION 947943174, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 (2) HOLDS THE LOCK(S): RECORD LOCKS space id 899935 page no 578533 n bits 200 index c_id_index of table `demo` trx id 947943174 lock_mode X (2) WAITING FOR THIS LOCK TO BE GRANTED: lock_mode X locks rec but not gap waiting

原因基本很明确：应该是两个 delete 同时获取了 gap 或 next_key 锁，然后因为不存在记录，导致锁的范围很大，甚至是一个 ∞ 的区间，同时 insert 语句无法获取插入意向锁，并互相等待引发死锁。（这样理解对吗）

那么解决方案是什么？

不能改变隔离级别。

是否先查一下要删除的条件有没有记录，如果有，再执行删除。这样是否就可以了。

Thank you!

c_id

insert

死锁

xyz'

12 条回复 2021-11-29 10:44:11 +08:00

codebigbang

2021-11-28 14:38:37 +08:00

看你的业务逻辑，或许可以试试 replace into

daoqiongsi1101

2021-11-28 14:41:15 +08:00

@codebigbang 可是需要先 delete 数据

iplayio2019

2021-11-28 15:08:15 +08:00

@daoqiongsi1101 replace 也是先 replace 再 insert

daoqiongsi1101

2021-11-28 15:20:46 +08:00

@iplayio2019 想删除所有 c_id = 5 的数据, 如果只是 insert replace (c_id,d_id) replace (5,1)(5,2)，那么还有（ 5,3) 是否就保留下来了

RipL

2021-11-28 15:27:55 +08:00 via iPhone

先查在删除？

sujin190

2021-11-28 15:37:44 +08:00

@iplayio2019 #3 replace 也需要间隙锁吧，似乎并不能解决这个问题来着

似乎你这个应该删除改成先查询出主键，然后用主键来删除，应该就没这个问题了吧，或者用外部锁来串行化也行，删除时可以先查询下看看，如果只有一条就改成更新估计可以提高一点性能吧

surfire91

2021-11-28 16:50:06 +08:00

先查出来，删的时候按 c_id + d_id 来删，或者按主键删都行

bxb100

2021-11-28 17:28:30 +08:00

有一个方案: 先查有值就删, 无值就 insert, 加个 unique(c_id, d_id) 保证不会重复插入

akira

2021-11-28 18:15:45 +08:00

生产环境业务代码中尽量不要做硬删除动作，改为软删除。定时清理维护就好。

Seayon

2021-11-28 19:26:48 +08:00

假如你本次要删除 c_id='abc' 的记录，你的本次操作中的 DELETE 和 INSERT 中间是否允许别的事务插入新的 c_id ='abc' 的新记录？
如果不允许，那改成先查是否有然后用主键删除的话，其中 SELECT 要写成 SELECT * FROM demo WHERE c_id='abc' FOR UPATE ，用来锁定避免别的事务插入新的记录，此所谓幻读问题。当然这样也会加上 GAP 锁进而又产生死锁。
如果允许，那我觉得吧第一步的 DELETE 和第二步的 INSERT 拆成两个事务即可。
最后，可以考虑下外部用锁进行串行化操作。

jwh199588

2021-11-29 09:43:18 +08:00

可以参考这篇文章 https://segmentfault.com/a/1190000018730103

abccccabc

2021-11-29 10:44:11 +08:00

要么优化业务逻辑，要么隔离机制改为 S 。二选一。