MySQL多实例插入触发死锁，求解决方案及索引排查

问题本质 #

这次死锁确实和索引直接相关，核心原因是自增主键的批量插入导致同一数据页的锁竞争：

• 表的主键id是自增字段，所有新插入的记录都会被分配到主键索引的最后一个数据页（对应日志里的space id 30 page no 28）。
• 当多个事务同时执行批量INSERT时，每个事务都会先获取该页的X锁（锁定页的supremum虚拟记录，即页的末尾边界），同时需要申请插入意向锁来写入新记录。两个事务互相持有对方需要的锁资源，最终形成死锁。
• 哪怕数据量不大，高并发的批量插入会持续触发同一页的锁竞争，导致死锁频繁发生。

解决办法 #

1. 拆分批量插入为单条语句 #

把事务中的多值INSERT拆成多个单条INSERT语句，比如：

-- 原批量插入
INSERT INTO categorised_domains (...) VALUES (...), (...);

-- 拆分为单条
INSERT INTO categorised_domains (...) VALUES (...);
INSERT INTO categorised_domains (...) VALUES (...);

单条插入时InnoDB只会申请插入意向锁，不会持有整个页的X锁，能大幅降低锁冲突概率。

2. 调整自增锁模式 #

修改MySQL的innodb_autoinc_lock_mode参数：

• 默认值1（连续模式）下，批量插入会持有较重的表级自增锁；改成2（交错模式）后，自增锁变为轻量级的行级锁，适合高并发插入场景。
• 注意：如果使用主从复制，必须确保复制格式为ROW（行级复制），否则会导致主从数据不一致。

3. 分散插入的数据页 #

如果业务允许，提前为不同应用实例分配不同范围的自增ID区间，让不同事务的插入记录分布在不同的数据页，避免集中竞争同一个页的锁。

4. 缩短事务周期 #

确保插入操作所在的事务只包含必要的SQL语句，不要在事务中加入查询、等待等耗时操作，减少锁的持有时间，降低冲突概率。

补充说明 #

虽然死锁日志显示锁冲突发生在主键索引，但也要注意唯一键domain_key的性能：插入时的唯一性检查会涉及该索引的锁操作，确保该索引没有碎片、统计信息准确，避免因为索引性能问题加剧锁竞争。

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