oracle - 相同的访问方式会出现死锁吗？

Question

如果两个并发的DML语句修改相同的数据并使用相同的访问方式，是否会出现死锁？

根据我的测试以及我对 Oracle 如何工作的猜测，答案是否定的。

但我想百分百确定。我正在寻找一个官方消息来源，表明死锁不会以这种方式发生，或者一个测试用例证明死锁可以以这种方式发生。

<小时/>

问这个问题的另一种方式是:如果使用相同的访问方法，Oracle 是否总是以相同的顺序返回结果？ (并且运行之间没有数据变化。)

例如，如果查询使用全表扫描并按 4/3/2/1 的顺序返回行，那么它是否总是按该顺序返回行？如果索引范围扫描按 1/2/3/4 的顺序返回行，它是否总是按该顺序返回行？实际顺序是什么并不重要，重要的是顺序是确定性的。

(并行性可能会增加这个问题的复杂性。语句的整体顺序会根据许多因素而有所不同。但是对于锁定，我相信只有每个并行 session 中的顺序才是重要的。并且，我的测试再次表明该顺序是确定性的，不会导致死锁。)

<小时/>

更新

我原来的问题有点笼统。我最感兴趣的是，是否有可能在两个不同的 session 中同时运行诸如 update table_without_index set a = -1 之类的东西，并出现死锁？ (我问的是单个更新，而不是一系列更新。)

首先，让我证明完全相同的语句可能会导致死锁。

创建表、索引和一些数据:

为了简单起见，我只更新同一列。在现实世界中会有不同的专栏，但我认为这不会改变任何事情。

请注意，我使用 pctfree 0 创建表，更新后的值将占用更多空间，因此会有大量行迁移。 (这是对 @Tony Andrew 的回答的回应，尽管我担心我的测试可能过于简单。另外，我认为我们不需要担心在更新之间插入行；只有其中一个更新会看到新行，因此它不会导致死锁。除非新行也移动了一堆其他内容。)

drop table deadlock_test purge;

create table deadlock_test(a number) pctfree 0;
create index deadlock_test_index on deadlock_test(a);

insert into deadlock_test select 2 from dual connect by level <= 10000;
insert into deadlock_test select 1 from dual connect by level <= 10000;

commit;

在 session 1 中运行此 block :

begin
    while true loop
        update deadlock_test set a = -99999999999999999999 where a > 0;
        rollback;
    end loop;
end;
/

在 session 2 中运行此 block :

--First, influence the optimizer so it will choose an index range scan.
--This is not gaurenteed to work for every environment.  You may need to 
--change other settings for Oracle to choose the index over the table scan.
alter session set optimizer_index_cost_adj = 1;

begin
    while true loop
        update deadlock_test set a = -99999999999999999999 where a > 0;
        rollback;
    end loop;
end;
/

几秒钟后，其中一个 session 抛出ORA-00060:WAITING资源时检测到死锁。这是因为同一查询在每个 session 中以不同的顺序锁定行。

排除上述情况，是否会发生死锁？

上面演示了执行计划的更改可能会导致死锁。但是即使执行计划保持不变也会发生死锁吗？

据我所知，如果删除optimizer_index_cost_adj，或任何其他会改变计划的内容，代码永远不会导致死锁。 (我已经运行代码一段时间了，没有错误。)

我问这个问题是因为我正在开发的系统偶尔会发生这种情况。它还没有失败，但我们想知道它是否真的安全，或者我们是否需要在更新周围添加额外的锁定？

有人可以构建一个测试用例，其中并发运行并使用相同计划的单个更新语句会导致死锁吗？

Answer 1

仅当您在查询中包含 ORDER BY 时，从您的角度来看“顺序”才是确定性的。 从服务器的角度是否是确定性的是一个实现细节，不值得依赖。

对于锁定，两个相同的 DML 语句可以互相阻塞(但不会死锁)。例如:

CREATE TABLE THE_TABLE (
    ID INT PRIMARY KEY
);

交易A:

INSERT INTO THE_TABLE VALUES(1);

交易B:

INSERT INTO THE_TABLE VALUES(1);

此时，事务 B 停滞，直到事务 A 提交或回滚。如果 A 提交，B 将因主键违规而失败。如果A回滚，B就成功。

可以为 UPDATE 和 DELETE 构建类似的示例。

重要的一点是阻塞不依赖于执行计划 - 无论 Oracle 选择如何优化您的查询，您将始终具有相同的阻塞行为。您可能想阅读 Automatic Locks in DML Operations了解更多信息。

<小时/>

对于死锁，可以通过多个语句来实现。例如:

A: INSERT INTO THE_TABLE VALUES(1);
B: INSERT INTO THE_TABLE VALUES(2);
A: INSERT INTO THE_TABLE VALUES(2);
B: INSERT INTO THE_TABLE VALUES(1); -- SQL Error: ORA-00060: deadlock detected while waiting for resource

或者，可能使用以不同顺序修改多行的语句以及一些非常不幸的时机(有人能证实这一点吗？)。

--- 更新 ---

针对您问题的更新，让我做一个一般性的观察:如果并发执行线程以一致的顺序锁定对象，则不可能出现死锁。对于任何类型的锁定都是如此，无论是普通多线程程序中的互斥锁(例如，参见 Herb Sutter's thoughts on Lock Hierarchies )还是数据库。一旦您以任意两个锁“翻转”的方式更改顺序，就会引入死锁的可能性。

如果不扫描索引，您将按一种顺序更新(并锁定)行，并按另一种顺序使用索引。所以，这可能就是您的情况发生的情况:

• 如果您对两个并发事务禁用索引扫描，它们都会以相同的顺序 [X] 锁定行，因此不可能出现死锁。
• 如果您仅对一个事务启用索引扫描，它们将不再以相同的顺序锁定行，因此可能会出现死锁。
• 如果您为两个事务启用索引扫描，那么它们都会以相同的顺序锁定行，并且不可能出现死锁(继续尝试 alter session set optimizer_index_cost_adj = 1; 在两个 session 中，您都会看到)。

[X] 虽然我不会依赖具有保证顺序的全表扫描 - 这可能只是当前 Oracle 在这些特定情况下的工作方式，并且某些 future 的 Oracle 或不同的情况可能会产生不同的行为。

因此，索引的存在是偶然的 - 真正的问题是排序。碰巧 UPDATE 中的排序可能会受到索引的影响，但如果我们可以通过其他方式影响排序，我们会得到类似的结果。

由于UPDATE没有ORDER BY，所以仅靠UPDATE并不能真正保证锁定的顺序。但是，如果您将锁定与更新分开，那么您可以保证锁定顺序:

SELECT ... ORDER BY ... FOR UPDATE;

虽然您的原始代码在我的 Oracle 10 环境中导致了死锁，但以下代码不会:

第 1 节:

declare
    cursor cur is select * from deadlock_test where a > 0 order by a 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;
/

第二节:

alter session set optimizer_index_cost_adj = 1;

declare
    cursor cur is select * from deadlock_test where a > 0 order by a 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;
/