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.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *