Sunday, June 23, 2013

How To Identify Related DeadLock Sqls in LMD Trace file

After oracle db 11gR2, the dead lock sql information will be collected in LMD trace file for RAC
You will see such infor in alert logs

2013-05-17 13:00:45.065000 -05:00
Global Enqueue Services Deadlock detected. More info in file

/test/oradiag/diag/rdbms/testadc/test1/trace/test_lmd0_1825.trc

This trc file can be appended to record many days deadlock infor before db bounce

There are quite many infor in the trc file,quite a few sql in the trc. Sometimes you are confused by what are related sql for the deadlocks. If you are not checking carefully, you may end up put unrelated sql into your conclusions. here are important parts to look

Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.6de0] :
BLOCKED 0x44ef30548 5 wq 2 cvtops x1 TX 0x200005.0x110998(ext 0x6,0x0)[7B000-0001-000006E1] inst 1
BLOCKER 0x45144f800 5 wq 1 cvtops x28 TX 0x200005.0x110998(ext 0x6,0x0)[7D000-0003-00000009] inst 3
BLOCKED 0x44e1a78a0 5 wq 2 cvtops x1 TX 0x10017.0x2ebd13(ext 0x1,0x0)[7D000-0003-00000009] inst 3
BLOCKER 0x45164caf0 5 wq 1 cvtops x28 TX 0x10017.0x2ebd13(ext 0x1,0x0)[8F000-0001-00000010] inst 1
BLOCKED 0x44ef308e8 5 wq 2 cvtops x1 TX 0x200005.0x110998(ext 0x6,0x0)[8F000-0001-00000010] inst 1
BLOCKER 0x44ef30548 5 wq 2 cvtops x1 TX 0x200005.0x110998(ext 0x6,0x0)[7B000-0001-000006E1] inst 1 



The 3rd column indicates it TX mode 5 lock
The last column indicates which rac node
 
search trc file for 0x44ef30548 0x45144f800 0x45164caf0 0x44ef308e8 
record the sql you find, they are related deadlock sql
Sometimes you will see same sql for all of the deadlocks. That's because you have many midtiers which are doing same job at the same time and may cause conflicts if applications are not well designed.

1 comment:

ZooKeeper said...

Thank you, Henry! I got the exact answer to my question!