MIME-Version: 1.0
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
 <3dddea5e-52ab-4075-970d-a87b0c921ae7@aklaver.com> <CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com>
 <225d1bc1-5117-4c72-85a1-bac6355fb659@aklaver.com> <CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
 <fd6afa3b-f5db-4632-8e25-678ef66703d5@aklaver.com> <CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com>
 <CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
 <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com> <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com>
 <CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@mail.gmail.com>
 <bc8363b5-28d0-4218-b3d6-73c7d78717da@aklaver.com> <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
In-Reply-To: <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sun, 22 Sep 2024 10:11:57 -0400
Message-ID: <CAKAnmm+nZaqvmy6rx7=aMVZXD9rEmtNK0L78dEHrOOgvCJZQ3g@mail.gmail.com>
Subject: Re: IO related waits
To: veem v <veema0000@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Christophe Pettus <xof@thebuild.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, 
	Ron Johnson <ronljohnsonjr@gmail.com>, "Peter J. Holzer" <hjp-pgsql@hjp.at>
Content-Type: multipart/alternative; boundary="00000000000013038c0622b5dde1"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BnZaqvmy6rx7%3DaMVZXD9rEmtNK0L78dEHrOOgvCJZQ3g%40mail.gmail.com>
Precedence: bulk

--00000000000013038c0622b5dde1
Content-Type: text/plain; charset="UTF-8"

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

--00000000000013038c0622b5dde1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">You may be able to solve this with advisory locks. In part=
icular, transaction-level advisory locks with the &quot;try-pass/fail&quot;=
 variant. Here, &quot;123&quot; is a unique number used by your app, relate=
d to this particular table. You also need to force read committed mode, as =
the advisory=C2=A0locks go away after the transaction=C2=A0ends, 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.<br><b=
r>drop table if exists t1;<br>create table t1(id int primary key);<br><br>-=
- Session 1:<br>begin transaction isolation level read committed;<br><br>--=
 inserts one row:<br>insert into t1 select 1 where pg_try_advisory_xact_loc=
k(123,1)<br>=C2=A0 on conflict(id) do nothing;<br><br>-- Session 2:<br>begi=
n transaction isolation level read committed;<br><br>-- inserts one row:<br=
>insert into t1 select 2 where pg_try_advisory_xact_lock(123,2)<br>=C2=A0 o=
n conflict(id) do nothing;<br><br>-- Session 1:<br><br>-- silently &#39;fai=
ls&#39; because no lock is granted, so inserts zero rows:<br>insert into t1=
 select 2 where pg_try_advisory_xact_lock(123,2)<br>=C2=A0 on conflict(id) =
do nothing;<br><br>-- Session 2:<br><br>-- silently &#39;fails&#39; because=
 no lock is granted, so inserts zero rows:<br>insert into t1 select 1 where=
 pg_try_advisory_xact_lock(123,1)<br>=C2=A0 on conflict(id) do nothing;<br>=
<br>-- inserts one row:<br>insert into t1 select 3 where pg_try_advisory_xa=
ct_lock(123,3)<br>=C2=A0 on conflict(id) do nothing;<br><div><br></div><div=
>commit; -- lock on 2 and 3 goes away<br><br>-- Session 1:<br><br></div><di=
v>-- silently fails because of the on conflict clause<br>insert into t1 sel=
ect 3 where pg_try_advisory_xact_lock(123,3)<br>=C2=A0 on conflict(id) do n=
othing;<br></div><div><br></div><div><br></div><div>Cheers,</div><div>Greg<=
/div><div><br></div></div>

--00000000000013038c0622b5dde1--