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:
- Mutual Exclusion - A resource is assigned to one process. Requests are delayed until the resources are released.
- Hold and wait - One process holds one resource and also waits for another.
- No preemption - Only a process can hold a resources when it's released from another process.
- 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.