A deadlock is a situation wherein two transactions wait for each other to give up their respective locks.
When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
The Deadlock
Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.
The Deadlock Situation
The below example shows the deadlock situation between the two transactions.
Transaction A
BEGIN TRANSACTION
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
Transaction B
BEGIN TRANSACTION
UPDATE Orders SET ShippingId = 12 WHERE OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
If both the transactions are executed at the same time, then Transaction A locks and updates Customer table whereas transaction B locks and updates Orders table. After a delay of 5 ms, transaction A looks for the lock on Orders table which is already held by transaction B and transaction B looks for lock on Customer table which is held by transaction A. So both the transactions cannot proceed further, the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.
(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 52) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
But what if you don't like the default behavior (aborting the transaction)? Can you change it? Yes, you can, by rewriting Transactions A and B as shown below.
Transaction A
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH
Transaction B
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH
Here I have used Label RETRY at the beginning of both the transactions. The TRY/CATCH method is used to handle the exceptions in the transactions. If the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back, and if the exception is occurred due to deadlock (Error_Number 1205), the transaction waits for 5 milliseconds. The delay is used here because the other transaction (which is not aborted) can complete its operation within delay duration and release the lock on the table which was required by the aborted transaction. You can increase the delay according to the size of your transactions. After the delay, the transaction starts executing from the beginning (RETRY: Label RETRY at the beginning of the transaction) using the below statement:
GOTO RETRY -- Go to Label RETRY
This statement is used to transfer the control to the label named RETRY (which is at the beginning).
Now Execute the Transaction A and Transaction B at the same time. Both the transactions will execute successfully. Have a look into the outputs of the transaction where the exception occurred.
Hide Copy Code
(1 row(s) affected)
Rollback Transaction
(1 row(s) affected)
(1 row(s) affected)
Using RetryCounter
Now, I guess you understood how to handle deadlock without aborting the transaction. Let's move to the next interesting topic about deadlock. Imagine if there are more than two processes that read/update the Customer or Orders table at the same time. Below, I have modified both the transactions where I have shown how we can use RetryCounter to solve the problem.
Transaction A
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if
-- still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH
Transaction B
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message
-- if still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH
The RetryCounter variable used here gives a chance for the transaction to execute again if it fails due to deadlock (Error_Number 1205). In this example, the transaction can try to execute up to three times if it fails due to a deadlock. This scenario would be very useful if the transaction looking for the lock which was not released by the other transactions for a long time. So the transaction can try three times to check whether the required lock is available.
When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
The Deadlock
Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.
The Deadlock Situation
The below example shows the deadlock situation between the two transactions.
Transaction A
BEGIN TRANSACTION
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
Transaction B
BEGIN TRANSACTION
UPDATE Orders SET ShippingId = 12 WHERE OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
If both the transactions are executed at the same time, then Transaction A locks and updates Customer table whereas transaction B locks and updates Orders table. After a delay of 5 ms, transaction A looks for the lock on Orders table which is already held by transaction B and transaction B looks for lock on Customer table which is held by transaction A. So both the transactions cannot proceed further, the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.
(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 52) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
But what if you don't like the default behavior (aborting the transaction)? Can you change it? Yes, you can, by rewriting Transactions A and B as shown below.
Transaction A
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH
Transaction B
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH
Here I have used Label RETRY at the beginning of both the transactions. The TRY/CATCH method is used to handle the exceptions in the transactions. If the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back, and if the exception is occurred due to deadlock (Error_Number 1205), the transaction waits for 5 milliseconds. The delay is used here because the other transaction (which is not aborted) can complete its operation within delay duration and release the lock on the table which was required by the aborted transaction. You can increase the delay according to the size of your transactions. After the delay, the transaction starts executing from the beginning (RETRY: Label RETRY at the beginning of the transaction) using the below statement:
GOTO RETRY -- Go to Label RETRY
This statement is used to transfer the control to the label named RETRY (which is at the beginning).
Now Execute the Transaction A and Transaction B at the same time. Both the transactions will execute successfully. Have a look into the outputs of the transaction where the exception occurred.
Hide Copy Code
(1 row(s) affected)
Rollback Transaction
(1 row(s) affected)
(1 row(s) affected)
Using RetryCounter
Now, I guess you understood how to handle deadlock without aborting the transaction. Let's move to the next interesting topic about deadlock. Imagine if there are more than two processes that read/update the Customer or Orders table at the same time. Below, I have modified both the transactions where I have shown how we can use RetryCounter to solve the problem.
Transaction A
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if
-- still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH
Transaction B
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message
-- if still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH
The RetryCounter variable used here gives a chance for the transaction to execute again if it fails due to deadlock (Error_Number 1205). In this example, the transaction can try to execute up to three times if it fails due to a deadlock. This scenario would be very useful if the transaction looking for the lock which was not released by the other transactions for a long time. So the transaction can try three times to check whether the required lock is available.
