Retry logic for deadlock in while loop

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  June 10, 2019 at 11:50 am

  #3651767

  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 ?

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  June 10, 2019 at 12:26 pm

  #3651775

  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 *******

• DesNorton

  SSC-Insane

  Points: 24461

  More actions

  June 10, 2019 at 12:37 pm

  #3651784

  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
  

   

  ---

  How to post data/code on a forum to get the best help.