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>
In-Reply-To: <bc8363b5-28d0-4218-b3d6-73c7d78717da@aklaver.com>
From: veem v <veema0000@gmail.com>
Date: Sat, 21 Sep 2024 01:31:23 +0530
Message-ID: <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
Subject: Re: IO related waits
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, Christophe Pettus <xof@thebuild.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000098ddcb06229281f4"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ%40mail.gmail.com>
Precedence: bulk

--00000000000098ddcb06229281f4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

> On 9/19/24 05:24, Greg Sabino Mullane wrote:
> > On Thu, Sep 19, 2024 at 5:17=E2=80=AFAM veem v <veema0000@gmail.com
>
> > This is really difficult to diagnose from afar with only snippets of
> > logs and half-complete descriptions of your business logic. Pull
> > everyone involved into a room with a whiteboard, and produce a document
> > describing exactly what your application does, and how it is doing it.
> > Switch from reactive to proactive.
>
>
Able to reproduce this deadlock graph as below.  Now my question is , this
is a legitimate scenario in which the same ID can get inserted from
multiple sessions and in such cases it's expected to skip that (thus "On
conflict Do nothing" is used) row. But as we see it's breaking the code
with deadlock error during race conditions where a lot of parallel threads
are operating. So how should we handle this scenario? Will setting the
"lock_timeout" parameter at session level will help us anyway here?

Create table t1(id numeric primary key);

Session 1:-
Begin
Insert into table1 values(1) on conflict(id) do nothing;

Session 2:
Begin
Insert into table1 values(2) on conflict(id) do nothing;

session 1:-
Insert into table1 values (2) on conflict(id) do nothing;

It got hung as it waits for the session-2 to commit/rollback the transactio=
n

Session-2:-
Insert into table1 values(1) on conflict(id) do nothing;

deadlock detected... and this session terminated.

Regards
Veem

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"auto"><br></div></div><div cl=
ass=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 19 Sept, =
2024, 8:40 pm Adrian Klaver, &lt;<a href=3D"mailto:adrian.klaver@aklaver.co=
m" rel=3D"noreferrer noreferrer" target=3D"_blank">adrian.klaver@aklaver.co=
m</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
>On 9/19/24 05:24, Greg Sabino Mullane wrote:<br>
&gt; On Thu, Sep 19, 2024 at 5:17=E2=80=AFAM veem v &lt;<a href=3D"mailto:v=
eema0000@gmail.com" rel=3D"noreferrer noreferrer noreferrer" target=3D"_bla=
nk">veema0000@gmail.com</a> <br>
<br>
&gt; This is really difficult to diagnose from afar with only snippets of <=
br>
&gt; logs and half-complete descriptions of your business logic. Pull <br>
&gt; everyone involved into a room with a whiteboard, and produce a documen=
t <br>
&gt; describing exactly what your application does, and how it is doing it.=
 <br>
&gt; Switch from reactive to proactive.<br><br></blockquote><div><br></div>=
<div dir=3D"auto">Able to reproduce this deadlock graph as below.=C2=A0 Now=
 my question is , this is a legitimate=C2=A0scenario in which the same ID c=
an get inserted from multiple sessions and in such cases it&#39;s expected =
to skip that (thus &quot;On conflict Do=C2=A0nothing&quot; is used) row. Bu=
t as we see it&#39;s breaking the code with deadlock error during race=C2=
=A0conditions where a lot of parallel threads are operating. So how should =
we handle this scenario? Will setting the &quot;lock_timeout&quot; paramete=
r at session level will help us anyway here?</div><div dir=3D"auto"><br></d=
iv><div dir=3D"auto"><div dir=3D"auto">Create table t1(id numeric primary k=
ey);<br></div><div dir=3D"auto"><br><div dir=3D"auto">Session 1:-</div><div=
>Begin</div><div dir=3D"auto">Insert into table1 values(1) on conflict(id) =
do nothing;</div><div dir=3D"auto"><br></div><div>Session 2:</div><div>Begi=
n</div><div>Insert into table1 values(2) on conflict(id) do nothing;</div><=
div><br></div><div>session 1:-</div><div>Insert into table1 values (2) on c=
onflict(id) do nothing;</div><div><br></div><div>It got hung as it waits fo=
r the session-2 to commit/rollback the transaction</div><div><br></div><div=
>Session-2:-</div><div>Insert into table1 values(1) on conflict(id) do noth=
ing;</div><div dir=3D"auto"><br></div><div dir=3D"auto">deadlock detected..=
. and this session=C2=A0terminated.<br></div><div dir=3D"auto"><br></div><d=
iv>Regards</div><div>Veem</div><div dir=3D"auto"><br></div></div></div><div=
>=C2=A0</div></div>
</div>

--00000000000098ddcb06229281f4--