MIME-Version: 1.0
References: <CAHAc2jd=x-6hM=CoEYOaJ6ST5p-nGkTxDmh=PrwxacnkUp=31A@mail.gmail.com>
 <1164079167.6346688.1772982934392@mail.yahoo.com> <CAHAc2jeME7KPPP3TgP9qWC5LMTOs4gYTEJ1MBDwnj7J2EvJTdA@mail.gmail.com>
 <1656643760.2116109.1772994207524@mail.yahoo.com> <CAKAnmmKD2kxF6pD3c5eFhJZ+TMogRS9dHcMWbmiZs2Vn=XvZ0g@mail.gmail.com>
 <CAHAc2jcTLhGPFYNUC++e9HXG2kt-KuCu9tuvGpd-2Hi+ry4yDg@mail.gmail.com>
In-Reply-To: <CAHAc2jcTLhGPFYNUC++e9HXG2kt-KuCu9tuvGpd-2Hi+ry4yDg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 9 Mar 2026 09:06:56 -0400
Message-ID: <CAKAnmm+r3MpuJdUEf6mzvD24ZZO4FXV17W+4D5D2ycbme9gGFA@mail.gmail.com>
Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17
 and Django's select_for_update()
To: Shaheed Haque <shaheedhaque@gmail.com>
Cc: felix.quintgz@yahoo.com, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000002fbc1064c9716ed"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2Br3MpuJdUEf6mzvD24ZZO4FXV17W%2B4D5D2ycbme9gGFA%40mail.gmail.com>
Precedence: bulk

--00000000000002fbc1064c9716ed
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 9, 2026 at 5:55=E2=80=AFAM Shaheed Haque <shaheedhaque@gmail.co=
m> wrote:

> I'm not sure I understand. The two queries are referencing separate,
> single rows in the child table (primary keys payroll_endpoint.id =3D 1 an=
d
> 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 =3D '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 =3D 1 for update;

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

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

DEADLOCK!

Logs:

2026-03-09 09:00:23.885 EDT [242171] LOG:  statement: alter system set
log_statement =3D '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=3D1 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=3D2 for update;
2026-03-09 09:01:08.397 EDT [242176] LOG:  statement: select * from t where
id=3D1 for update;
2026-03-09 09:01:14.278 EDT [242171] LOG:  statement: select * from t where
id=3D2 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=3D2 for update;
        Process 242176: select * from t where id=3D1 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=
=3D2
for update;


Cheers,
Greg

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

--00000000000002fbc1064c9716ed
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Mar 9, 2026 at 5:55=E2=80=AFAM Sh=
aheed Haque &lt;<a href=3D"mailto:shaheedhaque@gmail.com">shaheedhaque@gmai=
l.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container">=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr"><div><div>I&#39;m not sure I understand. The two queries are refer=
encing separate, single rows in the child table (primary keys <a href=3D"ht=
tp://payroll_endpoint.id" target=3D"_blank">payroll_endpoint.id</a> =3D 1 a=
nd 2), so where does the multi-row bit come in?=C2=A0Is it because the two =
parent tables are also being locked, in possibly different orders?</div></d=
iv></div></div></blockquote><div><br></div><div>This is unrelated to parent=
 tables. What is not shown is the previous locks. Process A locks id 1. Pro=
cess B locks id 2, then attempts to lock id 1. Process A attempts to lock i=
d 2. Hence, a deadlock, even if all four lock attempts are not shown in the=
 log.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div dir=3D"ltr"><div class=3D"gmail_quote"><div>I&#39;m not seeing &quot;=
Tom&#39;s suggestion&quot;. Is there a way to specify that the parent table=
s need not be locked? Perhaps by omitting them from the query?</div></div><=
/div></blockquote><div><br></div><div>alter system set log_statement =3D &#=
39;all&#39;;</div><div>select pg_reload_conf();</div><div><br></div><div>##=
 Run your program and get the deadlock error</div><div><br></div><div>alter=
 system reset log_statement;</div><div>select pg_reload_conf();</div><div><=
br></div><div>Now check your logs, find the PIDs involved in the deadlock, =
and trace what actions they did before the deadlock occurred.</div><div><br=
></div><div>Here&#39;s a real example, showing what the Postgres logs will =
look like:</div><div><br></div><div>-- Process A:</div><div>create table t =
(id int);</div><div>insert into t values (1),(2);</div><div>begin;</div><di=
v>select * from t where id =3D 1 for update;</div><div><br></div><div>-- Pr=
ocess B:</div><div>begin;</div><div>select * from t where id =3D 2 for upda=
te;</div><div>select * from t where id =3D 1 for update; -- Hangs, waiting =
for process A to finish</div><div><br></div><div>-- Process A:</div><div>se=
lect * from t where id =3D 2 for update;</div><div><br></div><div>DEADLOCK!=
</div><div><br></div><div>Logs:</div><div><br></div><div>2026-03-09 09:00:2=
3.885 EDT [242171] LOG: =C2=A0statement: alter system set log_statement =3D=
 &#39;all&#39;;<br>2026-03-09 09:00:25.804 EDT [242136] LOG: =C2=A0received=
 SIGHUP, reloading configuration files<br>2026-03-09 09:00:25.805 EDT [2421=
36] LOG: =C2=A0parameter &quot;log_statement&quot; changed to &quot;all&quo=
t;<br>2026-03-09 09:00:31.910 EDT [242171] LOG: =C2=A0statement: drop table=
 if exists t;<br>2026-03-09 09:00:36.649 EDT [242171] LOG: =C2=A0statement:=
 create table t(id int);<br>2026-03-09 09:00:39.522 EDT [242171] LOG: =C2=
=A0statement: insert into t values (1),(2);<br>2026-03-09 09:00:42.121 EDT =
[242171] LOG: =C2=A0statement: begin;<br>2026-03-09 09:00:50.788 EDT [24217=
1] LOG: =C2=A0statement: select * from t where id=3D1 for update;<br>2026-0=
3-09 09:00:59.755 EDT [242176] LOG: =C2=A0statement: begin;<br>2026-03-09 0=
9:01:05.509 EDT [242176] LOG: =C2=A0statement: select * from t where id=3D2=
 for update;<br>2026-03-09 09:01:08.397 EDT [242176] LOG: =C2=A0statement: =
select * from t where id=3D1 for update;<br>2026-03-09 09:01:14.278 EDT [24=
2171] LOG: =C2=A0statement: select * from t where id=3D2 for update;<br>202=
6-03-09 09:01:15.279 EDT [242171] ERROR: =C2=A0deadlock detected<br>2026-03=
-09 09:01:15.279 EDT [242171] DETAIL: =C2=A0Process 242171 waits for ShareL=
ock on transaction 15122348; blocked by process 242176.<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 Process 242176 waits for ShareLock on transaction 15122347; b=
locked by process 242171.<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 Process 242171: se=
lect * from t where id=3D2 for update;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 Proce=
ss 242176: select * from t where id=3D1 for update;<br>2026-03-09 09:01:15.=
279 EDT [242171] HINT: =C2=A0See server log for query details.<br>2026-03-0=
9 09:01:15.279 EDT [242171] CONTEXT: =C2=A0while locking tuple (0,2) in rel=
ation &quot;t&quot;<br>2026-03-09 09:01:15.279 EDT [242171] STATEMENT: =C2=
=A0select * from t where id=3D2 for update;</div><div><br></div><div><br></=
div><div>Cheers,</div></div><div dir=3D"ltr" class=3D"gmail_signature"><div=
 dir=3D"ltr"><div>Greg</div><div><br></div><div>--</div><div>Crunchy Data -=
 <a href=3D"https://www.crunchydata.com" target=3D"_blank">https://www.crun=
chydata.com</a></div><div>Enterprise Postgres Software Products &amp; Tech =
Support</div><div><br></div></div></div></div>

--00000000000002fbc1064c9716ed--