I am trying to simulate a solution for a deadlock issue happened on our production server.
Run this to create a table to be used in 2 and 3.
IF (SELECT OBJECT_ID('dbo.MyProduct') ) IS NOT NULL DROP TABLE dbo.MyProduct; GO CREATE TABLE dbo.MyProduct (ProductID INT ,Price MONEY); INSERT INTO dbo.MyProduct VALUES (1, 15.0) select * from MyProduct
create a proc. I set DEADLOCK_PRIORITY LOW to ensure the transaction will be rolled back in case of deadlock.
create PROCEDURE [dbo].[SP_xxx] as DECLARE @Price INT SET DEADLOCK_PRIORITY LOW BEGIN TRAN NormailizePrice SELECT @Price = Price FROM dbo.MyProduct AS mp WITH(REPEATABLEREAD) WHERE mp.ProductID = 1 --Allow transaction 2 to execute*/ WAITFOR DELAY '00:00:10' IF @Price > 10 UPDATE dbo.MyProduct SET Price = Price - 10 WHERE ProductID = 1 COMMIT
the other session. set deadlock priority to high to ensure the transaction will continue in case of deadlock.
--Transaction 2 from Connection 2 SET DEADLOCK_PRIORITY HIGH BEGIN TRAN ApplyDiscount UPDATE dbo.MyProduct SET Price = Price * 0.6 --Discount 40% WHERE Price > 10 COMMIT
Testing code to run the proc in 2. It is supposed to rerun the proc in case of deadlock.
DECLARE @retry TINYINT ,@retrymax TINYINT -- 3 times ,@retrycount TINYINT SELECT @retry = 1 ,@retrymax = 2 ,@retrycount = 0 WHILE @retry = 1 AND @retrycount <= @retrymax BEGIN SET @retry = 0 BEGIN TRY PRINT 'RetryCount: ' + CAST(@retrycount AS VARCHAR(5)) exec SP_xxx END TRY BEGIN CATCH PRINT 'ERROR_NUMBER: ' + CAST(ERROR_NUMBER() AS VARCHAR(5)) PRINT ERROR_MESSAGE() IF (ERROR_NUMBER() = 1205) BEGIN SET @retrycount = @retrycount + 1 SET @retry = 1 END END CATCH END
Now first run 4, then run 3 in 10 seconds. The expected result is a deadlock will happen and 3 will finish and 4 will retry after 3 is finished. But I got this: Why?
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.