MySQL 并发序列号重复：MAX+1 没加锁，压测一下就撞车

后台有一个"创建批次"接口，单号格式是 BATCH-YYYYMM-0001，每月从 0001 重置。单测跑得好好的，一旦上线多人同时操作，偶发两条记录拿到同一个序列号，触发唯一键冲突报错。更可怕的是：如果当时没加 UNIQUE 约束，这两条完全重复的业务单号会静悄悄地落库，等到对账才发现。

现象

并发压测（两个请求相差几毫秒）：其中一个抛 Duplicate entry 'BATCH-202604-0003'
单请求、低频场景：完全正常，从未复现
序列号偶发跳号（某个事务回滚后下一个不是连续的）

根因

问题代码长这样：

// 错误示范
const [maxNo] = await pool.query(
  'SELECT batch_no FROM order_batches WHERE batch_no LIKE ? ORDER BY batch_no DESC LIMIT 1',
  [`${prefix}-%`]
)
let seq = 1
if (maxNo.length > 0) {
  const last = maxNo[0].batch_no.split('-').pop()
  seq = (parseInt(last || '0', 10) || 0) + 1
}
const batchNo = `${prefix}-${String(seq).padStart(4, '0')}`
await pool.execute('INSERT INTO order_batches (batch_no, ...) VALUES (?, ...)', [batchNo, ...])

两个并发请求的执行时序：

请求 A: SELECT MAX → 读到 42
请求 B: SELECT MAX → 读到 42（A 还没 INSERT）
请求 A: 计算 43，INSERT
请求 B: 计算 43，INSERT → 💥 重复

InnoDB 默认隔离级别是 REPEATABLE READ，但普通 SELECT 走的是一致性读（快照），不会阻塞其他会话的读操作。两个事务同时读到同一个最大值，就都计算出了相同的下一个序列号。

坑：裸 SELECT MAX 在并发下没有任何互斥语义

SELECT MAX(col) 即使在事务里，也只是读快照，不锁行。两个并发事务可以同时读到相同的最大值，然后各自插入同一个"下一个"序列号。单线程测试永远不会触发这个问题，必须压测或真实多用户才会复现。

正解

在事务内把 SELECT 改为 SELECT ... FOR UPDATE，并确保 SELECT 和 INSERT 走同一个连接：

const batchNo = await withTransaction(async (conn) => {
  // FOR UPDATE：获取行锁（或间隙锁），阻塞其他事务读这一行
  const [maxNo] = await conn.query(
    'SELECT batch_no FROM order_batches WHERE batch_no LIKE ? ORDER BY batch_no DESC LIMIT 1 FOR UPDATE',
    [`${prefix}-%`]
  )
  let seq = 1
  if (maxNo.length > 0) {
    const last = maxNo[0].batch_no.split('-').pop()
    seq = (parseInt(last || '0', 10) || 0) + 1
  }
  const no = `${prefix}-${String(seq).padStart(4, '0')}`
 
  await conn.execute(
    'INSERT INTO order_batches (batch_no, ...) VALUES (?, ...)',
    [no, ...]
  )
  return no
})

三个关键点：

FOR UPDATE 必须在事务内：如果没有显式开启事务，锁会在语句执行完后立即释放，完全没有互斥效果。
SELECT 和 INSERT 必须用同一个连接（conn，不是连接池的 pool）：事务是连接级别的，跨连接会话独立，锁不共享。
加 UNIQUE KEY (batch_no) 作兜底：万一隔离级别被改、或代码路径绕过事务，数据库层面仍能拦截重复写入，不至于静默落脏数据。

正解：FOR UPDATE + 事务 + UNIQUE KEY 三件套

SELECT ... FOR UPDATE 让第二个并发事务阻塞等待，直到第一个事务提交后才能继续读。读到的值已经是最新的 MAX，计算出的序列号不会重复。再加一个 UNIQUE KEY 做数据库层兜底，双重保障。

备选方案

如果不需要按前缀分组重置，可以考虑：

自增主键：最简单，但回滚会产生间隙，且无法嵌入日期前缀。
专用计数表 + UPDATE counters SET val=val+1 WHERE key=? RETURNING val（MySQL 8.0+）：语义更清晰，适合多类型序列号共用一张表管理。

FOR UPDATE on MAX 的适用场景：序列号需要按前缀/分组重置（如每月从 0001 重新开始），且不想引入额外的计数表。

一句话外卖

业务序列号生成绝对不能用裸 SELECT MAX：加锁（FOR UPDATE）、包事务、建唯一索引，缺一不可。