Resolving ORA-00060: Deadlock Detected While Waiting for Resource and Oracle Timeouts

Understanding the Error

In high-throughput Oracle Database environments, database administrators and application developers frequently encounter concurrency issues. The most notorious of these are ORA-00060: deadlock detected while waiting for resource and ORA-01013: user requested cancel of current operation (which usually surfaces as a timeout at the application tier or ORM layer, such as Hibernate or Entity Framework). Understanding the mechanical difference between a deadlock and a timeout is essential for proper troubleshooting and resolution.

A deadlock is a specific state of circular blocking. Imagine Session A locks Row 1 and subsequently attempts to lock Row 2. Simultaneously, Session B has locked Row 2 and attempts to lock Row 1. Neither session can proceed because each is waiting on a resource held by the other. This creates an infinite wait state. Oracle Database has an internal mechanism (the LMD0 background process in Real Application Clusters, or equivalent local processes in single-instance deployments) that periodically scans for these cycles in the lock wait graph. When a cycle is detected, Oracle intervenes by raising the ORA-00060 error in one of the involved sessions and rolling back the specific statement that caused the deadlock. It is important to note that Oracle only rolls back the statement, not necessarily the entire transaction, leaving the session active and requiring the application to handle the exception gracefully, usually by issuing a full ROLLBACK.

Conversely, an Oracle DB timeout—often manifesting as an ORA-01013 error—occurs when a session waits for a resource or executes a query for a duration exceeding a predefined threshold configured at the application or driver level (e.g., JDBC setQueryTimeout). This is not a circular deadlock; it is severe, unidirectional blocking or simply a poorly optimized query taking too long to execute. The application loses patience, sends an interrupt signal to the database, and the operation is cancelled.

Step 1: Diagnose the Deadlock and Locate the Trace File

When Oracle detects a deadlock and throws ORA-00060, it immediately generates a deadlock graph and writes it to a trace file located in the database's Automatic Diagnostic Repository (ADR). Locating and analyzing this trace file is non-negotiable; it is the definitive source of truth for the incident.

1. Locate the Alert Log: The first step is to check the database alert log (alert_SID.log). The alert log will contain a clear message stating something like: ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345.trc.
2. Analyze the Deadlock Graph: Open the specified trace file using a text editor or a tool like tail or less. Search for the section titled Deadlock graph. This graph outlines the sessions involved, the resources they are contending for, the locks they hold, and the locks they are requesting.

Crucial Lock Types to Identify:

• TX (Transaction Lock): A row-level lock. Seeing TX locks in a deadlock graph indicates that two application sessions are trying to update the same set of rows but in a different order. This is almost exclusively an application logic issue.
• TM (DML Enqueue Lock): A table-level lock. If TM locks are present, it strongly points to missing indexes on foreign keys. When a parent record's primary key is updated or the record is deleted, Oracle must verify that no orphan records are left in the child table. If the foreign key column in the child table lacks an index, Oracle is forced to place a full table lock (TM lock) on the child table to perform this check. Concurrent operations on this child table will severely collide, leading directly to deadlocks.

Step 2: Identify Active Blocking Sessions (for Timeouts)

If your application is experiencing massive timeouts but no ORA-00060 errors are present in the alert log, you are dealing with lock contention, not a deadlock. You must identify which session is holding the lock that everyone else is waiting for. The V$SESSION and V$LOCK dynamic performance views are your primary diagnostic tools.

By querying V$SESSION, you can filter for sessions where the BLOCKING_SESSION column is not null. This immediately identifies the Session ID (SID) that is causing the bottleneck. You can then join this with V$SQL using the SQL_ID to see exactly what query the blocking session is running, and V$SESSION's MACHINE or OSUSER columns to trace it back to a specific application server or user.

Step 3: Implement Fixes and Preventative Measures

Resolving TM Deadlocks (Unindexed Foreign Keys): If your trace file analysis points to TM locks, the resolution is relatively straightforward but highly impactful. You must identify all foreign keys in your schema that lack corresponding indexes and create them. This prevents Oracle from escalating to table-level locks during parent table modifications. You can write a query joining DBA_CONSTRAINTS, DBA_CONS_COLUMNS, and DBA_IND_COLUMNS to systematically uncover these missing indexes. Once created, test thoroughly to ensure the new indexes do not negatively impact INSERT performance.

Resolving TX Deadlocks (Row-Level Contention): Fixing row-level deadlocks requires refactoring application code. The database is functioning correctly; the application is submitting conflicting instructions.

1. Establish a Consistent Locking Order: The golden rule of preventing deadlocks is that all application processes must acquire locks in the exact same deterministic order. If Process A always locks Table1, then Table2, and Process B also locks Table1, then Table2, a deadlock is mathematically impossible. Process B will simply queue and wait for Process A to complete its transaction. This applies to individual rows as well—always sort lists of primary keys before iterating through them to issue UPDATE or SELECT ... FOR UPDATE statements.
2. Minimize Transaction Duration: The longer a transaction remains open, the higher the probability it will collide with a concurrent transaction. Keep transactions as short as possible. Perform expensive computations or external API calls outside the database transaction boundary. Commit frequently, but strictly ensure business logic and data integrity are maintained.
3. Transition to Optimistic Locking: For highly concurrent systems, pessimistic locking (locking rows preemptively) can become a severe bottleneck. Consider optimistic locking. Instead of placing database locks, read the row along with a version number or timestamp column. When the application attempts to update the row, the WHERE clause must include the original version number. If another session has modified the row in the interim, the version number will have changed, the update will affect zero rows, and the application must handle the StaleObjectStateException (usually by retrying the operation or prompting the user). This shifts the burden from database locks to application logic.

Step 4: Immediate Mitigation During Production Outages

When severe blocking leads to widespread application timeouts and system degradation, you may need to take immediate, forceful action to restore service before a root cause fix can be deployed. This often involves terminating the blocking session.

You can use the ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; command to forcefully disconnect the offending session.

Critical Warning: Killing a session is a drastic measure. The database must immediately roll back any uncommitted changes made by that session. In a busy database, rolling back a massive, long-running transaction can consume significant I/O and CPU resources, potentially exacerbating performance issues temporarily and leaving application state inconsistent if the application is not designed to handle unexpected transaction failures gracefully. This should only be used as an absolute last resort when the system is effectively down.