Home / Databases / Oracle

DatabasesTroubleshootingIntermediate

Oracle blocking sessions and \"enq: TX - row lock contention\" - finding the blocker, the SQL and the object

When Oracle sessions hang on enq TX row lock contention, find the root blocker with v$session, identify the locked object and row, see what the blocker is doing, then fix it at the application rather than killing sessions by habit.

Deepak Bhardwaj5 min read

Applies toOracle Database 19c and 23ai (single instance and RAC); Amazon RDS for Oracle; OCI Base Database and Exadata

Symptoms

  • Users or app threads hang on simple updates.
  • AWR/ASH or monitoring shows the wait event enq: TX - row lock contention at the top.
  • Connection pools fill up because sessions don't return.

Oracle locks rows, not pages or tables. A session waits on enq: TX - row lock contention when it wants to change a row that another session has changed but not yet committed. The cause is almost always an uncommitted transaction, not a database fault.

1. Who is blocked and who is blocking?

SELECT s.inst_id, s.sid, s.serial#, s.username, s.program, s.event,
       s.seconds_in_wait, s.blocking_instance, s.blocking_session,
       s.final_blocking_instance, s.final_blocking_session, s.sql_id
FROM gv$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

final_blocking_session points straight to the root blocker in a chain, even across RAC instances.

Look at the root blocker:

SELECT inst_id, sid, serial#, username, osuser, machine, program, module, action,
       status, event, last_call_et, sql_id, prev_sql_id, logon_time
FROM gv$session
WHERE (inst_id, sid) IN ((&blocker_inst, &blocker_sid));
Blocker state Meaning
status = INACTIVE, event SQL*Net message from client, large last_call_et Changed rows and is waiting for the app; never committed (the most common case)
ACTIVE running a long DML Large update or delete still running
ACTIVE, itself waiting on something else Chain; the real problem is further along

2. What SQL and which object?

The blocker's current SQL is often not the statement that took the lock. Check prev_sql_id too:

SELECT sql_id, sql_text FROM gv$sql
WHERE sql_id IN ('&sql_id', '&prev_sql_id') AND inst_id = &blocker_inst;

The object and row the waiter wants:

SELECT s.sid, o.owner, o.object_name, o.object_type,
       s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#
FROM gv$session s
JOIN dba_objects o ON o.object_id = s.row_wait_obj#
WHERE s.event = 'enq: TX - row lock contention';

Build the ROWID and fetch the row:

SELECT * FROM app.orders
WHERE rowid = DBMS_ROWID.ROWID_CREATE(1, &row_wait_obj#, &row_wait_file#, &row_wait_block#, &row_wait_row#);

For a data object ID different from the object ID (partitions, after moves), use dba_objects.data_object_id in ROWID_CREATE.

3. How long has the transaction been open?

SELECT s.inst_id, s.sid, s.username, t.start_time, t.used_urec AS undo_records, t.used_ublk AS undo_blocks
FROM gv$transaction t
JOIN gv$session s ON s.saddr = t.ses_addr AND s.inst_id = t.inst_id
ORDER BY t.start_time;

A transaction open for hours with few undo records is a forgotten session, often an ad-hoc tool (SQL Developer, Toad) with autocommit off.

4. Resolve it

Prefer this order:

  1. Ask the owner to commit or roll back. The session's osuser, machine and program tell you who it is.
  2. Kill the blocking session if the owner can't be reached and the business impact justifies it:
ALTER SYSTEM KILL SESSION '&sid,&serial#,@&inst_id' IMMEDIATE;

Warning

Killing the blocker rolls back its transaction. A large transaction can take a long time to roll back and keeps holding its locks until rollback finishes. Check used_urec first, and tell the owner that their changes will be lost.

On Amazon RDS for Oracle, use EXEC rdsadmin.rdsadmin_util.kill(sid => &sid, serial => &serial);.

5. Other TX enqueue modes

The event name tells you the kind of contention:

Event Usual cause
enq: TX - row lock contention Same row changed by an uncommitted transaction; also unique key conflicts on insert, and unindexed foreign keys
enq: TX - index contention Index block splits on a hot right-hand index (sequence keys)
enq: TX - allocate ITL entry Too many concurrent transactions in one block; raise INITRANS
enq: TM - contention Unindexed foreign key columns when the parent table is updated or deleted

Find unindexed foreign keys, which is a classic cause of TM and TX waits:

SELECT c.owner, c.table_name, cc.column_name, c.constraint_name
FROM dba_constraints c
JOIN dba_cons_columns cc ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
WHERE c.constraint_type = 'R' AND c.owner = 'APP'
  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 AND ic.column_position = cc.position);

6. History (after the event)

SELECT sample_time, session_id, blocking_session, sql_id, event, current_obj#
FROM dba_hist_active_sess_history
WHERE event = 'enq: TX - row lock contention'
  AND sample_time > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY sample_time;

ASH and AWR require the Diagnostics Pack licence. Without it, use Statspack or your own sampling of v$session.

Prevent it next time

  • Applications commit promptly. No user think time inside a transaction.
  • Idle-in-transaction limits: Oracle profiles (IDLE_TIME) or app-side timeouts. Note that IDLE_TIME doesn't distinguish open transactions from idle sessions.
  • Index foreign key columns.
  • Alert on sessions blocking others for longer than N minutes.

Checklist

  • Root blocker found with final_blocking_session
  • Blocker's SQL (current and previous), user and program identified
  • Locked object and row known
  • Owner contacted before killing
  • Rollback size checked before kill
  • Root cause fixed in the application or schema

Related fixes