SQL实现：仅当同表status为FAILED时允许reference_id重复及并发处理

给定表结构：

create table dummy 
(
   id int primary key auto_increment,
   reference_id string not null,
   status enum('CREATED','FAILED','SUCCESS','RETRY') default 'CREATED',
   customId string unique not null
);

核心需求：

• 正常情况下reference_id需唯一，仅当该reference_id的最新记录status为FAILED时，允许插入同reference_id的新记录（插入时status固定为CREATED）
• 并发插入相同reference_id的请求需保证仅一条成功

方案1：原子化INSERT...SELECT语句（兼容性最优） #

用INSERT ... SELECT将检查和插入合并为一个原子操作，从根源避免并发竞态：

INSERT INTO dummy (reference_id, status, customId)
SELECT '要插入的reference_id', 'CREATED', '要插入的customId'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 
    FROM dummy 
    WHERE reference_id = '要插入的reference_id'
    AND status IN ('CREATED', 'SUCCESS', 'RETRY')
);

逻辑说明 #

• 子查询检查目标reference_id是否存在非FAILED状态的记录（CREATED/SUCCESS/RETRY）
• 只有当子查询无结果时，才执行插入操作
• 整个操作是原子的，数据库会自动保证并发场景下仅一个请求能满足条件完成插入，其他请求会返回插入行数0，业务层可据此判定插入失败

方案2：生成列+唯一约束（数据库层面自动维护） #

如果使用MySQL 8.0+或支持表达式索引的数据库（如PostgreSQL），可以通过生成列将条件约束转为唯一索引，让数据库自动拦截违规插入：

-- 添加生成列：非FAILED状态时等于reference_id，否则为NULL
ALTER TABLE dummy 
ADD COLUMN reference_valid VARCHAR(255) GENERATED ALWAYS AS (
    CASE WHEN status IN ('CREATED', 'SUCCESS', 'RETRY') THEN reference_id ELSE NULL END
) STORED;

-- 给生成列加唯一索引
ALTER TABLE dummy ADD UNIQUE INDEX idx_reference_valid (reference_valid);

逻辑说明 #

• 唯一索引不会限制NULL值，因此FAILED状态的同reference_id记录可以正常插入
• 当插入CREATED状态的记录时，若已有同reference_id的非FAILED记录，生成列会重复，触发唯一约束报错，直接拦截插入
• 业务层只需正常执行INSERT语句，捕获唯一约束冲突的错误即可返回失败响应

方案3：事务+行级锁（复杂场景适配） #

如果需要更复杂的判断逻辑（比如检查多条历史记录），可以用事务结合行级锁保证原子性：

BEGIN;
-- 查询目标reference_id的最新记录并加排他锁，阻塞其他并发请求
SELECT status FROM dummy WHERE reference_id = '要插入的reference_id' ORDER BY id DESC LIMIT 1 FOR UPDATE;
-- 根据查询结果判断：无记录 或 最新状态为FAILED时执行插入
INSERT INTO dummy (reference_id, status, customId) VALUES ('要插入的reference_id', 'CREATED', '要插入的customId');
COMMIT;

注意事项 #

• 该方案会引入锁等待，并发性能略低于前两种方案
• 需确保事务超时时间合理，避免长时间阻塞请求

为什么不推荐触发器？ #

触发器的检查逻辑与插入操作并非原子执行，并发场景下会出现竞态：两个请求同时通过触发器的检查，最终都插入成功，违反reference_id的唯一性要求。此外触发器逻辑隐蔽，后期维护成本更高。

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