MIME-Version: 1.0
From: Peter Hendriks <peter@mindloops.nl>
Date: Fri, 23 Sep 2022 09:31:17 +0200
Message-ID: 
 <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>
Subject: Delete from locking ordering differences
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000081c46105e9532a6d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFhXkLG%3DizjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w%40mail.gmail.com>
Precedence: bulk

--00000000000081c46105e9532a6d
Content-Type: text/plain; charset="UTF-8"

We are wondering if anyone can explain the difference we are having in
production with the following queries:

DELETE FROM store
WHERE id IN (
  SELECT id FROM store
  FOR UPDATE SKIP LOCKED
  ORDER BY ID
  LIMIT 1000
)
RETURNING id, payload

This query is sometimes executed with high concurrency, and then can hang
indefinitely, we assume because of a locking problem that postgresql is not
detecting as a deadlock.

This alternative query does not have the hanging problem:

WITH store_ids AS (
  SELECT id FROM store
  FOR UPDATE SKIP LOCKED
  ORDER BY ID
  LIMIT 1000
)
DELETE FROM store s
USING store_ids si
WHERE s.id = si.id
RETURNING s.id, s.payload

Can anyone explain why the first query is expected to fail (hang), and the
second query does not have this problem? We would be interested in more
understanding on this. Thanks!

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

<div dir=3D"ltr">We are wondering if anyone can explain the difference we a=
re having in production with the following queries:<div><br></div><div>DELE=
TE FROM store</div><div>WHERE id IN (</div><div>=C2=A0 SELECT id FROM store=
=C2=A0</div><div>=C2=A0 FOR UPDATE SKIP LOCKED=C2=A0</div><div>=C2=A0 ORDER=
 BY ID</div><div>=C2=A0 LIMIT 1000</div><div>)</div><div>RETURNING id, payl=
oad</div><div><br></div><div>This query is sometimes executed with high con=
currency, and then can hang indefinitely, we assume because of a locking pr=
oblem that postgresql is not detecting as a deadlock.</div><div><br></div><=
div>This alternative query does not have the hanging problem:</div><div><br=
></div><div>WITH store_ids AS (</div><div><div>=C2=A0 SELECT id FROM store=
=C2=A0</div><div>=C2=A0 FOR UPDATE SKIP LOCKED=C2=A0</div><div>=C2=A0 ORDER=
 BY ID</div><div>=C2=A0 LIMIT 1000</div></div><div>)</div><div><div>DELETE =
FROM store s</div><div>USING store_ids si=C2=A0</div><div>WHERE <a href=3D"=
http://s.id">s.id</a> =3D <a href=3D"http://si.id">si.id</a></div><div>RETU=
RNING <a href=3D"http://s.id">s.id</a>, s.payload</div></div><div><br></div=
><div>Can anyone explain why the first query is expected to fail (hang), an=
d the second query does not have this problem? We would be interested in mo=
re understanding on this. Thanks!=C2=A0</div></div>

--00000000000081c46105e9532a6d--