1 2 Previous Next 15 Replies Latest reply: Mar 5, 2013 7:06 AM by jeneesh RSS

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

    946279
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  (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
                                    thanks! amazing help!
                                    1 2 Previous Next