Scops
Docs Download

Oracle problems

enq: TX - row lock contention: The Row, the Holder, and the Fix

3 November 2026

enq: TX - row lock contention is the wait of a session that wants a row another transaction has changed and not yet committed. It is the most common lock wait in an OLTP database and the easiest to misread: the waiting session is the victim, the statement it runs is innocent, and the cause is a transaction somewhere else that is still open, often in a session that is sitting idle. Oracle's reference gives three reasons for the event. The first query below names the row, the second names the holder and how long it has held it, and the fix depends on which of the three reasons it is.

What the wait means

Oracle's reference says the event "can occur for several reasons" and lists three. The first is the ordinary one: one user wants to update or delete rows that another session is modifying, and "the session holding the lock will release it when it performs a COMMIT or ROLLBACK". The second is a unique key: two sessions insert the same value into a UNIQUE index, and the second "has to wait to see if an ORA-0001 should be raised or not", which is Oracle's own spelling of ORA-00001. The third is a bitmap index: each entry covers a range of rows, so two sessions updating rows under the same fragment wait on each other's TX lock. In V$LOCK the three look different. The waiter's REQUEST is 6, exclusive, when it wants the row; it is 4, share, for the unique key and the bitmap cases. The waits on index block splits and on transaction slots have events of their own, enq: TX - index contention and enq: TX - allocate ITL entry, both on the blocking sessions page.

Which row

V$SESSION carries the object, file, block and row the session is waiting for. DBMS_ROWID turns them into the rowid of the locked row:

SELECT w.sid, w.serial#, w.username, w.blocking_session AS holder,
       o.owner, o.object_name,
       DBMS_ROWID.ROWID_CREATE(1, o.data_object_id, w.row_wait_file#,
                               w.row_wait_block#, w.row_wait_row#)
                                               AS locked_rowid,
       ROUND(w.wait_time_micro / 1e6)          AS waited_s,
       w.sql_id
FROM   v$session w
JOIN   dba_objects o ON o.object_id = w.row_wait_obj#
WHERE  w.event = 'enq: TX - row lock contention';

Then the row itself, as the waiter will see it once the holder commits:

SELECT * FROM app.orders WHERE rowid = '&locked_rowid';

For a partitioned table the object is the partition, so the join returns the partition's name. A waiter with REQUEST 4 has no row to show: it is waiting on a key value or a bitmap fragment, and the statement in sql_id is the insert or update that carries the value.

Who holds it, and for how long

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,
       s.status, s.last_call_et AS idle_s, s.event, s.sql_id,
       s.prev_sql_id, t.start_date, t.used_urec AS undo_records
FROM   v$session s
LEFT   JOIN v$transaction t ON t.addr = s.taddr
WHERE  s.sid IN (SELECT blocking_session FROM v$session
                 WHERE event = 'enq: TX - row lock contention');

The row that matters is the holder's STATUS. INACTIVE with last_call_et in the thousands is the usual finding: the holder changed the row, the application went back to waiting for a person, a screen, or a web request that never came back, and the transaction stays open with its lock until a COMMIT, a ROLLBACK, or the end of the session. t.start_date is when that transaction began, and used_urec is roughly how many rows it has changed, which is also how long its rollback will take. prev_sql_id is the last statement the idle holder ran, often the UPDATE itself. An ACTIVE holder is a different case, a long transaction still running, and its own event says what it is doing. The same pair in V$LOCK, with the modes:

SELECT h.sid AS holder, h.lmode, w.sid AS waiter, w.request,
       h.id1, h.id2, ROUND(w.ctime / 60) AS waited_min
FROM   v$lock h
JOIN   v$lock w ON w.type = h.type AND w.id1 = h.id1 AND w.id2 = h.id2
WHERE  h.type = 'TX' AND h.block = 1 AND w.request > 0;

id1 and id2 identify the transaction, not the row, which is why the V$SESSION query above is the one that finds the row. A holder that is itself waiting on another TX lock is a chain, and the session at its end is the one to deal with: the final_blocking_session query on the root blocker page walks the chain in one statement.

The fix, in order

  1. Read the holder before you touch it. Idle, with an old start_date and a program that is a connection pool: the application has lost the transaction, and nobody is coming back to commit it. Active, in a batch: it will finish, and the question is whether the waiters can wait that long.
  2. End a lost transaction. ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE rolls it back; the rollback of a transaction with millions of undo records takes minutes, and the waiters stay until it is done. Tell the application owner first when the holder is a named user. The Resource Manager can do this for you: a plan directive with MAX_IDLE_BLOCKER_TIME ends a session that has been idle for that long while it blocks others, and nothing else, so an interactive user who holds no lock is left alone.
  3. Commit before the wait, not after. The lasting fix is in the application: update the row as the last step before the commit, never before a user interaction, a remote call, or a sleep. A transaction that opens a lock and then waits for a human is the cause of almost every finding on this page.
  4. Choose what to do when the row is taken. SELECT ... FOR UPDATE NOWAIT returns ORA-00054 at once instead of waiting; WAIT 5 waits five seconds; SKIP LOCKED takes the next free row, which is the right behaviour for a queue table or a work list that several workers share.
  5. Unique key waits: stop generating the same key twice. Two sessions inserting the same value wait on each other until one commits and the other gets ORA-00001. A sequence or an identity column gives every session its own value; an "insert if not exists" written as a SELECT followed by an INSERT is the usual source.
  6. Bitmap indexes belong on tables nobody updates. A bitmap index on a table with concurrent inserts and updates serialises the sessions on its fragments; a B-tree index on the same column does not.
  7. Hot rows by design. A counter row, a "last number" table, a status row every session updates: replace it with a sequence, or with a row per session that is aggregated later.
  8. Deadlocks are the same problem seen twice. ORA-00060 is two sessions each holding what the other wants; Oracle ends one statement and the trace file names both, and the fix is the same ordering of updates in both transactions.

See it coming

Radar Engine, the detection layer of Scops, watches every session of every database every 5 seconds, counts the sessions behind each blocker and knows how long a transaction usually stays open, so a lock that holds up a queue of sessions becomes a finding with the holder's name minutes before the first phone call:

DetectorClockWhen it fires
Sessions blockedLive, every 5 minutes10 sessions blocked for 3 minutes: Warning. 30 sessions, or 10 for 5 minutes: Critical.
Executions stalledLiveExecutions fall while sessions wait on one event or a blocker: Warning. 10% of normal for 5 minutes, or 30 sessions behind one blocker: Critical.
Concurrency or Application waits surgingLive, against the hour beforeTwice the hour before, well above its usual spread: Watch. With a second sign, such as 5 blocked sessions: Warning.
Long-running transactionHourlyThe largest open transaction at 3 hours, 2 GB of undo, and growing: Warning. 8 hours or 10 GB: Critical.
DeadlocksLive, against the hour beforeWatch. A burst of five, or a steady stream: Warning. Never Critical, because Oracle has already resolved each one.

The finding names the root blocker, its user, machine and program, how long it has been idle, and the sessions behind it, so the decision to kill is made with the facts. The event belongs to the Application wait class, and the Activity chart draws that class rising, with the blocked sessions and their statements, on any edition and without the Diagnostics Pack.

Questions

What does enq: TX - row lock contention mean?
A session wants a row that another transaction has changed and not yet committed, and it waits for that transaction's COMMIT or ROLLBACK. Oracle's reference also lists two rarer reasons: two sessions inserting the same value into a UNIQUE index, and two sessions updating rows under the same bitmap index fragment.

How do I find which row is locked?
V$SESSION carries ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW# for the waiting session; DBMS_ROWID.ROWID_CREATE turns them into the rowid, and a SELECT by rowid shows the row. BLOCKING_SESSION in the same row names the holder.

Why is the blocking session INACTIVE?
Because the lock belongs to its transaction, not to a running statement. The session changed the row, the application moved on without committing, and the lock stays until the transaction ends. LAST_CALL_ET in V$SESSION says how long it has been idle, and V$TRANSACTION.START_DATE when the transaction began.

Should I kill the blocking session?
When it is idle with an old transaction and a pooled connection, yes: nobody is coming back to commit it, and ALTER SYSTEM KILL SESSION rolls it back. When it is an active batch, let it finish or ask its owner. The lasting fix is a commit placed before the application waits for anything.

Related problems

Sources