行是否在SELECT ... ORDER BY ... FOR UPDATE语句中按顺序锁定？ [英] Are the rows locked in order in a SELECT ... ORDER BY ... FOR UPDATE statement?

问题描述

这个问题可以看作是我对两个并发但相同的DELETE语句会导致死锁吗？。

This question can be considered as a follow-up on my comment on Can two concurrent but identical DELETE statements cause a deadlock?.

我想知道行是否被锁定为以<$递增c $ c> my_status 在以下语句中的顺序：

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;

在 https://www.postgresql.org/docs/9.5/static/sql-select.html 其中表示：

在上运行 SELECT 命令是可能的事务隔离级别，并使用 ORDER BY 和一个锁定子句以无序方式返回行。这是因为 ORDER BY 首先被应用。该命令对结果进行排序，但随后可能会阻止尝试获得对一个或多个行的锁定。一旦 SELECT 解除阻止，某些排序列的值可能已被修改，导致那些行看起来是乱序的（尽管按照原始顺序排列）列值）。可以通过在子查询中放置 FOR UPDATE / SHARE 子句来解决此问题，例如

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 放在子查询中可确保在订购之前进行锁定。

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.

但这并不是真的告诉我们行实际上是否按照 ORDER BY 子句确定的顺序锁定？

But this does not really tell us if the rows are actually locked in the order determined by the ORDER BY clause?

推荐答案

行被按 ORDER BY 子句的顺序锁定，就像扫描表时一样。

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

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

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

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.

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

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

这篇关于行是否在SELECT ... ORDER BY ... FOR UPDATE语句中按顺序锁定？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！