Delete from locking ordering differences

From: Peter Hendriks <peter(at)mindloops(dot)nl>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Delete from locking ordering differences
Date: 2022-09-23 07:31:17
Message-ID: CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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!

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rob Sargent 2022-09-23 16:04:56 Re: Delete from locking ordering differences
Previous Message Geri Wright 2022-09-22 10:39:27 Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12