This business case is closely related to another post: Reduce Deadlocks
Business Case: A multi-client application frequently updates data. Occasionally, transactions fail with resulting loss of information and application instability. The application team has requested a solution in the database layer.
Environment: It has been determined that deadlock on lock resources is the primary cause of the failed transactions.
Approach: While there are a handful of ways to help mitigate locked resource deadlocks, in this instance we will simply fortify our database with a retry capability. In other words, if the first attempt at transaction execution fails, we will retry the transaction a couple more times. For deadlocks due to frequent, relatively short-duration transactions, this fortification can often due the trick.
Demonstration Preparation: For demonstration purposes, first we will create two tables with a handful of records in each. We will then create update queries that, when run back to back in separate processes, will produce a deadlock situation. For this example we will assume our database is named “DB” and will be working within the “dbo” schema.
-- If the first table already exists, drop it
IF OBJECT_ID('DB.dbo.TableOne') IS NOT NULL
DROP TABLE DB.dbo.TableOne
;
-- Create the first table
CREATE TABLE DB.dbo.TableOne (
ID INT)
;
-- Populate the first table with data
INSERT INTO DB.dbo.TableOne
VALUES
(1)
, (2)
, (3)
;
-- If the second table already exists, drop it
IF OBJECT_ID('DB.dbo.TableTwo') IS NOT NULL
DROP TABLE DB.dbo.TableTwo
;
-- Create the second table
CREATE TABLE DB.dbo.TableTwo (
ID INT)
;
-- Populate the second table with data
INSERT INTO DB.dbo.TableTwo
VALUES
(10)
, (11)
, (12)
;
Now we will create two queries that, when run within a few seconds of each other in separate processes, will produce a locked resource deadlock.
-- This transaction should be run in one query window (process)
BEGIN TRANSACTION
UPDATE DB.dbo.TableOne
SET ID = 1
;
-- Delay execution for three seconds, to give
-- the second transaction time to start
WAITFOR DELAY '00:00:03'
;
UPDATE DB.dbo.TableTwo
SET ID = 11
;
COMMIT TRANSACTION
-- This transaction should be run in a second query window (process)
BEGIN TRANSACTION
UPDATE DB.dbo.TableTwo
SET ID = 12
;
-- Delay execution for three seconds, to give
-- the first transaction time to create the
-- deadlock
WAITFOR DELAY '00:00:03'
;
UPDATE DB.dbo.TableOne
SET ID = 2
;
COMMIT TRANSACTION
When the two transactions above are run within three seconds of each other, in separate windows (processes), it will create a deadlock and SQL Server will decide the second transaction is the victim and cancel its execution.
Solution: To fortify these transactions, we can build in a retry capability. For demonstration purposes, we will only fortify the second transaction; in the real world, all transactions could be fortified depending on business/application requirements.
Our fortification technique will include creating a few variables to capture any error message, iterate through transaction attempts, and specify the maximum number of attempts we will try before allowing the transaction to fail. Then we will create a WHILE loop to iterate through attempts, and wrap our transaction in a TRY CATCH block to trap execution failures.
-- Declare and initialize variables
DECLARE @ErrorMessage NVARCHAR(2000) = '';
DECLARE @Iteration TINYINT = 0;
DECLARE @IterationLimit TINYINT = 2;
-- Begin the iteration via WHILE operation
WHILE
-- Conditions for iteration
@ErrorMessage IS NOT NULL --null indicates successful execution
AND @Iteration < @IterationLimit -- do not exceed iteration limit
BEGIN
-- Increment the iteration counter
SET @Iteration += 1;
-- Attempt to execute the transaction
BEGIN TRY
BEGIN TRANSACTION
UPDATE DB.dbo.TableTwo
SET ID = 12
;
-- Delay execution for three seconds, to give
-- the first transaction time to create the
-- deadlock
WAITFOR DELAY '00:00:03'
;
UPDATE DB.dbo.TableOne
SET ID = 1
;
-- Capture the error message
SET @ErrorMessage = ERROR_MESSAGE()
;
COMMIT TRANSACTION
END TRY
-- Catch any execution errors
BEGIN CATCH
-- Notify if iteration limit is reached
IF @Iteration = @IterationLimit
SELECT 'Iteration reached; last error: ' + @ErrorMessage
;
-- Rollback the transaction
ROLLBACK TRANSACTION
END CATCH
END
T-SQL Dev 