ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Deadlock Error Not in Alert.log and No Trace File Generated on OPS or RAC

Deadlock Error Not in Alert.log and No Trace File Generated on OPS or RAC

原创 Linux操作系统 作者:spider0283 时间:2011-08-21 11:03:45 0 删除 编辑
修改时间 20-OCT-2010     类型 TROUBLESHOOTING     状态 ARCHIVED  

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 10.2.0.4 - Release: 8.1.7 to 10.2
Information in this document applies to any platform.

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

October 12, 2010

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

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


显示相关信息 相关的


产品
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
错误
ORA-60; 60 ERROR

返回页首返回页首

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-705479/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    627887