从AUTO-INC等待到模式2调优：批量插入阻塞全表写入的内核机制拆解

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

有一次线上告警：订单创建接口的P99从80ms飙升到3秒。

第一反应是锁等待。查sys.innodb_lock_waits，没有行锁冲突。查慢查询日志，发现慢的都是INSERT语句。查磁盘I/O，正常。查网络，正常。

最后在SHOW ENGINE INNODB STATUS的输出里，看到了一段AUTO-INC等待信息。

问题找到了：不是行锁，是自增锁。

一条普通的INSERT，被一个正在跑的批量插入卡了3秒——因为批量插入持有自增锁直到语句结束，期间其他所有INSERT都在排队。

先搞清楚这条链路上的几个概念：

自增锁（AUTO-INC Lock）——InnoDB为了保证自增值唯一且连续，在分配自增值时加的锁。它和行锁不是一回事，是一种特殊的表级锁。

innodb_autoinc_lock_mode——控制自增锁行为的参数，有三种模式：0（传统模式）、1（连续模式）、2（交错模式）。

批量插入——INSERT ... SELECT、LOAD DATA、多行INSERT ... VALUES。这类语句需要一次性分配多个自增值。

Simple Insert——能提前确定插入行数的INSERT，比如单行INSERT、INSERT ... VALUES带固定行数。这类语句在模式1下只短暂持有自增锁。

一、三种模式的区别

模式0（传统模式）：所有INSERT语句（包括Simple Insert）都持有自增锁直到语句结束。并发插入性能最差，但自增值严格连续。MySQL 5.1之前的默认值。

模式1（连续模式）：Simple Insert只在分配自增值的瞬间持有锁，分配完立即释放。批量插入（INSERT ... SELECT、LOAD DATA）仍然持有锁直到语句结束。这是MySQL 5.1到8.0的默认值。

模式2（交错模式）：所有INSERT语句都只在分配自增值时短暂持锁，不等待语句结束。并发性能最好，但自增值可能不连续——多个语句交错分配，同一语句内的自增值可能不连续。MySQL 8.0的默认值。

关键差异：模式1下，批量插入会长时间持有自增锁。如果批量插入跑3秒，自增锁就持有3秒。期间所有其他INSERT——哪怕只是一条单行INSERT——都必须等锁释放。

二、为什么一条INSERT会被卡3秒

我遇到的那次告警，链路是这样的：

1. 凌晨有一个数据同步任务在跑INSERT ... SELECT，把历史数据从一张表搬到另一张表

2. 这个批量插入执行了3秒

3. 在模式1下，批量插入从开始到结束，一直持有自增锁

4. 期间所有订单创建的INSERT都在等这个锁

5. P99从80ms飙升到3秒

很多人会误以为是行锁或表锁。 但SHOW ENGINE INNODB STATUS里的AUTO-INC等待信息很明确——等的是自增锁，不是行锁。

这个问题的隐蔽性在于：平时单行INSERT很快，自增锁瞬间释放，完全感知不到。一旦有批量插入在跑，自增锁的持有时间被拉长到整个语句的执行时间，问题才暴露出来。

三、怎么确认是自增锁的问题

方法一：看InnoDB状态

SHOW ENGINE INNODB STATUS\G

搜索AUTO-INC关键字。如果有类似Mutex at ... lock wait的输出，说明有会话在等自增锁。

方法二：看performance_schema

SELECT * FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'TABLE' AND LOCK_TYPE = 'AUTO-INC';

或者更直接地看等待事件：

SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%autoinc%';

方法三：看参数当前值

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

如果值是1，且业务中有批量插入，就有被阻塞的风险。

四、解决方案：模式2 + 批量插入拆批

方案一：确认innodb_autoinc_lock_mode=2

MySQL 8.0默认已经是2。如果是5.7升级上来的，可能还是1。改成2后，所有INSERT都只在分配自增值时短暂持锁，批量插入不再长时间阻塞其他写入。

代价是：自增值可能不连续。主键出现“空洞”，同一个语句内的自增值可能被其他并发语句穿插。对于大多数业务来说，自增主键不连续完全可接受——主键的作用是唯一标识，不是连续编号。

方案二：批量插入拆批执行

即使模式是2，也不建议一次性INSERT ... SELECT几百万行。拆成每批几千行，提交后再跑下一批。减少单次语句的执行时间，也就减少了自增锁的持有时间。

方案三：避免在事务中做大批量INSERT

大批量INSERT放在事务里，事务不提交，自增锁可能被持有更久。拆批并独立提交，减少锁的持有窗口。

五、小结

一条INSERT卡3秒，根因往往不在行锁，在自增锁。innodb_autoinc_lock_mode的模式选择直接决定批量插入时自增锁的持有时间。模式1下，批量插入持有锁直到语句结束，期间所有其他INSERT全部阻塞。模式2下，所有INSERT只短暂持锁，并发性能最好，代价是自增值可能不连续。MySQL 8.0默认已经是2，从5.7升级上来的环境需要确认一下。批量插入拆批执行，是减少自增锁持有时间的另一个关键手段。

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~