9 Replies Latest reply: Oct 9, 2012 2:20 AM by TPD-Opitz RSS

    play it again, Sam (ie. retry the statement)

    946279
      there is a merge statement that causes ORA-08006 error from time to time: specified row no longer exists. I found out that the main solution cited on the internet is to retry the operation:
      ORA-08006: specified row no longer exists
      Cause: the row has been deleted by another user since the operation began
      Action: re-try the operation
      So I thought about wrapping it into pl/sql the way so that the merge would be re-tried after some time and then, if it failed again, the exception would be thrown to upper layer of application (here: shell). I came up with the following code below. I would appreciate your opinion, especcially if there should be something else incorporated to improve the code or maybe some other approach would be better, or maybe I should pay attention to other aspects.

      thank you
      create or replace
      package tst as 
        procedure main;
      end tst;
      /
      
      create or replace
      package body tst as
        do_merge_cnt pls_integer := 0;
      
        ora08006 exception;
        pragma exception_init(ora08006, -08006);
        
        /* here the exception is simulated */
        procedure throw8006 is begin raise ora08006; end; 
        
        /* this procedure will include original merge statement */
        procedure do_merge is
        begin
          do_merge_cnt := do_merge_cnt + 1; -- increase "failure" counter
          case 
            when do_merge_cnt < 10 then throw8006;
            else null;
          end case;
      
        exception
          when ora08006 then
            case when do_merge_cnt < 2 then -- if counter < 2 then re-try after a nap
              dbms_lock.sleep(5);
              do_merge;
            else
              raise;
            end case;
          when others then raise;      
        end;
      
        procedure main is
        begin
          do_merge;
        end;
      end tst;
      /
        • 1. Re: play it again, Sam (ie. retry the statement)
          TPD-Opitz
          I'd move the exeption handling to your main procedure.
          procedure do_merge is
            begin
              do_merge_cnt := do_merge_cnt + 1; -- increase "failure" counter
              if   do_merge_cnt < 10 then 
                  throw8006;
              end if;
            end do_merge;
           
          procedure main is
            begin
              do_merge;
            exception
              when ora08006 then
                  dbms_lock.sleep(5);
                  do_merge;
            end main;
          This way you don't need aditional logic to to end the recursion.

          bye
          TPD
          • 2. Re: play it again, Sam (ie. retry the statement)
            Sven W.
            > {quote:title=TPD Opitz-Consulting com wrote:}{quote} Sorry but this doesn't make any sense. You would throw an error even if there was no error, just to be able to wait 5 seconds and run the merge again? THis second merge would probably merge like.... 0 rows?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
            • 3. Re: play it again, Sam (ie. retry the statement)
              Sven W.
              I see no need to do something recursively here. As mentioned in your other thread it is wrong to retry the statement. But if you don't find the real cause for the issue, you can of cause simply run the merge statement 10 times.

              untested pseudo code
              ...
              for i in 1..10 loop
                 begin
                    do_merge_statement;
                    exit; /* leave the loop when the merge was successfull */
                 exception
                    when ora08006 then
                        null; /* repeat the merge */
                 end;
                 if i <10 then /* no need to wait 5 secs after the last merge */
                   dbms_lock.sleep(5);
                 end if;
              end loop;
              ...
              This is still APSA (a pretty stupid approach).
              I would consider to fire any of my programmers that do it in such a way. They would need a very very very solid reasoning, why they build such a performance bottleneck.

              Edited by: Sven W. on Oct 8, 2012 8:37 PM

              Edited by: Sven W. on Oct 8, 2012 8:40 PM - added if
              • 4. Re: play it again, Sam (ie. retry the statement)
                TPD-Opitz
                Sven W. wrote:
                >

                Sorry but this doesn't make any sense. You would throw an error even if there was no error,
                No.

                * If the first call to <tt>do_merge</tt> succeeds main exits normally.

                * If <tt>do_merge</tt> fails at the first call in <tt>main</tt> the exception block of <tt>main</tt> waits and calls <tt>do_merge</tt> again.
                - If this second call succeeds <tt>main</tt> also exits normally.
                - Ony if the second call of <tt>do_merge</tt> from the exception block of <tt>main</tt> fails <tt>main</tt> exits with an exception.

                AFAIS this is what the OP wanted. (But it's true: finding the root cause would be the better approach...)
                just to be able to wait 5 seconds and run the merge again?
                This is what the OP wanted.
                THis second merge would probably merge like.... 0 rows?
                Maybe, maybe not, depending on the cause of the exception.

                bye
                TPD
                • 5. Re: play it again, Sam (ie. retry the statement)
                  Sven W.
                  TPD Opitz-Consulting com wrote:
                  * If the first call to <tt>do_merge</tt> succeeds main exits normally.
                  I've now seen that you copied this wrong logic from the op. However it is still wrong.

                  Each call to do_merge will raise an exception.
                  This code part does it. After the first merge the do_merge_cnt = 1.
                  if   do_merge_cnt < 10 then 
                          throw8006;
                      end if;
                  1<10 => true => throw exception => sleep => do_merge again => raise exception again => error out with an exception.
                  • 6. Re: play it again, Sam (ie. retry the statement)
                    damorgan
                    Why not eliminate the possibility that anyone can delete a row while you are working on it and kill all of this superfluous code by using SELECT FOR UPDATE?

                    http://www.morganslibrary.org/reference/deadlocks.html#dlfu

                    If you lock 'em ... no one can delete 'em until after you are done.

                    Which all brings up the really interesting point that it seems you have a business rules problem that needs to be addressed ... why are two different processes simultaneously updating (the row has value) and deleting (the row has no value) within the application. Get into a conference room and sort out what you are doing.
                    • 7. Re: play it again, Sam (ie. retry the statement)
                      rp0428
                      >
                      Why not eliminate the possibility that anyone can delete a row while you are working on it and kill all of this superfluous code by using SELECT FOR UPDATE?
                      >
                      Hmmm. . . I'm not aware of any way to use a FOR UPDATE clause with a MERGE statement.
                      • 8. Re: play it again, Sam (ie. retry the statement)
                        damorgan
                        Nor did I suggest using it with a MERGE statement.

                        I suggested locking the rows before running the statement ... or ... alternatively ... locking the rows prior to performing the delete.

                        But the most important aspect of this issue is the collision in the business rules. Either the row is of value to the organization or it is not. You can not have it both ways and as a matter of business process they need to figure out which it is.
                        • 9. Re: play it again, Sam (ie. retry the statement)
                          TPD-Opitz
                          Sven W. wrote:
                          I've now seen that you copied this wrong logic from the op. However it is still wrong.
                          The shown implementation of <tt>do_merge</tt> is (hopefully) only explaining the problem, not the real production code. So yes: it does not make sense (other than forcing an exception).

                          bye
                          TPD