Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
All right, problem solved. I forgot to add rollback in the catch block. –  thotwielder Apr 10 '13 at 9:57

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.