public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Hendriks <[email protected]>
To: [email protected]
Subject: Delete from locking ordering differences
Date: Fri, 23 Sep 2022 09:31:17 +0200
Message-ID: <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com> (raw)

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!

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Delete from locking ordering differences
  In-Reply-To: <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox