ORA-00060

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:

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

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:

• >1 row in the Deadlock Graph
• All Lock Types are TX
• The lock modes for the Holders and the Waiters are all X (eXclusive, mode 6)

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

The most common types are:

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:

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:

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)

Deadlock graph:
                     ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-XXXXXXXX-00000000　      11     333    SX             22      44    SX   SSX
TX-XXXXXXXX-XXXXXXXX　      22      44     X             11     333           X

There is more about lock modes and locking in the following:

Oracle® Database Concepts
12c Release 1 (12.1)
E17633-20
Chapter 9 Data Concurrency and Consistency
Section: Lock Modes
https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT020

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

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:

Discuss ORA-00060 Deadlocks

Still have questions? Consider posting a discussion in the Database Tuning Community.