Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Implementing SQL Server Transaction Retry Logic for failed transactions


By:   |   Last Updated: 2014-03-25   |   Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Locking and Blocking

Problem

Under heavy contention your transactions could be the victim of a deadlock and therefore be rolled back. In this tip I will show you how to implement retry logic to re-run the failed transaction.

Solution

We all know that every RDBMS system has to guarantee the ACID principle (Atomicity, Consistency, Isolation and Durability). A transaction must be either committed or rolled back entirely (Atomicity). SQL Server cannot commit half a transaction because doing so will violate the second principle (Consistency). To keep consistency, concurrent transactions must be independent of each other (Isolation) and changes must persist (Durability).

Although this makes database systems reliable in most circumstances, following these properties is difficult and drastic measures are sometimes taken by SQL Server or any other RDBMS.  That's where deadlocks come to light.

A deadlock happens when two or more tasks block each other because each task has a lock on a resource that the other task(s) are trying to lock. Although you can set deadlock priority for each transaction by using SET DEADLOCK_PRIORITY option, one of them will be killed and you will get this error 1205: "Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

But transaction retry logic isn't limited to correcting deadlocks; there are several other circumstances in which you may want to retry a failed transaction like server timeouts, errors due to concurrent schema modification operations and so on.

SQL Server 2014's Memory-Optimized Tables and Transaction Retry

In SQL Server 2014, the In-Memory OLTP Engine (Hekaton) uses a lock free and latch free optimistic concurrency control and deadlocks cannot occur. But transactions in Hekaton have a validation phase that can set a transaction into a doomed state because of commit dependency failures or isolation level conflicts.

Here is a table with the error numbers you can face by using Memory-Optimized tables.

Error Number

Description

41302

The current transaction attempted to update a record that has been updated since the transaction started.

41305

The current transaction failed to commit due to a repeatable read validation failure.

41325

The current transaction failed to commit due to a serializable validation failure.

41301

A previous transaction that the current transaction took a dependency on has aborted, and the current transaction can no longer commit

SQL Server Transaction Retry Logic

The approach is really simple and requires little code modification. Basically it consists of enclosing the transaction into a TRY ...CATCH block. The TRY ...CATCH block was made available with versions 2005 and above, so if you are still using SQL Server 2000 this is a good reason to migrate.

Without getting into much detail, a TRY ...CATCH block consists of two sections: one contains the actions you want to perform (the TRY section), and the other is what to do if something goes wrong with those actions (the CATCH section). The power of TRY ...CATCH blocks relies on allowing us to trap errors without aborting the batch.

At this point we have half of the problem solved: we are able to handle the exception without aborting the batch and the other half is to find a way to re-execute the batch.

In programming when we talk about repeating an action, we talk about loops. And this is the way: we set a maximum retries count, say three and then repeat our transaction either until it commits or until the maximum retry count is reached. To do so, we have two possibilities: WHILE loops or GOTO unconditional jumps.

The following image is a graphical representation of previous statements, so you can see that retry logic can be easily implemented without too much code.

Transaction Retry Graphical Representation

For those of you who are more into structured programming the next image represents a flow chart of our transaction retry algorithm.

Transaction Retry Flow Chart

Sample SQL Server Transaction Retry Code

Now it's time to do our coding. I will guide you through all the steps.

1 - First we create our sample database

USE [master]
GO
CREATE DATABASE TestDB
 ON PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED , 
FILEGROWTH = 1024KB)
GO

2 - This will be our test table

USE TestDB
GO
IF OBJECT_ID('dbo.Customers','U') IS NOT NULL
  DROP TABLE dbo.Customers
GO
CREATE TABLE dbo.Customers(
  CustomerId        INT NOT NULL IDENTITY(1,1),
  CustomerCode      NVARCHAR(10) NOT NULL,
  CustomerName      NVARCHAR(50) NOT NULL,
  CustomerAddress   NVARCHAR(50) NOT NULL,
  LastModified  DATETIME NOT NULL DEFAULT  GETDATE()
  PRIMARY KEY CLUSTERED (CustomerId)
)
GO

3 - Simulating our deadlock

I am making it very simple: a single transaction consisting of an INSERT to Customers table followed by a delay of ten seconds and then a SELECT to the same table.

You must paste this code in two separate windows and run it simultaneously.

USE TestDB
GO
BEGIN TRANSACTION
INSERT INTO dbo.Customers ( CustomerCode ,
          CustomerName ,
          CustomerAddress
        )
VALUES  ( N'A15C6E' ,
          N'John Doe' ,
          N'Evergreen 1234'
        )
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Customers
COMMIT TRANSACTION

Here is a screen capture with the execution of the previous code from two different query windows (session 56 and session 58).

Window 1 Without Retry Logic


Window 1 Without Retry Logic

As you can see only one window successfully committed and the other one failed to commit due to Error 1205 which is Transaction was deadlocked and has been chosen as the deadlock victim.

4 - To keep our testing clean let's truncate our sample table

USE TestDB
GO
TRUNCATE TABLE dbo.Customers

5 - Sample Retry Logic Implementation

At first this code may be scary by its length, but keep in mind that I added more steps than needed in order to explain how it works.

As before, you must paste this code in two separate windows and run it simultaneously.

USE TestDB
GO
DECLARE @RetryCount INT
DECLARE @Success    BIT
SELECT @RetryCount = 1, @Success = 0
WHILE @RetryCount < =  3 AND @Success = 0
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION
      -- This line is to show you on which execution 
      -- we successfully commit.
      SELECT CAST (@RetryCount AS VARCHAR(5)) +  'st. Attempt'
  
      INSERT  INTO dbo.Customers
      ( CustomerCode ,
      CustomerName ,
      CustomerAddress
      )
      VALUES  ( N'A15C6E' ,
      N'John Doe' ,
      N'Evergreen 1234'
      )
 
      -- This Delay is set in order to simulate failure
      -- DO NOT USE IN REAL CODE!
      WAITFOR DELAY '00:00:05'
  
      SELECT * FROM dbo.Customers
  
      COMMIT TRANSACTION
  
      SELECT 'Success!'
      SELECT @Success = 1 -- To exit the loop
   END TRY
 
   BEGIN CATCH
      ROLLBACK TRANSACTION
 
      SELECT  ERROR_NUMBER() AS [Error Number],
      ERROR_MESSAGE() AS [ErrorMessage];     
  
      -- Now we check the error number to 
      -- only use retry logic on the errors we 
      -- are able to handle.
      --
      -- You can set different handlers for different 
      -- errors
      IF ERROR_NUMBER() IN (  1204, -- SqlOutOfLocks
                              1205, -- SqlDeadlockVictim
                              1222 -- SqlLockRequestTimeout
                              )
      BEGIN
            SET @RetryCount = @RetryCount + 1  
            -- This delay is to give the blocking 
            -- transaction time to finish.
            -- So you need to tune according to your 
            -- environment
            WAITFOR DELAY '00:00:02'  
      END 
      ELSE    
      BEGIN
            -- If we don't have a handler for current error
            -- then we throw an exception and abort the loop
            THROW;
      END
   END CATCH
END

The next two images speak for themselves. We have successfully committed both transactions without errors or warnings.

Window 1 With Retry Logic


Window 2 With Retry Logic
Next Steps


Last Updated: 2014-03-25


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, December 08, 2015 - 4:31:42 PM - Daniel Farina Back To Top

Hi n1k1t0ss!

You are right! My idea was that the code after the try.. catch can execute because we trapped the exeption in the catch block.  

Thank you!


Monday, December 07, 2015 - 8:57:55 PM - n1k1t0ss Back To Top

I think that your solution have a little minus: after all loops, if error still exists, nothing will happen. I mean, that if there would be some other code after try..catch, it would execute successfully, and no errors would be thrown. So you have to rethrow your error after several unsuccessful attempts, like this:

 

DECLARE

@RetryCount INT = 1,

@Success    BIT = 0;

 

WHILE @Success = 0

BEGIN

   BEGIN TRY

      BEGIN TRANSACTION

RAISERROR ('Error raised in TRY block.',  16, 1  );

      COMMIT TRANSACTION 

      SELECT @Success = 1 -- To exit the loop

   END TRY

   BEGIN CATCH

      ROLLBACK TRANSACTION

      select ERROR_MESSAGE() AS [ErrorMessage];     -- we can remove this message, case we will see last error in Results window anyway

      IF ERROR_NUMBER() IN (    50000  ) -- RAISERROR has his code  

      BEGIN

     -- exit from loop after several attemps. 

     if (@RetryCount > =  5) 

   THROW; -- rethrow error after several attemps

            SET @RetryCount = @RetryCount + 1  

            WAITFOR DELAY '00:00:01'  

      END 

      ELSE    THROW; -- If we don't have a handler for current error

   END CATCH

END

select 'You will not see this message, because transaction will throw error after all attempts'


Sunday, March 30, 2014 - 7:20:28 PM - Daniel Farina Back To Top

Yes! although in this sample XACT_ABORT doesn't make a difference, on a real and more complex SP XACT_ABORT should be ON. If you ask me, I would recommend to use XACT_ABORT ON always.

Thanks for reading!


Tuesday, March 25, 2014 - 5:10:05 PM - GO SQL Back To Top

Nice article, however, one thing you have missed is, SET XACT_ABORT ON in your sproc!


Tuesday, March 25, 2014 - 10:03:53 AM - Alec Wood Back To Top

If using SQL Server 2005, replace THROW; with the following code

DECLARE @ErrMsg NVARCHAR(4000)
SELECT  @ErrMsg = ERROR_MESSAGE()
RAISERROR(@ErrMsg, 16, 1)


Learn more about SQL Server tools