ORA-00060: Deadlock Detected While Waiting for Resource – How to Identify and Fix It Like a Pro

You’re in the middle of a busy day, users start calling — “our app froze”, “transactions failed”, or “we’re seeing deadlock errors.”

When you check the logs, you find this familiar Oracle error:

ORA-00060: deadlock detected while waiting for resource

Deadlocks are one of those issues that sound scary but are totally manageable once you understand how they happen.
Let’s walk through what a deadlock is, how to detect it, and most importantly, how to fix and prevent it.

---

What Is a Deadlock in Oracle?

A deadlock occurs when two or more sessions are waiting on each other to release a resource — typically a row lock — and neither can proceed.

For example:

• Session A locks Row 1 and waits for Row 2.
• Session B locks Row 2 and waits for Row 1.
  Neither can move forward, so Oracle detects this circular dependency and kills one of them to free the resource.

Oracle automatically rolls back one of the statements and returns ORA-00060 to prevent the system from hanging.

---

Typical Scenarios That Cause ORA-00060

1. Concurrent updates on the same rows in different sessions.
2. Foreign key constraints without proper indexes.
3. Application transactions that lock tables in different order.
4. Explicit row locking (SELECT ... FOR UPDATE) misused.
5. Batch jobs overlapping with OLTP operations.

---

Step-by-Step Deadlock Troubleshooting Guide

Let’s dig into the steps to identify the cause and fix it.

---

Step 1: Check the Alert Log for ORA-00060

Locate your database alert log:

tail -50 $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log

You’ll find an entry like this:

ORA-00060: Deadlock detected while waiting for resource
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15364.trc

Note the trace file path — it’s the key to understanding the cause.

---

Step 2: Open the Trace File

Open the trace file mentioned in the alert log:

vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15364.trc

Inside, you’ll see details like:

Deadlock graph:
Session 1: object=users, rowid=AAAGe3AABAAANgMAAA
Session 2: object=users, rowid=AAAGe3AABAAANgMAAB

This shows which sessions, objects, and row IDs were involved.

---

Step 3: Identify the SQL Statements

Further down the trace file, Oracle lists the SQLs involved:

Session 1 SQL:
UPDATE users SET balance = balance - 100 WHERE user_id = 101;

Session 2 SQL:
UPDATE users SET balance = balance + 100 WHERE user_id = 102;

You now know which queries caused the deadlock.

---

Step 4: Find Sessions in Real-Time (Optional)

If it’s happening right now, you can use:

SELECT s.sid, s.serial#, s.username, s.machine, s.program, l.OBJECT_ID, o.OBJECT_NAME
FROM v$lock l JOIN v$session s ON l.sid = s.sid
JOIN dba_objects o ON l.OBJECT_ID = o.OBJECT_ID
WHERE l.block > 0;

This will show blocking sessions and locked objects.

---

Step 5: Kill or Release Blocking Sessions (Temporary Fix)

If production is stuck, release the blocking session:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

However, this is only a short-term solution. The real fix is to address the root cause.

---

Permanent Fixes and Best Practices

1. Ensure All Foreign Keys Have Indexes

Missing foreign key indexes are one of the top causes of deadlocks.
Check for them:

SELECT a.table_name, a.constraint_name
FROM user_constraints a
WHERE a.constraint_type='R'
AND NOT EXISTS (
  SELECT 1 FROM user_indexes b
  WHERE b.table_name=a.table_name AND b.index_name=a.constraint_name
);

If missing, create the index:

CREATE INDEX idx_users_fk ON users(parent_id);

✅ 2. Maintain a Consistent Locking Order

All applications accessing the same tables should lock rows or tables in the same sequence.
If one app updates table A → table B and another updates table B → table A, deadlocks are inevitable.

---

✅ 3. Keep Transactions Short

The longer your transaction holds locks, the higher the chance of deadlocks.
Commit early and often where possible.

---

✅ 4. Avoid Unnecessary “SELECT … FOR UPDATE”

Only use it when you truly need to lock rows.
If your app logic doesn’t need it, remove it.

---

✅ 5. Use Retry Logic in Application Code

Applications should catch ORA-00060, wait a few seconds, and retry the transaction.
This ensures smoother user experience without manual DBA intervention.

Example pseudocode:

try:
    execute_transaction()
except ORA_00060:
    time.sleep(3)
    retry_transaction()

---

Real-World Example

During a high-volume banking batch process, our system started throwing ORA-00060 every few minutes.
After investigating the trace files, we discovered a foreign key without an index on a child table that multiple sessions were updating concurrently.

We created the missing index:

CREATE INDEX idx_txn_parent_id ON transactions(parent_id);

Immediately, the deadlocks disappeared.
Lesson learned — never skip foreign key indexes, even in non-critical tables.

---

Proactive Prevention Tips

1. Index all foreign keys
2. Maintain consistent transaction ordering
3. Keep transactions short
4. Enable retry logic in apps
5. Regularly monitor AWR or ASH for locking patterns

---

Related Articles

• Oracle Grid Infrastructure 19c Restart Home Installation — Complete Step-by-Step Guide
• How To Download And Install The Latest OPatch
• How to Fix “ORA-01111: Name for Data File Is Unknown” in Oracle Standby Databases

---

Final Thoughts

The ORA-00060: Deadlock Detected error isn’t a database bug — it’s a logical design or transaction handling issue.
By keeping your transactions short, indexing foreign keys, and maintaining consistent locking order, you can eliminate deadlocks entirely.

In the end, preventing deadlocks is about good database design and predictable application behavior — not luck.