Oracle blocking sessions: finding the blocker at the head of the chain

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

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. In V$LOCK, BLOCK=2 marks a lock that is not blocking any local node processes but may be blocking remote nodes — a value that appears only in RAC. 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 (available since 11g), 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 (BLOCKER_IS_VALID, NUM_WAITERS, CHAIN_ID); 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

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. The parameter is available starting with Oracle Database 19c.
• 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.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert
• ORA-00257: archiver error, connect internal only until freed
• ORA-01555: snapshot too old, rollback segment too small