From: | Seamus Abshere <seamus(at)abshere(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Surprising locking behavior with CTE, FOR SHARE, and UPDATE |
Date: | 2017-09-04 20:23:00 |
Message-ID: | 1504556580.3462773.1094999016.5B04A418@webmail.messagingengine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Atkins | 2017-09-04 21:18:55 | Re: Create Action for psql when NOTIFY Recieved |
Previous Message | Stefan Wagner | 2017-09-04 09:32:50 | Undefined Reference |