MIME-Version: 1.0
From: Shaheed Haque <shaheedhaque@gmail.com>
Date: Sat, 7 Mar 2026 09:24:32 +0000
Message-ID: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com>
Subject: Unexpected deadlock across two separate rows, using Postgres 17 and
 Django's select_for_update()
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000745674064c6bbd3d"
Archived-At: <https://www.postgresql.org/message-id/CAHAc2jd%3Dx-6hM%3DCoEYOaJ6ST5p-nGkTxDmh%3DPrwxacnkUp%3D31A%40mail.gmail.com>
Precedence: bulk

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

[I originally posted this over at
https://forum.djangoproject.com/t/unexpected-deadlock-across-two-separate-r=
ows-using-postgres-17-and-select-for-update/44294/1,
but that thread ran into a dead end. Apologies for the cross-post]

Hi,

I'm trying to understand/fix a rare deadlock in my application. Given my
limited knowledge, what seems odd to me is that the deadlock involves two
processes running exactly the same code/query, each of which (tries to)
avoid issues by locking exactly one row for update. In Django-speak, the
code does this:

#
# Select-for-update exactly one row by id.
#
qs =3D Endpoint.objects.select_for_update().filter(id=3Dinstance.id)
#
# The above returns a queryset of one row which we loop over:
#
for item in qs:

    ...do stuff with item...

    item.save()

The deadlock is reported in the Postgres server log like this:

ERROR: deadlock detected
DETAIL: Process 15576 waits for ShareLock on transaction 31053599; blocked
by process 16953.
Process 16953 waits for ShareLock on transaction 31053597; blocked by
process 15576.
Process 15576: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=
=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2=
=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai=
yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D FROM
=E2=80=9Cpaiyroll_endpoint=E2=80=9D *WHERE =E2=80=9Cpaiyroll_endpoint=E2=80=
=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE*
Process 16953: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=
=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2=
=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai=
yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D FROM
=E2=80=9Cpaiyroll_endpoint=E2=80=9D *WHERE =E2=80=9Cpaiyroll_endpoint=E2=80=
=9D.=E2=80=9Cid=E2=80=9D =3D 2 FOR UPDATE*
HINT: See server log for query details.
CONTEXT: while locking tuple (7,15) in relation =E2=80=9Cpaiyroll_endpoint=
=E2=80=9D
STATEMENT: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=9D,=
 =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D, =E2=80=9Cp=
aiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D, =E2=80=9Cpaiy=
roll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D, =E2=80=9Cpaiyroll=
_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D FROM =
=E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE

How can there be a deadlock between updates to different rows (as per the
bolded WHERE clauses)? Have I somehow turned off row-level locks? Is there
some additional logging I could enable to try to catch the data needed to
root-cause this?

Any help appreciated.

Thanks, Shaheed

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

<div dir=3D"ltr"><div><div>[I originally posted this over at=C2=A0<a href=
=3D"https://forum.djangoproject.com/t/unexpected-deadlock-across-two-separa=
te-rows-using-postgres-17-and-select-for-update/44294/1">https://forum.djan=
goproject.com/t/unexpected-deadlock-across-two-separate-rows-using-postgres=
-17-and-select-for-update/44294/1</a>, but that thread ran into a dead end.=
 Apologies for the cross-post]<br><br></div><div>Hi,</div><div><br></div>I&=
#39;m trying to understand/fix a rare deadlock in my application. Given my =
limited knowledge, what seems odd to me is that the deadlock involves two p=
rocesses=C2=A0running exactly the same code/query, each of which (tries to)=
 avoid issues by locking exactly one row for update. In Django-speak, the c=
ode does this:<br></div><div style=3D"margin-left:40px"><pre><code class=3D=
"gmail-hljs gmail-language-csharp">#<br># Select-for-update exactly one row=
 by id.<br>#<br>qs =3D Endpoint.objects.select_for_update().filter(id=3D<a =
href=3D"http://instance.id">instance.id</a>)<br>#<br># The above returns a =
queryset of one row which we loop over:<br>#<br>for item in qs:<br></code><=
/pre></div><div style=3D"margin-left:40px"><pre><code class=3D"gmail-hljs g=
mail-language-csharp">    ...do stuff with item...<br></code></pre></div><d=
iv style=3D"margin-left:40px"><pre><code class=3D"gmail-hljs gmail-language=
-csharp">    item.save() </code></pre></div><div>The deadlock is reported i=
n the Postgres server log like this:</div><div><br></div><div style=3D"marg=
in-left:40px">ERROR:  deadlock detected<br>
DETAIL:  Process 15576 waits for ShareLock on transaction 31053599; blocked=
 by process 16953.<br>
Process 16953 waits for ShareLock on transaction 31053597; blocked by proce=
ss 15576.<br>
Process 15576: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=
=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2=
=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai=
yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D=20
FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D <strong>WHERE =E2=80=9Cpaiyroll_en=
dpoint=E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE</strong><br>
Process 16953: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=
=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2=
=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai=
yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D=20
FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D <strong>WHERE =E2=80=9Cpaiyroll_en=
dpoint=E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 2 FOR UPDATE</strong><br>
HINT:  See server log for query details.<br>
CONTEXT:  while locking tuple (7,15) in relation =E2=80=9Cpaiyroll_endpoint=
=E2=80=9D<br>
STATEMENT:  SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=9D=
,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2=
=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr=
oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,=20
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai=
yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D=20
FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE =E2=80=9Cpaiyroll_endpoint=
=E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE</div><div></div><div><br></=
div><div>How can there be a deadlock between updates to different rows (as =
per the bolded WHERE clauses)? Have I somehow turned off row-level locks? I=
s there some additional logging I could enable to try to catch=C2=A0the dat=
a needed to root-cause this?<br><br></div><div>Any help appreciated.</div><=
div><br></div><div>Thanks, Shaheed</div></div>

--000000000000745674064c6bbd3d--