public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: [email protected]
Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
Date: Sun, 8 Mar 2026 18:23:27 +0000 (UTC)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHAc2jeME7KPPP3TgP9qWC5LMTOs4gYTEJ1MBDwnj7J2EvJTdA@mail.gmail.com>
References: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com>
	<[email protected]>
	<CAHAc2jeME7KPPP3TgP9qWC5LMTOs4gYTEJ1MBDwnj7J2EvJTdA@mail.gmail.com>

I've found some references indicating that it does this, but the lock on the 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 "ShareLock" 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 <[email protected]> wrote:

 On Sun, 8 Mar 2026 at 15:15, <[email protected]> wrote:
This is pure speculation.

It's possible that using SELECT FOR UPDATE also locks the rows in the parent 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.client_id ARE foreign keys to other tables.
But I don't see any reference to locking rows in parent tables in the docs around https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS. A quick poke around did not reveal any documentation that confirms this one way or another. And to my admittedly in-expert thinking, it seems surprising that the parent might need to be locked?

 On Saturday, March 7, 2026 at 04:25:01 AM GMT-5, Shaheed Haque <[email protected]> wrote:
 [I originally posted this over at https://forum.djangoproject.com/t/unexpected-deadlock-across-two-separate-rows-using-postgres-17-and..., 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:

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox