[GENERAL] Surprising locking behavior with CTE, FOR SHARE, and UPDATE

Поиск
Список
Период
Сортировка
От Seamus Abshere
Тема [GENERAL] Surprising locking behavior with CTE, FOR SHARE, and UPDATE
Дата
Msg-id 1504556580.3462773.1094999016.5B04A418@webmail.messagingengine.com
обсуждение исходный текст
Список pgsql-general
I have a query that splits up work (and manually does locking) according
to an id range:

WITH
new_data AS (
  SELECT [...] FROM data
  WHERE id BETWEEN 1 AND 2 -- here's my "id range"
),
old_data AS (
  SELECT [...] FROM data
  WHERE id IN (SELECT id FROM new_data)
  FOR UPDATE -- a manual lock to prevent race conditions
)
UPDATE data
SET [...]
FROM
new_data,
old_data
WHERE [...]

But I see that queries are blocking each other from non-overlapping id
ranges. For example, "BETWEEN 1 AND 2" is blocking "BETWEEN 5 AND 6".
This is Postgres 9.6.3.

Would it help to "redundantly" add the id ranges everywhere? (e.g, in
the where clauses of old_data AND the final update)?

Thanks!

--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere


В списке pgsql-general по дате отправления:

Предыдущее
От: "Stefan Wagner"
Дата:
Сообщение: [GENERAL] Undefined Reference
Следующее
От: Steve Atkins
Дата:
Сообщение: Re: [GENERAL] Create Action for psql when NOTIFY Recieved