PostgreSQL中为商家生成独立递增订单号的实现方案咨询

问题背景 #

我正在开发一个分布式电商应用，多台服务器与PostgreSQL数据库交互。需为每个商家实现订单号计数器，确保各商家拥有独立的递增订单号（如商家1的订单号为1、2、3…，商家2的订单号也为1、2、3…），该订单号独立于数据库ID。现有SQL表结构如下：

CREATE TABLE vendors (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL);

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_number INT NOT NULL,
vendor_id INT REFERENCES vendors(id));

考虑过使用sequence和trigger，但不确定是否合适；也了解到optimistic lock可能有用，但不熟悉其实现，且担心锁机制会导致性能问题或需处理乐观锁失败情况，求可行实现方式。

方案1：专用计数器表 + 悲观锁（推荐） #

通过单独的计数器表存储每个商家的当前订单号最大值，利用PostgreSQL行级锁保证原子递增，是最稳妥的实现方式。

操作步骤 #

1. 创建计数器表：

CREATE TABLE vendor_order_counters (
    vendor_id INT PRIMARY KEY REFERENCES vendors(id),
    current_order_num INT NOT NULL DEFAULT 0
);

2. 初始化已有商家的计数器（新商家创建时同步插入默认值0）：

INSERT INTO vendor_order_counters (vendor_id, current_order_num)
SELECT id, 0 FROM vendors;

3. 原子生成订单号并插入订单：

WITH updated_counter AS (
    UPDATE vendor_order_counters
    SET current_order_num = current_order_num + 1
    WHERE vendor_id = ?
    RETURNING current_order_num
)
INSERT INTO orders (order_number, vendor_id)
SELECT current_order_num, ? FROM updated_counter;

优缺点 #

• 优点：完全保证订单号唯一性与递增性，实现简单，行级锁粒度小，足以应对绝大多数电商场景的并发需求。
• 缺点：高并发下会存在轻微锁等待，但单个商家的订单并发量通常不会达到无法接受的程度。

方案2：每个商家单独创建Sequence #

利用PostgreSQL的sequence特性，为每个商家生成独立的序列，通过触发器自动填充订单号。

操作步骤 #

1. 为已有商家批量创建序列（新商家创建时自动生成对应序列）：

DO $$
DECLARE
    vendor RECORD;
BEGIN
    FOR vendor IN SELECT id FROM vendors LOOP
        EXECUTE format('CREATE SEQUENCE vendor_%s_order_seq START WITH 1 INCREMENT BY 1', vendor.id);
    END LOOP;
END $$;

2. 创建触发器函数与触发器：

CREATE OR REPLACE FUNCTION set_order_number()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('SELECT nextval(''vendor_%s_order_seq'')', NEW.vendor_id) INTO NEW.order_number;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_order_number
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION set_order_number();

优缺点 #

• 优点：无需手动处理锁逻辑，sequence本身线程安全，性能表现优秀。
• 缺点：商家数量较多时会生成大量冗余sequence对象，增加数据库维护成本；商家删除时需手动清理对应序列，否则会产生垃圾对象。

方案3：乐观锁实现 #

通过版本号字段控制并发更新，仅当版本号匹配时才允许递增计数器，失败请求由应用层重试。

操作步骤 #

1. 修改计数器表添加版本号字段：

CREATE TABLE vendor_order_counters (
    vendor_id INT PRIMARY KEY REFERENCES vendors(id),
    current_order_num INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 0
);

2. 应用层逻辑示例（需处理重试）：

-- 应用层循环执行直到更新成功
UPDATE vendor_order_counters
SET current_order_num = current_order_num + 1, version = version + 1
WHERE vendor_id = ? AND version = ?;

-- 检查更新行数，若为0则重新获取当前版本号并重试
-- 更新成功后，取出current_order_num插入orders表

优缺点 #

• 优点：无锁等待，高并发下性能表现优异。
• 缺点：应用层需实现重试逻辑，增加代码复杂度；极端高并发场景下重试次数会激增，反而影响整体性能。

方案对比 #

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