I have a deadlock being reported in my 3-node Oracle RAC database (version 11.2.0.4) as can be seen in the Alert Log. Being that this is an Oracle RAC database, resources are managed globally and the Lock Manager Daemon (LMD) gets involved. The message in the alert log pointed me to a LMD trace file which contained this Global Wait-For-Graph (GWFG).
*** 2015-02-27 04:16:33.183 Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)]. Global blockers dump end:----------------------------------- Global Wait-For-Graph(WFG) at ddTS[0.394d] : BLOCKED 0x551b2c698 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[6B000-0004-0000022D] inst 4 BLOCKER 0x5513ed318 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[B6000-0006-00000099] inst 6 BLOCKED 0x5513ed318 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[B6000-0006-00000099] inst 6 BLOCKER 0x5513ef5b8 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[66000-0005-00000FDB] inst 5 BLOCKED 0x5513ef5b8 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[66000-0005-00000FDB] inst 5 BLOCKER 0x551b2c698 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[6B000-0004-0000022D] inst 4 * Cancel deadlock victim lockp 0x551b2c698
Note: The actual SQL and a few other details may have been changed to protect the innocent.
So there I have 3 sessions involved in the deadlock. One each on instance id 4, 5, and 6.
I was looking at the trace file generated on instance id 4. Above the GWFG was this information:
user session for deadlock lock 0x551b2c698
sid: 1727 ser: 539 audsid: 996549224 user: 13944/MP_SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 107 O/S info: user: oracle, term: UNKNOWN, ospid: 11229
image: oracle@myhost04.naucom.com
client details:
O/S info: user: web-svc, term: web-server1, ospid: 4276:864
machine: DOMAIN\web-server1 program: iis.exe
client info: user: WEBSERVICE
current SQL:
INSERT INTO MP_SYS.T2( column_list) SELECT column_list FROM MP_SYS.T1 WHERE MP_SYS.T1.P_ID=:B1
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[107.11229] on resource TM-0011FFA3-00000000
So I can see information about the machine, program, and SQL statement being executed. The user session identifier in red above corresponds to the id value in the GWFG. Lets look at the first two lines of the GFWG again.
BLOCKED 0x551b2c698 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[6B000-0004-0000022D] inst 4 BLOCKER 0x5513ed318 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[B6000-0006-00000099] inst 6
So the SQL statement and session details match this first line. This session is blocked on inst 4. The blocker is on inst6 and is identified as 0x5513ed318. Well who is this? There are no other details in this GWFG to help us with the BLOCKER.
The awareness regarding the beneficial qualities of devensec.com generico viagra on line should be known in order to avoid the wrong selection of anti-impotency solution. Milton is a progressive city that combines modern urban life and small town charm, with a mixture of small communities, tourist centers, and state-of-the-art medical facilities for the management of cialis sample Andrological and Urological disorders. Tadalafil is a fast get viagra australia acting drug that starts to work to keep the platelets away from each other by forming a lubricated layer in their outer circles. However, there could purchase generic viagra one of the health risks associated with surgery are never shared with the patient? Treating scoliosis without surgery or braces is the best option to opt.
To learn more about the blocker, I went to inst 6 and did this:
cd /u01/app/oracle/diag/rdbms/admin/orcl/orcl6/trace
grep 0x5513ed318 *
So I just did a grep on the session identifying value and was given a LMD trace file. Looking in that LMD trace file on the other instance gives me details about the blocking session.
user session for deadlock lock 0x5513ed318
sid: 1206 ser: 2673 audsid: 996459926 user: 13944/MP_SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 182 O/S info: user: oracle, term: UNKNOWN, ospid: 7049
image: oracle@myhost06.naucom.com
client details:
O/S info: user: web-svc, term: web-server2, ospid: 4276:864
machine: DOMAIN\web-server2 program: iis.exe
client info: user: WEBSERVICE
current SQL:
DELETE FROM MP_SYS.T1 WHERE P_ID = :B1
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[182.7049] on resource TM-0011FFA3-00000000
I can see that the blocked session was issuing a INSERT statement on a table and the blocker was issuing a DELETE on the same table.
There is another session involved but at this point, it is elementary to get session details using the same steps above.
Hopefully, this blog entry has shown how to use the GWFG to diagnose the SQL statements and the object involved in the global deadlock. I know know the exact SQL statements involved in the deadlock, and by extension, the objects involved as well. The resolution to the problem is no different than deadlock resolution in single-instance databases.
For more information about Oracle RAC Global Enqueue Services (GES), read Chapter 2 of my Oracle RAC Performance Tuning book.
