1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to exclude locked rows when selecting from a table

Discussion in 'Other Development Tools' started by tyro, Apr 18, 2009.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Many a times we face a situation wherein a particular row or rows from a table are locked by some application/users and it causes our queries to not run if our query also requires the row to be locked. So here is a function which will exclude the rowids of those rows which are locked when used within a select query.
    Code (SQL):
    CREATE OR REPLACE FUNCTION find_locked_rows (v_rowid ROWID, TABLE_NAME VARCHAR2)
       RETURN ROWID
    IS
       x   NUMBER;
       PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
       EXECUTE IMMEDIATE    'Begin
         Select 1 into :x from '

                         || TABLE_NAME
                         || ' where rowid =:v_rowid for update nowait;
        Exception
         When Others Then
           :x:=null;
        End;'

                   USING OUT x, v_rowid;

       ROLLBACK;

       IF x = 1
       THEN
          RETURN v_rowid;
       ELSIF x IS NULL
       THEN
          RETURN NULL;
       END IF;
    END;
    The basic idea is to first locate all the rows that are locked and find their ROWIDs. To locate all the locked rows a cursor is built which will try to lock each row in the table. (Select for update nowait). If it succeeds then that row is not locked by anyone and the function returns the corresponding rowid. If it fails then the row is locked so the functions returns NULL.

    The function takes the rowid of each record in the table and the table name as parameters.The function can be directly used in a Select Query as part of a a co-related query. Or can also be used in another pl/sql program.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Nice article... thanks
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    A heartful thanks to u dear for giving such a valuable information
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't understand the 'logic' of locking an entire table, piecemeal, to find the locked rows, and in the process inconvenience those who may be attempting to update that same table after your 'process' starts. For very large tables this function can take a considerable amount of time to return its result set, increasing the time it takes to actually process the rows you claim you CAN process. And what is to stop someone else from updating this same table after your process runs but before you actually implement your data updates? Your process then fails because a previously unlocked rowid is now locked by someone else.

    A fairly simple query of V$SQL can reveal the update statement currently used to lock data in the desired table, and using that a simple query can be used to exclude the locked records by value. Oh, and the update will run much faster.

    I cannot see the point in implementing this function.


    David Fitzjarrell
     
  5. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    This article was purely for educational purposes, i never claimed or assumed that it would be used for very large tables.
    Hmmm well how long does it take for oracle to execute an update statement immediately after a function call? It is just an enquiry, i am not trying to debate with u.
    Would you care to demonstrate?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):

    SQL>
    SQL> --
    SQL> -- See if we can find the offending statement
    SQL> -- in V$SQL
    SQL> --
    SQL> -- We'll look for UPDATE, DELETE and SELECT .. FOR UPDATE
    SQL> -- statements
    SQL> --
    SQL>
    SQL> SELECT sql_text, first_load_time, last_active_time
      2  FROM v$sql
      3  WHERE sql_text LIKE 'update%emplock%'
      4  OR sql_text LIKE 'delete%emplock%'
      5  OR sql_text LIKE 'select%emplock%for update%';

    SQL_TEXT
    ------------------------------------------------------------------------------------------------------------------------------------
    FIRST_LOAD_TIME     LAST_ACTIVE_TIME
    ------------------- --------------------
    SELECT sql_text, first_load_time, last_active_time FROM v$sql WHERE sql_text LIKE 'update%emplock%' OR sql_text LIKE 'delete%emplock
    %'
    OR sql_text LIKE 'select%emplock%for update%'
    2009-07-25/12:18:05 25-JUL-2009 12:18:05

    SELECT * FROM emplock WHERE empno =7900 FOR UPDATE  <--- original statement locking data
    2009-07-25/10:58:50 25-JUL-2009 12:18:05

    UPDATE emplock SET sal = sal + 100 WHERE rowid IN (SELECT find_locked_rows(rowid, 'EMPLOCK')   FROM emplock)
    2009-07-25/11:26:24 25-JUL-2009 11:43:39

    SQL_TEXT
    ------------------------------------------------------------------------------------------------------------------------------------
    FIRST_LOAD_TIME     LAST_ACTIVE_TIME
    ------------------- --------------------

    UPDATE emplock SET sal = sal + 100 WHERE rowid IN (SELECT rowid FROM emplock WHERE empno <> 7900)
    2009-07-25/11:43:40 25-JUL-2009 11:44:33

    SELECT sql_text FROM v$sql WHERE sql_text LIKE 'update%emplock%' OR sql_text LIKE '%emplock%for update%'
    2009-07-25/11:23:57 25-JUL-2009 11:23:56


    Elapsed: 00:00:00.09

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 903671040

    --------------------------------------------------------------------------------------
    | Id  | Operation        | Name              | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |                   |     1 |   533 |     0   (0)| 00:00:01 |
    |*  1 |  FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   533 |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------

       1 - FILTER("INST_ID"=USERENV('INSTANCE') AND ("KGLNAOBJ" LIKE
                  'update%emplock%' OR "KGLNAOBJ" LIKE 'delete%emplock%' OR "KGLNAOBJ" LIKE
                  'select%emplock%for update%'))


    Statistics
    ----------------------------------------------------------
            143  recursive calls
             18  db block gets
             10  consistent gets
              2  physical reads
           3068  redo SIZE
           1266  bytes sent via SQL*Net TO client
            396  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              5  ROWS processed

    SQL>
    SQL> --
    SQL> -- Let's try an update using the output from
    SQL> -- that function
    SQL> --
    SQL> -- We'll see how long that takes
    SQL> --
    SQL> SET autotrace ON timing ON
    SQL>
    SQL> UPDATE emplock
      2  SET sal = sal + 100
      3  WHERE rowid IN (SELECT find_locked_rows(rowid, 'EMPLOCK')
      4              FROM emplock);

    1300013 ROWS updated.

    Elapsed: 00:16:59.97   <--- with function call

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2572479160

    ------------------------------------------------------------------------------------------
    | Id  | Operation             | Name     | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT      |          |    12G|   426G|       | 70792  (82)| 00:14:10 |
    |   1 |  UPDATE               | EMPLOCK  |       |       |       |            |          |
    |*  2 |   HASH JOIN           |          |    12G|   426G|    24M| 70792  (82)| 00:14:10 |
    |   3 |    VIEW               | VW_NSO_1 |  1128K|    11M|       |  7636   (1)| 00:01:32 |
    |   4 |     SORT UNIQUE       |          |  1128K|    12M|    21M|  7636   (1)| 00:01:32 |
    |   5 |      TABLE ACCESS FULL| EMPLOCK  |  1128K|    12M|       |  2472   (1)| 00:00:30 |
    |   6 |    TABLE ACCESS FULL  | EMPLOCK  |  1128K|    26M|       |  2477   (1)| 00:00:30 |
    ------------------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------

       2 - access(ROWID=CHARTOROWID("$kkqu_col_1"))

    Note
    -----
       - dynamic sampling used FOR this statement


    Statistics
    ----------------------------------------------------------
       37949324  recursive calls
       52038920  db block gets
       25508216  consistent gets
          20613  physical reads
     2450373756  redo SIZE
            683  bytes sent via SQL*Net TO client
            637  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              2  sorts (memory)
              0  sorts (disk)
        1300013  ROWS processed

    SQL>
    SQL> --
    SQL> -- Let's try this again with our direct query
    SQL> --
    SQL> UPDATE emplock
      2  SET sal = sal + 100
      3  WHERE rowid IN (SELECT rowid FROM emplock WHERE empno <> 7900);

    1300013 ROWS updated.

    Elapsed: 00:00:39.56   <--- without function call

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2098321760

    ---------------------------------------------------------------------------------------
    | Id  | Operation           | Name    | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    ---------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT    |         |  1047K|    49M|       |  8775   (1)| 00:01:46 |
    |   1 |  UPDATE             | EMPLOCK |       |       |       |            |          |
    |*  2 |   HASH JOIN         |         |  1047K|    49M|    36M|  8775   (1)| 00:01:46 |
    |*  3 |    TABLE ACCESS FULL| EMPLOCK |  1047K|    24M|       |  2474   (1)| 00:00:30 |
    |   4 |    TABLE ACCESS FULL| EMPLOCK |  1128K|    26M|       |  2477   (1)| 00:00:30 |
    ---------------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------

       2 - access(ROWID=ROWID)
       3 - FILTER("EMPNO"<>7900)

    Note
    -----
       - dynamic sampling used FOR this statement


    Statistics
    ----------------------------------------------------------
            146  recursive calls
        1327721  db block gets
          16832  consistent gets
            199  physical reads
      312853392  redo SIZE
            684  bytes sent via SQL*Net TO client
            626  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              1  sorts (memory)
              0  sorts (disk)
        1300013  ROWS processed

    SQL>

    David Fitzjarrell
     
  7. harish8255

    harish8255 Guest

    I am sorry to say dear...i am unable to understand ...could u plz explain it little bit more
     
  8. ShaheerBadar

    ShaheerBadar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    thanks you