MySQL 唯一索引并发插入死锁问题排查与优化方案记录本文分析线上 MySQL 死锁问题成因，给出幂等插入 + 重试的解

一、问题现象

最近几周观测线上服务的业务告警监控群，报出了几次 MySQL Deadlock 错误（Error 1213） ，由于应用服务日志获取不到mysql相关的日志，联系DBA同学获取到了死锁的相关日志，具体如下：

LATEST DETECTED DEADLOCK
...
*** (1) TRANSACTION:
INSERT INTO tb_xxx (...) VALUES (1, 'mp_data', 'app_trip_...', 'answer_cnt')
...
*** (2) TRANSACTION:
INSERT INTO tb_xxx (...) VALUES (1, 'mp_data_dev', 'app_trip_...', 'answer_cnt')
...
*** WE ROLL BACK TRANSACTION (2)

原因是两个几乎同时执行的 INSERT 语句产生死锁导致其中一个事务被回滚，影响业务写入成功率。

二、表结构分析

涉及表：tb_xxx

CREATE TABLE `tb_xxx` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `engine_type` tinyint(4) NOT NULL DEFAULT '1',
  `db_name` varchar(128) NOT NULL DEFAULT '',
  `table_name` varchar(256) NOT NULL DEFAULT '',
  `column_name` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_tab_col` (`engine_type`,`db_name`,`table_name`,`column_name`)
) ENGINE=InnoDB;

关键点：

存在一个 四字段联合唯一索引 uniq_tab_col
隔离级别为 MySQL 默认的 REPEATABLE READ（RR）

三、死锁原因分析

1. 死锁本质

在 RR 隔离级别下，InnoDB 为保证唯一性约束和防止幻读，会在插入前对目标索引位置加 间隙锁（Gap Lock） 和 插入意向锁（Insert Intention Lock） 。

2. 具体场景

两个事务并发插入 在唯一索引排序上相邻的记录，例如：
- T1: (1, 'mp_data', ..., 'answer_cnt')
- T2: (1, 'mp_data_dev', ..., 'answer_cnt')
虽然字段 db_name 的内容不同，但因字符串排序相邻（'mp_data' < 'mp_data_dev'），落在同一索引间隙（gap）内
两个事务互相持有对方所需的间隙锁，形成 循环等待 → 死锁

✅ 典型的 “并发插入相邻唯一索引值” 导致的死锁，属于 InnoDB 在 RR 模式下的正常行为，但需应用层规避。

四、解决方案

✅ 主方案：改用幂等插入语句

将原始 INSERT 改为以下任一形式，从根本上避免死锁 + 实现幂等：

方式一：`INSERT IGNORE`

INSERT IGNORE INTO tb_xxx
(engine_type, db_name, table_name, column_name)
VALUES (1, 'mp_data', 'xxx', 'yyy');

若唯一键冲突，静默跳过，不报错
适用于“存在则忽略，不存在则创建”场景

方式二：`INSERT ... ON DUPLICATE KEY UPDATE`

INSERT INTO tb_xxx
(engine_type, db_name, table_name, column_name)
VALUES (1, 'mp_data', 'xxx', 'yyy')
ON DUPLICATE KEY UPDATE
gmt_modified = CURRENT_TIMESTAMP;

冲突时可选择更新（如刷新修改时间）
更灵活，推荐用于需记录“最后关联时间”的场景

💡 两种方式均可显著减少间隙锁竞争，InnoDB 内部优化使其几乎不会在此类操作中产生死锁。

✅ 辅助方案：应用层死锁重试机制

即使优化 SQL，高并发下仍可能偶发其他类型死锁，建议统一增加重试逻辑：

for (int i = 0; i < 3; i++) {
    try {
        mapper.insertIgnore(record);
        break;
    } catch (DeadlockLoserDataAccessException e) { // Spring 封装
        if (i == 2) throw e;
        Thread.sleep(10 * (i + 1)); // 指数退避
    }
}

五、为什么不推荐其他方案？

方案	问题
降低隔离级别至 `READ COMMITTED`	可能引入幻读，破坏业务一致性，不适用于当前指标系统
删除唯一索引	违背数据模型设计，会导致重复关联，不可接受
应用层先查后插	增加 RT，且仍存在竞态条件，无法根治死锁

六、后续改进

将所有对 tb_xxx 的插入操作统一改为 INSERT ... ON DUPLICATE KEY UPDATE
在 DAO 层封装幂等写入方法，避免各处重复处理
监控 Deadlock是否仍然存在，验证优化效果

七、参考

MySQL 官方文档：InnoDB Locking
《高性能 MySQL》第 8 章：事务与锁
MySQL SHOW ENGINE INNODB STATUS 死锁日志解读

总结：本次死锁由唯一索引并发插入引发，通过改用幂等插入语句（INSERT IGNORE / ON DUPLICATE KEY UPDATE）可规避，同时提升系统健壮性与写入成功率。

MySQL 唯一索引并发插入死锁问题排查与优化方案记录