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>
In-Reply-To: <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 19 Sep 2024 08:24:19 -0400
Message-ID: <CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@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>
Content-Type: multipart/alternative; boundary="000000000000920b2f0622780276"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJhsNL%3Df%2Bz7R018L6NrLQuq_7%2BquqZkGUKbzHRctHaY3w%40mail.gmail.com>
Precedence: bulk

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

On Thu, Sep 19, 2024 at 5:17=E2=80=AFAM veem v <veema0000@gmail.com> wrote:

> 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 transactio=
n
> block
> 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMEN=
T:
>  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 transactio=
n
> block
> 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMEN=
T:
>
> 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 transactio=
n
> 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.

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Sep 19, 2024 at 5:17=E2=80=AFAM v=
eem v &lt;<a href=3D"mailto:veema0000@gmail.com">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 rgb(204,204,204);pa=
dding-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]:DETAIL: =C2=A0Proces=
s 14537 waits for ShareLock on transaction 220975629; blocked by process 14=
548.<br></div><div></div></div></div></blockquote><div><br></div><div>You n=
eed to find out exactly what commands, and in what order, all these process=
es are doing. Deadlocks can be avoided by rearranging your application logi=
c.</div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>2024-09-18 17:05:56 UT=
C:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR: =C2=A0current transaction=
 is 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=A0INSE=
RT INTO TRANDB.EXCEP_TAB (...)<br>2024-09-18 17:05:56 UTC:100.72.22.33(5458=
2):USER1@TRANDB:[14537]:ERROR: =C2=A0current transaction is aborted, comman=
ds 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>2024-09-18 17:05:56=
 UTC:100.72.22.33(36096):USER1@TRANDB:[14551]:ERROR: =C2=A0current transact=
ion is aborted, commands ignored until end of transaction block<br></div></=
div></div></blockquote><div><br></div><div>Fix your application. It should =
be checking that each command completed and not just blindly 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 a=
nd half-complete descriptions of your business logic. Pull everyone involve=
d 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.</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br><=
/div></div></div>

--000000000000920b2f0622780276--