Applies to:

Purpose

Symptoms

Your application reports a deadlock:
ORA-00060: deadlock detected while waiting for resource

However, there is no message or reference to a trace file reported in the Alert.log.
You can also not find any trace file showing the familiar deadlock graph that enables you to determine the current SQL, involved sessions and objects.

Cause

Since we are dealing with global resources in RAC, the deadlock detection mechanism differs from a non-RAC environment. In RAC, the LMD process periodically checks for deadlocks in the database.
The missing deadlock message in the Alert.log and the missing trace file with the deadlock graph are NOT an Operating System specific issue. This issue is GENERIC to an OPS or RAC environment.
Note also that the two sessions are not necessarily connected to different nodes. You can get the same effect with two sessions connected to just a single existing instance when that has been started with PARALLEL_SERVER = TRUE or CLUSTER_DATABASE = TRUE.

Last Review Date

Instructions for the Reader

Troubleshooting Details

Fix

Firstly, consider whether the deadlock can be reproduced with an instance started in exclusive mode (PARALLEL_SERVER=FALSE or CLUSTER_DATABASE=FALSE).
That configuration will produce the normal deadlock information you can use to debug your application according to
Note 62365.1 What to do with "ORA-60 Deadlock Detected" Errors

If you cannot test in exclusive mode, there is only some limited information in the LMD trace file.
That information will look like:

Global Wait-For-Graph(WFG) at ddTS[0.1] :
BLOCKED 6A084460 5 [0xd0004][0x30e],[TX] [1245185,21] 0
BLOCKER 6A092C40 5 [0xd0004][0x30e],[TX] [1245186,24] 1
BLOCKED 6A0932C0 5 [0x10003][0x5ef],[TX] [1245186,24] 1
BLOCKER 6A057EF0 5 [0x10003][0x5ef],[TX] [1245185,21] 0

These values are:


The second to last field () could be a pid or the txn id
depending on whether we have an XID based deadlock search flag.

In the example we see that pid 1245185 on node 0 is waiting for TX (transaction) lock
[0xd0004][0x30e],[TX] in mode 5 which is being held by pid 1245186 on node 1.
Node 1 - pid 1245186 is waiting for TX (transaction) lock [0x10003][0x5ef],[TX]
in mode 5 which is being held by pid 1245185 on node 0.

Unfortunately this graph will not show you the SQL that caused the deadlock.

There has been no reliable mechanism in RAC to dump the SQL for all sessions
involved in a deadlock if the deadlock spans across instances. The debugging
mechanisms have continually improved through various releases to add additional trace
data and diagnostic information - 9.2.0.6, 10.2.0.4 and 11.1.0.6. What RAC detects
is dumped in the Wait-For-Graph in the LMD trace file.

For assistance with further analysis of this issue in a RAC environment, please contact Oracle Support .

An enhancement request has been logged to improve the diagnostibility of ORA-60 errors in future releases.

References

返回页首