PostgreSQL并发事务中如何从表字段获取最新id避免插入冲突

问题本质 #

你的问题属于典型的读-改-写竞态条件：默认读提交事务隔离级别下，多个并发事务查询max(fbillid)时，无法感知到其他未提交事务生成的新ID，会拿到相同的ID值，最终插入时触发主键冲突。

可行解决方案 #

方案1：查询时加行锁（改造成本最低） #

仅需要在查询max值的语句末尾加FOR UPDATE，即可锁定当前查询条件匹配的所有行，其他事务查询相同条件的max值时会阻塞，直到当前事务提交/回滚，避免ID重复。
修改后的核心代码：

do $$ declare
  vnewid bigint;
  vdata jsonb;
begin
  -- 其他逻辑
  -- 加FOR UPDATE锁定对应条件的行范围，阻塞同条件的并发查询
  select coalesce(max(fbillid),0)+1 into vnewid 
  from bill 
  where flinkid=123 and fsystem='test' and fnaviid=123
  FOR UPDATE;
  -- 其他逻辑
  insert into bill(flinkid,fsystem,fnaviid,fbillid,fdata)
  values(123,'test',123,vnewid,vdata);
  -- 其他逻辑
  update bill set fdata=jsonb_set_lax(fdata,'{head,fbillno}','XXX'::jsonb)
  where flinkid=123 and fsystem='test' and fnaviid=123 and fbillid=vnewid;
  -- 其他逻辑
end;
$$ language plpgsql;

• 优点：不需要修改表结构，原有业务逻辑完全不变，生成的ID完全连续无跳号
• 缺点：相同业务条件下并发写入很高时，会出现锁等待，吞吐量受限

方案2：独立序列号生成表（性能最优） #

新增一张ID生成辅助表，使用INSERT ... ON CONFLICT ... RETURNING的原子操作生成ID，锁粒度仅为生成表的单行，性能远高于锁业务表。
首先创建辅助表：

create table bill_id_generator(
  flinkid bigint not null,
  fsystem text not null,
  fnaviid bigint not null,
  current_max_id bigint not null default 0,
  constraint bill_id_generator_pkey primary key (flinkid, fsystem, fnaviid)
);

修改ID生成逻辑：

do $$ declare
  vnewid bigint;
  vdata jsonb;
begin
  -- 原子生成ID，不存在则初始化，存在则+1返回
  insert into bill_id_generator(flinkid,fsystem,fnaviid,current_max_id)
  values(123,'test',123,1)
  on conflict (flinkid,fsystem,fnaviid) do update 
  set current_max_id = bill_id_generator.current_max_id + 1
  returning current_max_id into vnewid;
  -- 后续插入、更新逻辑不变
  insert into bill(flinkid,fsystem,fnaviid,fbillid,fdata)
  values(123,'test',123,vnewid,vdata);
  update bill set fdata=jsonb_set_lax(fdata,'{head,fbillno}','XXX'::jsonb)
  where flinkid=123 and fsystem='test' and fnaviid=123 and fbillid=vnewid;
end;
$$ language plpgsql;

• 优点：锁粒度极细，并发性能高，不存在大范围锁等待
• 缺点：需要额外维护ID生成表，事务回滚时会出现ID跳号

方案3：冲突自动重试（适合低并发场景） #

在逻辑外层加异常捕获，命中主键冲突时自动重试生成ID的流程，不需要加锁也不需要改表结构。
示例代码：

do $$ declare
  vnewid bigint;
  vdata jsonb;
  retry_count int := 0;
  max_retry int := 3; -- 最大重试次数可按需调整
begin
  loop
    begin
      select coalesce(max(fbillid),0)+1 into vnewid 
      from bill 
      where flinkid=123 and fsystem='test' and fnaviid=123;
      insert into bill(flinkid,fsystem,fnaviid,fbillid,fdata)
      values(123,'test',123,vnewid,vdata);
      update bill set fdata=jsonb_set_lax(fdata,'{head,fbillno}','XXX'::jsonb)
      where flinkid=123 and fsystem='test' and fnaviid=123 and fbillid=vnewid;
      exit; -- 执行成功退出循环
    exception when unique_violation then
      retry_count := retry_count + 1;
      if retry_count > max_retry then
        raise; -- 超过重试次数抛出异常
      end if;
    end;
  end loop;
end;
$$ language plpgsql;

• 优点：零额外改造，不需要加锁也不需要改表
• 缺点：并发冲突概率高时，重试会消耗性能，极端情况可能多次重试失败

选型建议 #

• 如果要求ID完全连续，并发量不高：选方案1
• 如果并发量高，允许ID跳号：选方案2
• 如果并发极低，不愿意改动现有逻辑：选方案3

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