1 2 Previous Next 27 Replies Latest reply: Mar 18, 2013 2:57 AM by Jonathan Lewis RSS

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

    940748
      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
          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....
            Check the MOS doc *1476298.1* .

            Aman....
            • 3. Re: How to find sql causing "enq: TX - row lock contention"
              Salman Qureshi
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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