Tuesday, June 1, 2010

Blocking session ... show table records that are blocked


Another part of "blocking session" issues are blocking session that occur undetected. If you haven't implemented proactive blocking session monitoring then this event is 100% probable to happened sooner or later.

In mine previous two post on blocking session theme (Blocking session ... detect, unblock, notify and Blocking session ... detect, unblock, notify (Part II)) I have explained how to handle that problem in praxis.

Because blocking session present normal state when action in one session prevent execution of other session(s), deeper investigation on cause of this subject should be very common task of all DBA's and developers as well. After making all preventing steps in proactive reaction, last step is to find the root cause of blocking issues.

Because of mine luck, that from past experience, I wasn't involved in any serious locking "situations" where I could do something special, Oracle EBS (not locking at all because it has no foreign keys-app level of relation, smaller ERP's (where transactions was to short to see some problems at this level), this is the main reason why blocking session on record level was not part of my experience and interest. Frankly, there was one system, where I was working as DBA, where blocking sessions occur very frequently and cause big problems, but there the problem was totally wrong design where one table has records that were too much popular and this is find out very soon so no other investigation was not needed!

So I was really surprised when a friend of mine, Mirko, come to me with question: "How to see which table record is blocked by other session?". And this is the moment when this theme was born...

The problem

Test will be on table EMP. Just for easier reference let me show it's content.
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7698 BLAKE      MANAGER         7839 01.05.81       2850                    30
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7566 JONES      MANAGER         7839 02.04.81       2975                    20
      7788 SCOTT      ANALYST         7566 09.12.82       3000                    20
      7902 FORD       ANALYST         7566 03.12.81       3000                    20
      7369 SMITH      CLERK           7902 17.12.80        800                    20
      7499 ALLEN      SALESMAN        7698 20.02.81       1600        300         30
      7521 WARD       SALESMAN        7698 22.02.81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28.09.81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08.09.81       1500          0         30
      7876 ADAMS      CLERK           7788 12.01.83       1100                    20
      7900 JAMES      CLERK           7698 03.12.81        950                    30
      7934 MILLER     CLERK           7782 23.01.82       1300                    10

14 rows selected.

SQL>
Suppose user1 make update statement like:
USER1@db1> @my_sid

USER                           INSTANCE            SID    SERIAL#
------------------------------ ------------ ---------- ----------
USER1                          db1                 307        292

USER1@db1> update emp set ename = '????' ;

14 rows updated.

USER1@db1> 
When user2 try to execute his update statement, he will be block:
USER2@db1> @my_sid

USER                           INSTANCE            SID    SERIAL#
------------------------------ ------------ ---------- ----------
USER2                          db1                 308        890

USER2@db1> update emp set ename = 'NEW_NAME' where empno= 7654;
running sb.sql (mentioned in one of mine previous topic) you notify that session 307 (blocker) is blocking other session 308 (waiter) :
DAMIRV@db1> @sb
Oracle version: 10.2.0.4.0 (10.2.0.3.0)
Blocker         Inst  SID     Serial      [sec]   Lock Type         Status       Module
------------------------------------------------------------------------------------------------------------------------
1. USER1         1     307         292       48   Transaction      INACTIVE       SQL*Plus
        USER2       1   308           890       42     Exclusive       INACTIVE        SQL*Plus

To kill first from the list, perform:

NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '307,292' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '307,292' IMMEDIATE;


RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''307,292'''' IMMEDIATE''; end; '
,instance=>1
);
  commit;
end;
/

PL/SQL procedure successfully completed.

DAMIRV@db1>
Regarding involved commands you may find:
  1. sql from blocker session
  2. sql from waiter session
by retrieving sql_text using info from gv$session or gv$active_session_history views.

Because case nr. 1) is harder to get (remember that commands in blocker session can continue to execute after "blocking statement" has been executed...so this sql what cause block is gone), here is an example for second case (find waiter sql).
DAMIRV@db1> @si 1 308
Instance ID = 1
SID         = 308
Current SQL....have result if session is active....

SQL_TEXT                                                                IID HASH_VALUE OPTIMIZER_COST
---------------------------------------------------------------------- ---- ---------- --------------
update emp set ename = 'NEW_NAME' where empno= 7654                       1  229437123              1

1 row selected.

Binding values....

no rows selected

Previous SQL....

SQL_TEXT                                                                IID HASH_VALUE OPTIMIZER_COST
---------------------------------------------------------------------- ---- ---------- --------------
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;                      1 1029988163              0

1 row selected.

 IID SID_SERIAL      AUDSID  ORAPID DBPID  APPPID     USERNAME   MODULE        ACTION          STATUS
---- ----------- ---------- ------- ------ ---------- ---------- ------------- --------------- --------
   1   308,  890     205187      32 356    1640:2780  USER2      SQL*Plus                      ACTIVE

1 row selected.

DAMIRV@db1>sql
And, in this case, the result, which record is blocking this session is obvious! So by killing the session 307 on instance 1 would give green light for our waiter session to continue running.

But as I said in real life situation may be (and always is by Murphy) different and worse. Let us make situation where user1 (blocker) perform:
update emp set ename='123' where empno=7654;
and then with user2 (which will become a "waiter") execute
update emp set ename='xxx';
Suppose also that in the meantime, in the blocker session some others sqls are performed (as they are in real life when you run PL/SQL block). In that situation there is no way to find out rows that originally cause the block in previously described way because blocker sql has been changed, and through waiter sql we cannot determine which row is blocking it because we are updating many of them.

The solution

For that we should use another columns from gv$session (row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#) and arrange that with some foreign tables to get more readable output. The result is sbr.sql, which expose problems on record level.
/* ---------------------------------------------------------------------------
 Filename: sbr.sql
 CR/TR#  : 
 Purpose : Find blocking records by locked session for RAC and non RAC environement  
           Resulting SQL shows rows which record is locked preventing from running blocked 
           session.
           
           Excellent way to find hotspot records (tables)
           
           
 Date    : 19.05.2010.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g/11g (should work on lower versions as well)
           
           "c_nr_cols" define number of columns (without pk columns!) of generated sql 
                       change it to your needs           

 Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */
declare
  c_nr_cols CONSTANT PLS_INTEGER := 3;
    
  -- blockers ....
  CURSOR cur_blockers IS
      SELECT DISTINCT 
         username blocker_user, 
         gvb.sid, 
         gvs.serial# serial, 
         gvb.inst_id, 
         gvb.ctime ,
         gvs.status STATUS,      
         module, 
         action
       FROM gv$lock gvb, gv$lock gvw, gv$session gvs
      WHERE (gvb.id1, gvb.id2) in (
                                   SELECT id1, id2 FROM gv$lock WHERE request=0
                                   INTERSECT
                                   SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                  )
        AND gvb.id1=gvw.id1
        AND gvb.id2=gvw.id2
        AND gvb.request=0
        AND gvw.lmode=0
        AND gvb.sid=gvs.sid
        AND gvb.inst_id=gvs.inst_id
      ORDER BY CTIME desc
  ;
  
  --waiters
  CURSOR cur_w IS
      SELECT  gvw.sid waiter_sid,
              gvw.inst_id waiter_inst_id
      FROM gv$lock gvb, gv$lock gvw, gv$session gvs
      WHERE (gvb.id1, gvb.id2) in (
                                   SELECT id1, id2 FROM gv$lock WHERE request=0
                                   INTERSECT
                                   SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                  )
        AND gvb.id1=gvw.id1
        AND gvb.id2=gvw.id2
        AND gvb.request=0
        AND gvw.lmode=0
        AND gvb.sid=gvs.sid
        AND gvb.inst_id=gvs.inst_id
    ORDER BY gvb.CTIME desc
  ;
   
  CURSOR cur_waiters (cp_blocker_waiter_sid gv$lock.sid%TYPE, cp_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
      SELECT /* ordered */ 
        username waiter_user, 
        gvw.sid, 
        gvs.serial# serial, 
        gvw.inst_id, 
        gvw.ctime , 
        gvs.status STATUS,
        module,
        decode(gvw.request, 
                           0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-'
        ) lock_req,
        action
      FROM gv$lock gvb, gv$lock gvw, gv$session gvs
      WHERE (gvb.id1, gvb.id2) in (
                                    SELECT id1, id2 FROM gv$lock WHERE request=0
                                    INTERSECT
                                    SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                   )
        AND gvb.id1=gvw.id1
        AND gvb.id2=gvw.id2
        AND gvb.request=0
        AND gvw.lmode=0
        AND gvw.sid=gvs.sid
        AND gvw.inst_id=gvs.inst_id
        AND gvw.sid=cp_blocker_waiter_sid
        AND gvw.inst_id=cp_blockers_waiter_inst_id
      ORDER BY CTIME desc
    ;
  
  
  -- blocked records 
  CURSOR cur_blocked_objects (cp_inst_id IN PLS_INTEGER, cp_sid IN PLS_INTEGER) IS  
      SELECT do.owner,
             do.object_name,
             dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) RID
      FROM gv$session s
          ,dba_objects do
      WHERE inst_id=cp_inst_id 
        AND sid=cp_sid
        AND s.ROW_WAIT_OBJ# = do.OBJECT_ID
  ;
  
  -- columns which user want to see (pk keys are excluded because they are shown by default!)
  CURSOR cur_cols (cp_owner IN VARCHAR2, cp_table IN VARCHAR2, cc_nr_cols IN PLS_INTEGER) IS  
      WITH q as (
                  SELECT column_name
                    FROM all_tab_columns
                   WHERE owner= cp_owner 
                     AND table_name = cp_table     
                     AND column_name NOT IN ( SELECT b.column_name column_name_pk
                                                FROM all_constraints a, 
                                                     all_cons_columns b
                                               WHERE a.OWNER = cp_owner
                                                 AND a.table_name = cp_table
                                                 AND a.constraint_type='P'
                                                 AND a.constraint_name=b.constraint_name
                                                   
                                             )     
                  ORDER BY column_id
                )
      SELECT q.column_name
        FROM q
       WHERE rownum<=cc_nr_cols
  ;
  
  -- pk_key columns (always shown in the front)
  CURSOR cur_pkeys (cp_owner IN VARCHAR, cp_table IN VARCHAR) IS 
      SELECT b.column_name column_name_pk
        FROM all_constraints a, 
             all_cons_columns b
       WHERE a.OWNER = cp_owner
         AND a.table_name = cp_table
         AND a.constraint_type='P'
         AND a.constraint_name=b.constraint_name
      ORDER BY position
  ;  
  
  stmt  VARCHAR2 (4000);   
BEGIN 
  FOR rec_blockers IN cur_blockers LOOP
    dbms_output.put_line(rec_blockers.blocker_user||' ('||rec_blockers.inst_id|| ' '''||rec_blockers.sid||','||rec_blockers.serial||''')');
    FOR rec_w in cur_w LOOP
      FOR rec_waiters IN cur_waiters (rec_w.waiter_sid,rec_w.waiter_inst_id) LOOP 
        dbms_output.put_line (lpad(chr(9),9)|| 
                              rpad(rec_waiters.waiter_user,10,' ')||
                              rpad(to_char(rec_waiters.inst_id),4,' ')||'  '||
                              rpad(to_char(rec_waiters.sid||','||to_char(rec_waiters.serial)),11,' ')||'  '|| 
                              lpad(to_char(rec_waiters.ctime),6,' ')||' sec   '|| 
                              lpad(to_char(rec_waiters.lock_req),13,' ')||' '|| 
                              lpad(rec_waiters.status,14,' ')|| ' '|| 
                              lpad(to_char(nvl(rec_waiters.module,'?')),15,' ')
                              );      
        FOR rec_blocked_objects IN cur_blocked_objects (rec_waiters.inst_id,rec_waiters.sid) LOOP
          stmt := 'SELECT ';
         
          -- place pks cols in the front of select
          FOR rec_pkeys IN cur_pkeys (rec_blocked_objects.owner, rec_blocked_objects.object_name) LOOP
            stmt := stmt || rec_pkeys.column_name_pk ||' , '; 
          END LOOP;  

          -- then show other "c_nr_cols" number of cols (pks are not counted)
          FOR rec_cols IN cur_cols (rec_blocked_objects.owner, rec_blocked_objects.object_name, c_nr_cols) LOOP
            stmt := stmt ||  rec_cols.column_name || ' , ' ;
          END LOOP;
          IF SUBSTR (stmt,LENGTH(stmt)-2)=' , ' THEN
            stmt  :=  SUBSTR (stmt,1, (LENGTH(stmt)-3));
          END IF;
          stmt := stmt || ' FROM ' || rec_blocked_objects.owner ||'.'|| rec_blocked_objects.object_name ;
          
          -- get involved rowid for this session (there is always one blocking row!)        
          stmt := stmt || ' WHERE rowid = ' || ''''||  rec_blocked_objects.RID || '''' || ' ;';
          dbms_output.put_line(chr(9)||chr(9)|| stmt);
        END LOOP;   
      END LOOP;
    END LOOP;
  END LOOP;
END; 
/
The result is:
DAMIRV@db1> @sbr
USER1 (1 '307,292')
        USER2     1     308,890         105 sec       Exclusive         ACTIVE        SQL*Plus
                SELECT EMPNO , ENAME , JOB , MGR FROM DAMIRV.EMP WHERE rowid = 'AAAPMQAALAAEGHUAAJ' ;

PL/SQL procedure successfully completed.
Based on blocking information from database views, script dynamically generate sql statement whose result shows which record in "waiter" sessions are waiting for release from blocking session action (commit or rollback). The only constant in script is c_nr_cols, which represent number of columns which will be included in generated sql result. Default is 3, but this exclude pk columns, which are always shown at the beginning of generated sql. In fact, script use data from gv$session, view which can help identifying records rowids of blocked sessions.

If you run generated sql result, you see records that are waiting to be "unlocked". Notice that for each session there is only one record:
DAMIRV@db1> SELECT EMPNO , ENAME , JOB , MGR FROM DAMIRV.EMP WHERE rowid = 'AAAPMQAALAAEGHUAAJ' ;

     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7654 MARTIN     SALESMAN        7698

1 row selected.

DAMIRV@db1>
Let us check that these results are correct. Let us see records by rowids:
DAMIRV@db1> select empno, ename, job, rowid
  2  from emp
  3  where rowid in ('AAAPMQAALAAEGHUAAJ');

     EMPNO ENAME      JOB       ROWID
---------- ---------- --------- ------------------
      7654 MARTIN     SALESMAN  AAAPMQAALAAEGHUAAJ

1 row selected.

DAMIRV@db1>
Then by pk values:
DAMIRV@db1> select empno, ename, job, rowid
  2  from emp
  3  where empno = 7654;

     EMPNO ENAME      JOB       ROWID
---------- ---------- --------- ------------------
      7654 MARTIN     SALESMAN  AAAPMQAALAAEGHUAAJ

1 row selected.

DAMIRV@db1> 
As you can see these queries generate the same outputs!

Because blocking session was updating all the records in EMP table (ENAME column), this is the record that really represent blocked record (were unable to be processed in waiter session). Of course there were some other ways to see the blocking row but shown method present "out of the box" solution for any case.

To show complexity of the problem, here is one "real life" output.
Oracle version: 10.2.0.4.0 (10.2.0.3.0)
Blocker         Inst  SID     Serial      [sec]   Lock Type         Status       Module
------------------------------------------------------------------------------------------------------------------------
  1. PETR          2     648       18149     1295   Transaction      INACTIVE              ?
            BIBE        2   651         14393      551   Share-Table       INACTIVE               ?
            SAIG        4   732         16425       67     Exclusive       INACTIVE               ?
            TAB         2   736          6976       11   Share-Table       INACTIVE               ?
            ANRU        2   682          4379       38     Exclusive       INACTIVE               ?
            BORO        2   725          4665       31   Share-Table       INACTIVE               ?
  2. BORO          2     725        4665      150   Transaction        ACTIVE              ?
            BIBE        2   651         14393      552   Share-Table         ACTIVE               ?
            TAB         2   736          6976       12   Share-Table         ACTIVE               ?
            ANRU        2   682          4379       38     Exclusive         ACTIVE               ?
            SAIG        4   732         16425       68     Exclusive         ACTIVE               ?
            BORO        2   725          4665       31   Share-Table         ACTIVE               ?
  3. SAIG          4     732       16425       66   Transaction        ACTIVE              ?
            BIBE        2   651         14393      552   Share-Table         ACTIVE               ?
            SAIG        4   732         16425       68     Exclusive         ACTIVE               ?
            TAB         2   736          6976       12   Share-Table         ACTIVE               ?
            ANRU        2   682          4379       38     Exclusive         ACTIVE               ?
            BORO        2   725          4665       31   Share-Table         ACTIVE               ?
    
PETR (2 '648,18149')
            BIBE      2     651,14393       554 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
            SAIG      4     732,16425        70 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            TAB       2     736,6976         27 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_BROJ_DOK WHERE rowid = 'AAAT6/AALAAAcduAAA' ;
            ANRU      2     682,4379         53 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            BORO      2     725,4665         58 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ; BORO (2 '725,4665')
            BIBE      2     651,14393       579 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
            SAIG      4     732,16425        94 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            TAB       2     736,6976         51 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_BROJ_DOK WHERE rowid = 'AAAT6/AALAAAcduAAA' ;
            ANRU      2     682,4379         77 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            BORO      2     725,4665         84 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ;
            ILBA      1     751,4084          4 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
SAIG (4 '732,16425')            BIBE      2     651,14393       616 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
            SAIG      4     732,16425       131 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            ANRU      2     682,4379        114 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            ILBA      1     751,4084         39 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            BORO      2     725,4665        131 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ;
            IVBD      1     642,6661         52 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , RBR , DATKNJ FROM TAB.P_DNEVNIK WHERE rowid = 'AAAUwzAAHAABxFoAAA' ;
            TAB       2     736,6976         27 sec     Share-Table         ACTIVE               ?

    
To kill first from the list, perform: 
    
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '648,18149' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '648,18149' IMMEDIATE;
    
    
RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM DISCONNECT SESSION ''''648,18149'''' IMMEDIATE''; end; '
,instance=>2
);
  commit;
end;
/

This is automated message so, do not reply to this mail.
Regards,
Your auto DBA d.o.o.

Zagreb, 01.06.2010 08:50:45
Just to mention that for delete statements sbr.sql cannot generate blocked records!

The end

Using this approach in blocking session issues, lead you to find "hot spot" tables or "hot spot" records in tables that present critical resources for your applications.

On Oracle Metalink you may find interesting document ID 15476.1-"FAQ about Detecting and Resolving Locking Conflicts" which widely covers locking subjects and presents comprehensive knowledge for studding this issue deeper.
Just for your notes, ID 729727.1-"Detecting blocking Locks in 10.2 and above", which shows a way to find locking issues, is not working on Oracle 11.2, what is not in mine cases.

Now I really think that blocking session theme is covered from all points of view. If you find something that is still not, please speak out and I'll try to present the solution.

Until then...as allways...

Cheers!

5 comments :

  1. Svaka cast na trudu! Htio sam isprobati tvoj sbr.sql skript, ali dobijam gresku:

    SQL> @sbr
    rpad(rec_waiters.waiter_user,10,' ')||
    *
    ERROR at line 141:
    ORA-06550: line 141, column 24:
    PLS-00201: identifier 'REC_WAITERS.WAITER_USER' must be declared
    ORA-06550: line 139, column 7:
    PL/SQL: Statement ignored

    ReplyDelete
  2. Dejane,

    THX for info...
    Agrhhhhhh ... version control software!

    Now is fixed!

    ReplyDelete
  3. Hi Damir,

    In 11R2 (don't know if R1) if you want to know which records are locked, you can use one cursor with "for update skip locked" and another one without it:

    create table test as select rownum r from dual connect by level <= 10;

    update test set r=r*10 where mod(r,2)=0;

    <>

    declare
    cursor c_locked is select rownum row_num, t.* from test t for update skip locked;
    cursor c_free is select rownum row_num, t.* from test t;
    r_locked c_free%rowtype;
    r_free c_free%rowtype;
    begin
    open c_locked;
    open c_free;
    loop
    if r_free.row_num is not null and r_free.row_num < r_locked.row_num then
    fetch c_free into r_free;
    else
    fetch c_locked into r_locked;
    fetch c_free into r_free;
    end if;
    exit when c_free%notfound;
    if r_free.row_num != r_locked.row_num then
    dbms_output.put_line(r_free.row_num);
    end if;
    end loop;
    close c_locked;
    close c_free;
    end;
    /

    It's just a 10-minute code, so it might contain error but I think the idea is ok.

    This way you don't have to write too much code.

    Regards,

    Joaquin Gonzalez.

    ReplyDelete
  4. --Posted again correcting one line.

    Hi Damir,

    In 11R2 (don't know if R1) if you want to know which records are locked, you can use one cursor with "for update skip locked" and another one without it:

    create table test as select rownum r from dual connect by level <= 10;

    update test set r=r*10 where mod(r,2)=0;

    --change to another session

    declare
    cursor c_locked is select rownum row_num, t.* from test t for update skip locked;
    cursor c_free is select rownum row_num, t.* from test t;
    r_locked c_free%rowtype;
    r_free c_free%rowtype;
    begin
    open c_locked;
    open c_free;
    loop
    if r_free.row_num is not null and r_free.row_num < r_locked.row_num then
    fetch c_free into r_free;
    else
    fetch c_locked into r_locked;
    fetch c_free into r_free;
    end if;
    exit when c_free%notfound;
    if r_free.row_num != r_locked.row_num then
    dbms_output.put_line(r_free.row_num);
    end if;
    end loop;
    close c_locked;
    close c_free;
    end;
    /

    It's just a 10-minute code, so it might contain error but I think the idea is ok.

    This way you don't have to write too much code.

    Regards,

    Joaquin Gonzalez.

    ReplyDelete
  5. @Joaquin Gonzalez,

    nice example of new features in 11g database.
    But still there are some processes that cannot be skipped (suppose the number must be in order) so there is not too help for that.
    However, seems that locking problem might be a history in next incoming versions...
    ;-)

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign