Date: Sun, 8 Mar 2026 18:23:27 +0000 (UTC)
From: felix.quintgz@yahoo.com
To: pgsql-general@lists.postgresql.org
Message-ID: <1656643760.2116109.1772994207524@mail.yahoo.com>
In-Reply-To: <CAHAc2jeME7KPPP3TgP9qWC5LMTOs4gYTEJ1MBDwnj7J2EvJTdA@mail.gmail.com>
References: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com> <1164079167.6346688.1772982934392@mail.yahoo.com> <CAHAc2jeME7KPPP3TgP9qWC5LMTOs4gYTEJ1MBDwnj7J2EvJTdA@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: 1890
Archived-At: <https://www.postgresql.org/message-id/1656643760.2116109.1772994207524%40mail.yahoo.com>
Precedence: bulk

I've found some references indicating that it does this, but the lock on th=
e parent table had to be shared to prevent the deletion of the row from the=
 parent table.

What makes me suspect it's a lock on the parent table is the word "ShareLoc=
k" in the logs. A SELECT ... FOR UPDATE statement shouldn't place that type=
 of lock on the table it's selecting.


 On Sunday, March 8, 2026 at 12:19:35 PM GMT-4, Shaheed Haque <shaheedhaque=
@gmail.com> wrote:

 On Sun, 8 Mar 2026 at 15:15, <felix.quintgz@yahoo.com> wrote:
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.

Interesting. In the query, paiyroll_endpoint.op_id and paiyroll_endpoint.cl=
ient_id ARE foreign keys to other tables.
But I don't see any reference to locking rows in parent tables in the docs =
around=C2=A0https://www.postgresql.org/docs/current/explicit-locking.html#L=
OCKING-ROWS. A quick poke around did not reveal any documentation that conf=
irms this one way or another. And to my admittedly in-expert thinking, it s=
eems surprising=C2=A0that the parent might need to be locked?

=C2=A0On Saturday, March 7, 2026 at 04:25:01 AM GMT-5, Shaheed Haque <shahe=
edhaque@gmail.com> wrote:
=C2=A0[I originally posted this over at=C2=A0https://forum.djangoproject.co=
m/t/unexpected-deadlock-across-two-separate-rows-using-postgres-17-and-sele=
ct-for-update/44294/1, but that thread ran into a dead end. Apologies for t=
he cross-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: