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>
In-Reply-To: <fd6afa3b-f5db-4632-8e25-678ef66703d5@aklaver.com>
From: veem v <veema0000@gmail.com>
Date: Thu, 19 Sep 2024 02:01:39 +0530
Message-ID: <CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@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="0000000000003ee94f06226ab2cb"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA%40mail.gmail.com>
Precedence: bulk

--0000000000003ee94f06226ab2cb
Content-Type: text/plain; charset="UTF-8"

On Wed, 18 Sept 2024 at 05:07, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 9/17/24 12:34, veem v wrote:
> >
>
> It does if autocommit is set in the client, that is common to other
> databases also:
>
> https://dev.mysql.com/doc/refman/8.4/en/commit.html
>
>
> https://docs.oracle.com/en/database/oracle/developer-tools-for-vscode/getting-started/disabling-and-enabling-auto-commit.html
>
>
> https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver16
>
> You probably need to take a closer look at the client/driver you are
> using and the code that interacting with it.
>
> In fact I would say you need to review the entire data transfer process
> to see if there are performance gains that can be obtained without
> adding an entirely new async component.
>
>
>
You were spot on. When we turned off the "auto commit" we started seeing
less number of commits as per the number of batches.

However we also started seeing deadlock issues. We have foreign key
relationships between the tables and during the batch we do insert into the
parent first and then to the child , but this does happen from multiple
sessions for different batches. So why do we see below error, as we
ensure in each batch we first insert into parent and then into the child
tables?

caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 10443 waits for ShareLock on transaction 220972157;
blocked by process 10454.
Process 10454 waits for ShareLock on transaction 220972155; blocked by
process 10443.
  Hint: See server log for query details.
  Where: while inserting index tuple (88736,28) in relation
"TAB1_p2024_08_29"

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

<div dir=3D"ltr"><div dir=3D"ltr"></div><br><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Wed, 18 Sept 2024 at 05:07, Adrian Kla=
ver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.=
com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">On 9/17/24 12:34, veem v wrote:<br>
&gt;<br>
<br>
It does if autocommit is set in the client, that is common to other <br>
databases also:<br>
<br>
<a href=3D"https://dev.mysql.com/doc/refman/8.4/en/commit.html" rel=3D"nore=
ferrer" target=3D"_blank">https://dev.mysql.com/doc/refman/8.4/en/commit.ht=
ml</a><br>
<br>
<a href=3D"https://docs.oracle.com/en/database/oracle/developer-tools-for-v=
scode/getting-started/disabling-and-enabling-auto-commit.html" rel=3D"noref=
errer" target=3D"_blank">https://docs.oracle.com/en/database/oracle/develop=
er-tools-for-vscode/getting-started/disabling-and-enabling-auto-commit.html=
</a><br>
<br>
<a href=3D"https://learn.microsoft.com/en-us/sql/t-sql/statements/set-impli=
cit-transactions-transact-sql?view=3Dsql-server-ver16" rel=3D"noreferrer" t=
arget=3D"_blank">https://learn.microsoft.com/en-us/sql/t-sql/statements/set=
-implicit-transactions-transact-sql?view=3Dsql-server-ver16</a><br>
<br>
You probably need to take a closer look at the client/driver you are <br>
using and the code that interacting with it.<br>
<br>
In fact I would say you need to review the entire data transfer process <br=
>
to see if there are performance gains that can be obtained without <br>
adding an entirely new async component.<br><br>
<br></blockquote><div><br></div><div>You were spot on. When we turned off t=
he &quot;auto commit&quot; we started seeing less number=C2=A0of commits as=
 per the number of batches.=C2=A0</div><div><br></div><div>However=C2=A0we =
also started seeing deadlock issues. We have foreign key relationships betw=
een the tables and during the batch we do insert into the parent first and =
then to the child , but this does happen from multiple sessions for differe=
nt batches. So why do we see below error, as we ensure=C2=A0in each batch w=
e first insert into parent and then into the child tables?</div><div><br></=
div><div>caused by: org.postgresql.util.PSQLException: ERROR: deadlock dete=
cted<br>=C2=A0 Detail: Process 10443 waits for ShareLock on transaction 220=
972157; blocked by process 10454.<br>Process 10454 waits for ShareLock on t=
ransaction 220972155; blocked by process 10443.<br>=C2=A0 Hint: See server =
log for query details.<br>=C2=A0 Where: while inserting index tuple (88736,=
28) in relation &quot;TAB1_p2024_08_29&quot;<br></div></div></div>

--0000000000003ee94f06226ab2cb--