Database Reference
In-Depth Information
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (prod.prod1)
os id: 26698
process id: 7322, oracle@racdb1
session id: 2066
session serial #: 26535
Hanganalyze trace file is self-explanatory, and by following the chain of waits, you can identify the process
blocking all other processes.
Deadlocks
A deadlock occurs if two or more transactions are waiting for resources locked by each other. In a single-instance
database, sessions waiting for a lock will execute a small piece of code known as deadlock detection algorithm, to
check if there is a deadlock. The deadlock detection algorithm checks if a session is repeated in a waiter-holder tree.
If a session is seen again in the locking hierarchy, then it would indicate that there is a deadlock and current session
aborts the statement to resolve the deadlock.
In a single-instance database, waiters and holders are visible to every session connected to the database. So, a
foreground process can perform deadlock detection. In RAC, waiters and holders may be in different instances, and
all necessary data for deadlock detection is not available to the foreground processes. So, LMD background process
performs deadlock detection in a RAC database.
I will explain the deadlock detection with a simple test case. From two sessions connected to two different
instances, I will update two rows in a table.
REM From session 1 connected to instance 1
UPDATE t1 SET n1=n1 WHERE n1=100;
1 row updated.
REM From session 2 connected to instance 2
UPDATE t1 SET n1=n1 WHERE n1=200;
1 row updated.
Next, I will update the row where n1=200 from session 1 and update the row with n1=100 from session 2. After the
update, since these two sessions are waiting for each other, we induce conditions for deadlock.
REM From session 1
UPDATE t1 SET n1=n1 WHERE n1=200;
<..session waiting..>
REM From session 2
UPDATE t1 SET n1=n1 WHERE n1=100;
<.. session waiting..>
At this time, both sessions are waiting for each other, a classic behavior of a deadlock. Figure
11-2
shows the
deadlock issue.
