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> <CAKAnmm+r3MpuJdUEf6mzvD24ZZO4FXV17W+4D5D2ycbme9gGFA@mail.gmail.com>
In-Reply-To: <CAKAnmm+r3MpuJdUEf6mzvD24ZZO4FXV17W+4D5D2ycbme9gGFA@mail.gmail.com>
From: Shaheed Haque <shaheedhaque@gmail.com>
Date: Mon, 9 Mar 2026 17:21:43 +0000
Message-ID: <CAHAc2jc5kq3seHOMvoJ+ZgnsceT2OnrhGyEGur-BPGpU5kq03A@mail.gmail.com>
Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17
 and Django's select_for_update()
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: felix.quintgz@yahoo.com, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a6e717064c9aa35b"
Archived-At: <https://www.postgresql.org/message-id/CAHAc2jc5kq3seHOMvoJ%2BZgnsceT2OnrhGyEGur-BPGpU5kq03A%40mail.gmail.com>
Precedence: bulk

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

Greg,

That's extremely helpful, thank you.

On Mon, 9 Mar 2026 at 13:07, Greg Sabino Mullane <htamfids@gmail.com> wrote=
:

> On Mon, Mar 9, 2026 at 5:55=E2=80=AFAM 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 =3D 1 a=
nd
>> 2), so where does the multi-row bit come in? Is it because the two paren=
t
>> 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 quer=
y?
>>
>
> 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 exist=
s
> 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 value=
s
> (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
>
>

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

<div dir=3D"ltr"><div>Greg,</div><div><br></div><div>That&#39;s extremely h=
elpful, thank you.</div></div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, 9 Mar 2026 at 13:07, =
Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmai=
l.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Mon, Mar 9, 2026 at 5:55=E2=80=AF=
AM Shaheed Haque &lt;<a href=3D"mailto:shaheedhaque@gmail.com" target=3D"_b=
lank">shaheedhaque@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"=
><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 di=
r=3D"ltr"><div><div>I&#39;m not sure I understand. The two queries are refe=
rencing separate, single rows in the child table (primary keys <a href=3D"h=
ttp://payroll_endpoint.id" target=3D"_blank">payroll_endpoint.id</a> =3D 1 =
and 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></=
div></div></div></blockquote><div><br></div><div>This is unrelated to paren=
t tables. What is not shown is the previous locks. Process A locks id 1. Pr=
ocess 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 th=
e log.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n: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 tabl=
es 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>alte=
r 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><b=
r></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><d=
iv>select * from t where id =3D 1 for update;</div><div><br></div><div>-- P=
rocess B:</div><div>begin;</div><div>select * from t where id =3D 2 for upd=
ate;</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>s=
elect * 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:=
23.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=A0recei=
ved SIGHUP, reloading configuration files<br>2026-03-09 09:00:25.805 EDT [2=
42136] LOG: =C2=A0parameter &quot;log_statement&quot; changed to &quot;all&=
quot;<br>2026-03-09 09:00:31.910 EDT [242171] LOG: =C2=A0statement: drop ta=
ble if exists t;<br>2026-03-09 09:00:36.649 EDT [242171] LOG: =C2=A0stateme=
nt: 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 E=
DT [242171] LOG: =C2=A0statement: begin;<br>2026-03-09 09:00:50.788 EDT [24=
2171] LOG: =C2=A0statement: select * from t where id=3D1 for update;<br>202=
6-03-09 09:00:59.755 EDT [242176] LOG: =C2=A0statement: begin;<br>2026-03-0=
9 09: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=A0stateme=
nt: select * from t where id=3D1 for update;<br>2026-03-09 09:01:14.278 EDT=
 [242171] LOG: =C2=A0statement: select * from t where id=3D2 for update;<br=
>2026-03-09 09:01:15.279 EDT [242171] ERROR: =C2=A0deadlock detected<br>202=
6-03-09 09:01:15.279 EDT [242171] DETAIL: =C2=A0Process 242171 waits for Sh=
areLock on transaction 15122348; blocked by process 242176.<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Process 242176 waits for ShareLock on transaction 1512234=
7; blocked by process 242171.<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 Process 242171=
: select * from t where id=3D2 for update;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 P=
rocess 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-09 09:01:15.279 EDT [242171] CONTEXT: =C2=A0while locking tuple (0,2) in=
 relation &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 Dat=
a - <a href=3D"https://www.crunchydata.com" target=3D"_blank">https://www.c=
runchydata.com</a></div><div>Enterprise Postgres Software Products &amp; Te=
ch Support</div><div><br></div></div></div></div>
</blockquote></div>

--000000000000a6e717064c9aa35b--