What is enq: TX - row lock contention

Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.

The V$EVENT_NAME view provides a complete list of all the enq: wait events.

TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

Troubleshooting:

for which SQL currently is waiting to,

select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));

The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

Realted Documents
Classes of Oracle Wait Events
ORA-00054: resource busy and acquire with NOWAIT specified
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT