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

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 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 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 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 <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 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 <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, 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:

On Sun, Mar 8, 2026 at 2:23 PM <felix.quintgz@yahoo.com> wrote:

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.

This looks 100% like a normal, multi-row deadlock situation. The CONTEXT
shows it is a row-level problem:

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

The ShareLocks are on the transaction, because each backend is waiting for
the other to finish their transaction, and thus release the lock(s) it may
have.

If you implement Tom's suggestion, I think you will find that this is a
classic failing to lock the rows in the same order problem.

Cheers,
Greg

Thank you for the replies.

On Sun, 8 Mar 2026 at 22:08, Greg Sabino Mullane <htamfids@gmail.com> wrote:

On Sun, Mar 8, 2026 at 2:23 PM <felix.quintgz@yahoo.com> wrote:

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.

This looks 100% like a normal, multi-row deadlock situation. The CONTEXT
shows it is a row-level problem:

I'm not sure I understand. The two queries are referencing separate, single
rows in the child table (primary keys payroll_endpoint.id = 1 and 2), so
where does the multi-row bit come in? Is it because the two parent tables
are also being locked, in possibly different orders?

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

The ShareLocks are on the transaction, because each backend is waiting for
the other to finish their transaction, and thus release the lock(s) it may
have.

If you implement Tom's suggestion, I think you will find that this is a
classic failing to lock the rows in the same order problem.

I'm not seeing "Tom's suggestion". Is there a way to specify that the
parent tables need not be locked? Perhaps by omitting them from the query?

Thanks, Shaheed

On Mon, Mar 9, 2026 at 5:55 AM Shaheed Haque <shaheedhaque@gmail.com> wrote:

I'm not sure I understand. The two queries are referencing separate,
single rows in the child table (primary keys payroll_endpoint.id = 1 and
2), so where does the multi-row bit come in? Is it because the two parent
tables are also being locked, in possibly different orders?

This is unrelated to parent tables. What is not shown is the previous
locks. Process A locks id 1. Process B locks id 2, then attempts to lock id
1. Process A attempts to lock id 2. Hence, a deadlock, even if all four
lock attempts are not shown in the log.

I'm not seeing "Tom's suggestion". Is there a way to specify that the

parent tables need not be locked? Perhaps by omitting them from the query?

alter system set log_statement = 'all';
select pg_reload_conf();

## Run your program and get the deadlock error

alter system reset log_statement;
select pg_reload_conf();

Now check your logs, find the PIDs involved in the deadlock, and trace what
actions they did before the deadlock occurred.

Here's a real example, showing what the Postgres logs will look like:

-- Process A:
create table t (id int);
insert into t values (1),(2);
begin;
select * from t where id = 1 for update;

-- Process B:
begin;
select * from t where id = 2 for update;
select * from t where id = 1 for update; -- Hangs, waiting for process A to
finish

-- Process A:
select * from t where id = 2 for update;

DEADLOCK!

Logs:

2026-03-09 09:00:23.885 EDT [242171] LOG: statement: alter system set
log_statement = 'all';
2026-03-09 09:00:25.804 EDT [242136] LOG: received SIGHUP, reloading
configuration files
2026-03-09 09:00:25.805 EDT [242136] LOG: parameter "log_statement"
changed to "all"
2026-03-09 09:00:31.910 EDT [242171] LOG: statement: drop table if exists
t;
2026-03-09 09:00:36.649 EDT [242171] LOG: statement: create table t(id
int);
2026-03-09 09:00:39.522 EDT [242171] LOG: statement: insert into t values
(1),(2);
2026-03-09 09:00:42.121 EDT [242171] LOG: statement: begin;
2026-03-09 09:00:50.788 EDT [242171] LOG: statement: select * from t where
id=1 for update;
2026-03-09 09:00:59.755 EDT [242176] LOG: statement: begin;
2026-03-09 09:01:05.509 EDT [242176] LOG: statement: select * from t where
id=2 for update;
2026-03-09 09:01:08.397 EDT [242176] LOG: statement: select * from t where
id=1 for update;
2026-03-09 09:01:14.278 EDT [242171] LOG: statement: select * from t where
id=2 for update;
2026-03-09 09:01:15.279 EDT [242171] ERROR: deadlock detected
2026-03-09 09:01:15.279 EDT [242171] DETAIL: Process 242171 waits for
ShareLock on transaction 15122348; blocked by process 242176.
Process 242176 waits for ShareLock on transaction 15122347; blocked
by process 242171.
Process 242171: select * from t where id=2 for update;
Process 242176: select * from t where id=1 for update;
2026-03-09 09:01:15.279 EDT [242171] HINT: See server log for query
details.
2026-03-09 09:01:15.279 EDT [242171] CONTEXT: while locking tuple (0,2) in
relation "t"
2026-03-09 09:01:15.279 EDT [242171] STATEMENT: select * from t where id=2
for update;

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Greg,

That's extremely helpful, thank you.

On Mon, 9 Mar 2026 at 13:07, Greg Sabino Mullane <htamfids@gmail.com> wrote: