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-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 = 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 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 > > >
