This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Mar 18, 2013 12:57 AM by Jonathan Lewis RSS

How to find sql causing "enq: TX - row lock contention"

940748 Newbie
Currently Being Moderated
Hi,

In one of our database we keep finding "enq: TX - row lock contention" for 2-3 days. But, then it stopped. The time it happened it blocked other sessions for some long time and it got resolved automatically. Now from ADDM report we got sql queries which waited for this but, we can not find which query from which module was causing the issue. What I should look for that past information. Please help.

Database is 10gR2 on AIX.

Regards,
Gourab
  • 1. Re: How to find sql causing "enq: TX - row lock contention"
    CKPT Guru
    Currently Being Moderated
    937745 wrote:
    Hi,

    In one of our database we keep finding "enq: TX - row lock contention" for 2-3 days. But, then it stopped. The time it happened it blocked other sessions for some long time and it got resolved automatically. Now from ADDM report we got sql queries which waited for this but, we can not find which query from which module was causing the issue. What I should look for that past information. Please help.

    Database is 10gR2 on AIX.

    Regards,
    Gourab
    You may get much useful information from the ASH report, If you have idea when it occurred, Then gather only for those 10 or 15 minutes. Also check below MOS note.
    TX Transaction and Enq: Tx - Row Lock Contention - Example wait scenarios [ID 62354.1]
  • 2. Re: How to find sql causing "enq: TX - row lock contention"
    Aman.... Oracle ACE
    Currently Being Moderated
    Check the MOS doc *1476298.1* .

    Aman....
  • 3. Re: How to find sql causing "enq: TX - row lock contention"
    SalmanQureshi Expert
    Currently Being Moderated
    Following will give you the detail of blocked session and blocking session. Once you get the blocking session, you can use same query to find out the SQLs run by the session which was blocking your session with this wait.
    select * from dba_hist_active_sess_history where event like 'enq: TX - row lock contention%' order by sample_time desc
    Salman
  • 4. Re: How to find sql causing "enq: TX - row lock contention"
    moreajays Pro
    Currently Being Moderated
    Hi,

    Try executing below query real time
    alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
    SELECT  vp.spid,c.inst_id,b.session_id sid,c.serial#,a.object_name,
           a.owner object_owner,
           Decode(b.locked_mode, 0, 'None',
                                 1, 'Null (NULL)',
                                 2, 'Row-S (SS)',
                                 3, 'Row-X (SX)',
                                 4, 'Share (S)',
                                 5, 'S/Row-X (SSX)',
                                 6, 'Exclusive (X)',
                                 b.locked_mode) locked_mode,
            b.oracle_username,
            c.program,
            d.event,
            c.status,
            c.last_call_et,
            c.sql_address,
            e.sql_text, c.logon_time
    FROM   all_objects a,
           gv$locked_object b,
           gv$session c,
      gv$session_wait d,
    gv$sqlarea e,
    gv$process vp
    WHERE  a.object_id = b.object_id
    and b.session_id=c.sid
    and c.sid=d.sid and c.paddr=vp.addr
    and e.address=c.sql_address and lower(d.event) like '%enq%'
    ORDER BY 1;
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 5. Re: How to find sql causing "enq: TX - row lock contention"
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    937745 wrote:
    Hi,

    In one of our database we keep finding "enq: TX - row lock contention" for 2-3 days. But, then it stopped. The time it happened it blocked other sessions for some long time and it got resolved automatically. Now from ADDM report we got sql queries which waited for this but, we can not find which query from which module was causing the issue. What I should look for that past information. Please help.
    Generally speaking, there's no guarantee that you can even find the statement that is causing the problem.
    There are various crude methods that allow you to make a sensible guess if you're lucky.
    See: http://jonathanlewis.wordpress.com/2009/04/19/locking-sql/


    Regards
    Jonathan Lewis
  • 6. Re: How to find sql causing "enq: TX - row lock contention"
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    moreajays wrote:

    Try executing below query real time
    alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
    SELECT  vp.spid,c.inst_id,b.session_id sid,c.serial#,a.object_name,
    a.owner object_owner,
    Decode(b.locked_mode, 0, 'None',
    1, 'Null (NULL)',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share (S)',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive (X)',
    b.locked_mode) locked_mode,
    b.oracle_username,
    c.program,
    d.event,
    c.status,
    c.last_call_et,
    c.sql_address,
    e.sql_text, c.logon_time
    FROM   all_objects a,
    gv$locked_object b,
    gv$session c,
    gv$session_wait d,
    gv$sqlarea e,
    gv$process vp
    WHERE  a.object_id = b.object_id
    and b.session_id=c.sid
    and c.sid=d.sid and c.paddr=vp.addr
    and e.address=c.sql_address and lower(d.event) like '%enq%'
    ORDER BY 1;
    The OP asked for a way of find a historic blocker, not a current blocker.
    This query - apart from being one that no-one should want to run on a production system - doesn't say anything about the past, and doesn't identify a blocker.
    It reports the sessions that are being blocked, and EVERY table that is currently locked by each session whether or not the table is involved in the current statement.

    You're using the gv$ (RAC) views but haven't put in any suitable join predicates on the instance (inst_id) columns; the OP is on 10g so you don't need to join to v$session_wait to pick up the wait information; and the join to v$sqlarea forces a "full tablescan" of the library cache (child cursor derived view) because you're joining on address rather than sql_id.


    Regards
    Jonathan Lewis
  • 7. Re: How to find sql causing "enq: TX - row lock contention"
    940856 Newbie
    Currently Being Moderated
    Great detailing and good catch , Jonathan Sir!! We need you to hit more and more and we happens to be the beneficiary here .


    Regards
    One of your fan...
  • 8. Re: How to find sql causing "enq: TX - row lock contention"
    940748 Newbie
    Currently Being Moderated
    Well ASH report suppose to provide blocker session and sql from past but unfortunately to me it provided error
    Error encountered in Top Blocking Sessions While executing SQL statement: Len = 6845 .......
    I am lucky enough the same problem happened again and I found the culprit sql with below query.
    SELECT DISTINCT a.sid "waiting sid" ,
    a.event ,
    c.sql_text "SQL from blocked session" ,
    b.sid "blocking sid" ,
    b.event ,
    b.sql_id ,
    b.prev_sql_id ,
    d.sql_text "SQL from blocking session"
    FROM v$session a,
    v$session b,
    v$sql c ,
    v$sql d
    WHERE a.event ='enq: TX - row lock contention'
    AND a.blocking_session=b.sid
    AND c.sql_id =a.sql_id
    AND d.sql_id =NVL(b.sql_id,b.prev_sql_id)
    It was a SELECT count(*) blocking 2 INSERT, and 2 INSERTS blocking 2 UPDATES. Though, I guess this is the cause of earlier issue though I lack actual evidence from past.

    It occurred on 4th March 2013 at 09:15:00 Am in the morning. The ASH report of that time providing error. Is there any other way apart from ASH with which I can extract what went wrong?
  • 9. Re: How to find sql causing "enq: TX - row lock contention"
    Dom Brooks Guru
    Currently Being Moderated
    It was a SELECT count(*) blocking 2 INSERT, and 2 INSERTS blocking 2 UPDATES.
    I doubt it.

    All this tells you is that the session that is holding the lock in an uncommitted transaction is now running a select count.
  • 10. Re: How to find sql causing "enq: TX - row lock contention"
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    937745 wrote:

    I am lucky enough the same problem happened again and I found the culprit sql with below query.
    SELECT DISTINCT a.sid "waiting sid" ,
    a.event ,
    c.sql_text "SQL from blocked session" ,
    b.sid "blocking sid" ,
    b.event ,
    b.sql_id ,
    b.prev_sql_id ,
    d.sql_text "SQL from blocking session"
    FROM v$session a,
    v$session b,
    v$sql c ,
    v$sql d
    WHERE a.event ='enq: TX - row lock contention'
    AND a.blocking_session=b.sid
    AND c.sql_id =a.sql_id
    AND d.sql_id =NVL(b.sql_id,b.prev_sql_id)
    It was a SELECT count(*) blocking 2 INSERT, and 2 INSERTS blocking 2 UPDATES. Though, I guess this is the cause of earlier issue though I lack actual evidence from past.
    I think you haven't yet read the link I posted above - finding what a blocker is doing NOW is no guarantee that that's the cause of the block.

    The big mantra from Oracle is "readers don't block writers and writers don't block readers", so a "select count(*)" shouldn't be able to block an insert. It's possible, of course, that a long running query that follows a quick DML could mean that there's a long gap between the transaction start and the commit (see http://jonathanlewis.wordpress.com/2008/02/06/trouble-shooting-2/ ) but don't be fooled that this is the actual source of the block.

    Regards
    Jonathan Lewis
  • 11. Re: How to find sql causing "enq: TX - row lock contention"
    940748 Newbie
    Currently Being Moderated
    Dom Brooks wrote:
    It was a SELECT count(*) blocking 2 INSERT, and 2 INSERTS blocking 2 UPDATES.
    I doubt it.

    All this tells you is that the session that is holding the lock in an uncommitted transaction is now running a select count.
    Yes, that is my guess too...why on earth a select will do that?

    But the query is returning that. Need to check the application code before the select.

    That's why I am still not satisfied by the inspection.

    By the way. I have the sid of the session. May be I can use audit details to check what happened before select. What do you suggest?


    Regards,
    Gourab.
  • 12. Re: How to find sql causing "enq: TX - row lock contention"
    940748 Newbie
    Currently Being Moderated
    Jonathan Sir,

    You are 100% right. But, with the SELECT statement I can tell the application team that they need to look the workflow before the SELECT was fired.

    Correct me if I am wrong.

    Regards,
    Gourab
  • 13. Re: How to find sql causing "enq: TX - row lock contention"
    940748 Newbie
    Currently Being Moderated
    This time the problem triggered again and I can at least got a hint of the problem.

    But, Seriously don't we have any other way without ASH to get this type of detail from past? If I am having only below details..........

    Problem occurred on 4th March 2013 at 09:15:00 Am in the morning.

    Regards,
    Gourab.
  • 14. Re: How to find sql causing "enq: TX - row lock contention"
    Mohamed Houri Pro
    Currently Being Moderated
    In one of our database we keep finding "enq: TX - row lock contention" for 2-3 days. But, then it stopped.
    I have been confronted to a similar situation where enq: TX - row lock contention was only a side effect

    http://hourim.wordpress.com/2011/06/07/trouble-shooting-read-by-other-sessions-wait-event/

    Please go through the above link and see how I have used the blog article Jonathan Lewis has mentioned to you, in order to trouble shoot this problem adequately

    If you are licensed to, an AWR snapshot during the period of contention may help you understanding your issue

    Best regards
    Mohamed Houri
    www.hourim.wordpress.com
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points