How to Diagnose and Resolve Oracle TX Row Lock Contention

Understanding Oracle TX Locks

In Oracle databases, concurrency control relies on locks. DML operations acquire a table‑level TM lock followed by a transaction (TX) lock, which becomes an exclusive row lock (X lock) on the affected rows. The TX lock persists until the transaction commits or rolls back, and contention can cause ORA‑00060 errors and application hangs.

Handling TX Lock Waits

When a TX lock wait occurs, you can identify the blocking session by querying v$session.blocking_session or the v$lock view, then terminate the blocker with ALTER SYSTEM KILL SESSION to restore normal operation.

Locating the Exact Locked Row (Non‑Binding Variables)

Example with two sessions updating the same row without bind variables:

Session 1:
SQL> update t1 set b=10 where a=3;

Session 2:
SQL> update t1 set b=99 where a=3;

Query the SQL text of the waiting session:

select sql_text
from v$sql a, v$session b
where a.sql_id = b.sql_id
  and b.event = 'enq: TX - row lock contention';

The result shows the exact statement update t1 set b=99 where a=3, indicating that row a=3 is locked.

Locating the Locked Row (Binding Variables)

When bind variables are used, the same query returns a statement with placeholders, making it impossible to pinpoint the row directly:

Session 1:
SQL> variable v_a number;
SQL> exec :v_a := 3;
SQL> update t1 set b=10 where a=:v_a;

Session 2:
SQL> variable v_a number;
SQL> exec :v_a := 3;
SQL> update t1 set b=99 where a=:v_a;

Querying v$sql yields the SQL with the bind variable, not the concrete row.

Getting Precise Row Information

Use the following query to retrieve the object, file, block, and row numbers of the waiting row:

SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
FROM v$session
WHERE event = 'enq: TX - row lock contention';

The columns represent: row_wait_obj# – object ID (e.g., 14255) row_wait_file# – file number (e.g., 4) row_wait_block# – block number (e.g., 133) row_wait_row# – row offset (e.g., 1, counting from 0)

Find the table name and data object ID:

select owner||'.'||object_name tab_name, data_object_id
from dba_objects
where object_id = 14255;

Result: TEST.T1 14296 Convert these values to a ROWID using DBMS_ROWID.ROWID_CREATE:

select dbms_rowid.ROWID_CREATE(1, 14296, 4, 133, 1) from dual;

Resulting ROWID: AAADfYAAEAAAACFAAB Query the table with this ROWID to see the locked row:

select * from TEST.T1 where rowid='AAADfYAAEAAAACFAAB';

The output shows the row A=3, B=4, confirming the exact locked record.

Note: This method works whether or not bind variables are used.

Preventing TX Lock Waits

Avoid using SELECT FOR UPDATE when possible.

Commit transactions promptly after modifying data.

Schedule large data modifications outside peak business hours or split them into smaller batches.

Reference: DBMS_ROWID.ROWID_CREATE Parameters

FUNCTION ROWID_CREATE RETURNS ROWID

Argument Name   Type    In/Out  Default?
----------------------------------------
ROWID_TYPE      NUMBER  IN
OBJECT_NUMBER    NUMBER  IN
RELATIVE_FNO    NUMBER  IN
BLOCK_NUMBER    NUMBER  IN
ROW_NUMBER      NUMBER  IN