MySQL 8.0中InnoDB自增锁机制如何配置

MySQL 8.0 默认启用 innodb_autoinc_lock_mode=2（交错模式），提供最高并发插入性能，但要求 binlog_format=ROW 才安全；可通过 SELECT @@innodb_autoinc_lock_mode 查看当前值，该参数为只读，需修改配置文件并重启生效。

MySQL 8.0 默认启用 innodb_autoinc_lock_mode = 2（交错模式），无需额外配置即可获得最高并发插入性能，但必须确认主从复制方式是否兼容——若仍用基于语句的复制（SBR），则需手动调回 1 或 0。

如何查看当前自增锁模式

直接查系统变量即可确认实际生效值：

SELECT @@innodb_autoinc_lock_mode;

返回值为 0、1 或 2。注意：该变量是只读的运行时参数，不能用 SET GLOBAL 动态修改，必须写入配置文件后重启 MySQL 生效。

1. 0：传统模式，所有 INSERT-like 语句都持表级 AUTO-INC 锁直到语句结束
2. 1：连续模式（MySQL 5.7 默认），简单插入用 mutex，批量插入仍用表锁
3. 2：交错模式（MySQL 8.0+ 默认），所有插入均无锁，预分配自增值，可能断号

修改配置必须重启，且影响复制行为

在 my.cnf 的 [mysqld] 段落中添加：

innodb_autoinc_lock_mode = 1

重启后生效。关键点在于：2 模式下，INSERT ... SELECT 类批量操作不再阻塞其他插入，但生成的自增值不保证连续；更重要的是，它与基于语句的复制（SBR）不兼容——从库重放时可能因执行顺序不同导致自增值错位。MySQL 8.0 默认使用基于行的复制（RBR），所以 2 是安全的；若你显式启用了 binlog_format=STATEMENT，就必须同步设为 1 或 0。

什么场景下必须避免用 2 模式

以下情况应降级到 1 或 0：

1. 主从使用 binlog_format=STATEMENT，且业务逻辑依赖自增值严格连续（如导出 ID 范围做分页或归档）
2. 存在大量 INSERT ... SELECT 或 LOAD DATA，同时要求事务回滚后不“浪费”ID（2 模式下已预分配的值不会回收）
3. 应用层做了自增 ID 的硬编码假设，例如认为“最大 ID + 1 就是下一个可用值”

尤其注意：2 模式下混合插入（如 INSERT INTO t(id, name) VALUES (1,'a'), (NULL,'b')）会一次性预分配多个值，哪怕只有一行需要自增，也可能跳过中间编号。

自增锁不是行锁，也不受事务隔离级别影响

自增锁（AUTO-INC lock）是独立于 MVCC 和事务隔离级别的机制。无论你用 READ COMMITTED 还是 REPEATABLE READ，只要插入语句触发自增列分配，就按 innodb_autoinc_lock_mode 规则走。它不参与 InnoDB 的 S/X 行锁或意向锁兼容性矩阵，也不会出现在 SHOW ENGINE INNODB STATUS 的 LOCK WAIT 部分里——它只在内部控制自增值生成器的并发访问。

真正容易被忽略的是：即使把 innodb_autoinc_lock_mode 设为 2，如果表上没有有效索引覆盖自增列（比如没建主键或唯一索引，或自增列不在索引最左位），InnoDB 会直接报错 ERROR 1075，根本启动不了插入。这个约束比锁模式更底层，也更常被漏掉。