PostgreSQL异步插入场景下避免分组自增folio字段重复方案咨询

问题根因 #

触发器中执行max(folio)查询时默认不加锁，高并发场景下多个事务会同时读取到相同的分组最大值，各自加1后插入就会产生重复值，默认的读提交隔离级别无法阻止这类并发冲突。

解决方案 #

第一步：添加唯一约束兜底 #

首先从数据库层面禁止重复值入库，避免脏数据生成：

ALTER TABLE "folios"
ADD CONSTRAINT unique_service_type_prefix_folio UNIQUE (service_type, prefix, folio);

第二步：优化触发器逻辑，根据并发量级选择对应方案 #

方案1：咨询锁实现（中等并发场景推荐，改造成本低）

基于分组字段生成唯一的事务级咨询锁，保证同一时间只有一个事务操作同一个分组的folio生成：

CREATE OR REPLACE FUNCTION public.insert_folio()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE 
    incremental INTEGER;
    lock_key BIGINT;
BEGIN
    -- 基于分组字段生成唯一锁键
    lock_key = hashtext(NEW.service_type || '|' || NEW.prefix);
    -- 申请排他事务锁，同分组的插入请求会排队等待锁释放
    PERFORM pg_advisory_xact_lock(lock_key);
    
    SELECT max(f.folio) INTO incremental 
    FROM "folios" f
    WHERE f.prefix = NEW.prefix AND f.service_type = NEW.service_type;
    
    NEW.folio = COALESCE(incremental + 1, 1);
    RETURN NEW;
END;
$function$;

该方案无需调整表结构，事务结束后锁会自动释放，性能损耗极低。

方案2：独立计数表实现（高并发场景推荐，性能最优）

单独维护分组计数表，直接通过更新计数表获取最新folio值，避免每次查询原表的最大值：

1. 新建计数表并初始化历史数据

-- 建计数表
CREATE TABLE folio_counters (
    service_type TEXT NOT NULL,
    prefix TEXT NOT NULL,
    current_folio INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (service_type, prefix)
);

-- 初始化历史分组的最大值
INSERT INTO folio_counters (service_type, prefix, current_folio)
SELECT service_type, prefix, max(folio)
FROM folios
GROUP BY service_type, prefix;

2. 替换触发器函数

CREATE OR REPLACE FUNCTION public.insert_folio()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    -- 插入或更新计数表，直接返回最新的folio值
    INSERT INTO folio_counters (service_type, prefix, current_folio)
    VALUES (NEW.service_type, NEW.prefix, 1)
    ON CONFLICT (service_type, prefix) DO UPDATE
    SET current_folio = folio_counters.current_folio + 1
    RETURNING current_folio INTO NEW.folio;
    
    RETURN NEW;
END;
$function$;

该方案依靠PostgreSQL行锁机制自动处理并发排队，完全避免重复问题，同时省去了每次全表扫描求max的开销，并发性能更高。

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