Hello all,
16.02.2016 2:41, Vitaly Burovoy:
[...]
UPDATE deals with rows in order they are fetched from a heap. In a
common case it can be considered as unordered.
[...]
However SELECT can fetch rows in a specific order and locking by FOR
UPDATE clause is applied _after_ sorting. Then you can use CTE[1] with
Ok. So if I get it correctly, plain UPDATE statement by itself can not
be constructed in such way that reliably avoids deadlocks in case of
possibly overlapping concurrent updates. So in order to be safe, UPDATE
statements will need to always be 'protected' by respective SELECT FOR
UPDATE first. I'd suppose this fact deserves some more explicit mention
in the manual, as it is not so obvious...
Thanks a lot for your code example and comprehensive advice.
I think I'm able to fix my deadlocks now.
Regards,
Nikolai
SELECT ... ORDER BY ... FOR UPDATE to pass all necessary data to the
UPDATE statement. Since UPDATE still deals with unordered rows they
have already locked, and parallel queries are waiting in SELECT
statement rather than in UPDATE:
WITH lck AS (
SELECT
id, -- for WHERE clause in UPDATE
-- you can do calculations here or in-place (see "field2" below)
field1 + 1 as field1,
...
FROM your_table
WHERE ...
ORDER BY id -- for example
FOR UPDATE
)
UPDATE your_table t
SET
field1=lck.field1, -- lhs is always field of updatable table;
-- rhs must be pointed by a "table" if they are the same in both "tables"
field2=field2 + 2, -- or if you make changes in-place and it doesn't appear
--in a table mentioned in "FROM" clause, you can avoid table/alias name
...
FROM lck
WHERE
t.id=lck.id
Thank you,
Nikolai
[1]http://www.postgresql.org/docs/current/static/queries-with.html
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
