Re: deadlock detection

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Fri, 2 Apr 2004 10:53:53 +0300
Message-ID: <097b01c41887$a4cb3950$6df923d5@porgand>

Hi!

Simply stated, every lock (enqueue) in Oracle has an owners list, waiters list and converters list, storing information of sessions already holding the enqueue, waiting to get it or waiting to convert it to another mode. When a session requests a lock in wait mode, then if it's not acquirable immediately, the session will wait for the lock for 3 seconds (timeout is probably defined somewhere in Oracle code), after which a deadlock detection is done, if couldn't still get the lock by then. If there is no deadlock, the session continues to wait for another 3 seconds (you'll start seeing enqueue waits in sql_trace for example).

In my understanding, all the waiters and converters queues are scanned to find all sessions waiting for the blocking session, some kind of clever dependency matrix is built. Then the queues are scanned to find for whom does the blocking session itself wait and this information is checked against the waiters matrix for dependencies again. This can continue recursively until the waiters/locks are resolved or a infinite recursion (deadlock) is found. In this case the session initiated deadlock detection will roll back its transaction and return ORA-60 to client or exception handler.

Metalink note 102925.1 has some information on it..

Tanel.

Original Message ----- From: "Ryan" <ryan.gaffuri_at_cox.net> To: <oracle-l_at_freelists.org> Sent: Friday, April 02, 2004 6:45 AM Subject: deadlock detection

> How does oracle detect deadlocks? Here is my best guess. Oracle's lock
manager tracks locks at the object level, but not at the row level.
> transaction A locks row 1 in table A
> transaction B locks row 1 in table B
>
> Transaction A attempts to lock row 1 in table B, but is forced to wait.
> Transaction B attempts to lock row 1 in table A, but is forced to wait.
>
>
> The oracle lock manager notes that this at the object level. So it is
possible that this is a deadlock. The lock manager then checks the transaction table for the row that is locked in each table and from that can determine whether this is a deadlock condition.
>
> Am I correct?
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Apr 02 2004 - 01:50:21 CST