MIME-Version: 1.0
References: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com>
 <1164079167.6346688.1772982934392@mail.yahoo.com> <CAHAc2jeME7KPPP3TgP9qWC5LMTOs4gYTEJ1MBDwnj7J2EvJTdA@mail.gmail.com>
 <1656643760.2116109.1772994207524@mail.yahoo.com> <CAKAnmmKD2kxF6pD3c5eFhJZ+TMogRS9dHcMWbmiZs2Vn=XvZ0g@mail.gmail.com>
In-Reply-To: <CAKAnmmKD2kxF6pD3c5eFhJZ+TMogRS9dHcMWbmiZs2Vn=XvZ0g@mail.gmail.com>
From: Shaheed Haque <shaheedhaque@gmail.com>
Date: Mon, 9 Mar 2026 09:55:08 +0000
Message-ID: <CAHAc2jcTLhGPFYNUC++e9HXG2kt-KuCu9tuvGpd-2Hi+ry4yDg@mail.gmail.com>
Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17
 and Django's select_for_update()
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: felix.quintgz@yahoo.com, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008ba4ac064c9466cc"
Archived-At: <https://www.postgresql.org/message-id/CAHAc2jcTLhGPFYNUC%2B%2Be9HXG2kt-KuCu9tuvGpd-2Hi%2Bry4yDg%40mail.gmail.com>
Precedence: bulk

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

Thank you for the replies.

On Sun, 8 Mar 2026 at 22:08, Greg Sabino Mullane <htamfids@gmail.com> wrote=
:

> On Sun, Mar 8, 2026 at 2:23=E2=80=AFPM <felix.quintgz@yahoo.com> wrote:
>
>> What makes me suspect it's a lock on the parent table is the word
>> "ShareLock" in the logs. A SELECT ... FOR UPDATE statement shouldn't pla=
ce
>> that type of lock on the table it's selecting.
>>
>
> This looks 100% like a normal, multi-row deadlock situation. The CONTEXT
> shows it is a row-level problem:
>

I'm not sure I understand. The two queries are referencing separate, single
rows in the child table (primary keys payroll_endpoint.id =3D 1 and 2), so
where does the multi-row bit come in? Is it because the two parent tables
are also being locked, in possibly different orders?


>
> CONTEXT: while locking tuple (7,15) in relation =E2=80=9Cpaiyroll_endpoin=
t=E2=80=9D
>
>
> The ShareLocks are on the transaction, because each backend is waiting fo=
r
> the other to finish their transaction, and thus release the lock(s) it ma=
y
> have.
>
> If you implement Tom's suggestion, I think you will find that this is a
> classic failing to lock the rows in the same order problem.
>

I'm not seeing "Tom's suggestion". Is there a way to specify that the
parent tables need not be locked? Perhaps by omitting them from the query?

Thanks, Shaheed


>
> Cheers,
> Greg
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div><div>Thank you for the replies.</div=
></div></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">On Sun, 8 Mar 2026 at 22:08, Greg Sabino Mull=
ane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail.com</a>&gt; wr=
ote:<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"><div dir=3D=
"ltr"><div dir=3D"ltr"><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D=
"gmail_attr">On Sun, Mar 8, 2026 at 2:23=E2=80=AFPM &lt;<a href=3D"mailto:f=
elix.quintgz@yahoo.com" target=3D"_blank">felix.quintgz@yahoo.com</a>&gt; w=
rote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">What makes=
 me suspect it&#39;s a lock on the parent table is the word &quot;ShareLock=
&quot; in the logs. A SELECT ... FOR UPDATE statement shouldn&#39;t place t=
hat type of lock on the table it&#39;s selecting.<br></blockquote></div><di=
v dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div><br></div></d=
iv></div></div><div dir=3D"ltr">This looks 100% like a normal, multi-row de=
adlock situation. The CONTEXT shows it is a row-level problem:</div></div><=
/blockquote><div><br></div><div>I&#39;m not sure I understand. The two quer=
ies are referencing separate, single rows in the child table (primary keys =
<a href=3D"http://payroll_endpoint.id">payroll_endpoint.id</a> =3D 1 and 2)=
, so where does the multi-row bit come in?=C2=A0Is it because the two paren=
t tables are also being locked, in possibly different orders?</div><div><br=
></div><div>=C2=A0</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"><div><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">CONTEXT: while locking tuple (7,15) in relation =E2=
=80=9Cpaiyroll_endpoint=E2=80=9D</blockquote><div><br></div><div>The ShareL=
ocks are on the transaction, because each backend is waiting for the other =
to finish their transaction, and thus release the lock(s) it may have.</div=
><div><br></div><div>If you implement Tom&#39;s suggestion, I think you wil=
l find that this is a classic failing to lock the rows in the same order pr=
oblem.</div></div></div></blockquote><div><br></div><div>I&#39;m not seeing=
 &quot;Tom&#39;s suggestion&quot;. Is there a way to specify that the paren=
t tables need not be locked? Perhaps by omitting them from the query?</div>=
<div><br></div><div>Thanks, Shaheed</div><div>=C2=A0</div><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 dir=3D"ltr"><div><br=
></div><div>Cheers,</div><div>Greg</div><div><br></div></div></div>
</blockquote></div></div>

--0000000000008ba4ac064c9466cc--