Retry logic for deadlock in while loop

  • Hi DBAs,

    I have a procedure to perform certain operations (insert and update in a table) in while loop with if...else conditions. At times, the operation fails with deadlock for any iteration. I am planning to implement retry logic but want to know how to do it with while loop. Please help me.

    In simple implementation, its straightforward but within while loop, I have my own doubts like parameter values will be retained with retry which are used in while loop and where actually in a proc body should it be implemented ?

  • what are you using the why loop for, what conditions? could you not re-write your query using merge instead of having separate insert and update statements?

     

     

    ***The first step is always the hardest *******

  • Having a loop is an immediate red flag.  Are you sure you can't do it without a loop?

    That said, if you can't avoid the loop, you can implement a retry mechanism like this.

    DECLARE @MaxRetries tinyint;

    -- Do some stuff

    -- Do the insert/update with deadlock retry
    SET @MaxRetries = 3;
    WHILE ( @MaxRetries > 0 )
    BEGIN
    BEGIN TRY
    -- UPDATE ...
    -- INSERT ...

    SET @MaxRetries = 0;
    END TRY
    BEGIN CATCH
    IF ( ERROR_NUMBER() = 1205 ) -- Error is a deadlock
    SET @MaxRetries -= 1;
    ELSE
    THROW; -- Error is NOT a deadlock
    END CATCH;
    END;

    -- Do some more stuff

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply