This question can be considered as a follow-up on my comment on Can two concurrent but identical DELETE statements cause a deadlock?.
I am wondering if the rows are locked in ascending my_status
order in the following statement:
SELECT 1 FROM my_table ORDER BY my_status FOR UPDATE;
There is an interesting remark on https://www.postgresql.org/docs/9.5/static/sql-select.html which says:
It is possible for a
SELECT
command running at theREAD COMMITTED
transaction isolation level and usingORDER BY
and a locking clause to return rows out of order. This is becauseORDER BY
is applied first. The command sorts the result, but might then block trying to obtain a lock on one or more of the rows. Once theSELECT
unblocks, some of the ordering column values might have been modified, leading to those rows appearing to be out of order (though they are in order in terms of the original column values). This can be worked around at need by placing theFOR UPDATE
/SHARE
clause in a sub-query, for exampleSELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
I am not sure if that answers my question. All this says is that ORDER BY
is applied first and that you need to put the FOR UPDATE
in a subquery to work around the side effect that the actual output order might differ if values of the order column(s) have been changed in the meantime. In other words, putting FOR UPDATE
in a subquery ensures that locking happens before ordering.
But this does not really tell us if the rows are actually locked in the order determined by the ORDER BY
clause?