public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rob Sargent <[email protected]>
To: [email protected]
Subject: Re: Delete from locking ordering differences
Date: Fri, 23 Sep 2022 10:04:56 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>
References: <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>
On 9/23/22 01:31, Peter Hendriks wrote:
> 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 <http://s.id; = si.id <http://si.id;
> RETURNING s.id <http://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!
The "optimizatin fence" nature of CTEs appears to be a win in this
case. Why the "order by"? I assume these are down within a transaction?
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: <[email protected]>
* 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