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.