Re: deadlock detection
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 2 Apr 2004 10:53:53 +0300
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@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
