MySQL自增锁实际是表级轻量互斥机制，InnoDB在innodb_autoinc_lock_mode=1时普通INSERT不锁表但INSERT...SELECT仍持表锁，mode=0全锁表，mode=2需ROW复制；REPLACE总消耗自增ID而INSERT...ON DUPLICATE KEY UPDATE仅新行插入时才推进计数器。
MySQL 自增锁（AUTO_INCREMENT Lock）到底锁什么
MySQL 的 AUTO_INCREMENT 列在插入时并非“无锁”，而是由一个表级的轻量级互斥机制保护——但这个机制在不同存储引擎和版本中行为差异极大。InnoDB 在 5.1.22+ 默认使用 innodb_autoinc_lock_mode=1（连续模式），此时普通 INSERT 不锁表，只对语句执行期间预分配的自增值加内存计数；但 INSERT ... SELECT、REPLACE ... SELECT 或带子查询的批量插入仍会持有 TABLE LOCK 直到语句结束。
容易踩的坑：
-
innodb_autoinc_lock_mode=0（传统模式）下，所有带AUTO_INCREMENT的INSERT都会持表锁，高并发插入直接串行化 - 即使
lock_mode=1，若事务中混用INSERT ... SELECT和单行INSERT，自增 ID 可能不连续且间隙变大 - 主从复制时，
STATEMENT格式依赖自增值顺序，lock_mode=2（交错模式）可能导致从库复制失败
为什么 INSERT ... SELECT 容易触发长等待甚至死锁
这类语句本质是先读（SELECT 部分）再写（INSERT 部分），InnoDB 会对 SELECT 扫描的每一行加 next-key lock，同时为新插入行申请自增 ID —— 如果多个事务并发执行相似语句，可能形成「A 等 B 的写锁，B 等 A 的读锁」的循环依赖。
典型现象：SHOW ENGINE INNODB STATUS 中看到 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ... AUTO_INC 和 *** (2) HOLDS THE LOCK(S): ... index `PRIMARY` of table `db`.`t` trx id ... 并列出现。
实操建议：
- 避免在高并发写场景下用
INSERT INTO t1 SELECT ... FROM t2，改用应用层分页 + 批量单行INSERT - 确需批量导入时，临时设
SET innodb_autoinc_lock_mode = 2（仅限ROW复制格式），但要接受 ID 不连续 - 给
SELECT部分显式加SELECT ... FOR UPDATE或LOCK IN SHARE MODE，让锁范围明确，减少隐式锁升级
REPLACE、INSERT ON DUPLICATE KEY UPDATE 与自增锁的交互
REPLACE 实际是 DELETE + INSERT，会触发两次自增逻辑：DELETE 不影响计数器，但 INSERT 必然消耗一个新 ID，即使最终因唯一键冲突被回滚，ID 也不会回收 —— 这导致 ID 快速耗尽，且在并发下加剧锁竞争。
INSERT ... ON DUPLICATE KEY UPDATE 更安全：它先尝试插入，冲突时转为更新，**不额外申请自增 ID**（除非真正插入了新行）。但注意：如果表有多个唯一索引，冲突检测顺序依赖索引定义顺序，可能意外触发插入而非更新。
关键区别：
-
REPLACE总是增加auto_increment值，哪怕没真正插入 -
INSERT ... ON DUPLICATE KEY UPDATE仅在实际插入新行时才推进计数器 - 两者在冲突路径上都可能持有
gap lock，但REPLACE的 DELETE 阶段还会持有被删行的记录锁，延长锁持有时间
如何验证当前自增锁行为是否异常
不能只看 SHOW CREATE TABLE，必须结合运行时状态判断。最直接的方式是观察 information_schema.INNODB_TRX 和 INNODB_LOCK_WAITS，并比对 innodb_autoinc_lock_mode 设置。
快速检查命令：
SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_autoinc_lock_mode';
模拟竞争验证：
-- 会话 A START TRANSACTION; INSERT INTO t VALUES (); -- 占住一个自增 ID -- 不提交
-- 会话 B（立即执行） INSERT INTO t SELECT * FROM t LIMIT 1; -- 观察是否卡住
真正复杂的地方在于：自增锁本身不记录在 INNODB_LOCKS 表中，它是内存态的轻量结构，只能通过等待现象、错误日志（如 Lock wait timeout exceeded）和 SHOW ENGINE INNODB STATUS 中的 AUTO_INC 提示交叉印证。线上环境一旦出现自增相关延迟，优先查 lock_mode 配置和是否有隐式批量插入语句。
