7 Replies Latest reply: Sep 1, 2011 1:49 PM by Randolf Geist RSS

    Identifying locked rows

    user12006502
      I have an uncommited session in one window and I'm attempting to locate the locked row for Oracle 10.2.0.4. I'm unable to locate the row successfully.

      I issue the following to retrieve the information on the session locking the row. I could clearly see the session holding a DML lock.

      SQLPLUS> select dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
      from v$session where sid=1324;

      USERNAME START_TIME STATUS SID SERIAL# SECONDS_IN_WAIT SQL_ID SQL_FULLTEXT TY
      ------------------------------ -------------------- -------- ---------- ---------- --------------- ------------- -------------------------------------------------------------------------------- --
      ALEX 08/25/11 20:36:30 INACTIVE 1199 30613 49 update test set CUSTOMER_ID=1235 where CUSTOMER_ID=11111 TM


      I select the detailed information of the location of the locked row to retrieve the RowID, but I'm not getting back the expected result. I'm getting back values -1,0,0,0 for columns ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW# respectively. I can't locate the row with the following:

      select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
      from v$session where sid=1199;

      SQLPLUS> 2

      ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
      ------------- -------------- --------------- -------------
      -1 0 0 0

      How do I identify the locked updated row? I'm not able to in my test.

      Any help would be appreciated.
      Thanks
        • 1. Re: Identifying locked rows
          rajeysh
          refer:-http://orafaq.com/node/854
          • 2. Re: Identifying locked rows
            Charles Hooper
            My best guess is that the output you posted shows either a potential primary key violation or a potential unique index violation if the first session issues a commit. Take a look at the following blog article, which summarizes some of my OTN posts on the topic of enqueues:
            http://hoopercharles.wordpress.com/2009/12/06/enqueue-experimentations/

            Search the blog article for #Test 1 (Primary Key Violation, No Commit)
            If that test case you will see similar output from V$SESSION_WAIT (or just V$SESSION as your query shows) with a -1 for the wait object.

            Charles Hooper
            Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
            http://hoopercharles.wordpress.com/
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: Identifying locked rows
              user12006502
              Unfortunately, I'm running a simple test to make sure I can identify the rows of a lock. I'll need to implement the SQL into a script for an environment where we need this information, but for now I'm unable to identify the rows from an update. My test table has no indexes neither unique nor primary.

              Thanks
              • 4. Re: Identifying locked rows
                Charles Hooper
                The formatting of your original post made it difficult to see that the lock type is TM. In the future, please use
                {code } (without the spaces) before and after the section of your posts where spacing is critical.
                user12006502 wrote:
                I have an uncommited session in one window and I'm attempting to locate the locked row for Oracle 10.2.0.4. I'm unable to locate the row successfully.

                I issue the following to retrieve the information on the session locking the row. I could clearly see the session holding a DML lock.
                SQLPLUS>  select dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
                from v$session where sid=1324;
                
                USERNAME                       START_TIME           STATUS          SID    SERIAL# SECONDS_IN_WAIT SQL_ID        SQL_FULLTEXT       TY
                ------------------------------ -------------------- -------- ---------- ---------- --------------- ------------- -------------------------------------------------------------------------------- --
                ALEX                           08/25/11 20:36:30    INACTIVE       1199      30613              49                update test  set CUSTOMER_ID=1235 where CUSTOMER_ID=11111                          TM
                I select the detailed information of the location of the locked row to retrieve the RowID, but I'm not getting back the expected result. I'm getting back values -1,0,0,0 for columns ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW# respectively. I can't locate the row with the following:
                select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
                from v$session where sid=1199;
                
                SQLPLUS>   2
                
                ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
                ------------- -------------- --------------- -------------
                -1              0               0             0
                How do I identify the locked updated row? I'm not able to in my test.

                Any help would be appreciated.
                Thanks
                The first caution, DBMS_ROWID.ROWID_CREATE takes as input the DATA_OBJECT_ID, not the OBJECT_ID, see the documentation:
                http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_rowid.htm#i1004385

                The Oracle documentation (and some books) suggest that V$SESSION and V$SESSION_WAIT display the DATA_OBJECT_ID, but that is not the case - those views actually show the OBJECT_ID, unless of course the OBJECT_ID value is -1. See this blog article:
                http://hoopercharles.wordpress.com/2010/06/03/lock-watching-what-is-wrong-with-this-sql-statement/

                With a TM type enqueue, the P2 value will show the OBJECT_ID of the object that is causing the wait, so you need to retrieve that column. The above blog article includes the following SQL*Plus output that was generated from the script that is attached to the blog article:
                SQL> SELECT /*+ ORDERED */
                  2  S.SID, S.USERNAME, S.PROGRAM, S.STATUS, SW.EVENT, SW.WAIT_TIME WT, SW.STATE,
                  3  SW.SECONDS_IN_WAIT S_I_W, S.SQL_ID, S.SQL_ADDRESS, S.SQL_HASH_VALUE,
                  4  S.SQL_CHILD_NUMBER, S.ROW_WAIT_OBJ# OBJ#, S.ROW_WAIT_FILE# FILE#,
                  5  S.ROW_WAIT_BLOCK# BLOCK#, S.ROW_WAIT_ROW# ROW#, SW.P1, SW.P2, SW.P3
                  6  FROM
                  7  V$SESSION_WAIT SW, V$SESSION S
                  8  WHERE
                  9  S.USERNAME IS NOT NULL
                 10  AND SW.SID=S.SID
                 11  AND SW.EVENT NOT LIKE '%SQL*Net%'
                 12  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue',
                 13  'wait for unread message on broadcast channel');
                
                SID USERNAME PROGRAM     STATUS   EVENT                WT STATE   S_I_W SQL_ID        SQL_ADDRESS      SQL_HASH_VALUE SQL_CHILD_NUMBER OBJ# FILE# BLOCK# ROW#         P1     P2 P3
                --- -------- ----------- -------- -------------------- -- ------- ----- ------------- ---------------- -------------- ---------------- ---- ----- ------ ---- ---------- ------ --
                214 TESTUSER sqlplus.exe ACTIVE   enq: TM - contention  0 WAITING   213 dxbtvfguaa0xq 000007FFA479BDE0     4104455094                0   -1     0      0    0 1414332421 114337  0
                217 TESTUSER sqlplus.exe ACTIVE   enq: TM - contention  0 WAITING    78 873s5kmm4fkfa 000007FFA5182400     3863431626                0   -1     0      0    0 1414332418 114337  0
                Notice in the above that the lock type is TM, that the ROW_WAIT_OBJ# is -1, and that the P2 column shows 114337 - 114337 is the OBJECT_ID. Lock type TM is typically considered a table-level lock, not a row-level lock.

                Charles Hooper
                Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                http://hoopercharles.wordpress.com/
                IT Manager/Oracle DBA
                K&M Machine-Fabricating, Inc.
                • 5. Re: Identifying locked rows
                  user12006502
                  Hi Charles,
                  Thanks for the info, but I have a row level lock. I'm unable to identify the rows being locked.

                  I have a single session
                  SQL> select * from test;

                  CUSTOMER CUSTOMER_ID
                  ---------- -----------
                  BETTY 1235
                  RON 0
                  BLAKE 0

                  SQL> update test set CUSTOMER='KEITH' where CUSTOMER='RON';

                  1 row updated.

                  SQL>
                  Locking Session: ALEX 08/31/11 20:08:18 INACTIVE 1674 6446 TM 12 fkh817jtq1qnj
                  Locking Session: ALEX 08/31/11 20:08:18 INACTIVE 1674 6446 TX 12 fkh817jtq1qnj

                  select sql_text from v$sql where sql_id='fkh817jtq1qnj';
                  update test set CUSTOMER='KEITH' where CUSTOMER='RON'

                  select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid=1674 and serial#=6446;

                  ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
                  ------------- -------------- --------------- -------------
                  -1 0 0 0

                  1 row selected.
                  • 6. Re: Identifying locked rows
                    Charles Hooper
                    user12006502,

                    I am struggling to see EXACTLY what you are doing. You are showing output without showing the SQL statements that produce the output. Additionally, there may be foreign key or other constraints involved - as it stands, the best that I can do is guess, and I would prefer not to do that. For example, assume that your table name is T1 rather than TEST. What if, someone created an index like this:
                    CREATE UNIQUE INDEX T1_CUSTOMER ON T1(DECODE(CUSTOMER,'KEITH','0','JOHN','0',CUSTOMER));
                    {pre}

                    Then, in that same session executed the following without a commit:
                    INSERT INTO T1 VALUES ('JOHN',0);
                    Now, in another session you enter the following:
                    UPDATE T1 SET CUSTOMER='KEITH' WHERE CUSTOMER='RON';
                    Your session then hangs for seemingly no reason.

                    The person with the other session issues the following:
                    SELECT
                      SID,
                      TYPE,
                      ID1,
                      ID2,
                      LMODE,
                      REQUEST,
                      BLOCK
                    FROM
                      V$LOCK
                    WHERE
                      SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);
                     
                    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
                    --- -- ---------- ---------- ---------- ---------- ----------
                    222 AE        100          0          4          0          0
                    222 TM      70568          0          3          0          0
                    222 TX      65554       1424          6          0          1
                    It seems that the first session has a TX lock that is blocking another session, a TM lock that is NOT blocking another session, and an AE lock that is not blocking another session.  Now, in that session, the following is executed:
                    SELECT /*+ ORDERED */
                      S.SID, 
                      S.STATUS,
                      SW.EVENT,
                      SW.WAIT_TIME WT,
                      SW.STATE,
                      SW.SECONDS_IN_WAIT S_I_W,
                      S.SQL_ID,
                      S.SQL_CHILD_NUMBER,
                      S.ROW_WAIT_OBJ# OBJ#,
                      S.ROW_WAIT_FILE# FILE#,
                      S.ROW_WAIT_BLOCK# BLOCK#,
                      S.ROW_WAIT_ROW# ROW#,
                      SW.P1,
                      SW.P2,
                      SW.P3
                    FROM
                      V$SESSION_WAIT SW,
                      V$SESSION S
                    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 STATUS   EVENT                                 WT STATE                    S_I_W SQL_ID        SQL_CHILD_NUMBER       OBJ#      FILE#     BLOCK#       ROW#         P1         P2         P3
                    ---- -------- ----------------------------- ---------- ------------------- ---------- ------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                     223 ACTIVE   enq: TX - row lock contention          0 WAITING                    146 9gag614u0kvyx                0         -1          0          0          0 1415053316      65554       1424
                    Notice in the above the -1 for the OBJ#.  You must provide the DDL and the DML for people to help you, otherwise you are forcing people to guess what you have done to set up the test case.

                    At the minimum, provide the DDL to recreate the table (and any indexes).  If you do not have the DML, you can use the following technique to retrieve it:
                    SET PAGESIZE 0
                    SET LONG 90000
                    SET LINESIZE 200
                    COLUMN OBJECT_DEF FORMAT A200
                    SPOOL 'GETMETA.SQL'
                     
                    SELECT
                      DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER) OBJECT_DEF
                    FROM
                      DBA_TABLES
                    WHERE
                      TABLE_NAME IN ('T1');
                    When I executed the above for my test table T1, I saw the following:
                      CREATE TABLE "TESTUSER"."T1"
                       (    "CUSTOMER" VARCHAR2(6),
                            "CUSTOMER_ID" NUMBER
                       ) SEGMENT CREATION IMMEDIATE
                      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                      TABLESPACE "USER_DATA"
                    That said, you could also have a trigger on the table that performs some action on an UPDATE, which could also cause problems.

                    Here is an example of what a full test case would look like:
                    DROP TABLE T1 PURGE;
                     
                    CREATE TABLE T1 (
                      CUSTOMER VARCHAR2(6),
                      CUSTOMER_ID NUMBER); 
                     
                    INSERT INTO T1 VALUES('BETTY',1235);
                    INSERT INTO T1 VALUES('RON',0);
                    INSERT INTO T1 VALUES('BLAKE',0);
                     
                    COMMIT;
                    In session 1 you execute the following and receive confirmation that the update was successful:
                    UPDATE T1 SET CUSTOMER='KEITH' WHERE CUSTOMER='RON';
                     
                    1 row updated.
                    You check V$LOCK for this session and notice that the session has 3 locks, none of which are blocking:
                    SELECT
                      SID,
                      TYPE,
                      ID1,
                      ID2,
                      LMODE,
                      REQUEST,
                      BLOCK
                    FROM
                      V$LOCK
                    WHERE
                      SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);
                     
                    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
                    --- -- ---------- ---------- ---------- ---------- ----------
                    223 AE        100          0          4          0          0
                    223 TM      70568          0          3          0          0
                    223 TX     262153       1493          6          0          0
                    In another session, you attempt to drop the table and are greeted with an error:
                    DROP TABLE T1 PURGE;
                     
                    DROP TABLE T1 PURGE
                               *
                    ERROR at line 1:
                    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
                    So, you try the update statement - the same one performed in the first session:
                    UPDATE T1 SET CUSTOMER='KEITH' WHERE CUSTOMER='RON';
                    The session hangs.

                    Back in the first session, you check the locks for the first session:
                    SELECT
                      SID,
                      TYPE,
                      ID1,
                      ID2,
                      LMODE,
                      REQUEST,
                      BLOCK
                    FROM
                      V$LOCK
                    WHERE
                      SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);
                     
                    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
                    --- -- ---------- ---------- ---------- ---------- ----------
                    223 AE        100          0          4          0          0
                    223 TM      70568          0          3          0          0
                    223 TX     262153       1493          6          0          1
                    Note the blocking lock.

                    Let's check the waits:
                    SELECT /*+ ORDERED */
                      S.SID, 
                      S.STATUS,
                      SW.EVENT,
                      SW.WAIT_TIME WT,
                      SW.STATE,
                      SW.SECONDS_IN_WAIT S_I_W,
                      S.SQL_ID,
                      S.SQL_CHILD_NUMBER,
                      S.ROW_WAIT_OBJ# OBJ#,
                      S.ROW_WAIT_FILE# FILE#,
                      S.ROW_WAIT_BLOCK# BLOCK#,
                      S.ROW_WAIT_ROW# ROW#,
                      SW.P1,
                      SW.P2,
                      SW.P3
                    FROM
                      V$SESSION_WAIT SW,
                      V$SESSION S
                    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 STATUS   EVENT                                 WT STATE                    S_I_W SQL_ID        SQL_CHILD_NUMBER       OBJ#      FILE#     BLOCK#       ROW#         P1         P2         P3
                    ---- -------- ----------------------------  ---------- ------------------- ---------- ------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                     222 ACTIVE   enq: TX - row lock contention          0 WAITING                    333 9gag614u0kvyx                1      70568          4      42601          1 1415053318     262153       1493
                    Notice that the second session is waiting in an enqueue, the STATE is WAITING and the OBJ#, FILE#, BLOCK#, and ROW# all contain values.

                    Please provide a full test case, otherwise people may provide to you an answer that has nothing to do with the question that you are asking.

                    Notice in the above that the spaces were retained in my posted output from the SQL statements.  To do the same, place the following tag before and after your code sections (do not include the space before and after the brackets):
                    { code }

                    Charles Hooper
                    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                    http://hoopercharles.wordpress.com/
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                    • 7. Re: Identifying locked rows
                      Randolf Geist
                      user12006502 wrote:
                      I have an uncommited session in one window and I'm attempting to locate the locked row for Oracle 10.2.0.4. I'm unable to locate the row successfully.

                      I issue the following to retrieve the information on the session locking the row. I could clearly see the session holding a DML lock.

                      How do I identify the locked updated row? I'm not able to in my test.

                      Any help would be appreciated.
                      It is a common misconception that you can locate a locked row in Oracle via a query. The point is that the information that you're querying only gets populated in case of a blocking lock, and even then not in every case, since you might have blocking locks that do not refer to a particular row.

                      Oracle stores the lock information within the block, so if you identified in which block the row is located that you've attempted to lock, you could get detailed information about the row locks of that block by performing a block dump.

                      Other than that Oracle doesn't maintain this information anywhere else and it is only externalized for blocking situations - it is a matter of design that there is no central lock manager in Oracle that would inherently limit scalability, hence the downside of that approach is that there is no central information pool where you could obtain detailed information about row level locks.

                      You might want to read through the following articles by Arup Nanda that address some of these points:

                      http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html

                      http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.html

                      Hope this helps,
                      Randolf