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

[daoqiongsi1101]

隔离级别是 RR ，这里 c_id 是一个普通索引，(c_id, d_id) 是一个唯一索引。

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!

[codebigbang]

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

[daoqiongsi1101]

@codebigbang 可是需要先 delete 数据

[iplayio2019]

@daoqiongsi1101 replace 也是先 replace 再 insert

[daoqiongsi1101]

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

[RipL]

先查在删除？

[sujin190]

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

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

[surfire91]

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

[bxb100]

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

[akira]

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

[Seayon]

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

[jwh199588]

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

[abccccabc]

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