Monday, March 7, 2016

enq tx - row lock contention tx mode 4

After transition of new project to live system, we have seen excessive "enq tx - row lock contention" wait event on 2 node RAC system.From top activity as seen below ,

Node 1 :


Node 2 :

these waits cause users not work properly. When examine the sql which causes lock wait was simple insert statement and from P1 field of v$session who waits for this event indicates that lock type was  shared mode 4 TX wait. Common causes for mode 4 are 

a.Unique index
b.Foreign Key
c.Bitmap index
  
v$session , for sessions which wait for this event gives object which cause wait.

select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#
  from gv$session
where event='enq: TX - row lock contention' and state='WAITING'

Output of sql gave me object which was bitmap index and after dropping index  problem has gone away.According to explanation for this type of operation , each entry in bitmap index can cover multiple rows   in table.If two sessions would like to update rows covered by the same index key entry , second session waits for the first session's transaction to be performed by commit or rollback by waiting for the TX lock in mode 4.

No comments:

Post a Comment