Delete from locking ordering differences
| От | Peter Hendriks |
|---|---|
| Тема | Delete from locking ordering differences |
| Дата | |
| Msg-id | CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Delete from locking ordering differences
Re: Delete from locking ordering differences |
| Список | 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
)
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!
В списке pgsql-sql по дате отправления: