SQL Server Deadlock Analysis and Prevention

Back to: SQL Server Tutorial For Beginners and Professionals

SQL Server Deadlock Analysis and Prevention

In this article, I am going to discuss SQL Server Deadlock Analysis and Prevention i.e. how to read and analyze the Deadlock Information captured in the SQL Server error log, so that we can understand what’s exactly causing the deadlocks and then we can take the necessary actions to prevent or minimize the occurrence of deadlocks. Please read the following article before proceeding to this article as it is the continuation part of that article where we discussed how to log the Deadlock information in SQL Server Error Log.

https://dotnettutorials.net/lesson/deadlock-logging-sql-server-error-log/

So please execute the following command to see the Error Log

EXECUTE sp_readerrorlog

Once you execute the above command then search for the deadlock-list as shown below in the result set.

Let’s understand the above code in detail.

The deadlock information that is stored in the SQL Server Error Log has three sections 

1. Deadlock Victim
2. Process List
3. Resource List

Deadlock Victim:

It contains the Process ID that was selected as the deadlock victim process and then killed by the SQL Server. The following section of the Error Log shows this.

Process List:

The Process List section contains the list of processes that were participated in the deadlock. The following diagram shows that there are two processes are participated in the deadlock and the first process was selected as the Deadlock victim.

As you can see from the above diagram, the Process List section contains a lot of useful information to understand and handle the deadlock is as follows.

1. Process ID: It is the unique id of the process.
2. Loginname: The Login which is associated with the process.
3. Isolationlevel: It specifies which Isolation Level is used with the transaction. As we have not specified any Isolation Level explicitly so it takes the default Isolation Level which is nothing but Read Committed.
4. Hostname: It specifies the Host Name
5. procname: It provides the fully qualified name of the stored procedure
6. Inputbuf: The code the process that is executing when the deadlock occurred i.e. EXEC spTransaction2

Resource List: 

The Resource List section provides the information which is particularly useful when we try to understand what caused the deadlock. The following diagram shows the Resource List section of the Deadlock.

The Resource List contains the following items

objectname: The Fully qualified name of the resource which is involved in the deadlock

owner-list: It contains (owner id) the id of the owning process and the lock mode it has acquired on the resource. The lock mode determines how the resource can be accessed by concurrent transactions. S for Shared lock, U for Update lock, X for Exclusive lock, etc.

waiter-list: It contains (waiter id) the id of the process that wants to acquire a lock on the resource and the lock mode it is requesting

To prevent the deadlock that we have in our example, we need to ensure that the database objects such as Table A & Table B are accessed in the same order every time. 

Let’s see how to do this.

Modify both the Stored Procedures as shown below.

ALTER PROCEDURE spTransaction1
AS
BEGIN
    BEGIN TRANSACTION
    UPDATE TableA SET Name = 'Anurag From Transaction 1' 
  WHERE Id = 101

    WAITFOR DELAY '00:00:10'

    UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
  WHERE Id = 1001
    COMMIT TRANSACTION
END

ALTER PROCEDURE spTransaction2
AS
BEGIN
    BEGIN TRANSACTION
  UPDATE TableA SET Name = 'Anurag From Transaction 2' 
  WHERE Id = 101
    
    WAITFOR DELAY '00:00:10'
  UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
  WHERE Id = 1001

    COMMIT TRANSACTION
END

Now open two instances of SQL Server Management Studio and from instance 1 execute the spTransaction1 stored procedure and from the second instance execute the spTransaction2 and you will see there is no deadlock and both the transactions are executed successfully.

In the next article, I am going to discuss How to Capture the Deadlock Information using SQL Profiler. Here, in this article, I try to explain the SQL Server Deadlock Analysis and Prevention. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.