Blocking sessions are the most common cause of “the database is slow but everything looks healthy.” One session holds an uncommitted transaction on a row; every other session that wants to touch that row queues behind it on enq: TX - row lock contention. The instance is OPEN, the listener responds, CPU is often low, and TPS quietly decays. From the outside it looks like a performance regression rather than a lock incident.

The trap during diagnosis is that V$SESSION.BLOCKING_SESSION only points at a session’s immediate blocker, not the head of the chain. When you have three or four levels of dependency, killing the session that most waiters point at can either do nothing (the chain re-points one level up) or roll back a transaction that was doing legitimate work. The skill is finding the single root blocker, confirming it is genuinely idle, and killing only that one.

This guide covers the chain model, the views that resolve it correctly, the page thresholds, and the safe kill procedure. It assumes you already understand Oracle’s wait event model and enqueue types. See How Oracle Database actually works in production for that background.

What this means

Oracle enqueues serialize access to logical resources. The two you will see most in a blocking incident are enq: TX - row lock contention (a session has uncommitted DML on a row and another wants to modify the same row) and enq: TM - contention (almost always an unindexed foreign key locking the child table during a parent delete or update). Less common but worth knowing: enq: TX - allocate ITL entry means the block header needs more INITRANS, and enq: TX - index contention is transient leaf block splitting.

The chain forms like this: the head session holds a row lock and has not committed. A second session blocks on it, waiting for that row. If that second session has its own uncommitted transaction holding a different row, a third session can be blocked by the second. V$SESSION.BLOCKING_SESSION for the third session shows the second, not the head. Naive queries that count which SID appears most often as a BLOCKING_SESSION value will finger the intermediate session, and killing it rolls back a transaction whose locks were not the root cause.

flowchart TD
  H["Head blocker
  INACTIVE, uncommitted TX"]
  B["Session B
  blocked by H, holds own TX"]
  C1["Waiter C"]
  C2["Waiter D"]
  C3["Waiter E"]
  H -->|"holds TX lock"| B
  B -->|"enqueue wait"| C1
  B -->|"enqueue wait"| C2
  B -->|"enqueue wait"| C3

C, D, and E all show BLOCKING_SESSION pointing at B in V$SESSION. The head of the chain is H. Killing B accomplishes nothing useful; the waiters re-block on whatever B was waiting for, and you have rolled back B’s transaction for no gain.

A second trap: the head blocker almost always looks INACTIVE in V$SESSION. It is sitting on SQL*Net message from client, burning no CPU and doing no I/O. Standard “top sessions by CPU” dashboards will not surface it. You have to look specifically for enqueue waiters and walk the chain.

Page thresholds: any single blocker with more than 10 waiters, or any session blocked for more than 5 minutes with a growing chain, is a PAGE. The distinguishing feature versus the archive hang (the other “looks up but is frozen” pattern) is the wait event: a lock cascade shows enq: TX on a subset of sessions while other sessions still succeed, while an archive hang shows log file switch (archiving needed) across all sessions uniformly.

Common causes

CauseWhat it looks likeFirst thing to check
Application not committingBlocker is INACTIVE, one SQL_ID, enq: TX waits growing, TPS droppingV$SESSION for the blocker: STATUS, SQL_ID, SECONDS_IN_WAIT
Developer tool left a transaction openOne idle session, small undo footprint, many waitersV$SESSION.USERNAME and PROGRAM for the blocker
Unindexed foreign key (TM contention)enq: TM - contention, child table locked on parent DMLDBA_INDEXES for FK columns on objects in V$LOCKED_OBJECT
Batch job holding locks longOne ACTIVE session, large USED_UBLK in V$TRANSACTION, OLTP blockedV$TRANSACTION.USED_UBLK and V$SESSION_LONGOPS for the batch
Insufficient ITL slotsenq: TX - allocate ITL entry, many sessions on one hot blockSegment INITRANS

Quick checks

All of these are read-only and safe to run during an incident.

-- Current enqueue waiters, longest first
SELECT s.SID, s.SERIAL#, s.USERNAME, s.EVENT, s.SECONDS_IN_WAIT,
       s.SQL_ID, s.BLOCKING_SESSION, s.BLOCKING_SESSION_STATUS
FROM V$SESSION s
WHERE s.EVENT LIKE 'enq:%' AND s.STATE = 'WAITING'
ORDER BY s.SECONDS_IN_WAIT DESC;

-- Which sessions are blocking others right now (naive: shows immediate blockers, not heads)
SELECT BLOCKING_SESSION, COUNT(*) AS waiters
FROM V$SESSION
WHERE BLOCKING_SESSION_STATUS = 'VALID'
GROUP BY BLOCKING_SESSION
ORDER BY waiters DESC;

-- Blocker detail: is the head INACTIVE or ACTIVE?
SELECT SID, SERIAL#, USERNAME, STATUS, PROGRAM, SQL_ID,
       SECONDS_IN_WAIT, EVENT, BLOCKING_SESSION, BLOCKING_SESSION_STATUS
FROM V$SESSION
WHERE SID IN (
  SELECT BLOCKING_SESSION FROM V$SESSION
  WHERE BLOCKING_SESSION_STATUS = 'VALID'
);

-- Lock detail: who holds (BLOCK=1) vs who requests (REQUEST>0)
SELECT l.SID, l.TYPE, l.LMODE, l.REQUEST, l.BLOCK, l.CTIME,
       o.OWNER, o.OBJECT_NAME
FROM V$LOCK l
LEFT JOIN V$LOCKED_OBJECT lo ON l.SID = lo.SESSION_ID
LEFT JOIN DBA_OBJECTS o ON lo.OBJECT_ID = o.OBJECT_ID
WHERE l.BLOCK = 1 OR l.REQUEST > 0
ORDER BY l.BLOCK DESC, l.CTIME DESC;

In RAC, use GV$LOCK and GV$SESSION for cluster-wide visibility, otherwise you can miss a cross-instance blocker. The legacy DBA_BLOCKERS and DBA_WAITERS views are single-instance only and unreliable for RAC; prefer the GV$ views.

How to diagnose it

  1. Confirm the symptom is locking, not an archive hang or redo stall. The wait event must be enq: TX or enq: TM on a subset of sessions, with other sessions still succeeding.

  2. List the enqueue waiters ordered by SECONDS_IN_WAIT. The longest-waiting sessions are the ones closest to user-visible impact.

  3. Resolve the chain to its head. BLOCKING_SESSION alone is not enough for chains deeper than one level. Two reliable options:

    • Walk the chain manually. For each waiter, look up its BLOCKING_SESSION, then look up that session’s BLOCKING_SESSION, until you reach a session whose BLOCKING_SESSION is null or whose BLOCKING_SESSION_STATUS is not VALID. That session is the head.
    • Use FINAL_BLOCKING_SESSION on V$SESSION, which Oracle resolves internally to the head of the chain when FINAL_BLOCKING_SESSION_STATUS = 'VALID'.
    • V$WAIT_CHAINS is the dedicated view for multi-level chains; a row whose blocker is not valid is the head.

  4. Confirm the head is genuinely idle and not doing useful work. Check STATUS (INACTIVE is the classic smoking gun), SQL_ID (what it last ran), PROGRAM and USERNAME (a person in a SQL tool, a batch job, or a connection pool?), and SECONDS_IN_WAIT (how long on SQL*Net message from client).

  5. Capture everything before touching it. Record SID, SERIAL#, SQL_ID, USERNAME, PROGRAM, the waiter count, and USED_UBLK from V$TRANSACTION if the head has an open transaction. You will need this if the kill turns out to be the wrong call or if someone asks for a postmortem.

  6. Correlate with undo. If the head is a large uncommitted batch transaction, killing it rolls back a lot of undo and generates redo. Check V$TRANSACTION.USED_UBLK for the head’s SADDR. A multi-gigabyte rollback is itself an incident.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
enq: TX - row lock contention wait timePrimary lock-contention signalGreater than 5% of DB time sustained
Sessions with BLOCKING_SESSION setLive blocking chainsAny chain growing over minutes
Waiters per blockerCascade severityOne SID blocking more than 10 sessions
Max SECONDS_IN_WAIT on enqueue waitsHow long victims have been stuckAny session above 300 seconds
V$LOCK TX with LMODE=6 and CTIME above 300Long-held exclusive locks by idle sessionsAny non-batch session
TPS (user commits)Throughput decay from lockingMore than 50% drop from baseline
ORA-00060 in alert logDeadlocks, auto-resolved, separate issueAny sustained rate

Fixes

The only operational fix for an active lock cascade is to remove the head blocker. The right tool is almost always ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE. IMMEDIATE terminates the session without waiting for it to self-terminate; PMON still does the rollback.

Before you run it, verify you have the SID and SERIAL# of the head, not an intermediate session. If you are wrong, the chain re-points one level up and you have rolled back a transaction for nothing. If the head is a batch job mid-run, killing it may roll back hours of work. Talk to the owner first if there is time.

Kill commands are destructive. They roll back the target transaction, which generates redo and undo activity. Do not fire multiple parallel kills if the sessions are part of the same chain; let each one settle and re-check the chain. In RAC, append @inst_id to target a specific instance: ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id'.

For the underlying causes:

  • Unindexed foreign key. Add an index on the FK column. This permanently removes enq: TM - contention for that parent/child pair.
  • Insufficient ITL. Rebuild the segment or index with higher INITRANS (and MAXTRANS on older releases). This addresses enq: TX - allocate ITL entry.
  • Application not committing. This is an application fix, not a database fix. The kill is a bandage.

Oracle’s deadlock detector resolves true two-session cycles automatically by killing one statement with ORA-00060. If you are seeing massive enqueue waits but no ORA-00060 in the alert log, you do not have deadlocks. You have long-held locks, and the deadlock detector will not help you.

Prevention

  • Index every foreign key unless you have a documented reason not to. This is the single highest-value prevention step for locking incidents.
  • Monitor V$LOCK for TX exclusive locks with CTIME above 300. The playbook calls this out as a signal critical during incidents but rarely proactively monitored. Alert on it.
  • Push the application to use bind variables and explicit commits. Open transactions during user think time are the root cause of most cascades.
  • On 19c and later, consider MAX_IDLE_BLOCKER_TIME. It automatically terminates idle sessions that are holding blocking locks. Default is 0 (disabled). Set it below MAX_IDLE_TIME to be effective. It does not affect SYS sessions or parallel query slaves.
  • On 23ai, evaluate Priority Transactions and Lock-Free Reservations. Priority Transactions can auto-rollback a low-priority blocker holding a high-priority transaction; Lock-Free Reservations allow concurrent updates to hot numeric columns without blocking.

How Netdata helps

  • Per-second collection of active sessions and the dominant wait class means a lock cascade shows up as a Concurrency or Application wait spike within seconds of forming, long before users open tickets.
  • Correlating the enqueue wait spike against TPS, session count, and process utilization confirms whether the cascade is exhausting connection capacity, which is the escalation path from “slow” to “application down.”
  • Trending enqueue deadlock counts and lock-wait time over days surfaces applications that habitually hold locks too long, so the fix happens before the next 3 a.m. page.
  • Anomaly detection on the wait-class mix flags the moment the database shifts from CPU- or I/O-bound into Concurrency waits, which is the signature of a fresh blocking chain.

Netdata’s Oracle Database monitoring brings these signals together with per-second metrics and ML anomaly detection.