public inbox for [email protected]
help / color / mirror / Atom feedRe: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
8+ messages / 4 participants
[nested] [flat]
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-08 15:15 [email protected]
0 siblings, 2 replies; 8+ messages in thread
From: [email protected] @ 2026-03-08 15:15 UTC (permalink / raw)
To: [email protected]
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 <[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:
#
# 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-08 16:19 Shaheed Haque <[email protected]>
parent: [email protected]
1 sibling, 1 reply; 8+ messages in thread
From: Shaheed Haque @ 2026-03-08 16:19 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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:
>
> #
> # 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
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-08 18:03 Laurenz Albe <[email protected]>
parent: [email protected]
1 sibling, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2026-03-08 18:03 UTC (permalink / raw)
To: [email protected]; [email protected]
On Sun, 2026-03-08 at 15:15 +0000, [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.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-08 18:23 [email protected]
parent: Shaheed Haque <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: [email protected] @ 2026-03-08 18:23 UTC (permalink / raw)
To: [email protected]
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:
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-08 22:08 Greg Sabino Mullane <[email protected]>
parent: [email protected]
0 siblings, 1 reply; 8+ messages in thread
From: Greg Sabino Mullane @ 2026-03-08 22:08 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On Sun, Mar 8, 2026 at 2:23 PM <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-09 09:55 Shaheed Haque <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Shaheed Haque @ 2026-03-09 09:55 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]; [email protected]
Thank you for the replies.
On Sun, 8 Mar 2026 at 22:08, Greg Sabino Mullane <[email protected]> wrote:
> On Sun, Mar 8, 2026 at 2:23 PM <[email protected]> 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
>
> Cheers,
> Greg
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-09 13:06 Greg Sabino Mullane <[email protected]>
parent: Shaheed Haque <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Greg Sabino Mullane @ 2026-03-09 13:06 UTC (permalink / raw)
To: Shaheed Haque <[email protected]>; +Cc: [email protected]; [email protected]
On Mon, Mar 9, 2026 at 5:55 AM Shaheed Haque <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
@ 2026-03-09 17:21 Shaheed Haque <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Shaheed Haque @ 2026-03-09 17:21 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]; [email protected]
Greg,
That's extremely helpful, thank you.
On Mon, 9 Mar 2026 at 13:07, Greg Sabino Mullane <[email protected]> wrote:
> On Mon, Mar 9, 2026 at 5:55 AM Shaheed Haque <[email protected]>
> 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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-03-09 17:21 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-08 15:15 Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update() [email protected]
2026-03-08 16:19 ` Shaheed Haque <[email protected]>
2026-03-08 18:23 ` [email protected]
2026-03-08 22:08 ` Greg Sabino Mullane <[email protected]>
2026-03-09 09:55 ` Shaheed Haque <[email protected]>
2026-03-09 13:06 ` Greg Sabino Mullane <[email protected]>
2026-03-09 17:21 ` Shaheed Haque <[email protected]>
2026-03-08 18:03 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox