Implementing SQL Server Transaction Retry Logic for failed transactions
By: Daniel Farina | Updated: 2014-03-25 | Comments (6) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Locking and Blocking
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.
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.
The current transaction attempted to update a record that has been updated since the transaction started.
The current transaction failed to commit due to a repeatable read validation failure.
The current transaction failed to commit due to a serializable validation failure.
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.
For those of you who are more into structured programming the next image represents a flow chart of our transaction retry algorithm.
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).
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.
- Here is an introduction to SQL Server 2005 Try and Catch Exception Handling.
- For more information on THROW statement take a look at SQL Server 2012 Throw Statement Introduction.
- Also check out Error Handling Tips Category.
- In this tip you will learn how to use delays in your code for testing: Create delays in SQL Server processes to mimic user input.
Last Updated: 2014-03-25
About the author
View all my tips
- Capturing SQL Server Deadlock Information in XML F...
- Finding and troubleshooting SQL Server deadlocks...
- Implementing SQL Server Transaction Retry Logic fo...
- SQL Server Deadlock Priority Configuration...
- SQL Server Profiler Graphical Deadlock Chain...
- Using a Clustered Index to Solve a SQL Server Dead...
- More SQL Server DBA Tips...