AWR TOP 5 Timed Event Analysis

enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. This event indicates that the session is waiting for a lock that is held by another session.

The name of the enqueue is included as part of the wait event name, in the form enq: enqueue_type - related_details. In some cases, the same enqueue type can be held for different purposes, such as the following related TX types:
enq: TX - allocate ITL entry
enq: TX - contention
enq: TX - index contention
enq: TX - row lock contention

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

• 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.

•Waits for TX in mode 4 can also 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.

Problem: The performance of overall database will be effected with this kind of Wait Event and DB Time in AWR will be very high.

Solution:The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK. In our situation we have seen extensive usage of select for update statements. Reducing these we have reduced a lot of wait events. Also we have found couple of procedures where commit is being kept at the end rather immediately after the update statement. After placing the commits closure to the update statement the wait event has drastically reduced.