Re: IO related waits

[Greg Sabino Mullane <htamfids@xxxxxxxxx>]

You may be able to solve this with advisory locks. In particular, transaction-level advisory locks with the "try-pass/fail" variant. Here, "123" is a unique number used by your app, related to this particular table. You also need to force read committed mode, as the advisory locks go away after the transaction ends, so at that point, we switch from advisory locks to the ON CONFLICT clause, which can only work smoothly if other processes can see the new row right away.

drop table if exists t1;
create table t1(id int primary key);

-- Session 1:
begin transaction isolation level read committed;

-- inserts one row:
insert into t1 select 1 where pg_try_advisory_xact_lock(123,1)
  on conflict(id) do nothing;

-- Session 2:
begin transaction isolation level read committed;

-- inserts one row:
insert into t1 select 2 where pg_try_advisory_xact_lock(123,2)
  on conflict(id) do nothing;

-- Session 1:

-- silently 'fails' because no lock is granted, so inserts zero rows:
insert into t1 select 2 where pg_try_advisory_xact_lock(123,2)
  on conflict(id) do nothing;

-- Session 2:

-- silently 'fails' because no lock is granted, so inserts zero rows:
insert into t1 select 1 where pg_try_advisory_xact_lock(123,1)
  on conflict(id) do nothing;

-- inserts one row:
insert into t1 select 3 where pg_try_advisory_xact_lock(123,3)
  on conflict(id) do nothing;

commit; -- lock on 2 and 3 goes away

-- Session 1:

-- silently fails because of the on conflict clause
insert into t1 select 3 where pg_try_advisory_xact_lock(123,3)
  on conflict(id) do nothing;

Cheers,

Greg