直接改innodb_autoinc_lock_mode=2仅对simple inserts（如insert into t values()）无锁，bulk/mixed插入仍需语句级锁，且binlog必须为row格式；预分配id导致主键空洞不可回填。
直接改 innodb_autoinc_lock_mode = 2 能显著缓解锁争用，但不是所有 INSERT 都受益——它只对 INSERT INTO t VALUES () 这类简单插入无锁，而 INSERT INTO t SELECT ... 仍会卡住。
为什么你的 INSERT 总在 Waiting for table level lock？
现象很具体：QPS 上不去、show processlist 里一堆 Waiting for table level lock、慢日志里单条 INSERT 耗时从几毫秒跳到几百毫秒甚至秒级。这不是行锁或间隙锁的问题，是 InnoDB 在分配自增值时被卡住了。
根本原因在于默认的 innodb_autoinc_lock_mode = 1：普通单行或多行 INSERT VALUES () 只加轻量互斥锁，很快释放；但只要语句里带 SELECT（比如 INSERT INTO t SELECT * FROM s）、REPLACE SELECT 或 LOAD DATA，InnoDB 就会升级为语句级 AUTO_INC 表锁，直到整条语句执行完才放——哪怕只有一条这样的语句在跑，其他所有插入都得排队。
-
INSERT INTO user VALUES (NULL, 'a'), (NULL, 'b')→ 拿 ID 快，释放快 -
INSERT INTO user_backup SELECT * FROM user WHERE id > 1000→ 拿 ID 前就锁表，执行多久锁多久
innodb_autoinc_lock_mode=2 真的能关掉锁吗？
能，但仅限于 Simple inserts（如 INSERT INTO t VALUES ()）。Bulk inserts（含 SELECT）和 Mixed-mode inserts（部分行显式指定自增值）在 mode=2 下依然退化为语句级锁，不会提速——所以开了 mode=2 却还是卡，大概率是你写法本身就不走轻量路径。
mode=2 的真实行为是“预分配段”，比如一次拿 100 个 ID，事务回滚后这些 ID 不回收，导致主键空洞（如刚插完 1001，下一条变成 1057）。这不是 bug，是设计取舍。空洞不影响查询或索引结构，但如果你业务里硬编码了 “ID+1 就是下一条” 或用 ID 做分页推算，就会出问题。
- 必须确认
binlog_format = ROW：SELECT @@binlog_format返回不是ROW就别开，否则主从复制可能中断或丢数据 - 不能依赖 ID 连续性：事务回滚、批量插入部分失败、重试逻辑都会扩大空洞
-
INSERT ON DUPLICATE KEY UPDATE在 mode=2 下可能因预分配冲突触发内部重试，反而增加开销
不改参数，还能怎么绕过自增锁瓶颈？
参数只是工具，真正压垮并发的常是写入模式本身。与其死磕锁，不如换写法：
- 把大
INSERT SELECT拆成小批量多值INSERT：比如每次 500–1000 行，用INSERT INTO t VALUES (),(),()...替代全量 SELECT - 用
INSERT IGNORE或INSERT ON DUPLICATE KEY UPDATE替代REPLACE：后者本质是 DELETE + INSERT，必然推进自增计数器；前者只在真正插入新行时才消耗 ID - 避免高频撞同一行：如果用手机号做唯一键，8000 QPS 全挤在一条记录上，锁队列比自增锁还致命——考虑虚拟分片或 Redis 预扣减过滤无效请求
- 主键类型够大：用
unsigned bigint，别用int，空洞再多也得有命撑到那一天
最常被忽略的一点：innodb_autoinc_lock_mode 是全局配置，但它的效果完全取决于你写的 SQL 类型。同一张表上，VALUES 和 SELECT 插入走的是两条完全不同的锁路径。调参前先看慢日志里到底是什么语句在堵，比盲目设成 2 更有效。
