Database Reference
In-Depth Information
lock_state : GRANTED
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
...
Session-level details are also printed. Details such as program, username, application name, etc., will be useful
in understanding the deadlock issues. Finally, SQL statement involved in the deadlock is also printed to debug the
deadlock further.
user session for deadlock lock 0x1657e67440
sid: 11303 ser: 58495 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 120 O/S info: user: oracle, term: UNKNOWN, ospid: 13787
image: oracle@rac1 (TNS V1-V3)
client details:
O/S info: user: oraperf, term: pts/3, ospid: 13786
machine: wsqfinc1a program: sqlplus@wsqfinc1a (TNS V1-V3)
application name: sqlplus@wsqfinc1a (TNS V1-V3), hash value=321046474
current SQL:
update t1 set n1=n1 where n1=200
In a nutshell, deadlock details are printed in LMD trace files. To better understand the root cause of deadlocks,
assemble the sections from LMD trace files from all nodes at the time of deadlock. By reviewing details, you can
identify the root cause of a deadlock.
Initially, LMD trace file details are overwhelming, since a huge amount of information is dumped to the trace file.
You can use this chapter to understand the trace file contents and identify SQL statements and the processes suffering
from deadlock. This approach should lead to a faster resolution of deadlocks.
Summary
Resources and locks are maintained in GRD by the GES layer of RAC code. This globalization of resources allows
RAC to coordinate changes to the resources. By reviewing GES layer views, you can understand the root cause of the
locking contention and resolve performance issues. In addition, the LMD process prints essential details in a trace file,
and the analysis of LMD trace files should lead to quicker root cause analysis.
