enq: TX - row lock contention

enq: TX - row lock contention:

This is indicative of a session waiting for a row lock held by another session.

There are several situations of TX enqueue:

In mode 6:

Waits for TX in mode 6 occur when a session is waiting for a row level lock that is already held by another session. This occurs when one application is updating or deleting a row that another session is also trying to update or delete. This will generate a wait event "enq: TX - row lock contention". To solve this particular instance, the session holding the lock must perform a COMMIT or ROLLBACK.

In mode 4:we have 3 reasons:

  1.     Unique key contention:

     In mode 4, a TX wait can occur if there is a potential duplicate in a unique index. When two sessions try to insert the same key value the second session must wait to see if an ORA-001 should be raised. This can cause the "enq: TX - row lock contention" wait event. This wait event can be handled by having the session holding the lock perform a COMMIT or ROLLBACK.

  2.     Foreign Key contention:

If referential constraints have been defined between tables and the foreign key columns in the child table are not indexed, then modifications to the parent table's key cause locks on the child table, and if the foreign key columns are not indexed this can cause contention.

  3.     Bitmap index contention:

The wait event “enq: TX – row lock contention” can also occur in mode 4 when a session is waiting on a 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 index fragment, then the second session must wait for the first session to perform either a COMMIT or a ROLLBACK by waiting for the TX lock in mode 4, otherwise it will generate the “enq: TX – row lock contention” wait event.  This index is suitable for data warehouse environments.

enq: HW - contention                  Configuration

enq: SQ - contention                   Configuration

enq: SS - contention                    Configuration

enq: ST - contention                   Configuration

enq: TM - contention                  Application

enq: TW - contention                  Administrative

enq: TX - allocate ITL entry       Configuration

enq: TX - index contention         Concurrency

enq: TX - row lock contention      Application

enq: TX – contention                    Application

select
           event, 
           p1,
           mod(p1,16)  as "mode"
    from v$active_session_history
    where event like 'enq:%‘;

select        count(*) cnt, 
                 session_id sid,
                 substr(event,1,30) event, 
                 mod(p1,16)  as lm,
                 sql_id,
                 CURRENT_OBJ# || ' ' || object_name obj
               , o.object_type type
               , CURRENT_FILE# file#
               , CURRENT_BLOCK#  block#
               , blocking_session bsid
    from v$active_session_history ash,
         all_objects o
    where
            event  like 'enq: T%'
      and o.object_id (+)= ash.current_obj#
   group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
   order by  count(*);

  select    substr(event,1,30) event, sql_id,
             CURRENT_OBJ# || ' ' || object_name obj
           , o.object_type type
           , CURRENT_FILE# file#
           , CURRENT_BLOCK#  block#
    from v$active_session_history ash,
             ( select a.object_name, 
                      a.object_id,
                      decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
               from all_objects a, all_indexes i where 
               a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
    where
            event  like 'enq: TX%'
      and o.object_id (+)= ash.current_obj#
    order by sample_time;