mysql - SELECT ... FOR UPDATE 是否应该始终包含 ORDER BY？

Question

假设我们执行...

SELECT * FROM MY_TABLE FOR UPDATE

...MY_TABLE 中不止一行。

理论上，如果两个并发事务执行这条语句，但恰好以不同的顺序遍历(并因此锁定)行，则可能会发生死锁。例如:

• 事务 1:锁定 A 行。
• 事务 2:锁定行 B。
• 事务 1:尝试锁定行 B 和 block 。
• 事务 2:尝试锁定行 A 和死锁。

解决这个问题的方法是使用 ORDER BY 来确保行总是以相同的顺序锁定。

所以，我的问题是:这种理论上的僵局会在实践中发生吗？我知道有办法artificially induce it ，但它会在正常操作中发生吗？我们应该始终使用 ORDER BY，还是忽略它实际上是安全的？

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

--- 编辑 ---

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

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

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

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

COMMIT;

...从一个客户端 session (我使用 SQL Developer)，运行以下 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;
/

从一个不同的客户端 session (我只是启动了另一个 SQL Developer 实例)，运行相同的 block ，但在 ORDER BY< 中使用 DESC/。几秒钟后，您将获得:

ORA-00060: deadlock detected while waiting for resource

顺便说一句，您可能会通过完全删除 ORDER BY(因此两个 block 是相同的)并添加 ...

来获得相同的结果

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;

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

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10000;

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

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

Answer 1

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

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

在实践中，这种情况非常特殊，无论如何都不应该经常发生:如果您担心死锁，我仍然认为有更简单的方法来防止死锁。

防止死锁的最简单方法是使用 FOR UPDATE NOWAIT 或 FOR UPDATE WAIT X(尽管 WAIT X 仍然可以触发 X 优先级的死锁到死锁检测机制，我相信目前从 11g 开始是 3 秒——感谢 @APC 的更正)。

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

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