使用相同的访问方法会发生死锁吗? [英] Can a deadlock occur with the same access method?

问题描述

如果两个并发的DML语句修改相同的数据并使用相同的访问方法，是否有可能发生死锁?

Is it possible for a deadlock to occur if two concurrent DML statements modify the same data and use the same access method?

根据我的测试以及对Oracle工作原理的猜测，答案是否定的.

Based on my tests, and my guesses about how Oracle works, the answer is no.

但我想100％确定.我正在寻找一个官方消息来源，说死锁不能以这种方式发生，或者一个测试用例证明死锁可以以这种方式发生.

But I want to be 100% certain. I am looking for an official source that says deadlocks cannot happen this way, or a test case demonstrating that deadlocks can occur this way.

问这个问题的另一种方法是:如果使用相同的访问方法，Oracle会始终以相同的顺序返回结果吗? (并且运行之间没有数据更改.)

Another way of asking this question is: will Oracle always return the results in the same order, if the same access method is used? (And no data changes between runs.)

例如，如果查询使用全表扫描并以4/3/2/1的顺序返回行，那么它会总是以该顺序返回行吗?而且，如果索引范围扫描按1/2/3/4的顺序返回行，它会总是按该顺序返回行吗?实际顺序是什么都没有关系，只是顺序是确定性的.

For example, if a query uses a full table scan and returns rows in the order 4/3/2/1, will it always return the rows in that order? And if an index range scan returns rows in the order 1/2/3/4, will it always return rows in that order? It doesn't matter what the actual order is, just that the order is deterministic.

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

(Parallelism may add some complexity to this question. The overall order of the statement will be different depending on many factors. But for locking, I believe that only the order within each parallel session is important. And again, my testing indicates that the order is deterministic and will not cause a deadlock.)

更新

我最初的问题有点笼统.我最感兴趣的是，是否可以在两个不同的会话中同时运行update table_without_index set a = -1之类的东西，并导致死机? (我要问的是单个更新，而不是一系列更新.)

My original question was a bit generic. What I'm mostly interested in is, is it possible to run something like update table_without_index set a = -1 in two different sessions at the same time, and get a deadock? (I'm asking about a single update, not a series of updates.)

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

First, let me demonstrate that the exact same statement can cause a deadlock.

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

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

For simplicity I'm only updating the same column. In the real world there would be different columns, but I don't think that changes anything.

请注意，我使用pctfree 0创建了表，更新后的值将占用更多的空间，因此将有很多行迁移. (这是对@Tony Andrew的回答，尽管我担心我的测试可能过于简单.而且，我认为我们不必担心在更新之间插入行；只有一个更新会看到新行，因此除非新行也转移了其他东西，否则不会造成僵局.

Note that I create the table with pctfree 0, and the updated values will take up significantly more space, so there will be lots of row migration. (This is in response to @Tony Andrew's answer, although I fear my test may be too simplistic. Also, I don't think we need to worry about inserting rows between the updates; only one of the updates would see the new row so it wouldn't cause a deadlock. Unless the new row shifted a bunch of other stuff as well.)

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;

在会话1中运行此块:

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

在会话2中运行此块:

--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;
/

几秒钟后，其中一个会话抛出ORA-00060: deadlock detected while waiting for resource.这是因为同一查询在每个会话中以不同顺序锁定行.

After a few seconds, one of those sessions throws ORA-00060: deadlock detected while waiting for resource. This is because the same query is locking the rows in a different order in each session.

除了上述情况之外，还会发生死锁吗?

以上内容表明，执行计划的更改可能导致死锁. 但是，即使执行计划保持不变，也会发生死锁吗?

The above demonstrates that a change in execution plan can lead to a deadlock. But can a deadlock occur even if the execution plan stays the same?

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

As far as I can tell, if you remove the optimizer_index_cost_adj, or anything else that would change the plan, the code will never cause a deadlock. (I've been running the code for a while now, with no errors.)

我问这个问题是因为我正在使用的系统偶尔会发生这种情况.它尚未失败，但是我们想知道它是否真的安全，还是我们需要在更新周围添加其他锁定?

I'm asking this question because a system I'm working on has this happen occasionally. It hasn't failed yet, but we want to know if it's really safe, or do we need to add additional locking around the updates?

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

Can someone build a test case where a single update statement, running concurrently and using the same plan, causes a deadlock?

推荐答案

仅当您在查询中包含ORDER BY时，顺序"才是确定性的 .从服务器的角度来看，确定性 是一个实现细节，不依赖于此.

The "order" is deterministic from your perspective only if you include ORDER BY in your query. Whether it is deterministic from the server's perspective is an implementation detail, not to be relied upon.

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

As for locking, two identical DML statements can block (but not deadlock) each other. For example:

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因违反PRIMARY KEY而失败.如果A回滚，则B成功.

At this point, Transaction B is stalled until Transaction A either commits or rolls back. If A commits, the B fails because of PRIMARY KEY violation. If A rolls back, the B succeeds.

可以为UPDATE和DELETE构造类似的示例.

Similar examples can be constructed for UPDATE and DELETE.

重要的一点是，阻塞将不依赖于执行计划-不管Oracle选择如何优化查询，您都将始终具有相同的阻塞行为.您可能需要阅读 DML操作中的自动锁有关更多信息.

The important point is that blocking will not depend on execution plan - no matter how Oracle chooses to optimize your query, you'll always have the same blocking behavior. You may want to read about Automatic Locks in DML Operations for more info.

对于死锁锁，可以用多个语句来实现.例如:

As for dead-locks, they are possible to achieve with multiple statements. For example:

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

或者，可能还有一些语句以不同的顺序和一些非常不幸的时机修改了多行(有人可以确认吗?).

Or, possibly with statements that modify more than one row in different order and some very unlucky timing (could anyone confirm this?).

为回答您的问题，让我作一个大致的观察:如果执行的并发线程以一致的顺序锁定对象，则死锁是不可能的.这对于任何类型的锁定都是正确的，因为它在普通的多线程程序中是互斥的(例如，请参见

In response to update of your question, let me make a general observation: If concurrent threads of execution lock objects in the consistent order, deadlocks are impossible. This is true for any kind of locking, be it mutexes in your average multi-threaded program (e.g. see Herb Sutter's thoughts on Lock Hierarchies) or be it databases. Once you change the order in such a way that any two locks are "flipped", the potential for deadlocks is introduced.

无需扫描索引，您将以一种顺序更新(并锁定)行，而以另一种顺序更新索引.因此，这可能是您遇到的情况:

Without scanning the index, you are updating (and locking) rows in one order, and with the index in another. So, this is probably what happens in your case:

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

• If you disable index scan for both concurrent transactions, they both lock rows in the same order [X], so no deadlock is possible.
• If you enable index scan for just one transaction, they no longer lock rows in the same order, hence the potential for a deadlock.
• If you enable index scan for both transactions, then again both of them are locking rows in the same order, and a deadlock is impossible (go ahead and try alter session set optimizer_index_cost_adj = 1; in both sessions and you'll see).

[X]尽管我不依赖于具有保证顺序的全表扫描-这可能只是当前的Oracle在这些特定情况下的工作方式，而某些将来的Oracle或不同的情况可能会产生不同的行为. /em>

[X] Though I wouldn't rely on full table scans having a guaranteed order - it might just be how current Oracle in these specific circumstances works, and some future Oracle or different circumstances might produce different behavior.

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

So, the presence of index is incidental - the real issue is ordering. It just so happens that ordering in UPDATE can be influenced by an index, but if we could influence ordering in another way, we would get similar results.

由于UPDATE没有ORDER BY，因此您不能真正保证仅通过UPDATE锁定的顺序.但是，如果将锁定与更新分开，则可以保证锁定顺序:

Since UPDATE does not have ORDER BY, you cannot really guarantee the order of locking by UPDATE alone. However, if you separate locking from updating, then you can guarantee the lock order:

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

虽然您的原始代码在Oracle 10环境中造成了死锁，但以下代码却没有:

While your original code caused deadlocks in my Oracle 10 environment, the following code doesn't:

会议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;
/

会议2:

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;
/

这篇关于使用相同的访问方法会发生死锁吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！