Oracle lock contention cascade: one idle session that stalls the whole application

An application opens a transaction, updates a few rows on a hot table, and never commits. A developer’s SQL tool is waiting for input, a connection pool returned a dirty connection, or a batch job is mid-update. From the database’s perspective the session is INACTIVE and waiting on SQL*Net message from client. From the application’s perspective, every other transaction touching those rows is stuck.

What follows is the lock contention cascade. Waiters queue on enq: TX - row lock contention. Application connection pools, seeing requests time out, spin up more sessions. Those new sessions hit the same rows and queue too. Process and session counts climb toward the PROCESSES and SESSIONS limits. Eventually ORA-00020 starts refusing new connections, the pool is wedged, and a single stuck session has become an application-wide outage.

The database looks healthy to the wrong checks. Instance is OPEN. Listener responds. CPU is low. Throughput is collapsing.

What this means

A TX enqueue in mode 6 (exclusive row lock) is Oracle’s normal mechanism for serializing updates to the same row. The cascade becomes an incident when the holder stops making progress without rolling back. The distinguishing signals are:

• Waiters accumulate on enq: TX - row lock contention (or enq: TM - contention for unindexed foreign keys).
• The session pointed to by V$SESSION.BLOCKING_SESSION is INACTIVE and typically shows the idle event SQL*Net message from client.
• V$SESSION.SQL_ID for the blocker is often NULL because the session is between statements, not currently executing.
• Transactions on different rows still succeed, so the outage looks partial and intermittent from the application side.
• TPS drops even though the instance, listener, and storage all look fine.

This pattern is distinct from the Archive Hang, where every session waits on log file switch (archiving needed) and no transactions succeed at all. See the archive hang pattern for that failure mode. In a lock cascade some transactions succeed; in an archive hang everything that generates redo freezes.

flowchart TD
    A[Session S1: uncommitted DML on hot rows] --> B[TX lock mode 6 held]
    B --> C[S2 tries to update same rows]
    C --> D[Waits on enq: TX - row lock contention]
    D --> E[App pool: request timeout]
    E --> F[Pool opens more sessions]
    F --> G[New sessions also block on same rows]
    G --> H[Session/process count climbs]
    H --> I[PROCESSES limit hit: ORA-00020]
    I --> J[Application-wide outage]

Common causes

Quick checks

Run these as a DBA-privileged user. All are read-only.

-- 1. Who is waiting on enq: TX right now, and who is blocking them
SELECT SID, SERIAL#, USERNAME, EVENT, SECONDS_IN_WAIT,
       BLOCKING_SESSION, BLOCKING_SESSION_STATUS
FROM V$SESSION
WHERE EVENT LIKE 'enq: TX%' AND STATE = 'WAITING'
ORDER BY SECONDS_IN_WAIT DESC;

-- 2. Count of waiters per blocker (the cascade signature)
SELECT BLOCKING_SESSION, COUNT(*) AS waiters,
       MAX(SECONDS_IN_WAIT) AS oldest_wait_sec
FROM V$SESSION
WHERE BLOCKING_SESSION IS NOT NULL
GROUP BY BLOCKING_SESSION
ORDER BY waiters DESC;

-- 3. Profile the blocker session itself
SELECT SID, SERIAL#, USERNAME, STATUS, SERVER, PROGRAM, OSUSER, MACHINE,
       EVENT, SQL_ID, PREV_SQL_ID, SQL_EXEC_START, MODULE, ACTION
FROM V$SESSION
WHERE SID IN (
  SELECT BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL
);

-- 4. Long-held TX exclusive locks (the proactive check)
SELECT S.SID, S.SERIAL#, S.USERNAME, S.STATUS, S.PROGRAM,
       L.TYPE, L.LMODE, L.REQUEST, L.CTIME
FROM V$LOCK L JOIN V$SESSION S ON L.SID = S.SID
WHERE L.TYPE = 'TX' AND L.LMODE = 6 AND L.CTIME > 300
ORDER BY L.CTIME DESC;

-- 5. What objects are locked, by which session
SELECT LO.SESSION_ID, S.USERNAME, O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
FROM V$LOCKED_OBJECT LO
JOIN DBA_OBJECTS O ON LO.OBJECT_ID = O.OBJECT_ID
JOIN V$SESSION S ON LO.SESSION_ID = S.SID
ORDER BY LO.SESSION_ID;

-- 6. Lock chain depth via DBA_WAITERS / DBA_BLOCKERS
-- Requires catblock.sql to have been run; not available on all instances
SELECT * FROM DBA_WAITERS;
SELECT * FROM DBA_BLOCKERS;

-- 7. How close are we to the process/session limit
SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ('sessions', 'processes');

-- 8. TPS sanity check (run twice, a few seconds apart, then delta)
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME IN ('user commits', 'user rollbacks');

-- 9. Largest in-flight transactions (relevant when batch is the blocker)
SELECT S.SID, S.SERIAL#, S.USERNAME, S.SQL_ID, S.STATUS,
       T.USED_UBLK AS used_undo_blocks
FROM V$TRANSACTION T
JOIN V$SESSION S ON T.SES_ADDR = S.SADDR
ORDER BY T.USED_UBLK DESC;

How to diagnose it

1. Confirm the pattern. Run check 1 and check 2 together. Many waiters on enq: TX pointing at one or a small number of blocker SIDs is a cascade. Waiters spread across many unrelated blockers is a systemic locking issue (often an unindexed FK driving TM contention), not a single-session cascade.

2. Identify the real blocker, not the apparent one. BLOCKING_SESSION can chain: A blocks B, B blocks C, and C’s row points at B. Walk the chain by repeatedly resolving the blocker’s own BLOCKING_SESSION until it is NULL. The session at the head of the chain is the true root.

3. Classify the blocker. From check 3, note:

   • STATUS: INACTIVE points at abandoned or think-time transactions; ACTIVE points at a long-running statement.
   • PROGRAM and MACHINE: a sqldeveloper.exe, toad.exe, or ad-hoc client from a workstation is almost always a human-driven mistake.
   • SQL_EXEC_START: if this is hours old on an INACTIVE session, the transaction has been open that long.
   • SQL_ID is frequently NULL because the session is between statements. PREV_SQL_ID is sometimes populated, but it can be misleading because it changes as cursors age out.

4. Recover the blocking DML when SQL_ID is NULL. This is the operator failure point most teams hit. When the blocker is INACTIVE, V$SESSION.SQL_ID is gone. Two paths:

   • Join V$LOCKED_OBJECT to DBA_OBJECTS (check 5) to identify the locked objects, then correlate with application code paths that update those tables.
   Some operators recover the actual SQL text by joining `V$OPEN_CURSOR` to `V$LOCKED_OBJECT.SESSION_ID` and inspecting pinned cursors. Reliability depends on whether the cursor is still open and is version-dependent.
   • For deterministic confirmation, LogMiner against the redo for the locked transaction can show the exact statements. This is heavyweight for an active incident.

5. Decide whether to kill. The decision matrix:

   • Blocker is a developer tool or interactive session: kill it.
   • Blocker is a batch job in the middle of a controlled long transaction: do not kill unless business impact justifies losing the work. Coordinate with the job owner first.
   • Blocker is a distributed transaction in doubt: resolve via DBA_2PC_PENDING (force commit or rollback) rather than killing the session.

6. Verify resolution. After acting, re-run check 1 and check 2. The waiter count should drop within seconds. TPS (check 8) should recover. If waiters persist, there was more than one blocker or the kill did not roll back the transaction cleanly.

Metrics and signals to monitor

The single highest-value proactive signal is the V$LOCK long-held TX check. It catches the pattern that pure wait-event monitoring misses, because the holder sits on SQL*Net message from client and looks idle.

Fixes

Immediate: break the chain

When business impact justifies it, the immediate fix is to terminate the blocker and let Oracle roll back the transaction:

-- Destructive: kills the session and rolls back its transaction
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

IMMEDIATE rolls back ongoing transactions, releases session locks, and recovers session state without waiting for the session to reach a clean interrupt point. Without it, the kill is deferred until the session reaches a state Oracle considers safe to terminate. Verify rollback progress:

-- STATE = 'RECOVERING' with growing UNDOBLOCKSDONE means rollback is in progress
SELECT * FROM V$FAST_START_TRANSACTIONS;

For RAC, the kill must target the instance where the session actually runs. Use GV$SESSION.BLOCKING_INSTANCE to find it, then connect to that instance or use the instance qualifier in the KILL command. Killing a session whose work was a four-hour batch update will roll back for hours; weigh that cost before pulling the trigger.

In Oracle 23ai, Automatic Transaction Rollback can short-circuit some cascades by automatically rolling back low-priority holders when high-priority waiters are blocked.

Resolve a distributed in-doubt transaction

If the blocker is a distributed transaction left in a prepared state, RECO normally resolves it. When RECO cannot (network to the coordinator is gone, coordinator dead), the lock persists:

-- Inspect pending in-doubt transactions
SELECT * FROM DBA_2PC_PENDING;

-- Force commit or rollback after confirming with the application owner
-- COMMIT FORCE 'local_tran_id';
-- ROLLBACK FORCE 'local_tran_id';

Decide force-commit vs force-rollback based on whether the other branches already committed. Wrong choices here cause data inconsistency, so verify the global state before acting.

Fix the unindexed foreign key (TM cascade)

If the wait event is enq: TM - contention rather than enq: TX, the cause is almost always an unindexed foreign key on the child table. When the parent row is updated or deleted, Oracle escalates to a table-level lock on the child. The fix is to create an index on the FK column. Identify candidates:

SELECT C.OWNER, C.TABLE_NAME, C.CONSTRAINT_NAME, CC.COLUMN_NAME
FROM DBA_CONSTRAINTS C
JOIN DBA_CONS_COLUMNS CC USING (OWNER, CONSTRAINT_NAME)
WHERE C.CONSTRAINT_TYPE = 'R'
  AND NOT EXISTS (
    SELECT 1 FROM DBA_IND_COLUMNS IC
    WHERE IC.TABLE_OWNER = C.OWNER
      AND IC.TABLE_NAME = C.TABLE_NAME
      AND IC.COLUMN_NAME = CC.COLUMN_NAME
  );

This query flags single-column FKs without a matching index. Composite FKs need the full leading column set checked; expand the subquery for those cases.

Clean up the connection pool

If the same blocker SIDs recur from the pool across incidents, the application is returning connections with open transactions. Fix in the application:

• Ensure every code path, including exception handlers, either commits or rolls back before returning the connection.
• Verify the pool’s release semantics. Some pools reset the session, others do not.
• Enable SQLNET.EXPIRE_TIME (dead connection detection) so abandoned client connections are eventually cleaned up by the server rather than holding state indefinitely.

Prevention

• Alert on V$LOCK TYPE=‘TX’, LMODE=6, CTIME > 300. This is the single best proactive signal for the idle-holder pattern. Anything older than five minutes is suspicious on an OLTP system.
• Alert on blocker fan-out: any BLOCKING_SESSION value with more than 10 waiters, or any session blocked more than 5 minutes with a growing chain.
• Track V$RESOURCE_LIMIT.MAX_UTILIZATION daily. If MAX_UTILIZATION has touched the limit at any point since startup, you have already had silent ORA-00020s even if the current count is low.
• Instrument the application so connection acquire and release events carry the user and request ID. The database SID alone is rarely enough to find the offending code path during an incident.
• Review batch jobs for transaction scope. A million-row update in one transaction is both a lock cascade risk and an undo pressure risk (see the snapshot too old failure mode). Chunked commits trade undo and lock duration for resumability.
• Codify an FK index review into DDL change control. A new unindexed FK can turn a previously safe parent delete into a TM cascade.

How Netdata helps

• Per-second collection of V$SESSION wait-state aggregates surfaces an enq: TX spike within seconds of when waiters start queuing, before the cascade reaches the connection pool.
• Correlating V$RESOURCE_LIMIT utilization against the TX wait count shows the progression from “some waits” to “approaching ORA-00020” on a single chart.
• TPS drop detection (delta-computed from V$SYSSTAT user commits) fires alongside the wait event spike, confirming the waits are user-impacting rather than background noise.
• Alerting on the proactive V$LOCK long-held TX pattern (LMODE=6, CTIME > 300) catches the idle-holder pattern that pure wait-event monitoring misses, because the holder sits on SQL*Net message from client and looks healthy to dashboards focused on active waits.
• Anomaly detection on session count and wait-event distributions reduces false positives from short, expected batch-window contention.

See Oracle Database monitoring with Netdata for the full integration.

Related guides

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