MIME-Version: 1.0
References: 
 <CAHOGQfWk-xb3q0QSXY=xUsMohChEEZwjxoTzrFUK+D_GEcO+uw@mail.gmail.com>
In-Reply-To: 
 <CAHOGQfWk-xb3q0QSXY=xUsMohChEEZwjxoTzrFUK+D_GEcO+uw@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 7 May 2025 09:31:10 -0400
Message-ID: 
 <CANzqJaDP=Eg_9d6aN4C8oy-na-MJhXyP7zAqLJhekBqxs75eUA@mail.gmail.com>
Subject: Re: Update command causing lock in DB.
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b486a806348bbf5e"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaDP%3DEg_9d6aN4C8oy-na-MJhXyP7zAqLJhekBqxs75eUA%40mail.gmail.com>
Precedence: bulk

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

On Wed, May 7, 2025 at 7:16=E2=80=AFAM Gambhir Singh <gambhir.singh05@gmail=
.com>
wrote:

> Hi,
>
> Application team was executing UPDATE statement in DB through Abinitio
> graph. When they trigger a job, a session is spawned in DB, in parallel
> another session is also spawned and executed the same UPDATE statement.
>
> When I checked the locks in DB, I found that both the sessions are
> updating the same record. My concern is how UPDATE causes locking in DB.
>
> Here is how MVCC works. If one session is updating a record, it should
> release the lock once it updated the row and other one should be able to
> acquire the row lock. Maybe I am wrong, please suggest how to handle this
> situation.
>

Do the applications use implicit autocommit, or do they use explicit
transaction blocks?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, May 7, 2025 at 7:16=E2=80=AFAM Ga=
mbhir Singh &lt;<a href=3D"mailto:gambhir.singh05@gmail.com">gambhir.singh0=
5@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_conta=
iner"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><d=
iv dir=3D"ltr"><div dir=3D"ltr"><div>Hi,<br clear=3D"all"></div><div><br></=
div><div><div style=3D"font-family:tahoma,sans-serif;font-size:small;color:=
rgb(0,0,0)"><span style=3D"font-family:Arial,Helvetica,sans-serif;color:rgb=
(34,34,34)">Application team was executing UPDATE statement in DB through A=
binitio graph. When they trigger a job, a session is spawned in DB, in para=
llel another session is also spawned and executed the same UPDATE statement=
.</span></div><br>When I checked the locks in DB, I found that both the ses=
sions are updating the same record. My concern is how UPDATE causes locking=
 in DB.<br><br>Here is how MVCC works. If one session is updating a record,=
 it should release the lock once it updated the row and other one should be=
 able to acquire the row lock. Maybe I am wrong, please suggest how to hand=
le this situation.</div></div></div></div></blockquote><div>=C2=A0</div><di=
v>Do the applications use implicit autocommit, or do they use explicit tran=
saction blocks?</div></div><div><br></div><span class=3D"gmail_signature_pr=
efix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"=
ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&=
#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div>=
</div></div></div>

--000000000000b486a806348bbf5e--