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）内

• 两个事务互相持有对方所需的间隙锁，形成 循环等待 → 死锁

四、解决方案

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

将原始 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;

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

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

即使优化 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)); // 指数退避
    }
}

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

六、后续改进

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

七、参考

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