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>
In-Reply-To: <CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@mail.gmail.com>
From: veem v <veema0000@gmail.com>
Date: Thu, 19 Sep 2024 18:33:04 +0530
Message-ID: <CAB+=1TVWsX5KAJyxcamvfDXr0Buj7_1kjFBFBMBdc5epi8of=A@mail.gmail.com>
Subject: Re: IO related waits
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Christophe Pettus <xof@thebuild.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bb2b490622788bbc"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TVWsX5KAJyxcamvfDXr0Buj7_1kjFBFBMBdc5epi8of%3DA%40mail.gmail.com>
Precedence: bulk

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

On Thu, 19 Sept 2024 at 17:54, Greg Sabino Mullane <htamfids@gmail.com>
wrote:

> On Thu, Sep 19, 2024 at 5:17=E2=80=AFAM veem v <veema0000@gmail.com> wrot=
e:
>
>> 2024-09-18 17:05:56 UTC:100.72.10.66(54582):USER1@TRANDB:[14537]:DETAIL:
>>  Process 14537 waits for ShareLock on transaction 220975629; blocked by
>> process 14548.
>>
>
> You need to find out exactly what commands, and in what order, all these
> processes are doing. Deadlocks can be avoided by rearranging your
> application logic.
>
>
>> 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR:
>>  current transaction is aborted, commands ignored until end of transacti=
on
>> block
>> 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEME=
NT:
>>  INSERT INTO TRANDB.EXCEP_TAB (...)
>> 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR:
>>  current transaction is aborted, commands ignored until end of transacti=
on
>> block
>> 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEME=
NT:
>>
>> 2024-09-18 17:05:56 UTC:100.72.22.33(36096):USER1@TRANDB:[14551]:ERROR:
>>  current transaction is aborted, commands ignored until end of transacti=
on
>> block
>>
>
> Fix your application. It should be checking that each command completed
> and not just blindly pushing on to the next statement while ignoring the
> error.
>
> 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.
>
>
>
Thank you Greg.

I was thinking there might be some oddity or difference in the behaviour
here in postgres as compared to others, because I have seen deadlock due to
UPDATES but never seen deadlocks with INSERT queries before in other
databases (though here we have "insert on conflict do nothing"). But I am
now thinking , here we have foreign keys and primary keys exist and if the
same PK gets inserted from multiple sessions then one will wait if the
other has not been committed and that might be creating a situation of
locking first and subsequently deadlock.

But also we are doing batch inserts from multiple sessions but each session
will first insert into the parent and then into the child table for those
related to PK and FK and it should not overlap across sessions. So I will
check if there is a loophole there.

Also another thing which we encountered here , if the session gets errors
out with any error(may be deadlock etc) , it's not executing any further
transactions and erroring out with "*current transaction aborted, command
ignored until end of transaction block*". And it seems it will need
explicit "rollback" and will not be the default rollback, which I was
expecting it to do.

Regards
Veem

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Thu, 19 Sept 2024 at 17:54, Greg S=
abino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail.com<=
/a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
div dir=3D"ltr"><div dir=3D"ltr">On Thu, Sep 19, 2024 at 5:17=E2=80=AFAM ve=
em v &lt;<a href=3D"mailto:veema0000@gmail.com" target=3D"_blank">veema0000=
@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote=
"><div>2024-09-18 17:05:56 UTC:100.72.10.66(54582):USER1@TRANDB:[14537]:DET=
AIL: =C2=A0Process 14537 waits for ShareLock on transaction 220975629; bloc=
ked by process 14548.<br></div><div></div></div></div></blockquote><div><br=
></div><div>You need to find out exactly what commands, and in what order, =
all these processes are doing. Deadlocks can be avoided by rearranging your=
 application logic.</div><div>=C2=A0<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>2024-=
09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR: =C2=A0cu=
rrent transaction is aborted, commands ignored until end of transaction blo=
ck<br>2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STAT=
EMENT: =C2=A0INSERT INTO TRANDB.EXCEP_TAB (...)<br>2024-09-18 17:05:56 UTC:=
100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR: =C2=A0current transaction i=
s aborted, commands ignored until end of transaction block<br>2024-09-18 17=
:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT: =C2=A0<br>20=
24-09-18 17:05:56 UTC:100.72.22.33(36096):USER1@TRANDB:[14551]:ERROR: =C2=
=A0current transaction is aborted, commands ignored until end of transactio=
n block<br></div></div></div></blockquote><div><br></div><div>Fix your appl=
ication. It should be checking that each command completed and not just bli=
ndly pushing on to the next statement while=C2=A0ignoring the error.</div><=
div><br></div><div>This is really difficult to diagnose from afar with only=
 snippets of logs and half-complete descriptions of your business logic. Pu=
ll everyone involved into a room with a whiteboard, and produce a document =
describing exactly what your application does, and how it is doing it. Swit=
ch from reactive to proactive.</div><div><br></div><div><br></div></div></d=
iv></blockquote><div><br></div><div>Thank you Greg.</div><div><br></div><di=
v>I was thinking there might be some oddity or difference in the behaviour =
here in postgres as compared to others, because I have seen deadlock due to=
 UPDATES but never seen deadlocks with INSERT queries before in other datab=
ases (though here we have &quot;insert on conflict do nothing&quot;). But I=
 am now thinking , here we have foreign keys and primary keys exist and if =
the same PK gets inserted from multiple sessions then one will wait if the =
other has not been committed and that might be creating a situation of lock=
ing first and subsequently deadlock.=C2=A0</div><div><br></div><div>But als=
o we are doing batch inserts from multiple sessions but each session will f=
irst insert into the parent and then into the child table for those related=
 to PK and FK and it should not overlap across sessions. So I will check if=
 there is a loophole there.</div><div><br></div><div>Also another thing whi=
ch we encountered here , if the session gets errors out with any error(may =
be deadlock etc) , it&#39;s not executing any further transactions and erro=
ring out with &quot;<i>current transaction aborted, command ignored until e=
nd of transaction block</i>&quot;. And it seems it will need explicit &quot=
;rollback&quot; and will not be the default rollback, which I was expecting=
 it to do.</div><div><br></div><div>Regards</div><div>Veem</div></div></div=
>

--000000000000bb2b490622788bbc--