Global deadlock can be compared with “normal” deadlock the difference is the fact that in RAC environment a deadlock is called a Global deadlock as the locking issue can be on a different instance in the cluster. 

Deadlocks normally occur when two or more session are both holding and requesting the same resources. In a non-RAC environment an ora-60 is reported. In a RAC environment a message is reported in the alert.log.

 

 

The fact that this is reported in nice, but how to analyze the transactions involved so the problem area can be solved?

A deadlock is an Application related issue (Bad Coding). Currently a lot of information is dumped into the tracefiles to investigate the problem. Below are 2 examples one in 11gR2 and one example of 10g.

In the alert.log of an 11.2.0.1 Database instance we see the following message:

 

 

If we open the reported LMD* trace file we can find  the following table which provide a lot of information to focus on.

 

Global Wait-For-Graph(WFG) at ddTS[0.3cd0] :

 

 

So here we can see that a session on instance 1 with XID 1D000-0001-0000006A is waiting for 1D000-0001-0000006A on instance 3. And also instance 3 XID 1D000-0003-00000571 is waiting on 1D000-0001-0000006A and there is our deadlock.

 

We can also see that the mode used is 5, which is an exclusive lock.

 

 

 

 

In the tracefile, but depend on the Oracle released used, we must also be able to find information about the SQL statement, user and programme being used. This will help the developers to find the possible cause in the code to fix the issue. To find for 11gR2 the Blocker from the above example search on the hexvalue 0x547d33988  and-or 0x547daf928 to get the additional information.

 

This will show the following information:

 

 

 

This is indicating sqlplus is used, code executed the user and host.

This information is useful for the developers to validate the cause.

 

 

 

Here is another example but know for Oracle 10g, you will notice the Wait Graph looks different as in Oracle 11gR2.

 

 

 

 

If we open the reported LMD* trace file we see the following table which provide a lot of information to focus on.

 

 

 

So here we see that a process on node 1 with xid 197000-0002-0000000A is waiting for

 

TM lock [0x9fbe][0x0],[TM]in mode 4 which is being held by a process with xid 28A000-0003-00000049 on node 2.  But also on node 2 xid 28A000-0003-00000049 is waiting on TM lock

[0x9fbe][0x0],[TM]in mode 4 which is being held by a process on node 1 with xid 197000-0002-0000000A

 

Now we know this, we want to know what program, sql or pl/sql is causing the problems.

The object involved can be found to use the Hex value 0x9fbe which is in decimal 40894. Query dba_objects to find the object involved.

 

But still this doesn’t show the sql involved. Depending on the Oracle Release and Patchset this can be different to find. Latest release report a part of the SQL, but you can also search your udump and bdump directory on the nodes involved. Use the PID to search through the tracefiles. This will give you the sql used.

  

In case you can’t find the tracefile or the traces are removed, you can set the ora-60 event on every instance in the cluster.

 

*LMD is a background process also called the Global enqueue services Daemon, taking care of the enqueues (locking).