Surprising locking behavior with CTE, FOR SHARE, and UPDATE

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

Browse pgsql-general by date

  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