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:
| "Key Signature" | Lock Type | Requested Lock Mode | Deadlock Graph | Likely 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:
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
Which is a combination of the "Application deadlock" and "Missing Index on Foreign Key (FK) Constraint" deadlock. In these cases, it is advisable to resolve the non-"TX X X" symptoms first since it is more likely that the less common FK/ITL/Bitmap signature is the base cause as opposed to an application deadlock.
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
http://docs.oracle.com/cd/E16655_01/server.121/e17633/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: