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;
