PostgreSQL实现Ticket按caseId分段连续自增orderNumber方案

针对你的需求——同一caseId下orderNumber从1开始连续无断层，同时解决原同步方法的扩展性和性能问题，可以通过PostgreSQL的行级锁+自定义函数/触发器实现，具体方案如下：

核心思路 #

放弃全局同步，转而针对单个caseId做行级锁定：在插入新Ticket时，先锁定该caseId下的所有已有记录，获取当前最大的orderNumber并加1作为新值。这种方式仅让同一caseId的插入请求串行执行，不同caseId的请求完全并行，大幅提升扩展性。

具体实现 #

1. 基础表结构（对应你的实体） #

先确保数据库表结构与实体匹配：

CREATE TABLE ticket (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    case_id INT NOT NULL,
    order_number INT NOT NULL
);

2. 方法一：手动调用函数生成orderNumber #

先封装一个获取下一个合法orderNumber的函数，内部自动加锁保证并发安全：

CREATE OR REPLACE FUNCTION get_next_order_number(p_case_id INT)
RETURNS INT AS $$
BEGIN
    -- 锁定当前case_id的所有行，防止并发插入导致重复或断层
    RETURN COALESCE((SELECT MAX(order_number) FROM ticket WHERE case_id = p_case_id FOR UPDATE), 0) + 1;
END;
$$ LANGUAGE plpgsql VOLATILE;

插入数据时直接调用该函数：

INSERT INTO ticket (case_id, order_number)
VALUES (123, get_next_order_number(123));

3. 方法二：触发器自动填充（更省心） #

如果不想每次插入都手动调用函数，可以用触发器在插入前自动计算并填充orderNumber：

CREATE OR REPLACE FUNCTION set_ticket_order_number()
RETURNS TRIGGER AS $$
BEGIN
    -- 同样通过行级锁保证并发安全
    NEW.order_number := COALESCE((SELECT MAX(order_number) FROM ticket WHERE case_id = NEW.case_id FOR UPDATE), 0) + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- 创建触发器，插入前触发函数
CREATE TRIGGER trigger_set_ticket_order_number
BEFORE INSERT ON ticket
FOR EACH ROW
EXECUTE FUNCTION set_ticket_order_number();

此时插入只需指定case_id，orderNumber会自动生成：

INSERT INTO ticket (case_id) VALUES (123);

性能优化建议 #

为了提升MAX(order_number)的查询速度，避免全表扫描，给case_id和order_number建联合索引：

CREATE INDEX idx_ticket_case_order ON ticket (case_id, order_number DESC);

关键注意事项 #

• 为什么不用序列（Sequence）？序列的nextval()不会回滚，一旦事务回滚会导致orderNumber断层，完全不符合连续无断层的要求；而且如果给每个caseId创建单独序列，当caseId数量极大时会造成资源浪费，维护成本极高。
• 行级锁的范围：SELECT ... FOR UPDATE仅锁定当前caseId的所有行，不同caseId的插入请求互不干扰，相比原全局同步方法，扩展性和性能提升明显。
• 断层避免机制：因为每次都基于数据库中已存在的orderNumber最大值加1，即使有事务回滚，回滚的orderNumber不会留在库中，后续插入会自动补上该空缺，确保序列连续。

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