public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Sabino Mullane <[email protected]>
To: Shaheed Haque <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
Date: Mon, 9 Mar 2026 09:06:56 -0400
Message-ID: <CAKAnmm+r3MpuJdUEf6mzvD24ZZO4FXV17W+4D5D2ycbme9gGFA@mail.gmail.com> (raw)
In-Reply-To: <CAHAc2jcTLhGPFYNUC++e9HXG2kt-KuCu9tuvGpd-2Hi+ry4yDg@mail.gmail.com>
References: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com>
<[email protected]>
<CAHAc2jeME7KPPP3TgP9qWC5LMTOs4gYTEJ1MBDwnj7J2EvJTdA@mail.gmail.com>
<[email protected]>
<CAKAnmmKD2kxF6pD3c5eFhJZ+TMogRS9dHcMWbmiZs2Vn=XvZ0g@mail.gmail.com>
<CAHAc2jcTLhGPFYNUC++e9HXG2kt-KuCu9tuvGpd-2Hi+ry4yDg@mail.gmail.com>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()
In-Reply-To: <CAKAnmm+r3MpuJdUEf6mzvD24ZZO4FXV17W+4D5D2ycbme9gGFA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox