Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update()

Greg,

That's extremely helpful, thank you.

On Mon, 9 Mar 2026 at 13:07, Greg Sabino Mullane <htamfids(at)gmail(dot)com> wrote:

> On Mon, Mar 9, 2026 at 5:55 AM Shaheed Haque <shaheedhaque(at)gmail(dot)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
>
>