简单SQL事务执行中异常死锁的原因及保速解决方案咨询

通过HTTP POST接收远程JSON数据并存入ChangeEvent表，为保证同rId数据唯一，用事务先删除旧数据再插入新数据，SQL语句如下：

BEGIN TRANSACTION;
DELETE FROM ChangeEvent WHERE rId = 'someid';
INSERT INTO ChangeEvent (records, rId) VALUES ('...', 'someid');
COMMIT;

表在自增id列和rId列均建有索引，但偶尔会触发死锁，且死锁仅发生在两条同rId的插入操作同时执行时，死锁日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-06-11 13:59:38 0x7f9a6804a700
*** (1) TRANSACTION:
TRANSACTION 7915, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 20, OS thread handle 140301147133696, query id 35 172.20.0.4 db3 Update
INSERT INTO `ChangeEvent` (`records`, `rId`)
VALUES ('[]', '153181')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 256 page no 4 n bits 72 index rId of table `usr`.`ChangeEvent` trx id 7915 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 7916, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 21, OS thread handle 140301146826496, query id 36 172.20.0.4 db3 Update
INSERT INTO `ChangeEvent` (`records`, `rId`)
VALUES ('...', '153181')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 256 page no 4 n bits 72 index rId of table `usr`.`ChangeEvent` trx id 7916 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 256 page no 4 n bits 72 index rId of table `usr`.`ChangeEvent` trx id 7916 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

移除rId索引后死锁消失，但担心无索引时DELETE操作会因表数据量达上万条而大幅变慢，现提出两个问题：

1. 这些死锁的根本原因是什么？
2. 如何在保持查询效率的前提下避免死锁？

1. 死锁的根本原因 #

核心在于InnoDB的**间隙锁（Gap Lock）与插入意向锁（Insert Intention Lock）**的交互逻辑：

• 当执行DELETE FROM ChangeEvent WHERE rId = 'someid'时，由于rId是普通索引，InnoDB不仅会锁定该rId对应的索引记录，还会锁定记录前后的间隙（防止幻读），包括索引末尾的supremum伪记录（即日志中提到的锁）。
• 当两个事务同时对同一个rId执行「先删后插」操作时：
  1. 事务A执行DELETE，锁定rId='153181'对应的索引记录、间隙及supremum；
  2. 事务B执行DELETE，因事务A未提交，找不到目标记录，于是同样锁定相同的间隙和supremum；
  3. 事务A执行INSERT，需申请插入意向锁，但发现事务B已持有supremum的X锁，进入等待；
  4. 事务B执行INSERT，同样需申请插入意向锁，却发现事务A持有supremum的X锁，也进入等待；
• 两个事务互相等待对方释放锁，形成死锁，最终MySQL会回滚其中一个事务。

另外，移除rId索引后，DELETE会变为全表扫描，InnoDB仅对找到的记录加行锁（无匹配记录则不加锁），不会触发间隙锁，因此死锁消失，但全表扫描的效率问题无法忽视。

2. 保持查询效率并避免死锁的方案 #

方案一：用REPLACE INTO替代先删后插 #

直接使用REPLACE INTO语句，它会先尝试插入，若发现rId存在唯一冲突（需给rId添加唯一约束），则自动删除旧记录并插入新记录，整个操作由InnoDB原子性处理，不会出现死锁：

REPLACE INTO ChangeEvent (records, rId) VALUES ('...', 'someid');

注意：必须给rId列添加唯一约束（UNIQUE KEY），既保证REPLACE INTO正常工作，又保留索引以维持查询效率。

方案二：提前加锁，统一事务执行顺序 #

先执行SELECT ... FOR UPDATE提前锁定目标rId对应的记录或间隙，再执行DELETE和INSERT，确保事务按顺序获取锁，避免互相等待：

BEGIN TRANSACTION;
SELECT * FROM ChangeEvent WHERE rId = 'someid' FOR UPDATE;
DELETE FROM ChangeEvent WHERE rId = 'someid';
INSERT INTO ChangeEvent (records, rId) VALUES ('...', 'someid');
COMMIT;

SELECT ... FOR UPDATE会优先锁定目标资源，事务A先拿到锁后，事务B会等待事务A释放锁再执行，不会形成死锁。

方案三：降低事务隔离级别至READ COMMITTED #

将MySQL事务隔离级别设置为READ COMMITTED，该级别下InnoDB不会使用间隙锁（外键、唯一键场景除外），仅加记录锁，DELETE操作不会锁定间隙，从而避免死锁：

• 全局配置：在MySQL配置文件中添加transaction-isolation = READ-COMMITTED；
• 会话级配置：执行SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;；
  注意：该级别会允许不可重复读，若业务场景可接受此特性，这是简单有效的方案，同时rId索引依然保留，保证DELETE效率。

方案四：用INSERT ... ON DUPLICATE KEY UPDATE替代 #

若无需删除旧记录，仅需更新records字段，可使用该语句（同样要求rId为唯一键），操作原子性且不会触发死锁：

INSERT INTO ChangeEvent (records, rId) VALUES ('...', 'someid')
ON DUPLICATE KEY UPDATE records = VALUES(records);

内容的提问来源于stack exchange，提问作者Emosewaj