sql server 2005 deadlock error 3930

Question

I am trying to simulate a solution for a deadlock issue happened on our production server.

1. 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
   

2. 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 
   

3. 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 
   

4. 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
   

5. 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?

RetryCount: 0

ERROR_NUMBER: 1205

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

RetryCount: 1

ERROR_NUMBER: 3930

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.

Answer 1

You must always check the XACT_STATE() inside a catch block. If the value is -1 then you must issue a ROLLBACK.