Re: Actual row order in UPDATE and SELECT FOR UPDATE

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