怎么在SQL存储过程中实现自定义流水号的并发生成-SQL-PHP中文网

直接用MAX(id)+1在并发下必然出错，因多个事务同时SELECT MAX(sn)会读到相同值，再各自加1插入，导致重复流水号；根本原因是“读-改-写”操作缺乏原子性保护，存在确定性竞态窗口。

怎么在sql存储过程中实现自定义流水号的并发生成

为什么直接用 `MAX(id)+1` 在并发下会出错

多个事务同时执行 SELECT MAX(sn) FROM orders，拿到同一个最大值，再各自加 1 插入，必然产生重复流水号。这不是“概率低”的问题，而是确定性冲突——只要并发量稍高（比如 Web 请求批量下单），几秒内就能复现 duplicate entry 错误。

根本原因在于：读取和写入之间没有原子性保护，中间存在竞态窗口。

避免在应用层做“查+算+插”三步操作
禁止在存储过程中用 SELECT ... INTO @var 后再 INSERT，除非加了显式锁
即使加了 SELECT ... FOR UPDATE，也必须确保锁定范围覆盖所有可能被后续插入影响的行（例如用范围锁或唯一索引兜底）

用 `GET_LOCK()` 实现轻量级互斥（MySQL 专用）

适用于低频、对延迟不敏感的场景，比如后台定时生成批次号。它不依赖表结构变更，也不需要事务隔离级别调高，但要注意锁名唯一性和超时设置。

示例逻辑：

DELIMITER $$
CREATE PROCEDURE gen_order_sn(OUT out_sn VARCHAR(20))
BEGIN
  DECLARE v_lock_result INT DEFAULT 0;
  DECLARE v_base_sn VARCHAR(20);
<p>-- 尝试获取全局锁，最多等 5 秒
SELECT GET_LOCK('order_sn_generator', 5) INTO v_lock_result;
IF v_lock_result = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Failed to acquire lock for sn generation';
END IF;</p><p>-- 安全读取当前最大值（此时已互斥）
SELECT IFNULL(MAX(sn), 'ORD2024000000') INTO v_base_sn FROM orders WHERE sn LIKE 'ORD2024%';</p><p>-- 解析并递增（假设格式为 ORD2024 + 6位序号）
SET out_sn = CONCAT('ORD', YEAR(NOW()), LPAD(SUBSTR(v_base_sn, 7)+1, 6, '0'));</p><p>-- 立即释放锁（不要等到事务结束）
DO RELEASE_LOCK('order_sn_generator');
END$$
DELIMITER ;
</p>

GET_LOCK() 是会话级的，不同连接间生效；锁名必须字符串字面量，不能是变量
必须显式调用 RELEASE_LOCK()，否则锁会持续到会话断开，极易导致死锁
该方案无法防止其他服务（如 Java 应用）绕过存储过程直接插入，需统一入口

用带 `INSERT ... ON DUPLICATE KEY UPDATE` 的唯一约束兜底（推荐）

这是更健壮的做法：把流水号生成逻辑下沉到单条 INSERT 语句中，靠唯一索引强制排重，失败后重试。它不阻塞其他事务，吞吐高，且天然兼容分布式部署（只要 DB 是单点）。

前提是在 orders 表上建唯一索引：ALTER TABLE orders ADD UNIQUE KEY uk_sn (sn);

核心思路是：先预生成一个候选号，尝试插入；若冲突，则生成下一个再试，最多循环 10 次（防无限重试）：

DELIMITER $$
CREATE PROCEDURE gen_sn_safe(OUT out_sn VARCHAR(20))
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE candidate VARCHAR(20);
  DECLARE last_max BIGINT DEFAULT 0;
<p>-- 先快速取一次当前最大序号（不加锁，允许脏读，仅作起点）
SELECT IFNULL(MAX(CONVERT(SUBSTR(sn, 7), UNSIGNED)), 0) INTO last_max 
FROM orders WHERE sn LIKE 'ORD<strong>__</strong>%';</p><p>WHILE i </p><pre class="brush:php;toolbar:false;">-- 尝试插入占位记录（可选：用临时表或专用 sn_gen 表减少主表干扰）
INSERT INTO orders (sn, created_at) VALUES (candidate, NOW()) 
  ON DUPLICATE KEY UPDATE sn = VALUES(sn);

-- 检查是否成功插入（影响行数为 1 表示新记录）
IF ROW_COUNT() = 1 THEN
  SET out_sn = candidate;
  LEAVE;
END IF;

SET i = i + 1;

END WHILE;

IF i >= 10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Failed to generate unique sn after 10 attempts'; END IF; END$$ DELIMITER ;

关键不是“一定生成第 N 个”，而是“一定生成一个没被用过的”——业务上通常接受跳号
如果流水号要求严格连续，此方案不适用；必须用序列器表 + SELECT ... FOR UPDATE，但性能明显下降
注意 ROW_COUNT() 返回值：INSERT 成功是 1，ON DUPLICATE 是 2（更新）或 0（无变化），需结合实际测试

为什么不用 `AUTO_INCREMENT` 或序列器表加锁

AUTO_INCREMENT 本身是线程安全的，但它生成的是纯数字，无法满足“前缀+日期+固定长度”这类业务流水号格式；而序列器表（如 seq_table(counter INT)）配合 SELECT counter FROM seq_table FOR UPDATE 虽然能保证顺序，但会成为热点瓶颈——所有生成请求都卡在同一行锁上，QPS 很快见顶。

真正要权衡的是：业务是否真的需要“绝对连续”？大多数财务、物流系统只校验唯一性，不校验是否连贯。强行保连续，代价是锁等待、超时、重试逻辑膨胀，还容易在主从延迟时出现从库幻读。

最容易被忽略的一点：流水号一旦对外暴露（比如返回给前端、写入日志），就必须保证其最终一致性——哪怕生成时短暂失败，也不能让下游看到“空号”或“重复号”。所以生成逻辑必须和主业务 INSERT 在同一事务中，或者用最终一致性补偿（如状态机+定时校对）。