June 10, 2019 at 11:50 am
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 ?
June 10, 2019 at 12:26 pm
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 *******
June 10, 2019 at 12:37 pm
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