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
