Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update() - Mailing list pgsql-general

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