public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Sabino Mullane <[email protected]>
To: veem v <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: Christophe Pettus <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Ron Johnson <[email protected]>
Cc: Peter J. Holzer <[email protected]>
Subject: Re: IO related waits
Date: Sun, 22 Sep 2024 10:11:57 -0400
Message-ID: <CAKAnmm+nZaqvmy6rx7=aMVZXD9rEmtNK0L78dEHrOOgvCJZQ3g@mail.gmail.com> (raw)
In-Reply-To: <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
<[email protected]>
<CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com>
<[email protected]>
<CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
<[email protected]>
<CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com>
<CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
<[email protected]>
<CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com>
<CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@mail.gmail.com>
<[email protected]>
<CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
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
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: IO related waits
In-Reply-To: <CAKAnmm+nZaqvmy6rx7=aMVZXD9rEmtNK0L78dEHrOOgvCJZQ3g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox