Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vzGqw-000r5s-2T for pgsql-general@arkaria.postgresql.org; Sun, 08 Mar 2026 16:19:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vzGqv-00B3SM-01 for pgsql-general@arkaria.postgresql.org; Sun, 08 Mar 2026 16:19:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vzGqu-00B3SD-25 for pgsql-general@lists.postgresql.org; Sun, 08 Mar 2026 16:19:37 +0000 Received: from mail-wr1-x42d.google.com ([2a00:1450:4864:20::42d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vzGqs-00000001Vlb-2t1F for pgsql-general@lists.postgresql.org; Sun, 08 Mar 2026 16:19:36 +0000 Received: by mail-wr1-x42d.google.com with SMTP id ffacd0b85a97d-439c4bde55cso3433595f8f.1 for ; Sun, 08 Mar 2026 09:19:34 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1772986774; cv=none; d=google.com; s=arc-20240605; b=bPCfr8mzWylfc9ZDELgXDvMuuCIsaOtB4oYdKdeLhuVAboNSV8+gZoX7EuycNd69Go cbVrsB2IZc0kiBunsy956MejxvDIW0WwfdjgAnb0AgQEBSBqs35BoH/dUAlVJZF1lh0I Zm9esQhrail1B9xiAQRUoWCCaGbCo6aDIjG8C9tp7IxSqDePPiptYo2bTIiajK9vzWxE TiNv2hXHOG95FzbtNW/eS9kHJLhPsHWFfeIB7dEhtLQwWP0tj8BSqC0IuY3LMzMjJl42 /lmaU42o172jJmnBQVhjyI72aLPYS/Xg08lpEfuTskm3AuxZImyZwkQ79hDSt1g3isbF lC6g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=jA4WAuvmyrySh1mj8u1yVYuHMRnWVp/7s+aPZ9iwnVg=; fh=pYlkZbsn/WOPctNgubLE5p+7czOJWMZ4WXLc7+RHM5U=; b=IPFRj5a+t2dhnE023uwdRQSKT/wamK0O6eJVYzLEUDooIG2Zj1MlW48epaOZj6m6lH qMXZZtmmVkDeM955CRlaZSzdKy8l1br2yN1n0AM25YdWx1C+idA51B4UlQ9rqMZYrRt5 WMjRdUmhmGjxztDPOCobKeED3SwUVPG8rTDNrWCDlBZPvdtDitI9frTOeq4lp9u4ta9X p0RWatCEw9SoXqjsOKrqX4BhUgjtxXhPJg/Oqw7GTSzT0TIDE/pG97xUENaQaUWc46uO oY22TOXnONrYpgT1H3Fir+EBev1JlY/YNFDRU2Qx4ccsEo6vF2PGAm+yV5wtMfH+wDI/ bHiQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1772986774; x=1773591574; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=jA4WAuvmyrySh1mj8u1yVYuHMRnWVp/7s+aPZ9iwnVg=; b=cHQ5g3hNdIc10fx0POwXlYN4GpbDN1tzHbhGRwt/EcWxKrofdl2NUTvNPzx/YwhYAt 5Kd99Mhp15dO1ReNEj18T45MZrJ8joIjk2FZYmcSsKGdpH0rC1GsYe2jPAneJvomCuce jipU15T75SjBoepqqEU1y4Q+4dEbEOMGDGZhlYVnxrZLnrXjOD8G52NMuxMEB22B6WzW Wz+r6ONPKSezuqb7ysNmpe1rWd5FzOKhONb8qlnNIzeBZQor0874PZf7DCBtEHoheh0h BO0uG0T8ci739ny7VgXAgROzW9G9JV5fDoHvtJS4MKe2wxErBpuG+LmPopJixLiWOGW0 78hg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772986774; x=1773591574; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=jA4WAuvmyrySh1mj8u1yVYuHMRnWVp/7s+aPZ9iwnVg=; b=TI3bQ7Mu7YuWCx4Pi+1v6ekp1XtSOs2QczcdoA+ujs+X1I3h3FOkqy9jvyDMMl2fYP T6AJdL99LwZJzgQtCHaEPbc2j90Cy/TgiTopQGbmILzaKdwLBJaZ3Nn4xhfc9ESsRdCq +k/TMObwVt662qQqHbiO9n3i18n+5bTAo2XpAgHHbQMhfTQ1OOfkXNW7hLPp92m6u5oW mdPifN7Lfc8onfZc8vuYsfu11gdlRso9FclLnazer/Q+Q189ZplqYyHJB19vo2GE6HS1 2wxW0umUjPgP9eCD4qKnXObTF92HkwIGgIOTvvdZtUM+SoDf6pGWk+cRqiVpr4CVrjdF lAkA== X-Gm-Message-State: AOJu0YwGsVAmtsKoC4h+rvj3pJOrf2QCyCr47d7iWkXqaRlXvRhBWbXe l+l4h+qG/DyZkjn7ynryyGTEu13XOFQRdR++6XHoOhdCUl6ZYO4lJrzPp1BDvpoEmFBkt/C69AA xtJ4RfPGnlIAANw2SKm8cykPkJGAem+E= X-Gm-Gg: ATEYQzyx0IFwVXv/rhih5cLU3+C7JThae7RySqsVQ5qdkaHu02xLE75mB7LF2CXJKSh 5SYHXMiAUM9XnCBfcq23Hz5VH1RS9BNm8m33y8DLq0pzsPNfVlEhZV0sTp6tZvq64H5CAdDCAYS +Hn5+r/KP0ApdXnaGqg4WFDtV+0AhAwphX415g/wnGRJfWsYI1r9WdBTE0BwhzE8o6zdiNcSSsp /cXoPty55G4LEfImJ3X8lJqYfdatq8MzNyrgRP+ST0SgRolXFwm5ZZiLgdpJ0nJfDTel67FGz1m Mz4TVXo= X-Received: by 2002:a5d:5d84:0:b0:439:c928:e8bc with SMTP id ffacd0b85a97d-439da32f844mr15220607f8f.16.1772986773556; Sun, 08 Mar 2026 09:19:33 -0700 (PDT) MIME-Version: 1.0 References: <1164079167.6346688.1772982934392@mail.yahoo.com> In-Reply-To: <1164079167.6346688.1772982934392@mail.yahoo.com> From: Shaheed Haque Date: Sun, 8 Mar 2026 16:19:22 +0000 X-Gm-Features: AaiRm509jO2wMlaURvZGLGbMz60dmBRju1XkJBKGV7wIqxJFQZu8XmE4Bhz4zHw Message-ID: Subject: Re: Unexpected deadlock across two separate rows, using Postgres 17 and Django's select_for_update() To: felix.quintgz@yahoo.com Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d456a4064c85a611" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d456a4064c85a611 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, 8 Mar 2026 at 15:15, wrote: > This is pure speculation. > It's possible that using SELECT FOR UPDATE also locks the rows in the > parent tables referenced in the field list. > I believe this happened in older versions of PostgreSQL. > Interesting. In the query, paiyroll_endpoint.op_id and paiyroll_endpoint.client_id ARE foreign keys to other tables. But I don't see any reference to locking rows in parent tables in the docs around https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS. A quick poke around did not reveal any documentation that confirms this one way or another. And to my admittedly in-expert thinking, it seems surprising that the parent might need to be locked? > > On Saturday, March 7, 2026 at 04:25:01 AM GMT-5, Shaheed Haque < > shaheedhaque@gmail.com> wrote: > > [I originally posted this over at > https://forum.djangoproject.com/t/unexpected-deadlock-across-two-separate= -rows-using-postgres-17-and-select-for-update/44294/1, > but that thread ran into a dead end. Apologies for the cross-post] > > Hi, > I'm trying to understand/fix a rare deadlock in my application. Given my > limited knowledge, what seems odd to me is that the deadlock involves two > processes running exactly the same code/query, each of which (tries to) > avoid issues by locking exactly one row for update. In Django-speak, the > code does this: > > # > # Select-for-update exactly one row by id. > # > qs =3D Endpoint.objects.select_for_update().filter(id=3Dinstance.id) > # > # The above returns a queryset of one row which we loop over: > # > for item in qs: > > ...do stuff with item... > > item.save() The deadlock is reported in the Postgres server log like thi= s: > ERROR: deadlock detected > > DETAIL: Process 15576 waits for ShareLock on transaction 31053599; blocke= d > by process 16953. > > Process 16953 waits for ShareLock on transaction 31053597; blocked by > process 15576. > > Process 15576: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2= =80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2= =80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cp= aiyroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D > FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE =E2=80=9Cpaiyroll_endpoint= =E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE > > Process 16953: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2= =80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2= =80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cp= aiyroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D > FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE =E2=80=9Cpaiyroll_endpoint= =E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 2 FOR UPDATE > > HINT: See server log for query details. > > CONTEXT: while locking tuple (7,15) in relation =E2=80=9Cpaiyroll_endpoin= t=E2=80=9D > > STATEMENT: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80= =9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2= =80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D, > =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cp= aiyroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D > FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE =E2=80=9Cpaiyroll_endpoint= =E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE > How can there be a deadlock between updates to different rows (as per the > bolded WHERE clauses)? Have I somehow turned off row-level locks? Is ther= e > some additional logging I could enable to try to catch the data needed to > root-cause this? > > Any help appreciated. > Thanks, Shaheed > > > --000000000000d456a4064c85a611 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, 8 Mar 2026 at 15:15, <felix.quintgz@yahoo.com> wrote:
This is pure speculat= ion.
It's possible that using SELECT FOR UPDATE also locks the rows in the p= arent tables referenced in the field list.
I believe this happened in older versions of PostgreSQL.

Interesting. In the query, paiyroll_endpoint.op_id and pa= iyroll_endpoint.client_id ARE foreign keys to other tables.

<= /div>
But I don't see any reference to locking rows in parent table= s in the docs around=C2=A0https://www.postgresql.org/docs/curre= nt/explicit-locking.html#LOCKING-ROWS. A quick poke around did not reve= al any documentation that confirms this one way or another. And to my admit= tedly in-expert thinking, it seems surprising=C2=A0that the parent might ne= ed to be locked?

=C2=A0

=C2=A0On Saturday, March 7, 2026 at 04:25:01 AM GMT-5, Shaheed Haque <shaheedhaque@gmai= l.com> wrote:

=C2=A0[I originally posted this over at=C2=A0http= s://forum.djangoproject.com/t/unexpected-deadlock-across-two-separate-rows-= using-postgres-17-and-select-for-update/44294/1, but that thread ran in= to a dead end. Apologies for the cross-post]

Hi,
I'm trying to understand/fix a rare deadlock in my application. Given m= y limited knowledge, what seems odd to me is that the deadlock involves two= processes=C2=A0running exactly the same code/query, each of which (tries t= o) avoid issues by locking exactly one row for update. In Django-speak, the= code does this:

#
# Select-for-update exactly one row by id.
#
qs =3D Endpoint.objects.select_for_update().filter(id=3Dinstance.id)
#
# The above returns a queryset of one row which we loop over:
#
for item in qs:

=C2=A0...do stuff with item...

=C2=A0item.save() The deadlock is reported in the Postgres server log like = this:
ERROR: deadlock detected

DETAIL: Process 15576 waits for ShareLock on transaction 31053599; blocked = by process 16953.

Process 16953 waits for ShareLock on transaction 31053597; blocked by proce= ss 15576.

Process 15576: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80= =9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr= oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2= =80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr= oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D
FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE =E2=80=9Cpaiyroll_endpoint= =E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE

Process 16953: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80= =9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr= oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2= =80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr= oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D
FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE =E2=80=9Cpaiyroll_endpoint= =E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 2 FOR UPDATE

HINT: See server log for query details.

CONTEXT: while locking tuple (7,15) in relation =E2=80=9Cpaiyroll_endpoint= =E2=80=9D

STATEMENT: SELECT =E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cid=E2=80=9D,=
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cop_id=E2=80=9D, =E2=80=9Cpaiyr= oll_endpoint=E2=80=9D.=E2=80=9Cclient_id=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cclient_private=E2=80=9D, =E2= =80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Cnetloc=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Ccalls=E2=80=9D, =E2=80=9Cpaiyr= oll_endpoint=E2=80=9D.=E2=80=9Cms=E2=80=9D,
=E2=80=9Cpaiyroll_endpoint=E2=80=9D.=E2=80=9Chistory=E2=80=9D, =E2=80=9Cpai= yroll_endpoint=E2=80=9D.=E2=80=9Ccurrent_history=E2=80=9D
FROM =E2=80=9Cpaiyroll_endpoint=E2=80=9D WHERE =E2=80=9Cpaiyroll_endpoint= =E2=80=9D.=E2=80=9Cid=E2=80=9D =3D 1 FOR UPDATE
How can there be a deadlock between updates to different rows (as per the b= olded WHERE clauses)? Have I somehow turned off row-level locks? Is there s= ome additional logging I could enable to try to catch=C2=A0the data needed = to root-cause this?

Any help appreciated.
Thanks, Shaheed


--000000000000d456a4064c85a611--