How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (Doc ID 1507093.1)

Last updated on MARCH 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.

Goal

When Oracle detects a deadlock, the current SQL in the session detecting the deadlock is cancelled and 'statement-level rollback' is performed so as to free up resources and not block all activity. The session that detected the deadlock is still 'alive' and the rest of the transaction is still active. If you repeat the last (cancelled) operation in the session, then you will get the deadlock again.

When such a deadlock is detected a trace file is produced containing a "Deadlock Graph" (along with other useful information). By examination of numerous Service Requests, we have seen that the most common types of deadlock can be identified by a "signature" deadlock graph that can be used to identify the "type" of deadlock being encountered. This article presents examples of each type so that investigation and resolution can continue along the right track.

The aim of this document is to show how to use a "Deadlock Graph" produced by and ORA-00060 error to identify the base problem.

NOTE: Some deadlock traces DO NOT contain a "Deadlock Graph"  section because the deadlock is such that it would be inappropriate or irrelevant. In these cases then the recommended action is to collect some extra diagnostic information and then create a Service Request with Support as outlined in the following document:

<Document 1552194.1> ORA-00060 Deadlock Graph Not Matching any Examples: Suggested Next Steps

If you are not already using it, you can use the Troubleshooting Assistant to help you diagnose common ORA-00060 Deadlock issues:

<Document 60.1> Troubleshooting Assistant: Oracle Database ORA-00060 Errors on Single Instance (Non-RAC) Diagnosing Using Deadlock Graphs in ORA-00060 Trace Files

Deadlock Graph Interpretation

A typical deadlock graph might look like this:

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms