[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
