3 Replies Latest reply: Jun 25, 2008 9:14 PM by Charles Hooper RSS

    enq TX  locked row

    280562
      Hi All,

      I'm trying to find the row that was locked during series of enq TX row lock contention event in the past with lock mode 6 . From active session history i could get object, file and block but not row. Any other view i can get this information.

      thanks for your help.

      Andrew.
        • 1. Re: enq TX  locked row
          Charles Hooper
          Hi All,

          I'm trying to find the row that was locked during
          series of enq TX row lock contention event in the
          past with lock mode 6 . From active session history i
          could get object, file and block but not row. Any
          other view i can get this information.

          thanks for your help.

          Andrew.
          Andrew,

          You might take a look at the final demo that I posted in this thread, which produces a ENQ: TX - ROW LOCK CONTENTION wait, where the session holding the lock has it in mode 6, and the second session is attempting to acquire a lock on the row in mode 4:
          Re: Tuning row lock contention wait events

          If you are able to sample V$SESSION_WAIT, V$SESSION, and V$LOCK as the contention is happening, you might be able to determine the row in the block that was the source of contention. I do not have access to active session history (it is an additional cost licensing option), but it is possible that an unique index is involved if wait row is 0.

          Charles Hooper
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.
          • 2. Re: enq TX  locked row
            280562
            Charles,

            I'm trying to trace back an event that occurred in the past. I wonder why oracle isn't storing v$session.ROW_WAIT_ROW# in ASH, when it can store row_wait_obj#, file# and block# from v$session.

            Still digging through awr snapshot tables to see if its stored anywhere there.

            Andrew.
            • 3. Re: enq TX  locked row
              Charles Hooper
              I'm trying to trace back an event that occurred in
              the past. I wonder why oracle isn't storing
              v$session.ROW_WAIT_ROW# in ASH, when it can store
              row_wait_obj#, file# and block# from v$session.

              Still digging through awr snapshot tables to see if
              its stored anywhere there.

              Andrew.
              Andrew,

              I have previouly seen what you described using performance monitoring software that I wrote. Let's try a couple experiments to see if we can reproduce what you are seeing (this may not be the only possible cause):
              #Basic Setup
              Session 1:
              CREATE TABLE T1 (
                C1 VARCHAR2(10),
                C2 NUMBER(12),
                PRIMARY KEY (C1));

              Table created.

              INSERT INTO
                T1
              VALUES(
                'ONE',
                1);

              1 row created.

              INSERT INTO
                T1
              VALUES(
                'TWO',
                2);

              1 row created.

              SELECT DISTINCT
                SID
              FROM
                V$MYSTAT;

                     SID
              ----------
                     150


              #Test 1 (Primary Key Violation, No Commit)
              Session 2:
              SELECT DISTINCT
                SID
              FROM
                V$MYSTAT;

                     SID
              ----------
                     143


              INSERT INTO
                T1
              VALUES(
                'TWO',
                2);

              (Session 2 Hung)


              In Session 1:
              SELECT
                S.SID,
                S.PROGRAM,
                S.STATUS,
                SW.EVENT,
                SW.WAIT_TIME,
                SW.STATE,
                SW.SECONDS_IN_WAIT,
                S.SQL_ADDRESS,
                S.SQL_HASH_VALUE,
                S.ROW_WAIT_OBJ#,
                S.ROW_WAIT_FILE#,
                S.ROW_WAIT_BLOCK#,
                S.ROW_WAIT_ROW#,
                SW.P1 ,
                SW.P2,
                SW.P3
              FROM
                V$SESSION S,
                V$SESSION_WAIT SW
              WHERE 
                S.USERNAME IS NOT NULL
                AND SW.SID=S.SID
                AND SW.EVENT NOT LIKE '%SQL*Net%'
                AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue','wait for unread message on broadcast channel');

                     SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE     SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
              ---------- ------------- -------- ------------------------------ --------- -------- --------------- -------- -------------- ------------- -------------- --------------- ------------- ---------- ---------- ----------
                     143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               424 1CA703CC     2983910641            -1              0               0             0 1415053316     393235      11543


              SELECT
                S.SID,
                S.PROGRAM,
                S.SQL_HASH_VALUE,
                L.REQUEST,
                L.ID1,
                L.ID2,
                L.LMODE,
                L.TYPE,
                L.BLOCK
              FROM
                V$LOCK L,
                V$SESSION S
              WHERE
                (L.ID1, L.ID2, L.TYPE) IN
                (SELECT
                  ID1,
                  ID2,
                  TYPE
                FROM
                  V$LOCK
                WHERE
                  REQUEST > 0)
                AND L.SID=S.SID;

                     SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
              ---------- ----------- -------------- ---------- ---------- ---------- ---------- -- ----------
                     143 sqlplus.exe     2983910641          4     393235      11543          0 TX          0
                     150 sqlplus.exe      883681740          0     393235      11543          6 TX          1


              #Test 1 missed the mark, no ROW_WAIT_OBJ#
              COMMIT;


              In Session 2:
              ERROR at line 1:
              ORA-00001: unique constraint (CUSTAPP.SYS_C0014294) violated


              #Test 2 (Primary Key Updated, No Commit)
              In Session 1:
              UPDATE
                T1
              SET
                C1=C1
              WHERE
                C1='TWO';

              1 row updated.


              In Session 2:
              UPDATE
                T1
              SET
                C1=C1
              WHERE
                C1='TWO';


              In Session 1:
              SELECT
                S.SID,
                S.PROGRAM,
                S.STATUS,
                SW.EVENT,
                SW.WAIT_TIME,
                SW.STATE,
                SW.SECONDS_IN_WAIT,
                S.SQL_ADDRESS,
                S.SQL_HASH_VALUE,
                S.ROW_WAIT_OBJ#,
                S.ROW_WAIT_FILE#,
                S.ROW_WAIT_BLOCK#,
                S.ROW_WAIT_ROW#,
                SW.P1 ,
                SW.P2,
                SW.P3
              FROM
                V$SESSION S,
                V$SESSION_WAIT SW
              WHERE 
                S.USERNAME IS NOT NULL
                AND SW.SID=S.SID
                AND SW.EVENT NOT LIKE '%SQL*Net%'
                AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue','wait for unread message on broadcast channel');

                     SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE     SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
              ---------- ------------- -------- ------------------------------ ---------- -------- --------------- -------- -------------- ------------- -------------- --------------- ------------- ---------- ---------- ----------
                     143 sqlplus.exe   ACTIVE   enq: TX - row lock contention           0 WAITING               38 222799C4     1367536044         73419              1           84362             1 1415053318     589828      11227


              SELECT
                S.SID,
                S.PROGRAM,
                S.SQL_HASH_VALUE,
                L.REQUEST,
                L.ID1,
                L.ID2,
                L.LMODE,
                L.TYPE,
                L.BLOCK
              FROM
                V$LOCK L,
                V$SESSION S
              WHERE
                (L.ID1, L.ID2, L.TYPE) IN
                (SELECT
                  ID1,
                  ID2,
                  TYPE
                FROM
                  V$LOCK
                WHERE
                  REQUEST > 0)
                AND L.SID=S.SID;

                     SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
              ---------- ----------- -------------- ---------- ---------- ---------- ---------- -- ----------
                     143 sqlplus.exe     1367536044          6     589828      11227          0 TX          0
                     150 sqlplus.exe      883681740          0     589828      11227          6 TX          1


              #Test 2 missed the mark, ROW_WAIT_OBJ# exists and ROW_WAIT_ROW# exists
              COMMIT;


              In Session 2:
              COMMIT;


              #Test 3 (Unique Index Violation) 
              In Session 1:
              CREATE UNIQUE INDEX IND_T1 ON T1(C2);

              Index created.

              INSERT INTO
                T1
              VALUES(
                'THREE',
                3);

              1 row created.


              In Session 2:
              INSERT INTO
                T1
              VALUES(
                'FOUR',
                3);

              (Session 2 Hung)


              In Session 1:
              SELECT
                S.SID,
                S.PROGRAM,
                S.STATUS,
                SW.EVENT,
                SW.WAIT_TIME,
                SW.STATE,
                SW.SECONDS_IN_WAIT,
                S.SQL_ADDRESS,
                S.SQL_HASH_VALUE,
                S.ROW_WAIT_OBJ#,
                S.ROW_WAIT_FILE#,
                S.ROW_WAIT_BLOCK#,
                S.ROW_WAIT_ROW#,
                SW.P1 ,
                SW.P2,
                SW.P3
              FROM
                V$SESSION S,
                V$SESSION_WAIT SW
              WHERE 
                S.USERNAME IS NOT NULL
                AND SW.SID=S.SID
                AND SW.EVENT NOT LIKE '%SQL*Net%'
                AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue','wait for unread message on broadcast channel');

                     SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE    SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
              ---------- ------------- -------- ------------------------------ --------- -------- --------------- -------- -------------- ------------- -------------- --------------- ------------- ---------- ---------- ----------
                     143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               41 1C8F8B60      463727853            -1              1           84362             1 1415053316     393242      11541


              SELECT
                S.SID,
                S.PROGRAM,
                S.SQL_HASH_VALUE,
                L.REQUEST,
                L.ID1,
                L.ID2,
                L.LMODE,
                L.TYPE,
                L.BLOCK
              FROM
                V$LOCK L,
                V$SESSION S
              WHERE
                (L.ID1, L.ID2, L.TYPE) IN
                (SELECT
                  ID1,
                  ID2,
                  TYPE
                FROM
                  V$LOCK
                WHERE
                  REQUEST > 0)
                AND L.SID=S.SID;

                     SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
              ---------- ----------- -------------- ---------- ---------- ---------- ---------- -- ----------
                     143 sqlplus.exe      463727853          4     393242      11541          0 TX          0
                     150 sqlplus.exe      883681740          0     393242      11541          6 TX          1

              #Test 3 missed the mark, no ROW_WAIT_OBJ#
              In Session 1:
              COMMIT;

              Commit complete.


              In Session 2:
              ERROR at line 1:
              ORA-00001: unique constraint (CUSTAPP.IND_T1) violated


              #Test 4 (Unique Index Violation)
              In Session 1:
              UPDATE
                T1
              SET
                C2=C2+1
              WHERE
                C2=3;

              1 row updated.


              In Session 2:
              INSERT INTO
                T1
              VALUES(
                'FOUR',
                3);

              (Session 2 Hung)


              In Session 1:
              SELECT
                S.SID,
                S.PROGRAM,
                S.STATUS,
                SW.EVENT,
                SW.WAIT_TIME,
                SW.STATE,
                SW.SECONDS_IN_WAIT,
                S.SQL_ADDRESS,
                S.SQL_HASH_VALUE,
                S.ROW_WAIT_OBJ#,
                S.ROW_WAIT_FILE#,
                S.ROW_WAIT_BLOCK#,
                S.ROW_WAIT_ROW#,
                SW.P1 ,
                SW.P2,
                SW.P3
              FROM
                V$SESSION S,
                V$SESSION_WAIT SW
              WHERE 
                S.USERNAME IS NOT NULL
                AND SW.SID=S.SID
                AND SW.EVENT NOT LIKE '%SQL*Net%'
                AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue','wait for unread message on broadcast channel');

                     SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE    SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
              ---------- ------------- -------- ------------------------------ --------- -------- --------------- -------- -------------- ------------- -------------- --------------- ------------- ---------- ---------- ----------
                     143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               18 1C8F8B60      463727853            -1              1           84362             1 1415053316      65560       8736


              SELECT
                S.SID,
                S.PROGRAM,
                S.SQL_HASH_VALUE,
                L.REQUEST,
                L.ID1,
                L.ID2,
                L.LMODE,
                L.TYPE,
                L.BLOCK
              FROM
                V$LOCK L,
                V$SESSION S
              WHERE
                (L.ID1, L.ID2, L.TYPE) IN
                (SELECT
                  ID1,
                  ID2,
                  TYPE
                FROM
                  V$LOCK
                WHERE
                  REQUEST > 0)
                AND L.SID=S.SID;

                     SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
              ---------- ----------- -------------- ---------- ---------- ---------- ---------- -- ----------
                     143 sqlplus.exe      463727853          4      65560       8736          0 TX          0
                     150 sqlplus.exe      883681740          0      65560       8736          6 TX          1

              #Test 4 missed the mark, ROW_WAIT_OBJ# does not exist and ROW_WAIT_ROW# exists
              ROLLBACK;


              In Session 2:
              ROLLBACK;


              #Test 5 (Select For Update in Two Sessions)
              In Session 1:
              SELECT
                *
              FROM
                T1
              FOR UPDATE;


              C1                 C2
              ---------- ----------
              ONE                 1
              TWO                 2
              THREE               3


              In Session 1:
              SELECT
                *
              FROM
                T1
              FOR UPDATE;

              (Session 2 Hung)


              In Session 1:
              SELECT
                S.SID,
                S.PROGRAM,
                S.STATUS,
                SW.EVENT,
                SW.WAIT_TIME,
                SW.STATE,
                SW.SECONDS_IN_WAIT,
                S.SQL_ADDRESS,
                S.SQL_HASH_VALUE,
                S.ROW_WAIT_OBJ#,
                S.ROW_WAIT_FILE#,
                S.ROW_WAIT_BLOCK#,
                S.ROW_WAIT_ROW#,
                SW.P1 ,
                SW.P2,
                SW.P3
              FROM
                V$SESSION S,
                V$SESSION_WAIT SW
              WHERE 
                S.USERNAME IS NOT NULL
                AND SW.SID=S.SID
                AND SW.EVENT NOT LIKE '%SQL*Net%'
                AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue','wait for unread message on broadcast channel');

                     SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE    SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
              ---------- ------------- -------- ------------------------------ --------- -------- --------------- -------- -------------- ------------- -------------- --------------- ------------- ---------- ---------- ----------
                     143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               18 1C911EE4     3199333328         73419              1           84362             0 1415053318     327707      11953

              SELECT
                S.SID,
                S.PROGRAM,
                S.SQL_HASH_VALUE,
                L.REQUEST,
                L.ID1,
                L.ID2,
                L.LMODE,
                L.TYPE,
                L.BLOCK
              FROM
                V$LOCK L,
                V$SESSION S
              WHERE
                (L.ID1, L.ID2, L.TYPE) IN
                (SELECT
                  ID1,
                  ID2,
                  TYPE
                FROM
                  V$LOCK
                WHERE
                  REQUEST > 0)
                AND L.SID=S.SID;

                     SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
              ---------- ----------- -------------- ---------- ---------- ---------- ---------- -- ----------
                     143 sqlplus.exe     3199333328          6     327707      11953          0 TX          0
                     150 sqlplus.exe      883681740          0     327707      11953          6 TX          1

              #Test 5 ROW_WAIT_OBJ# exist and ROW_WAIT_ROW# does not exists, lock request mode is 6 and the other session is holding the lock in mode 6.  It looks like one possibility was found.
              Charles Hooper
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.