oracle - Can a deadlock occur with the same access method?

Question

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

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

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.

---

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.)

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

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.

Create a table, index, and some data:

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.

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;

Run this block in session 1:

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

Run this block in session 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;
/

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.

Excluding the above scenario, can a deadlock happen?

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?

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?

Answer 1

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.

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

CREATE TABLE THE_TABLE (
    ID INT PRIMARY KEY
);

Transaction A:

INSERT INTO THE_TABLE VALUES(1);

Transaction B:

INSERT INTO THE_TABLE VALUES(1);

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.

Similar examples can be constructed for UPDATE and DELETE.

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?).

--- UPDATE ---

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:

• 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] 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.

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.

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;

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

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

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