Esat Erkec
Deadlock graph

How to resolve deadlocks in SQL Server

July 15, 2020 by

In this article, we will talk about the deadlocks in SQL Server, and then we will analyze a real deadlock scenario and discover the troubleshooting steps.

In general, we can find out various theoretical advice and examples about the deadlock problems on the web but, in this article, we will tackle a true deadlock story and learn the solution steps so that we’ll get a chance to work on a case based problem.

First of all, let’s explain the deadlock concept. A deadlock problem occurs when two (or more than two) operations already want to access resources locked by the other one. In this circumstance, database resources are affected negatively because both processes are constantly waiting for each other. This contention issue is terminated by the SQL Server intervention. It chooses a victim from the transactions who are involved in the deadlock, forces it to rollback all actions.

As can be seen from this explanation, deadlock in SQL Server is a special contention problem; also, each deadlock has a unique characteristic, so the solution has different approaches according to problem characteristics. Now, let’s take a glance at the problem scenario.

The problem sceneraio

In this real scenario, an in-house application returns an error to the users, and users notify the development team about this error.

Deadlock error message.

The development team realizes that it is a deadlock issue, but they could not find the main reason for the problem. Under these circumstances, the team decides to receive consultancy service from an experienced database administrator. In the next sections, we will learn how database administrators analyze and resolve this deadlock problem.

Prerequisites

The development team was using the following table to store the order numbers, and the following query was used to create the first row of the day.

The data structure of the table below is similar to the below illustration; namely, there is only one row per day.

The data structure of the sample table

I am using the following stored procedure to create a new order number.

We will also use the SQLQueryStress tool to generate a similar workload of the production system.

Monitoring the deadlocks in SQL Server with system_health session

The database administrator decided to research the deadlocks problems because of the error message. The error message obviously was indicating a deadlock problem. As a first step, he decided to check the system_health session for the deadlocks.

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The system_health is the default extended event session of the SQL Server, and it started automatically when the database engine starts. The system_health session collects various system data, and one of them is deadlock information. The following query reads the .xel file of the system_health session and gives information about the deadlock problems which were occurred. The system_health session can be a good starting point to figure out the deadlock problems. The below query helps to find out the deadlock problems which is captured by the system_health session.

Detecting deadlocks in SQL Server with help of the system_session

When we click any row of the XmlDeadlockReport column, the deadlock report will appear.

XML format of the deadlock report

Monitoring the deadlocks in SQL Server using Extended Events

The database administrator found some clues about the deadlock problem through the captured data by the system_health session. However, he thought that the system_health session shows the more recent events because of the file size limitations, so it cannot be reliable to detect all deadlocks in SQL Server. So, he decided to create a new extended event session that can capture all the deadlocks.

Extended Event is a system monitoring tool that helps to collect events and system information from SQL Server. With the help of the XEvent, we can also capture deadlock information from SQL Server. Firstly, we will launch SQL Server Management Studio and navigate to Session, which is placed under the Management folder. Right-click on the Sessions folder and select New Session.

Creating an extended event in SQL Server

In the New Session screen, we will give a name to the session and check the Start the event session immediately after the session creation checkbox; thus, the session will be started after the completion of the creating process.

Monitor deadlocks in SQL Server

On the Event tab, we select the events which we want to capture. For this session, we will select the following events:

  • database_xml_deadlock_report
  • xml_deadlock_report
  • xml_deadlock_report_filtered

Configure extended event in SQL Server

We will click the Configure button and select global events that will be captured with the events:

  • client app name
  • client connection id
  • client hostname
  • database id
  • database name
  • nt username
  • username
  • sql text
  • username

Global fields of the extended event

On the Data Storage tab, we will select the event_file type to store the captured data and click the OK button.

Data storage setting of the extended event

The session will be created and then started automatically to capture the deadlock events.

Analyzing and resolving deadlocks in SQL Server

In this section, firstly, we will simulate the deadlock problem and then try to find out the main reason for the deadlock issue. We start the SQLQueryStress with the following parameters and wait for the completion of the query execution process.

Creating fake workload with the help of the SQLQueryStress

When we open the details of the exception, it shows exceptions message.

Deadlock messages

To find out more details about the deadlock issue, we need to check out the extended event session, which was created to capture the deadlock events. We expand the MonitorDeadlock session and right-click on the target node then select the View Target Data. The captured deadlocks will be shown in the right pane.

Monitoring deadlock in SQL Server with extended events

The xml_deadllock_report event includes more details about the deadlock, and we can also find the deadlock graph.

Deadlock graph

When we interpret the deadlock graph, the SPID 65 (victim) has acquired an intent exclusive lock and wants to place an update lock to the TestTblCounter table. The SPID 64 has acquired an exclusive lock to the TestTblCounter and wants to place an exclusive lock to the same table because of the TABLOCKX hint.

TABLOCKX hint helps to place an exclusive lock to the table until the select statement will be completed or the transaction will be completed. The disadvantage of the TABLOCKX hint is to reducing the concurrency, so it increases the locking time. When we decided to use it, we need to take account of the lock and contention problems. Particularly for this scenario, this hint usage logic is improper. When we reconsider the query, the update statement modifies some rows, and then the select statement fetches the same modified rows, but it placed an exclusive lock to the whole table until it completed because of the TABLOCKX hint. The most pointless part of this query is the line at which the value is assigned to the variable because the data assignment to the variable is performed randomly.

Why do we need to place an exclusive lock to all rows of the table if we want to obtain the last updated or inserted row? As a result, we can remove the TABLOCKX hint, which causes deadlocks in the query. At the same time, when we get the last inserted or updated row value, we can use the OUTPUT clause.

When we simulate a new workload with the help of the SQLQueryStress for the 200 concurrent users for the changed stored procedure, we will not face any deadlock issues.

Creating fake workload in SQL Server

Conclusion

In this article, we explained deadlocks in SQL Server and then analyze a true story that was experienced by a development team. The significant point to generate a solution is properly understanding and interpreting the deadlock report and graph. Otherwise, it will be very difficult to find the main cause of the problem.

Recap the solution steps:

  • Check the system_health session for deadlocks
  • Create an extended event session to capture the deadlocks
  • Analyze the deadlock reports and graphs to figure out the problem
  • If it is possible to make improvements or changing the queries involved in the deadlock

Esat Erkec
Latest posts by Esat Erkec (see all)
Deadlocks, Performance

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

21,627 Views