sql server 2005 deadlock error 3930 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 &gt; 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 &gt; 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 &lt;= @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?


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.
You must always check the XACT_STATE() inside a catch block. If the value is -1 then you must issue a ROLLBACK.