PostgreSQL触发器生成唯一订单号并发冲突问题求解

问题根源 #

原触发器通过COUNT(*)获取对应国家的订单数量来生成流水号，并发场景下多个事务会同时读到相同的COUNT值，导致生成重复的订单号，最终触发唯一约束报错。

方案一：专用流水号表+行级锁（推荐） #

创建单独的表存储每个国家的当前最大流水号，生成订单号时通过行级锁锁定对应国家的记录，确保同一时间只有一个事务能获取并更新流水号，从根源避免并发冲突。

1. 创建流水号维护表 #

CREATE TABLE country_order_seq (
    country_code VARCHAR(10) PRIMARY KEY, -- 与order_main的order_country_code对应
    current_seq INT NOT NULL DEFAULT 0    -- 当前最大流水号
);

2. 初始化已有国家的流水号 #

如果已有订单数据，先同步初始化该表：

INSERT INTO country_order_seq (country_code, current_seq)
SELECT order_country_code, COUNT(*)
FROM order_main
GROUP BY order_country_code;

3. 修改触发器函数 #

CREATE OR REPLACE FUNCTION func_gen_order_no()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
DECLARE
    next_seq INT;
BEGIN
    -- 锁定对应国家的行，防止并发读取重复值
    SELECT current_seq + 1 INTO next_seq
    FROM country_order_seq
    WHERE country_code = NEW."order_country_code"
    FOR UPDATE;

    -- 处理新国家的首次订单
    IF next_seq IS NULL THEN
        next_seq := 1;
        INSERT INTO country_order_seq (country_code, current_seq)
        VALUES (NEW."order_country_code", next_seq);
    ELSE
        -- 更新流水号为新数值
        UPDATE country_order_seq
        SET current_seq = next_seq
        WHERE country_code = NEW."order_country_code";
    END IF;

    -- 格式化生成订单号
    NEW."order_no" = CONCAT(NEW."order_country_code", TO_CHAR(next_seq, '000000FM'));

    RETURN NEW;
END;
$$;

优势 #

• 行级锁粒度极小，仅锁定对应国家的记录，对并发性能影响极低
• 流水号存储独立，避免依赖订单表的COUNT操作（数据量大时COUNT性能差）
• 逻辑清晰，易于维护和扩展新国家

方案二：按国家创建独立序列 #

如果国家数量少且固定，可为每个国家单独创建序列，触发器中根据国家代码调用对应序列生成流水号。

1. 为国家创建序列（示例：中国CN、美国US） #

CREATE SEQUENCE seq_order_cn START 1 INCREMENT 1;
CREATE SEQUENCE seq_order_us START 1 INCREMENT 1;

2. 修改触发器函数 #

CREATE OR REPLACE FUNCTION func_gen_order_no()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
DECLARE
    next_seq INT;
BEGIN
    CASE NEW."order_country_code"
        WHEN 'CN' THEN next_seq := nextval('seq_order_cn');
        WHEN 'US' THEN next_seq := nextval('seq_order_us');
        -- 其他国家依次添加分支
        ELSE 
            RAISE EXCEPTION 'Unsupported country code: %', NEW."order_country_code";
    END CASE;

    NEW."order_no" = CONCAT(NEW."order_country_code", TO_CHAR(next_seq, '000000FM'));

    RETURN NEW;
END;
$$;

优势 #

• 序列是PostgreSQL原生高并发自增机制，性能最优
• 无需额外锁操作，数据库自动处理并发

劣势 #

• 国家数量多或动态新增时，需频繁创建序列，维护成本高

注意事项 #

• 保留order_no字段的唯一约束作为最后一道防线，极端情况也能避免脏数据
• 方案一中的插入、更新操作已通过事务保证原子性，无需额外处理

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