Oracle 'enq: TX - row lock contention': blocking sessions and uncommitted DML

The classic symptom is partial slowness. Some transactions succeed. Some hang indefinitely. Application logs show threads stuck inside database calls. There are no ORA- errors in the alert log, CPU is low, I/O latency is normal, the instance is OPEN, and the listener responds.

When you query V$SESSION for the waiters, they are all parked on the same event: enq: TX - row lock contention. When you walk BLOCKING_SESSION up the chain, you usually land on a session whose STATUS is INACTIVE and whose wait is SQL*Net message from client. That session is sitting idle with an open, uncommitted transaction. One stuck holder, many waiters behind it.

The bottleneck is application logic that opened a transaction and never closed it. The operational first response is still the same: identify the blocker, confirm it is safe to interrupt, and decide whether to kill it.

What this means

Oracle uses row-level locking with no lock escalation. When a session modifies a row, the lock is embedded in the data block: a lock byte in the row header, a transaction ID in the block’s Interested Transaction List (ITL), and an undo entry holding the before-image. Another session that tries to modify the same row, or to read it in current mode for DML, must wait for the holder to either commit or roll back.

That wait is reported as enq: TX - row lock contention. It is a normal serialization mechanism in small doses. It becomes an incident when the holder never commits.

The wait has two common sub-modes that you must distinguish before acting:

• Mode 6 (exclusive row lock). The waiter and the blocker want the same row. For the enqueue parameter name|mode, P1RAW ends in the lock mode 0006 (for example, TX mode 6 is 0x54580006). The fix is on the holder side: commit, roll back, or kill the session.
• Mode 4 (share, ITL or bitmap). P1RAW ends in 0004. The sessions may be updating different rows in the same block and contending for a free ITL slot, fighting over a bitmap index entry, or inserting rows that collide on a unique constraint. Killing a session does not help; the fix is structural (INITRANS, bitmap index removal, constraint design).

The blocker is usually INACTIVE. Its current SQL_ID is frequently NULL because it finished executing and is now waiting for the client. BLOCKING_SESSION in V$SESSION is populated from the waiter’s perspective; the blocker itself does not know it is blocking anyone. In RAC, use GV$SESSION.BLOCKING_INSTANCE and BLOCKING_SESSION together, because the blocker may live on another node.

Oracle’s deadlock detector resolves true cycles in roughly three seconds by sacrificing one statement with ORA-00060. If you see long TX waits with no ORA-00060 in the alert log, you do not have a deadlock; you have a long-held lock.

Common causes

Quick checks

All of the queries below are read-only and safe to run during an incident.

-- 1. Current TX enqueue waiters, oldest 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: TX%' AND s.STATE = 'WAITING'
ORDER BY s.SECONDS_IN_WAIT DESC;

-- 2. Blocker / waiter pairs (built-in views, no joins needed)
SELECT * FROM DBA_WAITERS;
SELECT * FROM DBA_BLOCKERS;

-- 3. Lock detail, including the locked object and lock mode
SELECT l.SID, l.TYPE, l.LMODE, l.REQUEST, l.BLOCK,
       o.OWNER || '.' || o.OBJECT_NAME AS 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.TYPE = 'TX' AND (l.BLOCK = 1 OR l.REQUEST > 0);

-- 4. The blocker itself: status, user, machine, last SQL (sql_id is often NULL on idle holders)
SELECT s.SID, s.SERIAL#, s.STATUS, s.USERNAME, s.MACHINE, s.PROGRAM,
       s.LOGON_TIME, s.EVENT, s.SECONDS_IN_WAIT,
       COALESCE(s.SQL_ID, s.PREV_SQL_ID) AS last_sql_id
FROM V$SESSION s
WHERE s.SID = &blocker_sid;

-- 5. What row is the waiter stuck on (run for one waiter SID)
SELECT o.OWNER || '.' || o.OBJECT_NAME AS object_name,
       DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#,
                               s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#) AS locked_rowid
FROM V$SESSION s
LEFT JOIN DBA_OBJECTS o ON s.ROW_WAIT_OBJ# = o.OBJECT_ID
WHERE s.SID = &waiter_sid
  AND s.ROW_WAIT_OBJ# <> 0;

-- 6. RAC: see blockers and waiters across instances
SELECT INST_ID, SID, SERIAL#, EVENT, BLOCKING_INSTANCE, BLOCKING_SESSION
FROM GV$SESSION
WHERE EVENT LIKE 'enq: TX%' AND STATE = 'WAITING';

-- 7. Long-held exclusive TX locks (the silent holder signal)
SELECT SID, TYPE, LMODE, CTIME
FROM V$LOCK
WHERE TYPE = 'TX' AND LMODE = 6 AND CTIME > 300
ORDER BY CTIME DESC;

-- 8. Has this already hit the process/session ceiling?
SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ('sessions', 'processes');

How to diagnose it

flowchart TD
    A["Waiters on enq: TX"] --> B{"P1RAW mode?"}
    B -->|"0006 row lock"| C["Walk BLOCKING_SESSION chain"]
    B -->|"0004 ITL / bitmap"| H["Structural fix: INITRANS / index"]
    C --> D{"Blocker STATUS?"}
    D -->|"INACTIVE, idle"| E["Holder is an open transaction"]
    D -->|"ACTIVE, long DML"| F["Batch job or large TX"]
    E --> G{"Safe to interrupt?"}
    F --> G
    G -->|"Yes"| I["ALTER SYSTEM KILL SESSION"]
    G -->|"No, business-critical"| J["Wait; notify app owner"]
    I --> K["Verify waiters drain"]

Work through the cascade in this order:

1. Confirm the symptom is TX, not TM. Run query 1. If the wait is enq: TM - contention, stop and look for an unindexed foreign key on the table being modified. TM contention has a different fix and a different blast radius.
2. Distinguish mode 6 from mode 4. Look at P1 / P1RAW on the waiters. Mode 4 means ITL, bitmap, or unique-constraint contention; killing the holder will not help and may make it worse if the holder is a legitimate batch job. Check V$SEGMENT_STATISTICS for ITL waits on the object.
3. Walk the blocking chain. Start from query 1, take the BLOCKING_SESSION, and re-query V$SESSION for that SID. If BLOCKING_SESSION_STATUS is VALID but BLOCKING_SESSION is NULL, suspect a distributed or XA in-doubt transaction (see the XA row in the causes table).
4. Profile the blocker. Run query 4. If STATUS is INACTIVE and EVENT is SQL*Net message from client, the holder is an idle session with an open transaction. SQL_ID is frequently NULL on idle sessions; fall back to PREV_SQL_ID and pull the text from V$SQL.
5. Find the SQL text. SELECT SQL_TEXT, SQL_FULLTEXT FROM V$SQL WHERE SQL_ID = :last_sql_id; This tells you whether the open transaction is an application statement, a manual tool, or a batch job.
6. Find the locked object and row. Run query 5 against a waiter. ROW_WAIT_OBJ# of 0 means Oracle has not yet resolved the row pointer; try a different waiter or wait a moment.
7. Measure blast radius. Run queries 7 and 8. If the blocker has more than 10 waiters, or CURRENT_UTILIZATION is approaching LIMIT_VALUE, you are inside a contention cascade that can exhaust PROCESSES and refuse new connections with ORA-00020.
8. Decide. If the holder is an idle session or a non-critical job, killing it is the correct operational move. If it is a critical batch job, contact the application owner first and only kill if business impact justifies the rollback cost.

Metrics and signals to monitor

Fixes

Kill the blocking session

When the holder is an idle session or a non-critical job, this is the correct first move. It rolls back the open transaction and releases every waiter immediately.

-- Disruptive: rolls back the holder's transaction.
-- Verify SID and SERIAL# from V$SESSION first.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

IMMEDIATE does not make the kill faster; it tells PMON to roll back the transaction and release locks without waiting for the session to acknowledge. On RAC, add the instance: ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id'. If the session is stuck in a state PMON cannot clean up, DISCONNECT SESSION 'sid,serial#' IMMEDIATE drops the connection at the network layer and forces cleanup.

Killing resolves the symptom. It does not fix the root cause. The same application code will produce the same blocker again.

Have the application commit or roll back

If the holder is a legitimate transaction that the business needs to complete, the only correct action is to let it finish. Killing it discards its work. The root fix belongs in the application: explicit COMMIT or ROLLBACK at the end of every transactional unit, including error paths. Missing ROLLBACK in exception handlers is a frequent cause of orphaned open transactions.

Break up long batch jobs

A batch job that updates millions of rows in one transaction holds every row lock until the final commit. The structural fix is to commit in batches, on the order of thousands of rows per transaction, with a bounded transaction size. The tradeoff is restartability: if the job dies mid-way, you need a way to resume from the last committed point. Do not commit per row; that turns the bottleneck into log file sync.

Resolve ITL contention (mode 4)

If P1RAW ends in 0004 and the sessions are touching different rows in the same block, the block’s ITL is too small. Increase INITRANS and rebuild the segment:

-- Structural change; requires maintenance window.
ALTER TABLE schema.tab MOVE INITRANS 16;
ALTER INDEX schema.idx REBUILD INITRANS 16;

Oracle now allows up to 255 concurrent update transactions per data block, subject to available block space; MAXTRANS is deprecated. Existing objects can retain an earlier MAXTRANS setting, but changing it is ignored and 255 is substituted. INITRANS still controls the initially reserved allocation. On freelist-managed segments, INITRANS is the hard minimum and ITL contention is more common. If the contended object is a bitmap index, replace it with a normal B-tree where concurrency requires it.

Clear in-doubt distributed transactions

When BLOCKING_SESSION is NULL but TX waiters exist, suspect an XA branch with no session attached. Query DBA_2PC_PENDING for in-doubt transactions. RECO resolves these automatically in normal operation; when it does not, force the outcome using the LOCAL_TRAN_ID or GLOBAL_TRAN_ID from DBA_2PC_PENDING:

-- Disruptive: forces commit or rollback of an in-doubt transaction.
-- Consult MOS Doc ID 1248848.1 for the full procedure.
-- Verify the transaction ID from DBA_2PC_PENDING before running.
ROLLBACK FORCE 'local_tran_id';   -- safer when in doubt
-- or
COMMIT FORCE 'local_tran_id';

Do not force commit unless you are certain the transaction should be committed; forcing rollback is safer when in doubt. Normally RECO purges correctly resolved rows; use DBMS_TRANSACTION.PURGE_MIXED for mixed outcomes and PURGE_LOST_DB_ENTRY only for the documented lost-remote-database case.

Remove the unindexed foreign key (TM contention)

If the wait event is actually enq: TM - contention, the cause is almost always an unindexed FK on a child table being modified while the parent is updated or deleted. Add an index on the FK columns.

Prevention

• Commit discipline. Every transaction in the application, including error paths, must end in COMMIT or ROLLBACK. Audit exception handlers specifically; missing rollback is the most common cause.
• Idle session reaping. Set IDLE_TIME in the user profile, and configure SQLNET.EXPIRE_TIME so dead connections are detected and cleaned up. A dead client holding an open transaction is a classic cascade trigger.
• Long-held lock alerts. Alert on V$LOCK rows with TYPE='TX', LMODE=6, CTIME > 300. This catches the idle holder before the waiters pile up.
• Index every foreign key. Make it a code-review rule. TM contention is fully preventable.
• Size INITRANS for hot tables. For tables with high concurrent DML on the same block, set INITRANS to 8 or higher at table creation. Rebuilding later is disruptive.
• Use SKIP LOCKED for queue patterns. Documented from the 11g-era SQL language reference and available in later releases, SELECT ... FOR UPDATE SKIP LOCKED lets queue consumers skip locked rows instead of waiting, which removes row-lock waiting from work queues.
• Connection pool backoff. When the pool detects blocked sessions, it should not open new connections. Retrying in a tight loop turns one slow transaction into a process exhaustion incident.

How Netdata helps

• Per-second collection of V$SYSTEM_EVENT makes the moment TX wait time starts climbing visible immediately, rather than at the next AWR snapshot. The leading indicator is the slope of the curve, not the absolute value.
• Correlating TX enqueue time with the session/process utilization signal from V$RESOURCE_LIMIT shows the cascade forming before ORA-00020 starts refusing connections.
• Anomaly detection on the active-session count by wait class surfaces a sudden jump in the Application wait class (where enq: TX lives) even when the absolute count is small for the hardware.
• A blocking-chain-depth gauge would make the difference between “one slow session” and “one holder with 50 waiters” obvious on a single chart.
• Pairing enqueue waits with the log file sync and log file parallel write signals rules out the redo path as the cause, narrowing the diagnosis to application locking.
• Alerting on V$LOCK rows with TYPE='TX', LMODE=6, CTIME > 300 catches the silent idle holder before users notice.

See Oracle Database monitoring with Netdata for the full metric set.

Related guides

• ORA-00060: deadlock detected while waiting for resource
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle lock contention cascade: one idle session that stalls the whole application
• Oracle Database monitoring checklist: the signals every production instance needs
• How Oracle Database actually works in production: a mental model for operators
• Oracle Database monitoring maturity model: from survival to expert
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle slow commit cascade: when redo storage degrades and every transaction waits
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle redo log switch frequency: undersized logs and checkpoint pressure
• Oracle redo generation rate: capacity planning for archiving and Data Guard