Are the rows locked in order in a SELECT ... ORDER BY ... FOR UPDATE statement?

Question

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 the READ COMMITTED transaction isolation level and using ORDER BY and a locking clause to return rows out of order. This is because ORDER 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 the SELECT 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 the FOR UPDATE/SHARE clause in a sub-query, for example

SELECT * 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?

Agogue

Answer 1

The rows are locked in the order of the ORDER BY clause as it was when the table was scanned.

The query is executed and the rows ordered, then PostgreSQL locks the rows in order. Essentially, ORDER BY happens before FOR UPDATE.

Now it can happen that locking a row blocks because of locks held by concurrent transactions. If that happens, and we are at the READ COMMITTED isolation level, PostgreSQL waits until it can get the lock and then fetches the current version of the row, which it locks.

If the concurrent transaction modified the columns which define the ordering, the final result will not be in the order defined by ORDER BY.

Filicide

Reading this other question (not specific to Postgres), it seems like ORDER BY does not guarantee the access order. Maybe this doesn't apply to Postgres, and ORDER BY is indeed the right way to prevent a deadlock from SELECT FOR UPDATE? – Stamp

@Stamp Look at EXPLAIN output to see the order of execution. That will show you if sorting happens before the rows are locked or not. – Filicide