How to get latest new id from one table's field from concurrent transaction in PostgreSQL

I have a big trouble.

In my function, I need get new id from table then insert into the table with the new id +1.

The table just like below:

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)
);

In my function, I need to get the max old fbillid from the table, then insert a new row with the new id +1, just as below:

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;

When meets concurrent transactions, that is two or more reqeusts call the function almost at the same time, because the function's transaction need some time to execute, the first transation hasn't commit yet, and the second transaction visit the table, the field fbillid is the same one. this will cause two row becomes one row, and the row data is the second, the first row's data is lost.

How to solve this problem? Please give me some advice, thanks a lot!

Solution

You can use SELECT FOR UPDATE command. FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row.

For example:

  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;

But, after finishing your operations, you must update this locked record for unlocking:

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