ORA-00060: Deadlock Detected While Waiting for Resource – Complete Oracle DBA Troubleshooting Guide

The ORA-00060: deadlock detected while waiting for resource error is one of the most important Oracle Database concurrency errors encountered in production environments. It occurs when two or more database sessions become trapped in a circular locking dependency, where each session is waiting for a resource held by another session. Because none of the sessions can proceed, Oracle automatically detects the deadlock and resolves it by terminating one of the conflicting SQL statements.

Unlike ordinary blocking situations, where one session simply waits for another to complete, a deadlock creates a circular dependency that cannot resolve itself. Oracle's deadlock detection mechanism identifies the cycle, selects one statement as the victim, rolls back only that statement, and allows the remaining transaction to continue.

ORA-00060 is commonly encountered in OLTP applications, Oracle E-Business Suite, ERP systems, banking applications, PL/SQL programs, Java applications, JDBC/ODP.NET applications, batch processing, and high-concurrency production environments where multiple sessions update the same data simultaneously.

Successfully resolving ORA-00060 requires identifying the SQL statements involved, analyzing the Oracle deadlock graph, understanding the locking sequence, reviewing application logic, and implementing changes that prevent circular locking dependencies.

This comprehensive Oracle DBA guide explains Oracle deadlock architecture, common causes of ORA-00060, production troubleshooting techniques, deadlock trace analysis, Oracle RAC, ASM, Oracle Data Guard, Oracle Cloud Infrastructure (OCI) considerations, and best practices for Oracle Database 11g, 12c, 18c, 19c, 21c, and Oracle Database 23ai.

Quick Solution

Review the Oracle Alert Log and generated deadlock trace file, identify the SQL statements and sessions involved, analyze the deadlock graph, determine the locking sequence, correct the application logic or transaction order, and ensure that concurrent transactions acquire database objects in a consistent order.

Error Message

ORA-00060: deadlock detected while waiting for resource

Whenever Oracle detects a deadlock, it records detailed diagnostic information in a trace file and writes an entry to the Oracle Alert Log.

What is ORA-00060?

ORA-00060 indicates that two or more Oracle sessions are waiting indefinitely for resources held by each other. Because none of the sessions can continue, Oracle detects the circular dependency and automatically breaks the deadlock.

Instead of terminating the entire transaction, Oracle rolls back only the SQL statement selected as the deadlock victim. The application receives ORA-00060 and may retry the operation if appropriate.

The database itself remains healthy, and other sessions continue to operate normally.

How Oracle Detects Deadlocks

Oracle continuously monitors lock dependencies between sessions. When a circular wait condition is detected, Oracle generates a deadlock graph showing the sessions, locks, and resources involved.

        Session A

      UPDATE EMP

           │

     Waiting for Row

           │

           ▼

        Session B

      UPDATE DEPT

           │

     Waiting for Row

           │

           ▼

        Session A

      Circular Wait

           │

           ▼

      Oracle Detects

        Deadlock

           │

           ▼

 Rolls Back One Statement

Oracle records this information in a trace file, making the deadlock graph the primary diagnostic tool for investigating ORA-00060.

Oracle Locking Architecture

Oracle uses several types of locks to maintain transaction consistency and data integrity. Most deadlocks involve row-level transaction locks, but metadata and table locks may also participate in specific scenarios.

TX Locks

TX (transaction) locks protect individual rows modified by INSERT, UPDATE, and DELETE statements. Most ORA-00060 errors involve TX enqueue locks.

TM Locks

TM (table) locks protect table structures during DML operations and ensure data consistency between concurrent sessions.

Library Cache Locks

Library cache locks protect SQL statements, PL/SQL objects, and metadata while they are parsed or executed. Although less common, metadata-related deadlocks can involve these locks.

Deadlock vs. Blocking

Blocking	Deadlock
One session waits for another.	Two or more sessions wait on each other.
Usually resolves after COMMIT or ROLLBACK.	Cannot resolve automatically.
No circular dependency.	Circular dependency exists.
No Oracle intervention required.	Oracle automatically terminates one statement.
Typically associated with ORA-00054.	Associated with ORA-00060.

Types of Oracle Deadlocks

1. Row-Level Deadlocks

The most common type of deadlock occurs when two sessions update rows in different orders.

2. Application Deadlocks

Poor application design, inconsistent transaction ordering, or improper locking logic may create deadlocks.

3. Distributed Deadlocks

Distributed transactions across multiple Oracle databases may produce deadlocks involving database links.

4. RAC Global Deadlocks

Oracle RAC environments can experience global deadlocks involving sessions executing on different cluster nodes.

Common Causes of ORA-00060

1. Inconsistent Row Update Order

Multiple sessions updating the same tables in different orders represent the most common cause of deadlocks.

2. Long-Running Transactions

Transactions that remain open for extended periods increase the probability of conflicting lock requests.

3. Poor Application Design

Applications that acquire locks inconsistently or hold locks longer than necessary frequently generate deadlocks.

4. Missing Commit Statements

Applications that delay commits unnecessarily extend lock duration and increase contention.

5. Concurrent Batch Jobs

Multiple batch processes updating the same objects simultaneously may create circular locking conditions.

6. PL/SQL Exception Handling

Improper exception handling that leaves transactions open after failures can contribute to deadlock scenarios.

7. Oracle E-Business Suite Customizations

Custom concurrent programs or poorly designed extensions may acquire application locks in conflicting sequences.

Common Symptoms

Applications receive ORA-00060 unexpectedly.
Transactions are rolled back automatically.
Users report intermittent update failures.
Alert Log records deadlock information.
Oracle generates trace files.
Batch jobs terminate unexpectedly.
High transaction concurrency results in occasional SQL failures.

ORA-00060 Compared with Related Oracle Errors

Error	Description	Primary Area
ORA-00060	Deadlock detected.	Concurrency Control
ORA-00054	Requested lock unavailable.	Locking
ORA-01555	Snapshot too old.	UNDO
ORA-04031	Shared memory allocation failure.	SGA Memory
ORA-03113	End-of-file on communication channel.	Client/Server Communication

Production DBA Recommendation

Never treat ORA-00060 as a database problem alone. In most production environments, the root cause is application transaction design rather than Oracle itself. Focus on identifying the locking sequence, reviewing the deadlock graph, and modifying application logic to acquire objects in a consistent order. This provides a permanent solution instead of repeatedly retrying failed transactions.

Step-by-Step Oracle DBA Troubleshooting

When ORA-00060 occurs, the objective is not simply to identify the blocking session, but to understand why the deadlock occurred. Oracle automatically resolves the deadlock by rolling back one SQL statement, but unless the root cause is corrected, the deadlock will likely occur again.

The following production troubleshooting workflow reflects Oracle DBA best practices.

Step 1 – Review the Oracle Alert Log

Whenever Oracle detects a deadlock, it records an entry in the Alert Log indicating that a deadlock occurred and references the generated trace file.

Typical Alert Log location:

$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/

alert_<SID>.log

Example Alert Log entry:

ORA-00060: deadlock detected while waiting for resource

Deadlock graph written to trace file.

Step 2 – Locate the Deadlock Trace File

The Oracle trace file contains the complete deadlock graph and is the most important source of diagnostic information.

Trace file location:

$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/

The trace file usually contains:

Deadlock graph
Session IDs
Lock types
SQL statements
Object information
Wait events

Step 3 – Analyze the Deadlock Graph

Oracle generates a graphical representation of the circular locking dependency.

Typical example:

Session 101 waits for Session 205

Session 205 waits for Session 101

Deadlock Detected

The graph identifies:

Sessions involved
Objects involved
Lock types
SQL statements
Deadlock victim

Step 4 – Review Session Information

Collect details about the sessions involved in the deadlock.

SELECT

SID,

SERIAL#,

USERNAME,

STATUS,

PROGRAM,

MACHINE,

SQL_ID,

EVENT

FROM V$SESSION

WHERE SID IN (<SID1>,<SID2>);

Step 5 – Review Active Locks

Use V$LOCK to examine the locks held and requested by each session.

SELECT

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

BLOCK

FROM V$LOCK

ORDER BY SID;

Pay particular attention to:

TX Locks
TM Locks
Requested Lock Mode
Blocking Indicators

Step 6 – Identify Locked Objects

Determine which database objects participated in the deadlock.

SELECT

LO.SESSION_ID,

DO.OWNER,

DO.OBJECT_NAME,

DO.OBJECT_TYPE

FROM V$LOCKED_OBJECT LO

JOIN DBA_OBJECTS DO

ON LO.OBJECT_ID = DO.OBJECT_ID;

Step 7 – Review the SQL Statements

Retrieve the SQL executed by the affected sessions.

SELECT

SQL_ID,

SQL_TEXT

FROM V$SQL

WHERE SQL_ID='<SQL_ID>';

Understanding the SQL execution order is critical for identifying the root cause.

Step 8 – Review Transaction Information

Check active transactions associated with the sessions.

SELECT

ADDR,

START_TIME,

STATUS,

USED_UBLK

FROM V$TRANSACTION;

Long-running transactions significantly increase the probability of deadlocks.

Step 9 – Analyze Application Logic

Most ORA-00060 errors originate from application design rather than the Oracle database itself.

Review whether:

Tables are accessed in inconsistent order.
Transactions remain open unnecessarily.
Multiple updates occur in different sequences.
Commit frequency is appropriate.
Retry logic is implemented correctly.

Step 10 – Oracle RAC Considerations

In Oracle RAC environments, deadlocks may occur between sessions running on different instances.

Review global sessions:

SELECT

INST_ID,

SID,

SERIAL#,

USERNAME,

SQL_ID

FROM GV$SESSION;

Also review:

GV$LOCK
Global enqueue waits
Cluster synchronization events

Step 11 – ASM Considerations

ASM itself rarely causes ORA-00060, but storage performance problems can prolong transaction duration and indirectly increase lock contention.

Verify ASM status.

asmcmd lsdg

Step 12 – Oracle Data Guard Considerations

ORA-00060 normally occurs on the primary database where application transactions execute.

If Data Guard role transitions recently occurred:

Verify database role.
Check redo transport.
Review application reconnections.

Step 13 – Oracle Cloud Infrastructure (OCI)

For databases hosted on OCI:

Monitor database CPU utilization.
Review application connection pools.
Check OCI Monitoring dashboards.
Review Autonomous Database activity (if applicable).

Real Production Case Study

A financial application experienced intermittent ORA-00060 errors during peak business hours. Investigation of the deadlock trace file revealed that two application modules updated the CUSTOMERS and ORDERS tables in opposite order. One module updated CUSTOMERS first and then ORDERS, while the second module performed the updates in reverse order.

The development team standardized the transaction sequence so that all application modules updated the tables in the same order. After deployment, deadlocks were eliminated without requiring database configuration changes.

Oracle DBA Troubleshooting Checklist

Verification	Status
Alert Log Reviewed	☐
Deadlock Trace File Located	☐
Deadlock Graph Analyzed	☐
Sessions Identified	☐
Locks Reviewed	☐
Objects Identified	☐
SQL Statements Reviewed	☐
Application Logic Reviewed	☐
Root Cause Confirmed	☐
Application Corrected	☐
Deadlock Eliminated	☐

Oracle Version Considerations

ORA-00060 has existed since the earliest Oracle releases because deadlock detection is a fundamental part of Oracle's transaction management architecture. While the underlying detection mechanism remains consistent, newer Oracle versions provide richer diagnostics, improved trace files, enhanced wait event reporting, and better online maintenance capabilities to simplify deadlock analysis.

Oracle Version	Deadlock & Diagnostic Enhancements
Oracle 10g	Automatic Workload Repository (AWR), Active Session History (ASH), improved lock monitoring.
Oracle 11g	Automatic Diagnostic Repository (ADR), enhanced deadlock trace files.
Oracle 12c	Multitenant architecture, improved online maintenance, better session diagnostics.
Oracle 18c / 19c	Enhanced wait event analysis, improved SQL monitoring, Real-Time SQL Monitoring.
Oracle 21c / 23ai	Improved observability, Autonomous Health Framework (AHF), OCI monitoring integration.

Deadlock Prevention Best Practices

Always access database tables in a consistent order across all application modules.
Keep transactions as short as possible.
Commit transactions promptly.
Avoid unnecessary user interaction while transactions remain open.
Reduce the number of rows locked within a transaction.
Implement proper retry logic for transient deadlocks.
Use indexed predicates to minimize row locking.
Avoid full table scans during update operations whenever possible.
Review deadlock trace files instead of repeatedly restarting applications.
Regularly monitor blocking sessions and long-running transactions.

Common Developer Mistakes

Updating the same tables in different orders.
Holding transactions open during user input.
Missing COMMIT or ROLLBACK statements.
Using excessively large transactions.
Ignoring ORA-00060 retry requirements.
Performing unnecessary updates.
Leaving cursors and transactions open.
Poor exception handling in PL/SQL programs.
Ignoring concurrency testing during development.
Assuming Oracle is responsible instead of reviewing application design.

Useful SQL Queries

Review Active Locks

SELECT

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

BLOCK

FROM V$LOCK

ORDER BY SID;

Review Session Information

SELECT

SID,

SERIAL#,

USERNAME,

STATUS,

SQL_ID,

EVENT

FROM V$SESSION;

Identify Locked Objects

SELECT

LO.SESSION_ID,

DO.OWNER,

DO.OBJECT_NAME,

DO.OBJECT_TYPE

FROM V$LOCKED_OBJECT LO

JOIN DBA_OBJECTS DO

ON LO.OBJECT_ID = DO.OBJECT_ID;

Review Active Transactions

SELECT

ADDR,

START_TIME,

STATUS,

USED_UBLK,

USED_UREC

FROM V$TRANSACTION;

RAC Global Session Review

SELECT

INST_ID,

SID,

SERIAL#,

USERNAME,

SQL_ID

FROM GV$SESSION;

Useful Linux Commands

Command	Purpose
`top`	Monitor CPU utilization and Oracle processes.
`ps -ef \| grep ora_`	Display Oracle background processes.
`vmstat 5`	Monitor memory, CPU, and process activity.
`iostat -x 5`	Monitor storage performance and latency.
`tail -100 alert.log`	Review recent Alert Log entries.
`srvctl status database`	Verify Oracle RAC database status.

ORA-00060 Troubleshooting Flowchart

ORA-00060

      │

      ▼

Review Alert Log

      │

      ▼

Locate Trace File

      │

      ▼

Analyze Deadlock Graph

      │

      ▼

Identify Sessions

      │

      ▼

Review SQL Statements

      │

      ▼

Identify Locked Objects

      │

      ▼

Review Transaction Order

      │

      ▼

Correct Application Logic

      │

      ▼

Deploy Fix

      │

      ▼

Monitor for Recurrence

Frequently Asked Questions (FAQ)

Does Oracle automatically resolve ORA-00060?

Yes. Oracle detects the deadlock and automatically rolls back one SQL statement to break the circular dependency. The remaining transaction can continue, but the application should handle the ORA-00060 error appropriately, often by retrying the operation if it is safe to do so.

Is ORA-00060 caused by Oracle Database?

In most cases, no. ORA-00060 is typically caused by application transaction design, inconsistent locking order, or long-running transactions rather than a defect in the Oracle Database engine.

Can ORA-00060 occur in Oracle RAC?

Yes. Oracle RAC supports global deadlock detection. The sessions involved in the deadlock may reside on different cluster instances, making it important to analyze GV$SESSION and GV$LOCK views.

Should I restart the database to resolve ORA-00060?

No. Restarting the database is unnecessary and does not address the root cause. Instead, analyze the deadlock trace file, identify the locking sequence, and correct the application logic to prevent future deadlocks.

Where can I find the deadlock details?

Oracle records complete deadlock information in a trace file under the Automatic Diagnostic Repository (ADR). The Alert Log contains a reference to the trace file, which includes the deadlock graph, participating sessions, SQL statements, and locked resources.

About the Author

Rana Abdul Wahid is an Oracle Database Consultant with over 15 years of experience in Oracle Database Administration, Oracle RAC, Oracle Data Guard, RMAN Backup & Recovery, Oracle E-Business Suite, Oracle Cloud Infrastructure (OCI), Performance Tuning, Linux/Unix Administration, MySQL, Microsoft SQL Server, PostgreSQL, and enterprise database management.

He shares production-tested Oracle DBA solutions, performance tuning techniques, backup and recovery strategies, and enterprise troubleshooting guides to help database professionals diagnose and resolve complex Oracle Database issues efficiently.

Learn more about the author →

Conclusion

The ORA-00060: Deadlock Detected While Waiting for Resource error indicates that Oracle has identified a circular locking dependency between two or more sessions. Rather than allowing the sessions to wait indefinitely, Oracle automatically resolves the deadlock by rolling back one SQL statement and recording detailed diagnostic information in a trace file.

Successful troubleshooting requires more than identifying the affected sessions. DBAs should analyze the deadlock graph, review the SQL statements involved, examine transaction ordering, and work with development teams to eliminate inconsistent locking patterns. Most recurring deadlocks are resolved through application design improvements rather than database configuration changes.

By enforcing consistent object access order, keeping transactions short, committing changes promptly, and monitoring high-concurrency workloads, organizations can significantly reduce ORA-00060 occurrences and improve application reliability in production Oracle environments.

Final DBA Recommendation

Treat every ORA-00060 as an opportunity to improve application concurrency design. The deadlock trace file contains the evidence needed to identify the root cause. Resolving the underlying transaction sequence—not merely retrying failed SQL statements—is the key to preventing future deadlocks and maintaining stable, high-performance Oracle systems.

Found this guide helpful? Explore our Oracle Error Codes Guide for more production-tested Oracle DBA troubleshooting guides, performance tuning techniques, backup and recovery solutions.

Oracle Database Error Solutions & DBA Knowledge Base