使用CTE插入自增版本值是否安全？求最优版本控制方案

问题背景 #

现有表结构：

create table if not exists template (
       id serial primary key, 
       label text not null, 
       version integer not null default 1,
       created_at timestamp not null default current_timestamp,
       unique(label, version)
);

需求是插入新记录时，若label相同则自动递增version字段，最初采用以下CTE实现：

with v as (
  select coalesce(max(version), 0) + 1 as new_version 
  from template t where label = 'label1'
)
insert into template (label, version) 
values ('label1', (select new_version from v))
returning *;

你的判断是正确的：原方案存在并发安全问题 #

当多个请求同时插入同一label的记录时，所有请求会在各自的事务快照中读取到相同的max(version)值，进而插入相同的version，触发unique(label, version)的约束冲突，导致部分插入失败。即使把原查询包裹在普通事务里也没用——默认的READ COMMITTED隔离级别下，每个事务依然会读取独立的快照，无法避免并发读取到旧版本号的问题。

解决方案与更优实现方式 #

方式一：用行锁避免并发读取 #

在查询最大版本号时加上FOR UPDATE锁，这样会锁定该label对应的所有行（如果没有行则会加间隙锁），强制其他并发事务等待当前事务完成后再执行查询，保证版本号的唯一性：

with v as (
  select coalesce(max(version), 0) + 1 as new_version 
  from template t where label = 'label1'
  for update
)
insert into template (label, version) 
values ('label1', (select new_version from v))
returning *;

这种方式无需额外建表，逻辑简单，适合template表中单个label数据量不大的场景。

方式二：用独立版本控制表实现原子自增 #

创建单独的表来管理每个label的当前版本，通过UPDATE ... ON CONFLICT实现原子性的版本递增，避免锁定template表的大量数据，性能更优：

-- 先创建版本控制表
create table if not exists label_version (
  label text primary key,
  current_version integer not null default 0
);

-- 插入逻辑
with updated as (
  update label_version
  set current_version = current_version + 1
  where label = 'label1'
  returning current_version as new_version
  on conflict (label) do nothing
), inserted as (
  insert into label_version (label, current_version)
  values ('label1', 1)
  where not exists (select 1 from updated)
  returning current_version as new_version
)
insert into template (label, version)
values ('label1', (select new_version from updated union all select new_version from inserted))
returning *;

这种方式把版本管理和业务数据分离，适合高并发场景或单个label数据量较大的情况。

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