Deadlock - Lets Solve it

Many Thanks for Balmukund and Roji for helping me learn and write this post. The more I learn, The more i understand I know less and need to learn more.....

From Bart Blog Definition is - A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed

It is two processes Holding locks and waiting to acquire lock on resource held by other process. This results in a deadlock

Lets Try to Repro a Simple Cyclic Deadlock. Lets setup required tables.

• Transaction A now holds an exclusive lock on row 1, and is blocked until transaction B finishes and releases the share lock it has on row 2.
• Transaction B now holds an exclusive lock on row 2, and is blocked until transaction A finishes and releases the share lock it has on row 1.

Step 1 - Setup Tables for Demo (SQL 2008 R2)

USE tempdb
IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
IF OBJECT_ID ('TestTable2') IS NOT NULL DROP TABLE TestTable2
CREATE TABLE TestTable1
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL
)
CREATE TABLE TestTable2
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL
)

STEP 2 - Populate Data
DECLARE @I INT
SET @I = 100
WHILE 1 = 1
BEGIN
    SET @I = @I + 1
    INSERT INTO TestTable1(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    INSERT INTO TestTable2(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    IF @I=10000
    BREAK;
END

STEP 3 - Transaction A in New Query Editior Window

USE tempdb
    BEGIN TRAN
    UPDATE TestTable1 SET Name = 'Updated Name'
    WHERE Number = 1000
    WAITFOR DELAY '00:00:15'
    UPDATE TestTable2 SET Name = 'Updated Name'
    WHERE Number = 1000

STEP 4 - Transaction B in New Query Editor Window
USE tempdb

    BEGIN TRAN
    UPDATE TestTable2 SET Name = 'Updated Name'
    WHERE Number = 1000
    WAITFOR DELAY '00:00:15'
    UPDATE TestTable1 SET Name = 'Updated Name'
   WHERE Number = 1000

STEP 5 - Got Deadlock for one of the transaction

STEP 6 - Modify code to handle Deadlock and retry
Have a Look at Attachment and Demo Code by Adam Mechanic on Best Practices for Exception Handling and Defensive Programming

Modify the code as below for retry attempt logic

STEP 7 - Transaction A in NEW Window
USE tempdb

DECLARE @Retries INT
SET @Retries = 3
WHILE @Retries > 0
BEGIN
BEGIN TRAN
     BEGIN TRY
        UPDATE TestTable2 SET Name = 'Updated Name XXX1'
        WHERE Number = 1000
        SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
        RAISERROR ('Inside Tran2', 0, 1) WITH NOWAIT
        WAITFOR DELAY '00:00:35'
        UPDATE TestTable1 SET Name = 'Updated Name XXX2'
        WHERE Number = 1000
        COMMIT TRAN
        BREAK;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Inside Tran2 Deadlock', 0, 1) WITH NOWAIT
            SET @Retries = @Retries - 1
            IF @Retries = 0
           BREAK;
        ELSE
           CONTINUE
       END
      ELSE
      BEGIN
         RAISERROR ('Test Message', 0, 1) WITH NOWAIT
         SET @Retries = 0
      END
    END CATCH
END
GO
STEP 8 - Transaction B in New Window
USE tempdb

DECLARE @Retries INT
SET @Retries = 3
WHILE @Retries > 0
BEGIN
    BEGIN TRAN
    BEGIN TRY
    UPDATE TestTable1 SET Name = 'Updated Name XXX1'
    WHERE Number = 1000
    SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
    RAISERROR ('Inside Tran2', 0, 1) WITH NOWAIT
    WAITFOR DELAY '00:00:35'
    UPDATE TestTable2 SET Name = 'Updated Name XXX2'
    WHERE Number = 1000
    COMMIT TRAN
BREAK;
END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Inside Tran2 Deadlock', 0, 1) WITH NOWAIT
            SET @Retries = @Retries - 1
            IF @Retries = 0
            BREAK;
            ELSE
                CONTINUE
       END
       ELSE
       BEGIN
            RAISERROR ('Test Message', 0, 1) WITH NOWAIT
            SET @Retries = 0
       END
    END CATCH
END
GO
STEP 9 - Output for Transactions
Transaction A
(1 row(s) affected)
(3 row(s) affected)
Inside Tran1
(1 row(s) affected)

Transaction B

(1 row(s) affected)
(3 row(s) affected)
Inside Tran2
Inside Tran2 Deadlock
(1 row(s) affected)
(3 row(s) affected)
Inside Tran2
(1 row(s) affected)

Step 10 - You see the transaction deadlocks and retry logic takes care of retry attempts
Hope this is useful and you can use it in your implementation. Thanks to balmukund and roji...
Happy Reading.......
Reference - Try-Catch to resolve Deadlocks
Behavior of WITH NOWAIT option with RAISERROR in SQL Server

Repro a Classic Deadlock Scenario
STEP 1
USE deadlocktest

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TestTable1 WHERE Number = 1000
--DO SOME Processing
WAITFOR DELAY '00:00:10'
UPDATE TestTable1 SET Name = 'Updated Name'
WHERE Number = 1000

STEP 2
USE deadlocktest

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TestTable1 WHERE Number = 1000
--DO SOME Processing
WAITFOR DELAY '00:00:10'
UPDATE TestTable1 SET Name = 'Updated Name'
WHERE Number = 1000

Both Transactions STEP1 and STEP2 listed above would deadlock each other.

More Reads - The Curious Case of the Dubious Deadlock and the Not So Logical Lock

Happy Reading!!