如何在PostgreSQL中从并发事务中从一个表的字段中获取最新的新ID

Question

我遇到了一个大麻烦。

在我的函数中，我需要从表中获取新的id，然后用新的id+1插入到表中。

下表如下：

create table bill(
flinkid bigint not null,
fsystem text not null,
fnaviid bigint not null,
fbillid bigint not null,
fdata jsonb,
constraint bill_pkey primary key (flinkid, fsystem, fnaviid, fbillid)
);

在我的函数中，我需要从表中获得最大旧fbillid，然后插入一个新id为+1的新行，如下所示：

do $$ declare
vnewid bigint;
vdata jsonb;
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;
...
end;
$$ language plpgsql;

当遇到并发事务时，即两个或多个reqeust几乎同时调用函数，因为函数的事务需要一些时间来执行，第一个事务还没有提交，第二个事务访问表，所以字段fbillid是同一个。这将导致两行变成一行，而行数据是第二行，第一行的数据丢失。

如何解决这个问题？请给我一些建议，非常感谢！

Answer 1

您可以使用SELECT FOR UPDATE命令。FOR UPDATE导致SELECT语句检索到的行被锁定，就像是为了更新一样。这样可以防止它们被其他事务锁定、修改或删除，直到当前事务结束。也就是说，尝试这些行的UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或SELECT FOR KEY SHARE的其他事务将被阻止，直到当前事务结束；相反，SELECT FOR UPDATE将等待在同一行上运行了任何这些命令的并发事务，然后锁定并返回更新的行。

例如：

select fbillid into vnewid 
from bill where fbillid = (select coalesce(max(fbillid),0) from bill)
for update; 
------------ OR -------------- 
select fbillid into vnewid 
from bill 
order by fbillid desc 
limit 1
for update; 

但是，在完成操作后，您必须更新此锁定记录才能解锁：

update bill 
set 
updated_date = now()
where fbillid = vnewid;