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