Deadlock Detection

By some strange coincidence, the “London Bus” effect perhaps, there have been three posts on the OTN database forum in the last couple of days relating to deadlocks; and they have prompted me to indulge in a little rant about the myth of Oracle and deadlock detection; it’s the one that goes:

“Oracle detects and resolves deadlocks automatically.”

Oracle may detect deadlocks automatically, but it doesn’t resolve them, it simply reports them (by raising error ORA-00060 and rolling back one statement) then leaves the deadlocked sessions stuck until the session that received the report resolves the problem or an external agent resolves the problem.

Consider the following example (which, I have to admit, I wrote without access to a live instance):

Session 1
delete from t1 where id between 1 and 1000000;
1000000 rows deleted

                                                   Session 2
                                                   update jobs set status = 'STARTING' where id = 99;
                                                   1 row updated

Session 1
update jobs set status = 'FINISHED' where id = 99;
-- session 1 is now waiting on session 2

                                                   Session 2
                                                   delete from t1 where id between 1 and 1000000;
                                                   -- session 2 is now waiting on session 1

Session 1 (some time within the next 3 seconds)
ORA-00060: deadlock detected
(with rollback of the most recent statement - i.e. the update)

On receiving the ORA-00060 error most of the applications that I’ve seen would then crash session 1, resulting in an implicit rollback of the million row delete (the worst case I’ve seen in s production system: session 2 ran for 10 seconds and resulted in session 1 crashing and rolling back for 3.5 hours); some applications would report “Unexpected Oracle error” to the front-end and retry the most recent action (the update that rolled back), at which point Session 2 would receive an ORA-00060 error and the two sessions would see-saw back and forth every 3 seconds until someone noticed what was going on. [Update: In March 2014 I published this example of a flaw in some Oracle internal code that behaves in exactly this way.]

In what way has the deadlock been “resolved” ?

In this specific case I think I’d probably want a supervisor (person or program) to log and kill session 2 and allow session 1 to retry its second update – and then I’d want to find out why this sequence of events had happened at all.

Deadlocks are NOT resolved automatically by Oracle, they are merely reported so that the client code can decide how to resolve them.

Update (March 2024)

A new feature in Oracle 23c gives us an option for “proper” deadlock resolution. It is now possible to set three levels of priority for transactions and limits on the amount of time that transactions running at higher levels are prepared to wait for transactions running at lower levels before sniping them. For more details see this blog note by FatDBA.

Further Update (March 2024)

It’s too easy to fall into the trap of “narrow thinking”. If I say “deadlock” most people are likely to think of TM or TX enqueues colliding and transactions being deadlocked. There are, of course, all sorts of other Oracle activities involving enqueues which means all sorts of deadlocks other than transaction deadlocks (for example this one between a row cache lock and a library cache lock). So my statement about “Oracle detects but doesn’t resolve deadlocks” demonstrates the fallacy of generalising from the particular. This was brought to my attention following a recent (March 2024) tweet where I referenced this blog note – and got a reply pointing out that Hang Manager (renamed Blocker Resolver in 23c) was able to  resolve deadlocks in RAC systems and had been able to in many versions of Oracle.