ORACLE – enq: TX row lock contention

Enqueues are locks that coordinate access to database resources. enq: wait eventindicates
that the session is waiting for a lock that is held by another session.
The amount of wait time associated with this wait event is excessive and can be responsible
for performance issues observed in the application. TX enqueue are acquired exclusive when a
transaction initiates its first change and held until the transaction does a COMMIT or
ROLLBACK.
 Some situations of TX enqueue:
– 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.
To solve this you would have the first session already holding the lock perform a COMMIT or ROLLBACK.
– 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.
To solve this again you have the first session already holding the lock perform a COMMIT or ROLLBACK.
– A wait for the TX enqueue in mode 4 can also occur when multiple sessions try to update
or delete different rows that are covered by the same bitmap entry. Of course, this does
not apply if the application does not use bitmap indexes.
Unlike the B-tree index entry, which contains a single ROWID, a bitmap entry can
potentially cover a range of ROWIDs. So when a bitmap index entry is locked, all the
ROWIDs that are covered by the entry are also locked.
Troubleshooting:
For which SQL currently is waiting on:
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:
select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where
blocking_session is not NULL order by blocking_session;