Esat Erkec
Illustration of the deadlock definition in SQL Server

Understanding the deadlock definition in SQL Server

April 7, 2020 by

This article explains the deadlock definition in SQL Server, and it also mentions how to capture deadlocks with extended events.

Deadlock is a resource contention issue that occurs between two or more than two processes. To handle this problem, we need to clearly understand how it occurs.

Deadlock Definition

Deadlocks occur when two processes want to access resources that are mutually being locked by each other. This locked situation can continue forever if nobody stops it. Assume that two plumbers are making some repair in the same bathroom, and one of them is using a plunger and require wrench at the same time in his repair. The other one is using a wrench and required a plunger at the same time in his repair. Otherwise, none of them can complete their work.

Illustration of the deadlock definition

After waiting for a while, the boss decided to abort one of the repairing processes so that one of the plumbers obtain the required resource and can complete his repair.

Illustration of the deadlock definition

This example mainly explained how deadlock occurs in a scenario.

Deadlock definition in SQL Server

In terms of SQL Server, a deadlock occurs when two (or more) processes lock the separate resource. Under these circumstances, each process cannot continue and begins to wait for others to release the resource. However, the SQL engine understands that this contention would never end with the help of the lock manager warning and then it decides to kill one process to solve this conflict problem so that the other process can be completed. The killed process is called the deadlock victim.

Simulate a deadlock in SQL Server

After all these theoretical details, we will simulate a deadlock in SQL Server so that we can reinforce our learnings practically. At first, we will create two tables and insert some random data.

As we explained in the deadlock definition, we need at least two processes for the deadlock, so that we will execute the following queries at the same time in the separated query windows.

Simulating the deadlock in SQL Server

As we can see that, the session 76 acquired a lock on table A and wanted to acquire a lock on the table B. At the same time, session 78 acquired a lock on table B and wanted to acquire a lock on the table A. In this circumstance, both of the sessions conflict each other and cannot proceed. Finally, SQL Server has chosen a victim and rollbacked this session. For our case, session 78 selected as a deadlock victim. The following image illustrates this scenario.

Illustration of the deadlock definition in SQL Server

SQL chooses the victim according to the cost of the rollback. It means that the victim of the process has been decided based on the minimum resource consumption.

Capturing the Deadlocks with Extended Events

Extended events are used to collect and monitor various events and information from SQL Server. With the help of the extended events, we can easily capture details when a deadlock occurred.

At first, we will launch SQL Server Management Studio (SSMS) and navigate to Session, which is located under the Management folder. Right-click on the Sessions and select the New Session Wizard.

Starting the Extended Events in SQL Server

We will click the Next button and skip to the next screen on the Introduction screen.

Extended Event introduction screen

In the Set Session Properties screen, we will give a name to the extended event and click the Next button.

Set Session Properties screen

On the Choose Template screen, we will select the Do not use a template option and click the Next button.

Choose Template screen in the extended event

On the Select Events To Capture screen, we will add the following events from the Event library to Selected events list.

  • database_xml_deadlock_report
  • lock_deadlock
  • lock_deadlock_chain
  • scheduler_monitor_deadlocks_ring_buffer_recorded
  • xml_deadlock_report
  • xml_deadlock_report_filtered

Select Events to Capture screen in the extended event

On the Capture Global Fields screen, we will select global events that will be captured with the events:

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

Capture Global Fields screen in the extended event

On the Specify Session Data Storage screen, we will set the target_file path that the events will be stored and we also set the maximum size of the event file.

Specify Session Data Storage in the extended event

In this step, we will click the Finish button and create an extended event.

Summary screen of the extended event

On the final step, we will check the Start the event session immediately option and click the Close button. This will cause the extended event to capture deadlocks occurring on the SQL Server.

Success screen of the extended event

Now, we will re-execute deadlock simulating queries at the same time on the separated query windows and generate a deadlock error again.

Simulate deadlock in SSMS

We will right-click on the created extended event and select the View Target Data… to analyze the captured deadlock details.

Seeing the captured data in the extended events

On this screen, we can display the deadlock details.

Analyzing the details of the extended event

When we click the xml_report field on the xml_deadlock_report event, the XML report of the deadlock will be opened. This report can be very helpful in understanding the details of the deadlock.

XML report of the deadlock

Also, in the xml_deadlock_report event, we can see the deadlock graph and it offers a virtual representation of the deadlock.

Deadlock graph in SQL Server

Preventing Deadlocks in SQL Server

There is no exact and clear resolving formula for the deadlocks because all deadlocks might have unique characteristics. However, it is significant to understand the circumstances and the situation under which these deadlocks have occurred because this approach will broadly help to resolve them. After then, the following solution recommendations might help.

  • Access the resources in the same order
  • Write the shortest transactions as much as possible and lock the resource for the minimum duration
  • Consider using READ COMMITTED SNAPSHOT ISOLATION and SNAPSHOT ISOLATION levels
  • Limiting the usage of the cursors
  • Design more normalized databases
  • Avoid poorly-optimized queries

Conclusion

Resolving the deadlock can be more complicated and struggling, so as a first step, we should clearly understand the deadlock definition and then set to work capturing and handling the deadlocks. In the final step, we can work on preventing or minimizing the deadlock.

Esat Erkec
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

627 Views