How to Deal with Deadlock in SQL Server?

The estimated reading time for this post is 8 minutes

A deadlock is a special blocking scenario in which two sessions get blocked by each other. Each session, while holding its own resources, attempts to access a resource that is locked by the other session. This will lead to a circular blocking scenario, also known as a deadly embrace, as illustrated below.

Deadlocks also frequently occur when two processes attempt to escalate their locking mechanisms on the same resource. In this case, each of the two processes has a shared lock on a resource, such as an RID, and they both attempt to promote the lock from shared to exclusive, but neither can do so until the other releases its shared lock. This too leads to one of the processes being chosen as a deadlock victim.

SQL Server has a deadlock detection routine, called a lock monitor, that regularly checks for the presence of deadlocks in SQL Server. Once a deadlock condition is detected, SQL Server selects on of the sessions participating in the deadlock as a victim to break the circular blocking. This process involves withdrawing all the resources held by the victim session. SQL Server does so by rolling back the uncommitted transaction of the session picked as a victim.

SQL Server determines the session to be a deadlock victim by evaluating the cost of undoing the transaction of the participating sessions, and it selects the one with the least cost. You can exercise some control over the session to be chosen as a victim by setting the deadlock priority of its connection to LOW:

SET DEADLOCK_PRIORITY LOW

This steers SQL Server toward choosing this particular session as a victim in the event of a deadlock. You can reset the deadlock priority of the connection to its normal value by executing the following SET statement:

SET DEADLOCK_PRIORITY NORMAL

This will not prevent deadlocks on a given session, but it will reduce the likelihood of a given session being picked as the victim. You can even set the priority level to a number value from -10 to 10 from lowest to highest priority.

SET Deadlock_Priority Low; 
SET Deadlock_Priority Normal;
SET Deadlock_Priority High;
--OR
SET Deadlock_Priority  -10;
SET Deadlock_Priority  0;
SET Deadlock_Priority  10;

Consider the following example:-

DECLARE @retry TINYINT = 0 , @retryMax TINYINT = 3 , @retryCount TINYINT = 0;
WHILE @retry = 1 And @retryCount < @retryMax
BEGIN
	BEGIN Try
		UPDATE HumanResources.Employee
		SET LoginID = '54321'
		WHERE BusinessEntityID = 100;
	END Try
	BEGIN Catch
		IF ( ERROR_NUMBER() = 1205 )
		BEGIN
			SET @retryCount = @retryCount + 1;
		END
	END Catch
END

When SQL Server chooses a session as a victim, it raises an error with the error number. You can use the TRY/CATCH construct within TSQL to handle the error as above statement.

On determining a deadlock situation in the error handler, it is possible to attempt to restart the transaction within TSQL statement as above statement. You can check the error number by using the ERROR_NUMBER() function to determine whether you have a deadlock.

You can collect the deadlock information three ways: 1) using a specific trace event through the Profiler Tool, 2) Setting Trace Flag 1222 and 3) setting Trace Flag 1204. Trace flags are used to customize certain SQL Server behavior such as, in this case, generating the deadlock information. To ensure that the trace flags are always set, it is possible to make them part of the SQL Server startup in the SQL Server configuration manager.

Consider the following example:-

DBCC TraceOn (1222,-1); DBCC TraceOn (1204,-1);
Go
--First Connection.
--First Execution.
BEGIN TRANSACTION 
	UPDATE Purchasing.PurchaseOrderHeader 
	SET Freight = Freight * 0.9
	WHERE PurchaseOrderID = 1255;
Go
--Third Execution.
BEGIN TRANSACTION
	UPDATE Purchasing.PurchaseOrderDetail
	SET OrderQty = 4
	WHERE ProductID = 448 and PurchaseOrderID = 1255;
 
 
--Second Connection.
--Second Execution.
BEGIN TRANSACTION
  UPDATE Purchasing.PurchaseOrderDetail
  SET OrderQty = 2
  WHERE ProductID = 448 and PurchaseOrderID = 1255

To analyze the cause of a deadlock, let’s consider a straightforward little example as above. First, make sure the trace flags are enabled.

From the deadlock graph displayed in above figure, it’s fairly clear that two processes were involved, session 54 and session 55. session 54, the one with the big blue X crossing it out, was chosen as the deadlock victim. Two different keys were in question. The top key was owned by session 55, as demonstrated by the arrow pointing to the session object, named owner mode, and marked with an X for exclusive. Session 54 was attempting to request the same key for an update. The other key was owned by session 54 with session 55 requesting an update. You can see the exact HoBT ID, object ID, and index name for the objects in question for the deadlock.

Avoiding Deadlocks

One of the most commonly adopted techniques in avoiding a deadlock is to ensure that every transaction accesses the resources in the same physical order. For instance, suppose that two transactions need to access two resources. If each transaction accesses the resources in the same physical order, then the first transaction will successfully acquire locks on the resources without being blocked by the second transaction. The second transaction will be blocked by the first while trying to acquire a lock on the first resource. This will cause a typical blocking scenario without leading to a circular blocking.

If the resources are not accessed in the same physical order, as follows this can cause a circular blocking between the two transactions:

Transaction 1:

  1.   Access Resource A
  2.   Access Resource B

Transaction 2:

  1.   Access Resource B
  2.   Access Resource A

In the current deadlock scenario, the following resources are involved in the deadlock:

Resource 1:  hobtid=72057594046578688 dbid=6 objectname=AdventureWorks2008.Purchasing.PurchaseOrderDetail indexname=PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID id=lock7967c80 mode=X associatedObjectId=72057594046578688

Resource 2:  keylock hobtid=72057594046644224 dbid=6 objectname=AdventureWorks2008.Purchasing.PurchaseOrderHeader indexname=PK_PurchaseOrderHeader_PurchaseOrderID id=lock63d1880 mode=X associatedObjectId=72057594046644224

Both sessions attempt to access the resources; unfortunately, the order in which they access the key are different.

A deadlock involves at least two resources. A session holds the first resource and then requests the second resource. The other session holds the second resource and request the first resource. If you can prevent the sessions from accessing one of the resources involved in the deadlock, then you can prevent the deadlock. You can achieve this by redesigning the application, which is a solution highly resisted by developers late in the project. However, you can consider using the following features of SQL Server without changing the application design by converting a non-clustered index to a clustered index and/or use a covering indexes for a SELECT statement.

Convert Non-Clustered Index to a Clustered Index: a you know, the leaf pages of a non-clustered index are separate form the data pages of the heap or the clustered index. Therefore, a non-clustered index takes two locks: one for the base and one for the non-clustered index. However, in the case of a clustered index, the leaf pages of the index and the data pages of the table are the same. It requires one lock and that one lock protects both the clustered index and the table, since the leaf pages and the data pages are the same. This decreases the number of resources to be accessed by the same query, compared to a non-clustered index.

Use a Covering Index for a SELECT Statement: By using a covering index, you are able to decrease the number of resources accessed by SELECT statement. Since a SELECT statement can get everything from the covering index itself, it does not need to access the base table. Otherwise, the SELECT statement needs to access both the index and the base table to retrieve all the required column values. Using a covering index stops the SELECT statement from accessing the base table, leaving the base table free to be locked by another session.

Implement Row Versioning

Instead of attempting to prevent access to resources using a more stringent locking scheme, you could implement row versioning through the READ_COMMITTED_SNAPSHOT isolation level or through the SNAPTSHOT level. The row versioning isolcation levels are used to reduce blocked as outlined in previous module. Because they reduce blocking, which is the root cause of deadlocks, they can also help with deadlocking. By introducing READ_COMMITTED_SNAPSHOT with the following TSQL command. This possibly eliminating the contention caused by the lock escalation in the preceding deadlock scenario.

ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON;

This will allow any necessary reads without causing lock contention since the reads are on a different version of the data. There is overhead associated with row versioning, especially in TEMPDB and when marshaling data from multiple resources instead of just the table or indexes used in the query. But that trade-off of increased TEMPDB overhead vs. the benefit of reduced deadlocking and increased concurrency may be worth the cost.

Decrease Isolation Level

Sometimes the SHARED lock requested by a SELECT statement contributes to the formation of circular blocking. You can avoid this type of circular blocking by reducing the isolation level of the transaction containing the SELECT statement to READ UNCOMMITTED. This will allow the SELECT statement to read the data without requesting and SHARED lock and thereby avoid the circular blocking. However, reading uncommitted data carries with it a serious issue by returning bad data to client. You need to be in very dire straights to consider this as a method of eliminating your deadlocks.

Use Query Lock Hint

You can resolve the deadlock presented in the preceding technique using the following locking hints:

  • NOLOCK
  • READUNCOMMITTED

Like READ UNCOMMITTED isolation level, the NOLOCK or READUNCOMMITTED locking hint will avoid the SHARED locks requested by a given session, thereby preventing the formation of circular blocking. The effect of the locking hint is at a query level and is limited to the table and its indexes on which it is applied. The NOLOCK and READUNCOMMITTED locking hints are allowed only in SELECT statements and the data selection part of the INSERT, DELETE, and UPDATE statements. The resolution techniques of minimizing lock contention introduce the side effect of ta dirty read, which may not be acceptable in every transaction. Therefore, use these resolution techniques only in situations in which a dirty read is acceptable.

You have number of techniques to avoid a deadlock; which technique is applicable depends upon the type of queries executed by the participating sessions, the locks held and requested on the involved resources, and the business rules governing the degree of isolation required.

Conclusion

SQL Server deadlock only occurs once the transactions resource utilization order has clash, to overcome this issue you are required to analyze the deadlock graph or output to find most efficient solution to resolve deadlock immediately. Deadlock consumes lots of transaction resources and reduces the database performance significantly.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn