MySQL中按JobNum实现PalletNum独立自增的解决方案咨询

方案一：事务 + SELECT ... FOR UPDATE（推荐） #

这是处理分组自增并发冲突的标准方案，核心靠行锁保证同一工单下的编号生成唯一且连续。

具体实现步骤 #

1. 启动事务，确保查询和插入操作的原子性；
2. 用带FOR UPDATE的查询锁定目标工单的相关记录，获取当前最大托盘编号；
3. 计算新编号：无记录则从1开始，否则取最大值+1；
4. 执行插入后提交事务，失败则回滚。

代码示例 #

START TRANSACTION;

-- 锁定当前工单的记录，阻止并发修改
SELECT MAX(PalletNum) INTO @max_pallet FROM pallet_table WHERE JobNum = 'JOB001' FOR UPDATE;

-- 生成新托盘编号
SET @new_pallet = IF(@max_pallet IS NULL, 1, @max_pallet + 1);

-- 插入新托盘记录
INSERT INTO pallet_table (JobNum, PalletNum, CreatingMachine, LastUsedMachine, Locked)
VALUES ('JOB001', @new_pallet, 1001, 1001, 0);

COMMIT;

锁冲突处理 #

• 多会话并发操作同一工单时，后发起的会话会自动等待前一个事务完成，不会出现编号重复；
• 可通过SET innodb_lock_wait_timeout = 5;设置锁等待超时，超时后事务自动回滚，应用层捕获异常并重试即可。

方案二：前置插入触发器 #

触发器可自动在插入前计算编号，本质还是依赖事务内的行锁来避免并发问题。

具体实现 #

DELIMITER //

CREATE TRIGGER before_insert_pallet
BEFORE INSERT ON pallet_table
FOR EACH ROW
BEGIN
    -- 锁定当前工单的记录，保证编号唯一
    SELECT MAX(PalletNum) INTO @max_pallet FROM pallet_table WHERE JobNum = NEW.JobNum FOR UPDATE;
    SET NEW.PalletNum = IF(@max_pallet IS NULL, 1, @max_pallet + 1);
END //

DELIMITER ;

注意事项 #

• 触发器对所有插入操作生效，应用层无需额外处理编号逻辑，但调试难度比事务方案高；
• 同样存在锁等待场景，应用层需处理锁超时异常。

锁表方案的潜在问题 #

除了吞吐量低，还有这些隐患：

• 死锁风险：多会话同时锁不同资源时，容易触发死锁；
• 资源阻塞：锁表期间，整个表的所有操作（包括其他工单的查询、插入）都会被阻塞，并发能力骤降；
• 扩展性差：表数据量越大，锁表的代价越高，无法支撑高并发场景。

事务外管理ID的可行实现 #

单独创建一张工单编号序列表，存储每个JobNum对应的当前最大托盘编号，通过更新该表获取新编号：

步骤1：创建序列表 #

CREATE TABLE job_pallet_sequence (
    JobNum VARCHAR(50) PRIMARY KEY,
    CurrentMaxPallet INT NOT NULL DEFAULT 0
);

步骤2：获取新编号并插入主表 #

START TRANSACTION;

-- 锁定目标工单的序列行
SELECT CurrentMaxPallet INTO @current_max FROM job_pallet_sequence WHERE JobNum = 'JOB001' FOR UPDATE;

IF @current_max IS NULL THEN
    -- 首次生成该工单的托盘，初始化序列
    INSERT INTO job_pallet_sequence (JobNum, CurrentMaxPallet) VALUES ('JOB001', 1);
    SET @new_pallet = 1;
ELSE
    -- 更新序列并生成新编号
    SET @new_pallet = @current_max + 1;
    UPDATE job_pallet_sequence SET CurrentMaxPallet = @new_pallet WHERE JobNum = 'JOB001';
END IF;

-- 插入到主表
INSERT INTO pallet_table (JobNum, PalletNum, CreatingMachine, LastUsedMachine, Locked)
VALUES ('JOB001', @new_pallet, 1001, 1001, 0);

COMMIT;

优势 #

• 序列表数据量小，锁行代价更低，并发性能优于直接操作主表；
• 主表与序列表职责分离，逻辑更清晰。

内容的提问来源于stack exchange，提问作者Brenden