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.
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.
This example mainly explained how deadlock occurs in a scenario.
Note: To learn more about how to avoid deadlocks, please read The Anatomy of SQL Server Deadlocks and the Best Ways to Avoid Them article.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DROP TABLE IF EXISTS Table_A CREATE TABLE Table_A (Id INT PRIMARY KEY, FruitName VARCHAR(100)) GO INSERT INTO Table_A VALUES(1,'Lemon') INSERT INTO Table_A VALUES(2,'Apple') GO DROP TABLE IF EXISTS Table_B CREATE TABLE Table_B (Id INT PRIMARY KEY, FruitName VARCHAR(100)) GO INSERT INTO Table_B VALUES(1,'Banana') INSERT INTO Table_B VALUES(2,'Orange') GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Table_A SET FruitName ='Mango' WHERE Id=1 WAITFOR DELAY '00:00:08' UPDATE Table_B SET FruitName ='Avacado' WHERE Id=1 COMMIT TRAN SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Table_B SET FruitName ='Papaya' WHERE Id=1 WAITFOR DELAY '00:00:08' UPDATE Table_A SET FruitName ='Kiwi' WHERE Id=1 COMMIT TRAN |
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.
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.
We will click the Next button and skip to the next screen on the Introduction screen.
In the Set Session Properties screen, we will give a name to the extended event and click the Next button.
On the Choose Template screen, we will select the Do not use a template option and click the Next button.
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
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
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.
In this step, we will click the Finish button and create an 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.
Now, we will re-execute deadlock simulating queries at the same time on the separated query windows and generate a deadlock error again.
We will right-click on the created extended event and select the View Target Data… to analyze the captured deadlock details.
On this screen, we can display the deadlock details.
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.
Also, in the xml_deadlock_report event, we can see the deadlock graph and it offers a virtual representation of the deadlock.
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.
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023