SELECT ... FOR UPDATE是否应始终包含ORDER BY? [英] Should SELECT ... FOR UPDATE always contain ORDER BY?

问题描述

假设我们执行...

SELECT * FROM MY_TABLE FOR UPDATE

...并且MY_TABLE中有多于一行.

...and there is more than one row in MY_TABLE.

理论上，如果两个并发事务执行该语句，但是碰巧以不同的顺序遍历(并因此锁定)行，则可能会发生死锁.例如:

Theoretically, if two concurrent transactions execute this statement, but it happens to traverse (and therefore lock) the rows in different order, a deadlock may occur. For example:

• 事务1:锁定A行.
• 交易2:锁定B行.
• 事务1:尝试锁定B行和块.
• 事务2:尝试锁定A行和死锁.

解决此问题的方法是使用ORDER BY以确保行始终以相同的顺序锁定.

The way to resolve this is to use ORDER BY to ensure rows are always locked in the same order.

所以，我的问题是:这种理论上的僵局是否会在实践中发生?我知道有几种方法可以人为地诱发，但是在正常操作中会发生吗?我们应该只使用ORDER BY，还是忽略它实际上是安全的?

So, my question is: will this theoretical deadlock ever occur in practice? I know there are ways to artificially induce it, but could it ever occur in the normal operation? Should we just always use ORDER BY, or it's actually safe to omit it?

我主要对Oracle和MySQL/InnoDB的行为感兴趣，但是对其他DBMS的评论也将有所帮助.

I'm primarily interested in behavior of Oracle and MySQL/InnoDB, but comments on other DBMSes would be helpful as well.

以下是在锁定顺序不同时如何在Oracle下重现死锁的方法:

Here is how to reproduce a deadlock under Oracle when locking order is not the same:

创建测试表并填充一些测试数据...

Create the test table and fill it with some test data...

CREATE TABLE DEADLOCK_TEST (
    ID INT PRIMARY KEY,
    A INT 
);

INSERT INTO DEADLOCK_TEST SELECT LEVEL, 1 FROM DUAL CONNECT BY LEVEL <= 10000;

COMMIT;

...从一个客户端会话(我使用SQL Developer)中，运行以下代码块:

...from one client session (I used SQL Developer), run the following block:

DECLARE
    CURSOR CUR IS 
        SELECT * FROM DEADLOCK_TEST
        WHERE ID BETWEEN 1000 AND 2000 
        ORDER BY ID 
        FOR UPDATE;
BEGIN
    WHILE TRUE LOOP
        FOR LOCKED_ROW IN CUR LOOP
            UPDATE DEADLOCK_TEST 
            SET A = -99999999999999999999 
            WHERE CURRENT OF CUR;
        END LOOP;
        ROLLBACK;
    END LOOP;
END;
/

从一个不同的客户端会话(我只是启动了另一个SQL Developer实例)，运行相同的块，但在ORDER BY中使用DESC.几秒钟后，您将得到:

From a different client session (I simply started one more instance of SQL Developer), run that same block, but with DESC in the ORDER BY. After few seconds, you'll get the:

ORA-00060: deadlock detected while waiting for resource

顺便说一句，通过完全删除ORDER BY(因此两个块都相同)并添加...来实现相同的结果.

BTW, you'll likely achieve the same result by completely removing the ORDER BY (so both blocks are identical), and adding the...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;

...在一个街区前面，但是...

...in front of one block but...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10000;

...在另一个的前面(因此Oracle选择不同的执行计划，并可能以不同的顺序获取行).

...in front of the other (so Oracle chooses different execution plans and likely fetches the rows in different order).

这说明锁定确实是在从游标中获取行时完成的(而不是在打开游标时立即针对整个结果集).

This illustrates that locking is indeed done as rows are fetched from the cursor (and not for the whole result-set at once when the cursor is opened).

推荐答案

您的问题示例表明，锁定顺序取决于访问方法.此访问路径不是由查询的ORDER BY子句直接确定的，有很多因素会影响此访问路径.因此，不能仅通过添加ORDER BY来防止死锁，因为您仍然可以有两个不同的访问路径.实际上，通过按顺序运行测试用例并更改会话参数，我能够使两个会话通过相同的查询运行到ORA-60中.

Your example in your question shows that the order of locking depends upon the access method. This access path is not directly decided by the ORDER BY clause of the query, there are many factors that can influence this access path. Therefore, you can't prevent a deadlock just by adding an ORDER BY because you could still have two distinct access paths. In fact by running your test case with the order by and changing the session parameters I was able to cause two session to run into an ORA-60 with the same query.

如果所涉及的会话没有其他未决锁，则在所有会话中以相同顺序锁定行 可以防止死锁，但是如何可靠地强制执行此顺序?请注意，无论如何这只会防止这种非常特殊的死锁情况.您仍然可能在每个会话或不同的计划中遇到多个查询而陷入僵局.

If the sessions involved have no other lock pending, locking the rows in the same order in all sessions will prevent deadlocks but how can you reliably force this order? Note that this would only prevent this very special case of deadlock anyway. You could still get deadlocks with multiple queries in each session or different plans.

实际上，这种情况确实很特殊，无论如何都不应该经常发生:如果您担心死锁，我仍然认为有更简单的方法可以防止死锁.

In practice this case is really special and shouldn't happen often anyway: if you're worried about deadlocks, I still think there are easier methods to prevent them.

防止死锁的最简单方法是使用FOR UPDATE NOWAIT或FOR UPDATE WAIT X(尽管WAIT X仍然可以触发死锁，其X值要优于死锁检测机制，目前我认为11g时为3秒- -感谢 @APC .)

The easiest way to prevent a deadlock is to use either FOR UPDATE NOWAIT or FOR UPDATE WAIT X (although WAIT X can still trigger a deadlock with values of X superior to the deadlock detection mechanism, currently 3 seconds as of 11g I believe -- thanks @APC for the correction).

换句话说，两个事务都应该询问:给我这些行并锁定它们，但是如果另一个用户已经具有锁定，则返回错误而不是无限期地等待.无限期的等待会导致死锁.

In other words, both transactions should ask: give me those rows and lock them but if another user already has a lock return an error instead of waiting indefinitely. It is the indefinite waiting that causes deadlocks.

在实践中，我想说大多数具有真实用户的应用程序宁愿立即收到错误，而不是让事务无限期地等待另一个事务完成.我会考虑将FOR UPDATE不使用NOWAIT仅用于非关键批处理作业.

In practice I would say that most applications with real person users would rather receive an error immediately than have a transaction wait indefinitely for another transaction to finish. I would consider FOR UPDATE without NOWAIT only for non-critical batch jobs.

这篇关于SELECT ... FOR UPDATE是否应始终包含ORDER BY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！