Date: Sun, 8 Mar 2026 15:15:34 +0000 (UTC)
From: felix.quintgz@yahoo.com
To: pgsql-general@lists.postgresql.org
Message-ID: <1164079167.6346688.1772982934392@mail.yahoo.com>
In-Reply-To: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com>
References: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com>
Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17
 and Django's select_for_update()
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 3961
Archived-At: <https://www.postgresql.org/message-id/1164079167.6346688.1772982934392%40mail.yahoo.com>
Precedence: bulk

This is pure speculation.
It's possible that using SELECT FOR UPDATE also locks the rows in the paren=
t tables referenced in the field list.
I believe this happened in older versions of PostgreSQL.

 On Saturday, March 7, 2026 at 04:25:01 AM GMT-5, Shaheed Haque <shaheedhaq=
ue@gmail.com> wrote:

 [I originally posted this over at=C2=A0https://forum.djangoproject.com/t/u=
nexpected-deadlock-across-two-separate-rows-using-postgres-17-and-select-fo=
r-update/44294/1, but that thread ran into a dead end. Apologies for the cr=
oss-post]

Hi,
I'm trying to understand/fix a rare deadlock in my application. Given my li=
mited knowledge, what seems odd to me is that the deadlock involves two pro=
cesses=C2=A0running exactly the same code/query, each of which (tries to) a=
void issues by locking exactly one row for update. In Django-speak, the cod=
e 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 proce=
ss 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=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
How can there be a deadlock between updates to different rows (as per the b=
olded WHERE clauses)? Have I somehow turned off row-level locks? Is there s=
ome additional logging I could enable to try to catch=C2=A0the data needed =
to root-cause this?

Any help appreciated.
Thanks, Shaheed