Oracle Locking, Blocking and Deadlocks: A Practical Guide to ORA-00060

"The application is frozen." You log in, and everything looks healthy - CPU idle, plenty of memory, no I/O storm. The database is not slow; it is waiting. One session is holding a lock another session needs, and a queue has formed behind it. Locking, blocking and the dreaded deadlock (ORA-00060) are among the most misunderstood problems in Oracle, because they look like performance issues but are really concurrency issues. This guide explains how Oracle locking actually works, how to find the session at the head of the queue in seconds, how to read a deadlock trace, and how to fix the application patterns that cause it.

1. How Oracle Locking Works (The Part People Get Wrong)

Oracle's concurrency model is its crown jewel, and it rests on two rules that surprise people from other databases:

• Readers never block writers, and writers never block readers. A query sees a consistent snapshot via undo (the mechanism behind read consistency), so it never has to wait for a session that is changing data.
• Writers block writers only on the same row. When you UPDATE a row, Oracle places a row-level lock on just that row until you commit or roll back. Another session updating a DIFFERENT row proceeds freely; one updating the SAME row waits.

There is no lock manager maintaining a giant list, and Oracle does not escalate row locks to table locks under pressure the way some databases do. Locks are stored in the data block itself, so you can lock millions of rows without running out of 'lock memory'. This design means blocking, when it happens, is almost always about one specific row and one specific slow transaction - not about the database being 'overloaded'.

2. Blocking: Normal Until It Isn't

Blocking is not inherently a problem. If session A updates a row and session B wants the same row, B waits - correctly - until A commits. That is data integrity working. It becomes a problem only when A holds the lock far too long and a queue builds up behind it.

The classic cause is a transaction that stays open across user think-time: an application selects a row FOR UPDATE, then waits for the user to click 'Save' - and the user goes to lunch. Every other session needing that row is now stuck behind a coffee break. The wait event you will see is enq: TX - row lock contention.

3. Finding the Blocker in Seconds

Forget hand-decoding V$LOCK. Modern Oracle tells you directly who is blocking whom:

-- Who is blocked, and who is blocking them?
SELECT s.sid, s.serial#, s.username, s.status,
       s.blocking_session        AS blocked_by_sid,
       s.event, s.seconds_in_wait,
       s.sql_id
FROM   v$session s
WHERE  s.blocking_session IS NOT NULL
ORDER  BY s.seconds_in_wait DESC;

-- Full blocking tree (who is the root blocker?)
SELECT LEVEL, s.sid, s.username, s.event, s.sql_id
FROM   v$session s
START WITH s.blocking_session IS NULL
       AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER  SIBLINGS BY s.sid;

BLOCKING_SESSION points straight at the culprit. The tree query finds the ROOT blocker - important when a chain has formed, because killing a session in the middle does nothing; you must clear the head.

-- See exactly what the blocker is (or isn't) doing:
SELECT sid, status, last_call_et AS idle_seconds, sql_id, machine, program
FROM   v$session WHERE sid = :root_blocker;
-- status=INACTIVE + large last_call_et = a transaction left open by the app

4. Clearing a Block - and Why Killing Is a Last Resort

The immediate fix is often to remove the blocker, but do it thoughtfully.

-- If the blocker is a genuine runaway or abandoned transaction:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Killing forces a rollback of that session's transaction, which can itself take time and undoes the user's work. Prefer, where possible, to contact the application or user to commit/close. The real fix is never the kill - it is stopping the pattern that created the long-held lock, which lives in the application (section 6). If you are killing blockers weekly, you have an application design problem wearing an operations costume.

5. Deadlocks and ORA-00060

A deadlock is a special, mutual block. Session A holds row 1 and wants row 2; session B holds row 2 and wants row 1. Neither can proceed, and neither will ever release - a true standoff.

Oracle handles this automatically. Its deadlock-detection code notices the cycle within seconds and breaks it by rolling back one statement (not the whole transaction) in one of the sessions, which then receives:

ORA-00060: deadlock detected while waiting for resource

This is important and widely misunderstood: a deadlock does not hang your database. Oracle resolves it in seconds by sacrificing one statement. The victim session gets the error; the other proceeds. So ORA-00060 is not a fatal event - it is Oracle protecting you from an application that asked for the impossible. But it IS a signal that your application has a lock-ordering bug.

6. Reading the Deadlock Trace

Every ORA-00060 writes a trace file (and an alert-log entry pointing to it). It contains a 'Deadlock graph' that names the exact rows, objects, and SQL involved - the evidence you need to fix the cause.

-- Find recent deadlock traces
SELECT name, value FROM v$diag_info WHERE name='Diag Trace';
-- look in that directory for *ora*.trc files around the alert-log timestamp

In the trace, the deadlock graph shows two sessions, what each HOLDS and what each WAITS-FOR, plus the SQL and the ROWIDs. Nine times out of ten it reveals the same story: two code paths updating the same two tables (or rows) in opposite order. That is the bug - and it is fixable.

7. The Real Fixes (All in the Application)

You cannot parameter your way out of deadlocks; the fixes are design changes:

• Consistent access order. The number-one cure. If every transaction updates parent before child, and table A before table B, cyclic deadlocks become impossible. Enforce a canonical order in the code.
• Short transactions. Commit as soon as the logical unit of work is done. Never hold locks across user interaction or external calls. The 'FOR UPDATE then wait for the user' pattern must die.
• SELECT ... FOR UPDATE NOWAIT / SKIP LOCKED. Instead of blocking indefinitely, fail fast (NOWAIT) or skip already-locked rows (SKIP LOCKED, ideal for queue-processing workers). This turns silent blocking into a handled condition.
• Right-size the work. Updating a million rows in one transaction holds a million locks for the whole duration; batching into smaller commits (where business rules allow) shortens lock lifetimes.
• Beware unindexed foreign keys. A missing index on a foreign key column causes Oracle to take a share lock on the whole child table when the parent key changes - a notorious, easily-fixed source of surprise blocking.

8. A Real Case: The Nightly Deadlock Storm

A manufacturing ERP logged a cluster of ORA-00060 errors every night between 1 and 2 AM. The application team assumed a database bug and asked us to 'increase the lock limit' (which does not exist). The deadlock trace told the real story in one read: two batch jobs - inventory revaluation and order settlement - both updated the ITEMS and LEDGER tables, but in opposite order. When they overlapped, they deadlocked, Oracle killed one statement, the job retried, and the noise repeated.

The fix touched no database parameter. We aligned both jobs to update ITEMS before LEDGER (a consistent order), and shortened an over-large transaction that had been holding locks for minutes. The nightly deadlocks stopped completely. The unindexed foreign key we found along the way got its index too, removing a separate daytime blocking issue. As with most concurrency problems, the database was behaving perfectly - it was faithfully executing an application that asked two jobs to grab the same locks in the wrong order.

Frequently Asked Questions

What is the difference between blocking and a deadlock in Oracle?

Blocking is one-directional and temporary: session B waits for a row that session A has locked, and proceeds once A commits. A deadlock (ORA-00060) is mutual: A holds what B needs and B holds what A needs, so neither can proceed. Oracle detects deadlocks and breaks them automatically by rolling back one statement; ordinary blocking it leaves alone.

Does a deadlock crash or hang the Oracle database?

No. Oracle's deadlock detection notices the cycle within seconds and resolves it by rolling back one statement in one session, which receives ORA-00060. The database keeps running and the other session proceeds. A deadlock is a signal of an application lock-ordering bug, not a database failure.

How do I find which session is blocking others?

Query V$SESSION where BLOCKING_SESSION IS NOT NULL - that column names the blocker directly. Use a CONNECT BY tree to find the root blocker when a chain has formed, and check the blocker's STATUS and LAST_CALL_ET: an INACTIVE session with a large idle time is usually a transaction the application left open.

What causes ORA-00060 deadlocks?

Almost always application design: two code paths updating the same tables or rows in opposite order, or long transactions holding locks across user think-time. The deadlock trace file names the exact SQL and rows. Fixes are in the application - consistent access order, short transactions, and NOWAIT/SKIP LOCKED - not database parameters.

How do I prevent blocking and deadlocks?

Keep transactions short and commit promptly; never hold locks across user interaction; enforce a consistent order for updating tables and rows; use SELECT ... FOR UPDATE NOWAIT or SKIP LOCKED to fail fast instead of waiting; and index foreign key columns to avoid whole-table share locks when parent keys change.