TX - row lock contention

we saw this in one of our databases.while investigating we found this as the event for most of the application schema sessions


 SID           SERIAL# USERNAME                       SQL_HASH_VALUE EVENT
---------- ---------- ------------------------------ -------------- ----------------------------------------
      1563       4259 APPLOGPRODPRO                      3190491100 latch: cache buffers chains
      1564      44504 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1565      32328 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1566       4817 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1569      37523 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1570      19923 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1573      64233 APPLOGPRODPRO                       782409889 SQL*Net message from client
      1575      13060 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1576      16088 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1578      15137 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1579      12304 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention


all the sessions were having same sql_hash_value and event

following queries can be used to investigate further
For which SQL is currently waiting on:

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

select

    blocking_session,

    sid,    serial#,

    wait_class,

    seconds_in_wait 

from

    v$session 

where

    blocking_session is not NULL

order by

    blocking_session;

other way to check is 

 v$wait_chains - only in 11g

otherwise we can take hang analysis also to see the hanging session

oradebug setmypid

oradebug unlimit

oradebug hanganalyze 3

------------wait for 2 mins

oradebug hanganalyze 3

oradebug tracefile_name

HANG ANALYSIS:

==============

Found 31 objects waiting for

    <1 data-blogger-escaped-color="#ff0000" data-blogger-escaped-font="font">1573/64233/0x5c902070/1554/SQL*Net message from client>

Open chains found:

Chain 1 : :

    <1 data-blogger-escaped-client="client" data-blogger-escaped-et="et" data-blogger-escaped-from="from" data-blogger-escaped-message="message" data-blogger-escaped-x5c902070="x5c902070">

 -- <1 data-blogger-escaped--="-" data-blogger-escaped-contention="contention" data-blogger-escaped-enq:="enq:" data-blogger-escaped-lock="lock" data-blogger-escaped-row="row" data-blogger-escaped-tx="tx" data-blogger-escaped-x5f903a70="x5f903a70">

Other chains found:

Chain 2 : :

    <1 data-blogger-escaped-jobq="jobq" data-blogger-escaped-slave="slave" data-blogger-escaped-wait="wait" data-blogger-escaped-x5f909218="x5f909218">

Chain 3 : :

    <1 data-blogger-escaped-jobq="jobq" data-blogger-escaped-slave="slave" data-blogger-escaped-wait="wait" data-blogger-escaped-x5c911778="x5c911778">

Chain 4 : :

    <1 data-blogger-escaped-o="o" data-blogger-escaped-wait="wait" data-blogger-escaped-x5c914748="x5c914748">

Chain 5 : :

    <1 data-blogger-escaped-jobq="jobq" data-blogger-escaped-slave="slave" data-blogger-escaped-wait="wait" data-blogger-escaped-x5c912768="x5c912768">

Chain 6 : :

    <1 data-blogger-escaped-o="o" data-blogger-escaped-wait="wait" data-blogger-escaped-x5f905a50="x5f905a50">

Session 1573 was the one blocking all the session,Kill the session and you are done!!!!