Deadlock in SQL Server 2012

Introduction

Today, I have provided an article showing you how to handle deadlock in SQL Server 2012. Deadlock is a special situation in which two transactions block each other. The first transaction has a lock on some database object that the other transaction wants to access and vice versa. Several transactions can cause a deadlock by building a circle of dependencies. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

For example

Suppose we create two tables in a database named UserDetail and Countrydetail. The First transaction attempts to update the table UserDetail and subsequently read/update data from the Countrydetail table, whereas the second transaction attempts to update the Countrydetail table and subsequently read/update data from the UserDetail table. In such situations, the first transaction has a lock on some database object that the other transaction is accessing and vice versa, causing a deadlock by building a circle of dependencies. Neither transaction can complete until the other transaction releases locks. That type of situation is called a Deadlock. The following figure represents a deadlock:

When a deadlock occurs

These are the following conditions when a deadlock can occur:

1. Mutual Exclusion - A resource is assigned to one process. Requests are delayed until the resources are released.
2. Hold and wait - One process holds one resource and also waits for another.
3. No preemption - Only a process can hold a resources when it's released from another process.
4. Circular wait - The first transaction has a lock on some database object that the other transaction wants to access and vice versa, causing a deadlock by building a circle of dependencies.

Creating second table named countryDetail

The table looks as in the following:

CREATE TABLE countryDetail(                                            

  CountryID INTEGER NOT NULL PRIMARY KEY,  

   City VARCHAR(15),    

   countryName VARCHAR(15)                                                                       

  )

  go

INSERT INTO countryDetail  VALUES (1, 'Agra', 'india');                 

INSERT INTO countryDetail  VALUES (2, 'cap town', 'south africa');              

INSERT INTO countryDetail  VALUES (3, 'NEW YORK', 'USA');

 

Output

 

Creating first table named UserDetail

The table looks as in the following:

 

Deadlock situation

The following example shows the deadlock situation between the two transactions. The following two queries should be executed in parallel on two separate connections to illustrate how SQL Server can handle deadlock situations. The first connection attempts to update FactResellerSales and read the FactFinance table; the second connection attempts to update FactFinance and read the FactResellerSales table.

Transaction A

--Transaction A

BEGIN

TRANSACTION

UPDATE userdetail  SET firstname ='rahul' WHERE user_id  =1

WAITFOR DELAY '00:00:05' -- Wait for 5 ms

UPDATE countrydetail  SET countryname ='pakistan' WHERE Countryid =2

COMMIT TRANSACTION

 

Transaction B

--Transaction B

BEGIN TRANSACTION

UPDATE countrydetail  SET countryname = 'sri lanka' WHERE Countryid =2

WAITFOR DELAY '00:00:05' -- Wait for 5 ms

UPDATE userdetail  SET firstname ='rahul' WHERE user_id  =1

COMMIT TRANSACTION

 

If both transactions execute at the same time then a deadlock will occur and the system return the following output:

Output

Handling the deadlock situation

Rewriting the transaction A and B with try and catch as shown below:

Transaction A

RETRY:

BEGIN TRANSACTION

BEGIN TRY

UPDATE userdetail  SET firstname ='rahul' WHERE user_id  =1

WAITFOR DELAY '00:00:05' -- Wait for 5 ms

UPDATE countrydetail  SET countryname ='pakistan' WHERE Countryid =2

 

      COMMIT TRANSACTION

END TRY

BEGIN CATCH

      PRINT 'Rollback Transaction'

      ROLLBACK TRANSACTION

      IF ERROR_NUMBER() = 1205 -- Deadlock Error Number

      BEGIN

            WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms

            GOTO RETRY -- Go to Label RETRY

      END

END CATCH

 

Transaction B

 

RETRY:

BEGIN TRANSACTION

BEGIN TRY

      UPDATE countrydetail  SET countryname = 'sri lanka' WHERE Countryid =2

WAITFOR DELAY '00:00:05' -- Wait for 5 ms

UPDATE userdetail  SET firstname ='rahul' WHERE user_id  =1

 

COMMIT TRANSACTION

END TRY

BEGIN CATCH

      PRINT 'Rollback Transaction'

      ROLLBACK TRANSACTION

      IF ERROR_NUMBER() = 1205 -- Deadlock Error Number

      BEGIN

            WAITFOR DELAY '00:00:00.05' -

            GOTO RETRY -- Go to RETRY

      END

END CATCH

 

In the preceding code, if the deadlock occurs then a try block will fail and control will automatically be transferred to the catch block. In the catch block we check for deadloak error_number 1205. If the condition is true, then the transaction waits for 5 milliseconds. In that delay other transactions (which are not aborted) will have completed during the delay and release the lock on the table which was required by the aborted transaction. Both the transactions will execute successfully.