sql - SELECT ... ORDER BY ... FOR UPDATE 语句中的行是否按顺序锁定？

Question

这个问题可以看作是我对 Can two concurrent but identical DELETE statements cause a deadlock? 的评论的后续。 .

我想知道在以下语句中行是否按 my_status 升序锁定:

SELECT 1 FROM my_table ORDER BY my_status FOR UPDATE;

https://www.postgresql.org/docs/9.5/static/sql-select.html 上有一个有趣的评论其中说:

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;

我不确定这是否回答了我的问题。所有这一切都是说首先应用 ORDER BY 并且您需要将 FOR UPDATE 放在子查询中以解决实际输出顺序可能不同的副作用，如果同时更改了订单列的值。换句话说，将 FOR UPDATE 放在子查询中可确保在排序之前发生锁定。

但这并没有真正告诉我们行是否真的按照 ORDER BY 子句确定的顺序锁定？

Answer 1

行按 ORDER BY 子句的顺序锁定，与扫描表时的顺序相同。

执行查询并对行进行排序，然后 PostgreSQL 按顺序锁定行。本质上，ORDER BY 发生在 FOR UPDATE 之前。

现在可能会发生锁定行 block ，因为并发事务持有锁。如果发生这种情况，并且我们处于 READ COMMITTED 隔离级别，PostgreSQL 等待 直到它可以获取锁并然后获取当前版本它锁定的行。

如果并发事务修改了定义顺序的列，则最终结果将不是ORDER BY定义的顺序。