MySQL Insert on Duplicate Key Update 死锁定位与解决方案
在 MySQL 高并发写入场景中，INSERT ... ON DUPLICATE KEY UPDATE 语句因混合使用间隙锁和插入意向锁，极易引发死锁。本文详细剖析了其底层锁机制冲突原理，并通过锁兼容性表格与事务还原示例解释死锁形成过程，最终给出切换为普通 Insert+异常重试或业务限流的实用解决方案。
问题背景与定位
在线上监测中发现，某 MySQL 业务数据库频繁出现间歇性死锁日志。经分析，死锁根源在于并发执行 INSERT ... ON DUPLICATE KEY UPDATE 语句时触发的锁冲突。生产环境使用 MySQL 5.7，事务隔离级别为默认的行重复度（RR, Repeatable Read）。
第1步：确认表结构与触发 SQL
首先查看导致问题的表结构及对应的 SQL 语句。表中存在一个基于手机号 phone 的唯一索引，这是冲突的关键点。
CREATE TABLE IF NOT EXISTS `user_info` (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
phone BIGINT(20) UNSIGNED NOT NULL,
update_time timestamp NOT NULL,
UNIQUE KEY phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;引发死锁的具体 SQL 如下：
insert into user_info (name, phone, update_time) values (X,Y,Z)
on duplicate key update update_time=Z;第2步：查看死锁诊断信息
当发生死锁后，执行 show engine innodb status 查看日志，发现关键报错信息：lock_mode X locks gap before rec insert intention waiting。这表明事务正在等待获取“间隙锁”以申请“插入意向锁”，暗示了 Gap Lock 导致的阻塞。
原理分析：Lock 机制
INSERT ... ON DUPLICATE KEY UPDATE 的语义是“存在则更新，不存在则插入”。但在 RR 隔离级别下，其加锁逻辑与普通 INSERT 不同：
- 若记录存在：获取 Next-Key Lock（记录锁 + 间隙锁），锁定该索引记录及其前方间隙。
- 若记录不存在：在唯一键上获取 Gap Lock（间隙锁）以及 Insert Intention Lock（插入意向锁）。
其中，Gap Lock 用于防止其他事务插入到该间隙中。例如，若现有唯一键值为 1、5、10： - 插入 4 时，Gap Lock 范围是 (1, 5)。 - 插入 15 时，Gap Lock 范围是 (10, +∞)。
锁兼容性矩阵
理解以下锁兼容性矩阵是理解死锁的核心：
| 当前持有 \ 后续申请 | 插入意向锁 | 行锁 (Record Lock) | Gap Lock |
|---|---|---|---|
| 插入意向锁 | 兼容 | 互斥 | 互斥 |
| 行锁 | 兼容 | 互斥 | 兼容 |
| Gap Lock | 兼容 | 兼容 | 兼容 |
关键点在于：持有 Gap Lock 的事务会拒绝其他事务获取同一范围的插入意向锁。而在 ON DUPLICATE KEY 场景中，多个并发事务往往都试图向同一巨大间隙（如末尾）插入新记录，从而产生锁等待循环。
死锁场景还原
假设数据库中已有手机号 157xxxxxx00、157xxxxxx1、157xxxxxx2。我们模拟三个事务 tx1, tx2, tx3 并发尝试插入新的递增手机号：
| 阶段 | tx1 动作 | tx2 动作 | tx3 动作 |
|---|---|---|---|
| 1 | 插入 157xxxxxx1 (Gap: x00~x01) | - | - |
| 1a | 持有 Gap Lock (157xxxxxx1, +∞) | - | - |
| 2 | - | 插入 157xxxxxx2 | - |
| 2a | - | 申请插入意向锁失败（被tx1 Gap Lock排斥），成功获取 Gap Lock，进入等待 | - |
| 3 | - | - | 插入 157xxxxxx3 |
| 3a | - | - | 申请插入意向锁失败（被tx1或tx2排斥），成功获取 Gap Lock，进入等待 |
| 4 | Commit，释放所有锁 | - | - |
| 5 | - | 获得插入意向锁，准备提交 | 获得插入意向锁，准备提交 |
| 6 | - | tx2 与 tx3 均处于最后一步，相互检测对方持有的资源无法获取，触发死锁检测，其中一个回滚。 | |
这种因有序插入产生的“排队效应”，使得多个事务几乎同时在同一个大间隙尾部争抢锁资源，从而形成死锁。
问题解决与优化方案
由于 INSERT ... ON DUPLICATE KEY UPDATE 本身原子性强且语法简洁，但在高并发无序或近有序插入下稳定性较差。建议根据业务场景选择以下两种解决方案之一：
方案一：应用层改造（推荐）
抛弃 ON DUPLICATE KEY，改用普通 INSERT 语句：
- 执行标准
INSERT INTO user_info (...) VALUES (...)。 - 捕获 SQL 异常（如 Duplicate entry for key 'phone'）。
- 若捕获异常，则转为执行
UPDATE user_info SET ... WHERE phone = ?或在应用层进行重试。
虽然代码量增加，但避免了数据库层面的复杂锁竞争，将逻辑控制在应用层更可控。
方案二：业务层限流与分片
如果必须使用原 SQL，需从业务入口减少并发密度：
- 接口限流：限制特定时间窗口内的最大请求数。
- 批量合并：在事务内对即将插入的数据列表进行排序去重，或将大批量数据拆分为小批次分批执行，避免海量并发请求同时到达数据库同一索引区域。
总结
INSERT ... ON DUPLICATE KEY UPDATE 虽好，但在高并发写入大量新增数据的场景下并非最佳选择。其背后隐藏的间隙锁与插入意向锁的交互机制容易在相同 Range 内引发死锁。开发者应意识到性能权衡，根据实际并发情况选择合适的替代方案。以上就是关于 MySQL INSERT ... ON DUPLICATE KEY UPDATE 死锁问题定位与解决的详细内容，更多关于 MySQL 高并发处理技巧的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
