Database Reference
In-Depth Information
Row with n1=100
Row with n1=200
Session #1
@inst1
Session #2
@inst2
Figure 11-2.
Deadlock between two sessions
The deadlock detection algorithm executed by LMD process will detect deadlocks in nearly 10 seconds.
After 10 seconds of lock wait, a waiting session is chosen and the statement is rolled back.
update t1 set n1=n1 where n1=200
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:09.59
■
deadlock detection is performed in ~10 seconds from oracle database version 11g onward. In version 10g,
deadlock detection can take 60 seconds. parameter _lm_dd_interval controls this behavior. of course, there is no reason
to adjust this parameter though. In single-instance database, deadlock detection routine is executed every 3 seconds, and
therefore deadlock will be detected in 3 seconds.
Note
In a single-instance database, a deadlock trace file is written by the foreground process, but in a RAC database
as deadlocks are detected by LMD processes, you must review LMD trace file to understand the root cause of a
deadlock.
LMD Trace File Analysis
Information written by the LMD processes to trace files follows a specific format. LMD process detecting deadlock
writes resource and lock details in its own trace file. In addition, LMD processes running in other nodes are requested
to dump details about holders and waiters of resources and locks involved in the deadlocks from their respective
nodes. This coordinated dump by LMD processes is written to their own trace files, and so it is important to review
LMD trace files from all instances at the same time to gain full insight into the deadlock issue.
LMD trace file dumps for deadlocks follow a general structure outlined in Figure
11-3
. The first section prints
resource details, queue details, and a summary of lock structures in the queue. Then, details about each of these lock
structures are printed. If the lock structure is in local instance, then process such as program, user name, etc., are
printed along with the lock details.
