开发者日记

外观

自增 ID 断号踩坑实录：回滚跳号与锁模式实测 ​

问题背景：回滚了，为什么自增 ID 还是没了 ​

很多业务系统把 AUTO_INCREMENT 当作“天然连续编号”使用：订单号要求连续、明细行要求可对账、数据归档后希望编号没有空洞。一次批量导入失败后，开发同事发现事务确实回滚了，表数据也没有新增，但下一次插入的自增 ID 却跳过了一大段。这不是 MySQL 丢数据，而是 InnoDB 自增主键的分配机制与事务提交机制本来就不同。

下面先构造一张表，复现批量插入回滚后的断号现象。示例使用 MySQL 8.0，默认存储引擎为 InnoDB。

sql
-- 1. 建表：使用最常见的自增主键
CREATE TABLE t_order (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 先插入一条基线数据
INSERT INTO t_order(order_no, amount) VALUES ('NO_001', 10.00);

-- 3. 查看当前自增值；不同版本表现可能略有差异
SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 't_order';

-- 4. 模拟批量插入，随后主动回滚
START TRANSACTION;
INSERT INTO t_order(order_no, amount)
SELECT CONCAT('BATCH_', seq), 20.00
FROM (
  SELECT 1 AS seq UNION ALL SELECT 2 UNION ALL SELECT 3
  UNION ALL SELECT 4 UNION ALL SELECT 5
) s;
ROLLBACK;

-- 5. 表中只有 NO_001，但下一次插入可能从 7 开始
INSERT INTO t_order(order_no, amount) VALUES ('NO_002', 30.00);
SELECT id, order_no FROM t_order ORDER BY id;

查询结果通常类似下面这样：NO_001 的 ID 为 1，NO_002 的 ID 却可能是 7，中间 2 到 6 成为空洞。

根因分析：自增值分配不等于行数据提交 ​

理解这个现象，关键是区分两件事：

  • 事务回滚保护的是数据页、索引记录和 undo log 对应的行变更；
  • AUTO_INCREMENT 值是一种序列生成器，为了并发性能，分配后通常不会随事务回滚而归还。

如果每次回滚都把自增值“还回去”，MySQL 就必须记住每个事务预占了哪些编号，还要处理事务提交顺序不同导致的编号冲突。例如事务 A 拿到 2 到 6 但未提交，事务 B 已经拿到 7 并提交；若 A 回滚后把计数器改回 2，后续事务就可能生成与 B 冲突的 ID。因此，数据库更倾向于用“可能产生空洞”换取高并发插入能力。

INSERT 到达批量申请 ID自增计数器前进编号立即消耗写入聚簇索引受事务控制COMMIT行可见ROLLBACK撤销行，不还 ID下次插入继续向后取号

锁模式对比：0、1、2 到底差在哪 ​

MySQL InnoDB 通过参数 innodb_autoinc_lock_mode 控制自增锁行为。可以使用下面语句查看：

sql
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

三种模式的核心差异如下：

模式名称加锁方式编号连续性并发性能典型影响

| 0 | traditional | 持有表级 AUTO-INC 锁到语句结束 | 单语句内连续 | 最低 | 批量插入阻塞其他插入 | | 1 | consecutive | 简单插入用轻量锁，批量插入用表级锁 | 多数场景连续 | 中等 | MySQL 5.7 默认，可能预分配整段 ID | | 2 | interleaved | 不使用表级 AUTO-INC 锁，使用轻量互斥量 | 不保证连续 | 最高 | MySQL 8.0 默认，并发下更易交错 |

这里的“连续”是指一条 INSERT 语句生成的自增值在没有其他语句干扰时按顺序分配，并不是承诺业务表永远没有空洞。回滚、唯一键冲突、INSERT IGNORE、批量插入预分配，都可能让编号不可回收。

simple insert 与 bulk insert 的行为不同 ​

类似下面这种能提前确定行数的语句，属于简单插入：

sql
INSERT INTO t_order(order_no, amount)
VALUES ('A', 1), ('B', 2), ('C', 3);

而 INSERT ... SELECT、LOAD DATA 等在执行前可能无法准确知道会产生多少行，InnoDB 可能按批次预分配自增值。即使最终只插入 3 行，或者语句失败回滚，也可能消耗超过 3 个 ID。

sql
-- 批量导入常见写法：行数由 SELECT 结果决定
INSERT INTO t_order(order_no, amount)
SELECT customer_no, total_amount
FROM staging_orders
WHERE import_batch_id = 20250101;

在模式 1 下，这类语句通常会持有 AUTO-INC 表级锁直到语句结束，以保证基于语句复制时从库重放能得到相同自增 ID。模式 2 下并发放宽，但如果仍使用 STATEMENT 格式 binlog，自增 ID 在主从之间可能不安全。MySQL 8.0 默认使用 ROW 复制，因此通常可以安全获得更好的并发。

不要为了“连续”轻易改成模式 0 ​

一些开发者看到断号后，会尝试在线修改：

sql
-- 该参数是只读变量，不能通过 SET GLOBAL 在线修改
SET GLOBAL innodb_autoinc_lock_mode = 0;

该操作通常会报错，因为它属于启动参数，需要在配置文件中调整并重启实例：

ini
[mysqld]
innodb_autoinc_lock_mode=0

但即使改成 0，也只能改变自增锁的持有方式，不能解决事务回滚导致的 ID 空洞。相反，批量导入期间所有插入都可能被串行化，造成明显锁等待。在高并发订单表上，这种修改往往是用吞吐下降换一个无法实现的“连续 ID 承诺”。

实测步骤：观察锁等待与跳号 ​

可以开启两个会话模拟批量导入与普通订单写入。

会话 1：

sql
-- 会话 1：执行批量插入，但不提交
START TRANSACTION;
INSERT INTO t_order(order_no, amount)
SELECT CONCAT('LOCK_TEST_', n.seq), 1.00
FROM (
  SELECT 1 AS seq UNION ALL SELECT 2 UNION ALL SELECT 3
  UNION ALL SELECT 4 UNION ALL SELECT 5
) n;
-- 先不 COMMIT，观察会话 2

会话 2：

sql
-- 会话 2：尝试普通插入
INSERT INTO t_order(order_no, amount) VALUES ('NORMAL_ORDER', 9.90);

在模式 0 或批量语句触发 AUTO-INC 表级锁时，会话 2 可能等待会话 1 的语句结束。注意，AUTO-INC 锁通常在语句结束时释放，并不一定等待整个事务提交；但插入后的行锁、唯一索引记录锁仍会持续到提交或回滚。可以用下面语句观察：

sql
-- 查看当前连接与状态
SHOW FULL PROCESSLIST;

-- MySQL 8.0 查看最近锁等待
SELECT
  r.ENGINE_TRANSACTION_ID AS waiting_trx,
  b.ENGINE_TRANSACTION_ID AS blocking_trx,
  r.OBJECT_NAME,
  r.LOCK_TYPE,
  r.LOCK_MODE,
  r.LOCK_STATUS
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r
  ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b
  ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

随后会话 1 执行：

sql
ROLLBACK;

再查看数据和自增值，就会发现行被撤销，但编号已经被消耗：

sql
SELECT id, order_no, amount
FROM t_order
ORDER BY id DESC
LIMIT 10;

SHOW CREATE TABLE t_order\G

正确处理：把技术主键和业务编号分开 ​

如果业务只是需要唯一主键，自增 ID 有洞完全正常，不应在应用层假设 max(id) 与行数相等，也不应通过重置自增值来“修复空洞”。

sql
-- 危险做法：不要为了补洞随意重置 AUTO_INCREMENT
ALTER TABLE t_order AUTO_INCREMENT = 1;

当表中仍有数据时，MySQL 会把值提升到不小于现有最大 ID 的位置；删除部分数据后强行回调，还可能在复用 ID 时撞上外键、缓存、消息日志或下游数仓中的历史引用。

更稳妥的建模方式是：

sql
CREATE TABLE t_order_v2 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '技术主键，只保证唯一和趋势递增',
  order_no VARCHAR(32) NOT NULL COMMENT '业务订单号，由独立编号规则生成',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

业务编号可以使用号段模式、雪花算法或独立发号器。若确实要求“按日连续、作废留痕”，应建立编号表，而不是依赖事务回滚：

sql
CREATE TABLE t_biz_sequence (
  seq_date DATE NOT NULL,
  current_value BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (seq_date)
) ENGINE=InnoDB;

-- 通过行锁串行取号；调用方提交后编号即被视为已分配
UPDATE t_biz_sequence
SET current_value = LAST_INSERT_ID(current_value + 1)
WHERE seq_date = CURDATE();

SELECT LAST_INSERT_ID();

如果编号必须随业务成败回收，就需要额外的“作废状态”或定期回收任务，并接受它带来的串行化与复杂度。

避坑总结 ​

坑 1：用自增 ID 做连续凭证号 ​

常见现象是财务或审计要求“不能断号”，开发直接使用表主键当凭证号；批量导入失败后出现 1001、1052、1053，中间编号消失。修正方法是将自增 ID 定位为内部技术主键，凭证号由独立序列生成，并在业务上设计作废记录表，凭证一旦正式占用，即使业务失败也保留“作废”痕迹。

坑 2：认为设置锁模式 0 可以阻止回滚跳号 ​

常见现象是把 innodb_autoinc_lock_mode 改成 0 后，执行 ROLLBACK，ID 仍然不连续。原因是锁模式控制的是取号阶段的并发与锁定粒度，不负责事务失败后的编号回收。模式 0 还会让 INSERT ... SELECT、LOAD DATA 更容易阻塞普通写入，应只在特定兼容场景使用。

坑 3：删除数据后重置 AUTO_INCREMENT ​

常见现象是删除一批历史数据后执行 ALTER TABLE ... AUTO_INCREMENT=较小值，短时间看似恢复连续，后续却出现主键复用，导致外键历史指向错误、缓存对象串号、binlog 同步异常。正确做法是不回收主键；确需清空整表并重置编号，只能在没有外部引用的测试或初始化环境使用 TRUNCATE TABLE。

坑 4：忽视唯一键冲突和忽略插入造成的空洞 ​

INSERT IGNORE、ON DUPLICATE KEY UPDATE 未实际插入新行、唯一索引冲突后语句回滚，都可能消耗自增值。排查时不要只盯着显式 ROLLBACK，还应检查导入脚本是否使用了冲突忽略策略。可以通过通用查询日志、业务导入日志和 SHOW WARNINGS 对照确认。

坑 5：在高并发表上盲目追求连续锁 ​

连续编号意味着更强的串行化，往往会降低吞吐量并增加锁等待。MySQL 8.0 默认模式 2 配合 ROW binlog，通常更适合高并发 OLTP 业务。只有明确遇到旧版 STATEMENT 复制兼容问题，或经过压测证明需要模式 1 的语义时，才考虑调整，并同步评估主从复制与批量导入窗口。

最终结论很简单：自增 ID 的契约是唯一、整数、趋势递增，不是连续无洞。把主键、排序、路由、业务凭证号这四类需求拆开设计，才能避免在回滚、并发和锁模式上反复踩坑。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。