PostgreSQL并发插入时before insert触发器如何避免batch_id重复

你当前的触发器逻辑出现重复batch_id是必然结果：PostgreSQL默认读提交隔离级别下，触发器内的普通SELECT MAX(batch_id)查询不会加排他锁，两个并发插入同company_id记录的事务，会同时读到相同的最大batch_id值，各自计算+1后写入，就会产生重复值。另外你SQL里的GROUP BY company_id是冗余写法，WHERE条件已经限定了单个company_id，不需要分组操作。

根据业务对batch_id的连续性要求、并发量级，可以选以下方案：

方案1：独立序列表+行级排他锁（最通用的数据库层方案） #

不要直接在orders表上做max计算，单独建一张维护每个公司当前批次号的表，通过行锁保证同company_id的批次号生成串行，锁粒度更细，不会阻塞其他company_id的插入操作。

1. 先建序列维护表：

CREATE TABLE company_batch_seq (
  company_id bigint PRIMARY KEY,
  current_batch_id bigint NOT NULL DEFAULT 0
);

2. 重写触发器逻辑：

CREATE OR REPLACE FUNCTION before_orders_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  -- 新公司首次插入时初始化序列记录
  INSERT INTO company_batch_seq(company_id)
  VALUES (NEW.company_id)
  ON CONFLICT (company_id) DO NOTHING;

  -- 对对应公司的序列行加排他锁，同公司的批次生成会在此排队，避免并发读重复
  UPDATE company_batch_seq
  SET current_batch_id = current_batch_id + 1
  WHERE company_id = NEW.company_id
  RETURNING current_batch_id INTO NEW.batch_id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

注意：如果事务回滚，已经自增的batch_id不会回退，会出现跳号，如果业务要求batch_id必须严格连续无空洞，这个方案不适用。

方案2：动态绑定独立序列（中低公司量级场景适用） #

如果公司总量不大，可以为每个company_id创建独立的PostgreSQL序列，通过序列的原生nextval保证不重复，并发性能比行锁方案更好：

CREATE OR REPLACE FUNCTION before_orders_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
  seq_name text := 'orders_batch_seq_' || NEW.company_id;
BEGIN
  -- 新公司首次插入时创建对应序列
  EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I', seq_name);
  -- 取序列下一个值赋值
  EXECUTE format('SELECT nextval(%L)', seq_name) INTO NEW.batch_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

注意：如果company_id量级达到十万、百万级，会产生大量序列对象，增加数据库元数据管理负担，不适合这类场景，同样存在事务回滚跳号的问题。

方案3：应用层生成ID（高并发场景首选） #

如果业务并发量很高，不建议在数据库触发器内实现批次号生成逻辑，可以把逻辑上移到应用层：

• 按company_id加细粒度分布式锁（比如Redis锁），在应用层维护计数生成batch_id
• 直接用雪花算法、号段模式等分布式ID生成方案生成batch_id，完全不依赖数据库计数，性能最高，从根源上避免并发重复问题

不推荐通过调整数据库隔离级别为可串行化来解决问题，这种方式会大幅提升事务回滚概率，高并发下性能损耗极大，不适合生产环境使用。

兜底保障 #

无论选哪种方案，都建议给orders表加(company_id, batch_id)的联合唯一约束，作为最后一道防线，避免异常情况下重复数据落库：

ALTER TABLE orders ADD CONSTRAINT uk_orders_companyid_batchid UNIQUE (company_id, batch_id);

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