public inbox for [email protected]help / color / mirror / Atom feed
Delete from locking ordering differences 3+ messages / 2 participants [nested] [flat]
* Delete from locking ordering differences @ 2022-09-23 07:31 Peter Hendriks <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Peter Hendriks @ 2022-09-23 07:31 UTC (permalink / raw) To: [email protected] 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! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete from locking ordering differences @ 2022-09-23 16:04 Rob Sargent <[email protected]> parent: Peter Hendriks <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Rob Sargent @ 2022-09-23 16:04 UTC (permalink / raw) To: [email protected] 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? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete from locking ordering differences @ 2022-09-29 13:51 Peter Hendriks <[email protected]> parent: Peter Hendriks <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Peter Hendriks @ 2022-09-29 13:51 UTC (permalink / raw) To: [email protected] Yes, we do not really care about order, but to prevent locking issues. Multiple transactions may run this query at the same time. It should never contend because of the skip locked, so maybe we should try this without an order by too. We can not get this query to fail in test so far, just in production, wo we are bit hesitant to change now that it is finally working... Our guess is to why this makes such a difference is that the delete statement in postgres does not guarantee ordering, so maybe the optimizer makes different choices than expected. Asking the question here, so maybe someone with more understanding can explain why we need the CTE. Op vr 23 sep. 2022 om 09:31 schreef Peter Hendriks <[email protected]>: > 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! > -- Met vriendelijke groet, Peter Hendriks Mindloops B.V. T. +31 (0)6 37 23 26 73 E. [email protected] KvK: 85061921 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-09-29 13:51 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-09-23 07:31 Delete from locking ordering differences Peter Hendriks <[email protected]> 2022-09-23 16:04 ` Rob Sargent <[email protected]> 2022-09-29 13:51 ` Peter Hendriks <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox