Fixing Locking and Blocking Issues in SQL Server - Part 7 - Handling Deadlocks in T-SQL
This is part 7 in a series of posts:
- Part 1 covered being sure there is a locking and blocking issue
- Part 2 covered the impact of RCSI
- Part 3 looked at the impacts of indexing on locking and blocking
- Part 4 looked at what deadlocks really are and how SQL Server handles them
- Part 5 looked at how applications should handle deadlocks
- Part 6 looked at how to avoid deadlocks in the first place
Today, though, I want to look at how to handle deadlocks if you must do that in T-SQL.
The Situation
OK, let’s start with the following situation:
Your application is calling a stored procedure, and it regularly ends up being involved in deadlocks. To make this easy to demonstrate, let’s create a table of data:
[code language=“sql” gutter=“false”]
USE tempdb; GO
DROP TABLE IF EXISTS dbo.Products; GO
CREATE TABLE dbo.Products ( ProductID int NOT NULL CONSTRAINT PK_dbo_Products PRIMARY KEY, ProductName nvarchar(50) NOT NULL, UnitPrice decimal(18,2) NOT NULL, OnHandQuantity decimal(18,3) NULL ); GO
INSERT dbo.Products ( ProductID, ProductName, UnitPrice, OnHandQuantity ) VALUES (1, N’Product 1’, 12.35, 150), (2, N’Product 2’, 14.30, 155), (3, N’Product 3’, 15.80, 165); GO
[/code]
Then we’ll create a stored procedure that can be used to convert a quantity of one product into another product. This can lead to deadlocks:
[code language=“sql” gutter=“false”]
CREATE OR ALTER PROCEDURE dbo.ConvertProduct @FromProductID int, @FromProductQuantity decimal(18,3), @ToProductID int, @ToProductQuantity decimal(18,3) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON;
BEGIN TRAN;
UPDATE dbo.Products SET OnHandQuantity -= @FromProductQuantity WHERE ProductID = @FromProductID;
WAITFOR DELAY ‘00:00:10’;
UPDATE dbo.Products SET OnHandQuantity += @ToProductQuantity WHERE ProductID = @ToProductID;
COMMIT; END; GO
[/code]
Note that I added a delay of 10 seconds between the updates to make it easy for us to generate a deadlock. So let’s cause one now.
In one query window, execute the following:
[code language=“sql” gutter=“false”]
-- Query window 1
USE tempdb; GO
EXEC dbo.ConvertProduct @FromProductID = 2, @FromProductQuantity = 12.0, @ToProductID = 3, @ToProductQuantity = 1;
[/code]
And at the same time, in another query window, execute a similar but different query:
[code language=“sql” gutter=“false”]
-- Query window 2
USE tempdb; GO
EXEC dbo.ConvertProduct @FromProductID = 3, @FromProductQuantity = 1, @ToProductID = 2, @ToProductQuantity = 14.1;
[/code]
No surprise, we get the dreaded deadlock message in one window:
Adding Retry Logic
So how do we fix this? We can either change this procedure to have retry logic, or we can rename this procedure and call it from a wrapper procedure that has the deadlock retry logic.
Let’s do the second option this time.
We’ll recreate the procedure as ConvertProduct_Inner:
[code language=“sql” gutter=“false”]
CREATE OR ALTER PROCEDURE dbo.ConvertProduct_Inner @FromProductID int, @FromProductQuantity decimal(18,3), @ToProductID int, @ToProductQuantity decimal(18,3) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON;
BEGIN TRAN;
UPDATE dbo.Products SET OnHandQuantity -= @FromProductQuantity WHERE ProductID = @FromProductID;
WAITFOR DELAY ‘00:00:10’;
UPDATE dbo.Products SET OnHandQuantity += @ToProductQuantity WHERE ProductID = @ToProductID;
COMMIT; END; GO
[/code]
And then we’ll add a wrapper procedure:
[code language=“sql” gutter=“false”]
CREATE OR ALTER PROCEDURE dbo.ConvertProduct @FromProductID int, @FromProductQuantity decimal(18,3), @ToProductID int, @ToProductQuantity decimal(18,3) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON;
DECLARE @MAXIMUM_RETRIES int = 5;
DECLARE @RemainingRetries int = @MAXIMUM_RETRIES;
WHILE (@RemainingRetries > 0) – retry update for victim BEGIN BEGIN TRY BEGIN TRANSACTION;
EXEC dbo.ConvertProduct_Inner @FromProductID = @FromProductID, @FromProductQuantity = @FromProductQuantity, @ToProductID = @ToProductID, @ToProductQuantity = @ToProductQuantity;
SET @RemainingRetries = 0;
COMMIT; END TRY BEGIN CATCH IF (ERROR_NUMBER() = 1205) – deadlock victim BEGIN SET @RemainingRetries -= 1; PRINT ‘Warning: Deadlock occurred’; PRINT ‘Remaining retries: ’ + CAST(@RemainingRetries AS varchar(20)); END ELSE BEGIN SET @RemainingRetries = -1; END; IF XACT_STATE() <> 0 BEGIN ROLLBACK TRANSACTION; END; END CATCH; END; END; GO
[/code]
Now I’ll execute both procedures again, and notice that both complete as expected. One has a warning about a deadlock that had occurred but was retried:
This is great, and is something we couldn’t do before SQL Server 2005 so many people weren’t used to doing it. However, while this will help at the T-SQL end, most other types of retries need to happen at the client end.
2019-05-30