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

隔离级别是 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!

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 拆成两个事务即可。
最后，可以考虑下外部用锁进行串行化操作。