My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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:

 

In order to differentiate different types, we have taken the Lock Type and the mode held/waited for by the holder and waiter and used this to create a signature for each type. For example, the previous graph shows the following characteristics:

By focusing on these particular characteristics in the graph:

 

will give us the following type (which is typically an application deadlock):

TX X X
TX X X

Note that the most relevant parts of the "Key Signature" for deadlock type recognition are the lock Type and the Mode it is requesting. The main types are highlighted in the table below

The most common types are:

"Key Signature"Lock TypeRequested
Lock Mode
Deadlock GraphLikely
Deadlock Type
Comments
Type TX Lock Requesting Mode X (6) TX X(6) TX X X
TX X X
Application TX Lock Held in Mode X (6) Requesting Mode X (6)
Type TM Lock Requesting Mode SSX (5) TM SSX (5) TM SX SSX SX SSX
TM SX SSX SX SSX
Missing Index on Foreign Key (FK) Constraint TM  Lock Held in Mode SX (3) Held SSX (5) Requested
Type TX Lock Requesting Mode S(4) TX S(4) TX X S
TX X S
Insufficient Interested Transaction List (ITL) Provision
OR
Bitmap Index
OR

PK/UK Index

TX Lock Held in Mode X (6) Requesting Mode S (4)

ITL, Bitmap Index and PK/UK Index Signatures are the Same. Further Investigation will be required to identify absolute cause

Type TX Lock Requesting Mode X (6)
Single Row in Deadlock Graph
TX X(6) TX X X
Single Row in Deadlock Graph
Self Deadlock
OR
Autonomous Transaction Self Deadlock
This looks the same as a standard application deadlock except that there is only a single row in the deadlock graph.
Type UL Lock in Deadlock Graph UL ANY UL ? ?
?
Application Deadlock Featuring User Defined Locks This is very similar to the standard application deadlock except that it features User Defined Locks

 

Note: this table is not exhaustive and outlines the most common issues. There are some rare conditions where deadlocks can be achieved that are not mentioned. For cases that do not match those above, 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

For information on how to identify and diagnose the various different types of ORA-00060 Deadlock Types that you may encounter, please refer to the following document:

<Document 1559695.1> How to Diagnose Different ORA-00060 Deadlock Types Using Deadlock Graphs in Trace

Note: these are the most common types and causes. There are rare cases where similar symptoms can be found with different causes. If there is any doubt about the identification of a particular non-application deadlock type or if different graphs are seen, then file a Service Request with Oracle Support

For Reference, the Oracle lock modes are :

0 - none
1 - null (NULL)
2 - Row Share, also called a subshare table lock  (SS)
3 - Row eXclusive Table Lock, also called a subexclusive table lock (SX)
4 - Share Table Lock (S)
5 - Share Row-eXclusive, also called a share-subexclusive table lock (SSX)
6 - EXclusive (X)

Note: Often you will see a combination of an application deadlock "Signature" plus one of the others as opposed to a "classic" repeating signature. For example you may see something like:

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Solution

To view full details, 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 a vibrant support community of peers and Oracle experts.