Deadlock Analysis

Deadlock Fundamentals

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

Choosing the Deadlock 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 estimated 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;

The SET statement allows you to mark a session as a HIGH deadlock priority, too. This won’t 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 for the lowest priority or to 10 for the highest.

Using Error Handling to Catch a Deadlock

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 T-SQL to handle the error. SQL Server ensures the consistency of the database by automatically rolling back the transaction of the victim session. The rollback ensures that the session is returned to the same state it was in before the start of its transaction.

Take the following T-SQL statement as an example of one method for handling a deadlock error:

DECLARE @retry AS TINYINT = 1,
    @retrymax AS TINYINT = 2,
    @retrycount AS TINYINT = 0;
WHILE @retry = 1
    AND @retrycount <= @retrymax
    BEGIN
        SET @retry = 0;

        BEGIN TRY
            UPDATE  HumanResources.Employee
            SET     LoginID = '54321'
            WHERE   BusinessEntityID = 100;
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205)
                BEGIN
                    SET @retrycount = @retrycount + 1;
                    SET @retry = 1;
                END
        END CATCH
    END

Deadlock Analysis

You can sometimes prevent a deadlock from happening by analyzing the causes. You need the following information to do this:

• The sessions participating in the deadlock
• The resources involved in the deadlock
• The queries executed by the sessions
• Collecting Deadlock Information

You have four ways to collect the deadlock information.

• Use Extended Events
• Set trace flag 1222
• Set trace flag 1204
• Use trace events

Extended Events provides several ways to gather the deadlock information. This is probably the best method you can apply to your server for collecting deadlock information. You can use these options:

• Lock_deadlock: Displays basic information about a deadlock occurrence
• Lock_deadlock_chain: Captures information from each participant in a deadlock
• Xml_deadlock_report: Displays an XML deadlock graph with the cause of the deadlock

Avoiding Deadlocks

The methods for avoiding a deadlock scenario depend upon the nature of the deadlock. The following are some of the techniques you can use to avoid a deadlock:

• Access resources in the same physical order.
• Decrease the number of resources accessed.
• Minimize lock contention.