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

Understanding the Error: ORA-00060 and Timeouts

In Oracle database environments, a deadlock is a specific, unresolvable condition where two or more database sessions are indefinitely waiting for data locked by each other, resulting in a circular dependency. Because neither session can proceed without the other releasing its lock, the system grinds to a halt for those transactions.

Fortunately, Oracle's lock manager is proactive. It periodically scans the locking queues and automatically detects this impasse, typically within three seconds. To break the deadlock, Oracle sacrifices one of the transactions. It rolls back the current statement of the session that detected the deadlock and throws the infamous error to the client: ORA-00060: deadlock detected while waiting for resource.

While Oracle automatically resolves the immediate deadlock, the root cause remains. In high-throughput environments, this architectural flaw will trigger recurring ORA-00060 errors and frequently cascade into broader systemic failures, manifesting as Oracle DB timeouts, such as ORA-02049: timeout: distributed transaction waiting for lock or ORA-01013: user requested cancel of current operation (often triggered when an application connection pool times out waiting for a blocked query to return).

Step 1: Diagnose the Deadlock using Trace Files

The most critical mistake SREs make is attempting to guess the cause of a deadlock based on application logs. You must go directly to the database layer.

1. Locate the Alert Log When an ORA-00060 is thrown, Oracle writes an entry to the database alert log (alert_<SID>.log). You will see a message similar to this:

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345.trc.

2. Analyze the Trace File (The Deadlock Graph) Open the referenced .trc file. Search for the section titled Deadlock graph. It will look something like this:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0002001A-00000034        15     123     X             20     456           X
TX-0003002B-00000012        20     456     X             15     123           X

Pay immediate attention to the Resource Name prefix:

• TX (Transaction Lock): This indicates a row-level lock. The issue is almost certainly an application logic flaw where sessions are grabbing rows in inconsistent orders.
• TM (Table/DML Lock): This indicates a table-level lock. This is the smoking gun for missing indexes on foreign keys.

Step 2: Fix Unindexed Foreign Keys (TM Locks)

If your trace file reveals TM locks, you are dealing with an architectural schema issue.

When you delete a row from a parent table or update its primary key, Oracle is forced to check the child table to ensure referential integrity is maintained (e.g., ensuring no orphaned records are left behind). If there is no index on the foreign key column in the child table, Oracle cannot quickly verify the constraints. Instead, it must place a full table lock on the entire child table to perform a full table scan safely.

In a highly concurrent system, locking an entire child table brings performance to a standstill and rapidly leads to deadlocks if another session is attempting a similar operation in reverse.

The Solution: You must identify all unindexed foreign keys in your schema and apply B-Tree indexes to them. (Refer to the code block section below for a comprehensive diagnostic script to locate these orphaned foreign keys). Once indexed, Oracle only needs to lock the specific rows involved in the relationship, rather than the entire table.

Step 3: Resolve Application Logic Deadlocks (TX Locks)

If the trace file points to TX locks, the database schema is likely fine, but your application code is at fault.

The Scenario:

• Thread A (Session 1): Executes UPDATE inventory SET stock = stock - 1 WHERE item_id = 100; (Acquires lock on Item 100)
• Thread B (Session 2): Executes UPDATE inventory SET stock = stock - 1 WHERE item_id = 200; (Acquires lock on Item 200)
• Thread A: Needs to update another item in the same cart: UPDATE inventory SET stock = stock - 1 WHERE item_id = 200; (Blocks, waiting for Thread B)
• Thread B: Needs to update another item: UPDATE inventory SET stock = stock - 1 WHERE item_id = 100; (Blocks, waiting for Thread A)

Deadlock.

The Solution:

1. Strict Ordering: Enforce a strict, deterministic ordering protocol within your application code. If a transaction must update multiple rows, it should always sort the primary keys of those rows and update them in ascending order (e.g., always update Item 100 before Item 200).
2. Pessimistic Locking: Use SELECT ... FOR UPDATE at the beginning of the transaction to acquire locks on all required rows in a specific order before performing any updates. This forces other transactions to wait in line rather than acquiring partial locks that lead to deadlocks.

Step 4: Mitigate Bitmap Index Contention

Bitmap indexes are heavily optimized for read-heavy, low-concurrency environments like Data Warehouses (OLAP). They are catastrophic in high-concurrency transactional databases (OLTP).

A single entry in a bitmap index maps to multiple rows (often hundreds or thousands). If an application updates a single row, Oracle must lock the entire bitmap segment corresponding to that value. This inadvertently locks hundreds of unrelated rows from being updated by other sessions, causing massive contention and frequent ORA-00060 errors.

The Solution: Identify heavily updated tables utilizing bitmap indexes. Drop the bitmap indexes and replace them with standard B-Tree indexes. If bitmap indexes are strictly necessary for reporting, consider dropping them before batch data loads and rebuilding them afterward.

Step 5: Address ITL (Interested Transaction List) Shortages

Occasionally, you will see a deadlock graph pointing to the exact same block and row, or you will see sessions hanging with high wait events for enq: TX - allocate ITL entry.

Every data block in Oracle contains an Interested Transaction List (ITL) header. A transaction must acquire a free ITL slot in the block to modify any row within it. If the block is highly compressed or the INITRANS parameter is set too low, concurrent transactions attempting to update completely different rows in the same data block will queue up waiting for an ITL slot. If the queueing becomes circular across multiple blocks, a deadlock occurs.

The Solution: Increase the INITRANS value for the affected table or index.

ALTER TABLE high_concurrency_table MOVE INITRANS 10;
ALTER INDEX high_concurrency_idx REBUILD INITRANS 10;

Note that this requires moving/rebuilding the segment to apply the new setting to existing blocks.

Step 6: Managing Oracle DB Timeouts

Deadlocks and blocking locks often manifest to the end-user as application timeouts. If a session is blocked but not in a circular deadlock, Oracle will not kill it. It will wait indefinitely.

To protect the database and application servers from thread exhaustion:

1. Distributed Transactions: If using database links, adjust DISTRIBUTED_LOCK_TIMEOUT. If a transaction waits longer than this value (in seconds), it fails with ORA-02049.
2. Application Tier: Implement strict query timeouts in your ORM (e.g., Hibernate, Entity Framework) or connection pool (e.g., HikariCP) to ensure connections are forcefully returned to the pool and rogue queries are cancelled (ORA-01013) rather than hanging indefinitely.
3. Resource Manager: Use Oracle Database Resource Manager (DBRM) to set up idle time limits and maximum execution time limits for specific consumer groups.