Search Postgresql Archives

Surprising locking behavior with CTE, FOR SHARE, and UPDATE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux