This discussion is archived
9 Replies Latest reply: Oct 9, 2012 12:20 AM by TPD-Opitz-Consulting-com RSS

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

946279 Newbie
Currently Being Moderated
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-Consulting-com Expert
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    > {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. Guru
    Currently Being Moderated
    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-Consulting-com Expert
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Oracle ACE Director
    Currently Being Moderated
    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-Consulting-com Expert
    Currently Being Moderated
    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

Legend

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