Обсуждение: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()

Shaheed Haque <shaheedhaque@gmail.com> writes:
> How can there be a deadlock between updates to different rows (as per the
> bolded WHERE clauses)?

In isolation, there couldn't be ... but you've told us nothing of
what these transactions did beforehand.  The simplest explanation
is that each one is already holding a lock on the row the other
one wants.

Maybe you are not committing between row updates?

If you have no better debugging ideas, try setting log_statements = all
and then examining everything the transactions did up to the failure.

            regards, tom lane

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.

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

 [I originally posted this over
at https://forum.djangoproject.com/t/unexpected-deadlock-across-two-separate-rows-using-postgres-17-and-select-for-update/44294/1,
butthat 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
thatthe deadlock involves two processes running exactly the same code/query, each of which (tries to) avoid issues by
lockingexactly one row for update. In Django-speak, the code does this: 

#
# Select-for-update exactly one row by id.
#
qs = Endpoint.objects.select_for_update().filter(id=instance.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 “paiyroll_endpoint”.“id”,
“paiyroll_endpoint”.“op_id”, “paiyroll_endpoint”.“client_id”,
“paiyroll_endpoint”.“client_private”, “paiyroll_endpoint”.“netloc”,
“paiyroll_endpoint”.“calls”, “paiyroll_endpoint”.“ms”,
“paiyroll_endpoint”.“history”, “paiyroll_endpoint”.“current_history”
FROM “paiyroll_endpoint” WHERE “paiyroll_endpoint”.“id” = 1 FOR UPDATE

Process 16953: SELECT “paiyroll_endpoint”.“id”,
“paiyroll_endpoint”.“op_id”, “paiyroll_endpoint”.“client_id”,
“paiyroll_endpoint”.“client_private”, “paiyroll_endpoint”.“netloc”,
“paiyroll_endpoint”.“calls”, “paiyroll_endpoint”.“ms”,
“paiyroll_endpoint”.“history”, “paiyroll_endpoint”.“current_history”
FROM “paiyroll_endpoint” WHERE “paiyroll_endpoint”.“id” = 2 FOR UPDATE

HINT: See server log for query details.

CONTEXT: while locking tuple (7,15) in relation “paiyroll_endpoint”

STATEMENT: SELECT “paiyroll_endpoint”.“id”,
“paiyroll_endpoint”.“op_id”, “paiyroll_endpoint”.“client_id”,
“paiyroll_endpoint”.“client_private”, “paiyroll_endpoint”.“netloc”,
“paiyroll_endpoint”.“calls”, “paiyroll_endpoint”.“ms”,
“paiyroll_endpoint”.“history”, “paiyroll_endpoint”.“current_history”
FROM “paiyroll_endpoint” WHERE “paiyroll_endpoint”.“id” = 1 FOR UPDATE
How can there be a deadlock between updates to different rows (as per the bolded WHERE clauses)? Have I somehow turned
offrow-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

On Sun, 2026-03-08 at 15:15 +0000, felix.quintgz@yahoo.com 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.

I thought about that too, but since both updates affect the same table,
the foreign key would have to reference the table itself.

You are right that a SELECT ... FOR UPDATE will place a lock on any
referenced row (and FOR UPDATE will probably use a lock that is too
strong!), but those locks would still be SHARE locks, which can
coexist.

Yours,
Laurenz Albe

I've found some references indicating that it does this, but the lock on the parent table had to be shared to prevent
thedeletion 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
statementshouldn'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 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
anydocumentation that confirms this one way or another. And to my admittedly in-expert thinking, it seems
surprising thatthe parent might need to be locked? 

 On Saturday, March 7, 2026 at 04:25:01 AM GMT-5, Shaheed Haque <shaheedhaque@gmail.com> wrote:
 [I originally posted this over
at https://forum.djangoproject.com/t/unexpected-deadlock-across-two-separate-rows-using-postgres-17-and-select-for-update/44294/1,
butthat 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
thatthe deadlock involves two processes running exactly the same code/query, each of which (tries to) avoid issues by
lockingexactly one row for update. In Django-speak, the code does this: