This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Mar 5, 2013 5:06 AM by jeneesh RSS

SELECT for update + WAIT: why this doesn't work?

946279 Newbie
Currently Being Moderated
here is sample data:
create table t1 (x number, y number);
create table t2 (x number, y number);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
insert into t2 values(2,2);
commit;
session 1:
update t1 set y = 20 where x = 2; -- do not commit here
session 2:
declare
  cursor c is ( select t1.rowid as t1rid, t2.x, t2.y
  from t1, t2
  where t1.x = t2.x ) for update wait 300;

  type c_t is table of c%rowtype;
  c_v c_t;
begin
  open c;
  loop
    fetch c bulk collect into c_v;
    exit when c%notfound;

    forall i in 1..c_v.count
      update t1 set y = 40 where rowid = c_v(i).t1rid;
  end loop;
  close c;        
end;
now session 2 waits because the row is locked in session 1. do a rollback in session 1. session 2 continues but doesn't update a row. why?

thank you
  • 1. Re: SELECT for update + WAIT: why this doesn't work?
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    session 2 should wait for 300 (5 min) seconds and if the row is still lock it will end with this error:
    Error at line 1
    ORA-30006: resource busy; acquire with WAIT timeout expired
    ORA-06512: at line 2
    ORA-06512: at line 9
    Edit: sorry, misunderstood the question. Jeneesh already answered in the meantime.

    Regards.
    Al

    Edited by: Alberto Faenza on Mar 5, 2013 1:02 PM
  • 2. Re: SELECT for update + WAIT: why this doesn't work?
    jeneesh Guru
    Currently Being Moderated
    Change your code in session2 as below..Now it will work as you expect..Hopefully you understood the issue now..
    declare
      cursor c is ( select t1.rowid as t1rid, t2.x, t2.y
      from t1, t2
      where t1.x = t2.x ) for update wait 300;
     
      type c_t is table of c%rowtype;
      c_v c_t;
    begin
      open c;
      loop
        fetch c bulk collect into c_v;
        exit when c_v.count = 0;--"Look at the change"
     
        forall i in 1..c_v.count
          update t1 set y = 40 where rowid = c_v(i).t1rid;
      end loop;
      close c;        
    end;
  • 3. Re: SELECT for update + WAIT: why this doesn't work?
    jeneesh Guru
    Currently Being Moderated
    Or you could code like this also - Then also you will get expected output..
    declare
      cursor c is ( select t1.rowid as t1rid, t2.x, t2.y
      from t1, t2
      where t1.x = t2.x ) for update wait 300;
     
      type c_t is table of c%rowtype;
      c_v c_t;
    begin
      open c;
      loop
        fetch c bulk collect into c_v;
         --exit when c%notfound; --"Commented"
     
        forall i in 1..c_v.count
          update t1 set y = 40 where rowid = c_v(i).t1rid;
        exit when c%notfound; --"Added"
      end loop;
      close c;        
    end;
  • 4. Re: SELECT for update + WAIT: why this doesn't work?
    946279 Newbie
    Currently Being Moderated
    jeneesh, great help! I see the change, it indeed works now, but I don't quite get it. why it didn't work with c%notfound. could you explain please? my reasoning was that it tries to open a cursor, sees that it cannot lock all the rows, starts to wait for max. 300s, at some point in time the lock is released, cursor is opened with one row and where and why my reasoning is wrong?

    thank you
  • 5. Re: SELECT for update + WAIT: why this doesn't work?
    jeneesh Guru
    Currently Being Moderated
    jeneesh wrote:
    Change your code in session2 as below..Now it will work as you expect..Hopefully you understood the issue now..
    declare
    cursor c is ( select t1.rowid as t1rid, t2.x, t2.y
    from t1, t2
    where t1.x = t2.x ) for update wait 300;
    
    type c_t is table of c%rowtype;
    c_v c_t;
    begin
    open c;
    loop
    fetch c bulk collect into c_v;
    exit when c_v.count = 0;--"Look at the change"
    
    forall i in 1..c_v.count
    update t1 set y = 40 where rowid = c_v(i).t1rid;
    end loop;
    close c;        
    end;
    It is no way related to locks..

    Even if you don' have a lock (And no NOWAIT)., you will get the same result..

    In BULK COLLECT, the last fetch will always result in %NOTFOUND

    (You can try BULK COLLECT with LIMIT and check that..)

    Thats why when you put %NOTFOUND at the end, it is working fine

    You can see Tom's Sample here
  • 6. Re: SELECT for update + WAIT: why this doesn't work?
    padders Pro
    Currently Being Moderated
    In BULK COLLECT, the last fetch will always result in %NOTFOUND
    Yes, agree.

    Another way to put it might be %NOTFOUND is always TRUE for BULK COLLECT unless LIMIT is hit.
  • 7. Re: SELECT for update + WAIT: why this doesn't work?
    jeneesh Guru
    Currently Being Moderated
    padders wrote:

    Another way to put it might be %NOTFOUND is always TRUE for BULK COLLECT unless LIMIT is hit.
    That is a better way to say it.. :)

    English is not my first language..
  • 8. Re: SELECT for update + WAIT: why this doesn't work?
    padders Pro
    Currently Being Moderated
    Your way was fine, really.

    Sometimes it just helps to state things in more than one way - so I added how I think of it.
  • 9. Re: SELECT for update + WAIT: why this doesn't work?
    jeneesh Guru
    Currently Being Moderated
    And the main point to notice is that - You actually dont need a LOOP in the code, since you are not using LIMIT...
  • 10. Re: SELECT for update + WAIT: why this doesn't work?
    946279 Newbie
    Currently Being Moderated
    thanks!

    I followed your remarks and found this interesting article:

    http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

    I have to re-read it again and give it a second thought but I grasped the idea. so, if there is no LIMIT clause this code should be OK, right?
      open c;
      fetch c bulk collect into c_v;
      forall i in 1..c_v.count
        update t1 set y = 40 where rowid = c_v(i).t1rid;
      close c;  
    while when I decide to process in "batches" this one should stay, right?
      open c;
      loop
        fetch c bulk collect into c_v limit 1000;
        exit when c_v.count = 0;
     
        forall i in 1..c_v.count
          update t1 set y = 40 where rowid = c_v(i).t1rid;
      end loop;
      close c;  
    my concern is that I don't understand the way WAIT works when incorporated in above scenario. is it:
    a) delaying opening a cursor for declared time (max) or untill the lock can be placed on all rows? OR
    b) cursor opens anyway, it is bulk update which is waiting OR
    c) something else?

    could you please in a few words describe what is happening within the scenario above with two sessions?

    thank you
  • 11. Re: SELECT for update + WAIT: why this doesn't work?
    jeneesh Guru
    Currently Being Moderated
    943276 wrote:
    thanks!

    I followed your remarks and found this interesting article:

    http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

    I have to re-read it again and give it a second thought but I grasped the idea. so, if there is no LIMIT clause this code should be OK, right?
    open c;
    fetch c bulk collect into c_v;
    forall i in 1..c_v.count
    update t1 set y = 40 where rowid = c_v(i).t1rid;
    close c;  
    You are absolutely right, now..
    while when I decide to process in "batches" this one should stay, right?
    open c;
    loop
    fetch c bulk collect into c_v limit 1000;
    exit when c_v.count = 0;
    
    forall i in 1..c_v.count
    update t1 set y = 40 where rowid = c_v(i).t1rid;
    end loop;
    close c;  
    This also looks fine.. (Hopefully, you are just learning..the samething can be done with a single UPDATE
    my concern is that I don't understand the way WAIT works when incorporated in above scenario. is it:
    a) delaying opening a cursor for declared time (max) or untill the lock can be placed on all rows? OR
    This is what is happening..
    b) cursor opens anyway, it is bulk update which is waiting OR
    c) something else?

    could you please in a few words describe what is happening within the scenario above with two sessions?

    thank you
  • 12. Re: SELECT for update + WAIT: why this doesn't work?
    946279 Newbie
    Currently Being Moderated
    you wrote in a way that I'm not sure if you're referencing to (a) or (b) :) is that (a) what is happening?

    thank you
  • 13. Re: SELECT for update + WAIT: why this doesn't work?
    jeneesh Guru
    Currently Being Moderated
    (a)

    And you can test it easily..
    create table test_lock(rn number);
    
    
    
    declare
      cursor c is ( select t1.rowid as t1rid, t2.x, t2.y
      from t1, t2
      where t1.x = t2.x ) for update wait 300;
     
      type c_t is table of c%rowtype;
      c_v c_t;
    begin
      --"Added"
      insert into test_lock values(1);
      commit;
      open c;
      --"Added"
      insert into test_lock values(2);
      commit;
      loop
        fetch c bulk collect into c_v;
         --exit when c%notfound;
     
        forall i in 1..c_v.count
          update t1 set y = 40 where rowid = c_v(i).t1rid;
        exit when c%notfound;
      end loop;
      close c;        
    end;
    Now monitor, what is getting populated in TEST_LCOK, when your SESSION2 is in WAIT mode..

    you will see only the record "1" in test_lcok..

    Edited by: jeneesh on Mar 5, 2013 6:33 PM
  • 14. Re: SELECT for update + WAIT: why this doesn't work?
    946279 Newbie
    Currently Being Moderated
    thanks! amazing help!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points