To paraphrase Annie, Deadlocks ARE JUST AWFUL! When they occur, it means one transaction was the victim and rolled back, the other succeeded. You have a couple different ways to monitor them in Azure SQL Managed Instance. Let's review those together.
What is a deadlock? To quote our MS Learn Documentation, "Deadlocks occur when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs".
In layman's terms a deadlock occurs when two queries are blocking one another and neither could ever complete its transaction. Pretend two people need to use the same door. They cannot fit through at the same time. One must stop and let the other proceeded first. Now add on top of that they both reached for the door knob and grabbed it at the same time, both pulling the door in an opposite direction.
Instead of simply waiting, one must let go of the door knob. In this case the door knob is a table, the hands reaching out are attempting to gain a lock. In SQL Server, all versions of the database engine, this is accomplished by the Lock Manager it uses a process called FIFO, first in first out, to determine who has to let go.
In the case of SQL one transaction is a deadlock victim, it is killed and the transaction is rolled back. So if this is occurring on your system fixing it is very important. First you have to find out if they are occurring, and that's what we will cover in this blog post today!
If you'd rather watch a video of this content, here is one to check out.
SET UP
To demonstrate this we need to start by creating an environment to cause a deadlock. I'm going to do this by running 3 different scripts. The first will create two very simple table and insert three rows of data into each table. The second two are separate SQL transactions that I will run in a particular order to cause our dreaded deadlock.
We run the first script to create our table. Then we will go to our first mechanism to monitor a deadlock. To do so we will use the following code. This is going to be a simple deadlock example so we will create simple tables. Tables myt1 and myt2 that have the same schema and values.
drop table if exists dbo.myt1
go
create table dbo.myt1(myid int, mychar char(50))
go
drop table if exists dbo.myt2
go
create table dbo.myt2(myid int, mychar char(50))
insert into dbo.myt1(myid, mychar)
values(1,'a')
,(2,'b')
,(3,'c')
insert into dbo.myt2(myid, mychar)
values(1,'a')
,(2,'b')
,(3,'c')
Now that we have created our table and our data, we need to review our scripts to create our deadlock. Each of these scripts should be in separate query windows, as pictured above. In order to trigger the deadlock we will run specific portions of the code in a specific order. We will call these Script 1 and Script 2
Script 1:
/*move to new window*/
begin transaction
Update dbo.myt2
set mychar='bb'
where myid=2
select * from dbo.myt1
--commit transaction
Script 2:
/*move to new window*/
begin transaction
Update dbo.myt1
set mychar='bb'
where myid=2
select * from dbo.myt2
--commit transaction
SQL SERVER PROFILER
Hello Profiler, my old friend. This tool has long been a stalwart in the toolbox of DBA's. It has been deprecated for quite some time, but it is still usable. While this is not the first choice I would make for the modern DBA, that is coming up in a moment, it is important to realize it still exists.
To open Profiler, in SQL Server Management Studio (SSMS) go to Tools and select SQL Server Profiler.
Profiler will require a SQL Server Authentication login. While it does support Active Directory authentication, it does not support Azure Active Directory, or Multi-Factor Authentication.
There is no template specifically for Deadlocks. We could just move to Event Selection, but I prefer to use a template. Select the T-SQL template from the Use the Template dropdown.
We do not need everything from this template. I want to keep the events as non-verbose as possible. Uncheck Audit Login, Audit Logout, ExistingConnection, and SQL:BatchStarting. Keep RPC Completed and SQL:BatchCompleted.
Check Show all events so we can select our deadlock event.
Check the Deadlock graph check box. You can see in the description it states, "Deadlock graph - Occurs simultaneously with the Lock:Deadlock event class. The Deadlock Graph event class provides an XML description of the deadlock.".
Click Run.
We now have our Profiler trace running and we can go back to SSMS and trigger our deadlock.
TRIGGER THE DEADLOCK
In Script 1 we want to execute lines 2 - 7, as pictured below.
In Script 2 we want to execute lines 3 - 8, as pictured below.
In Script 1 now execute line 11, as pictured below.
In Script 2 now execute line 11, as pictured below.
If we ran this in the correct order a deadlock should have occurred.
Now go back to profiler. You should have an entry under EventClass that reads Deadlock graph.
When you select the event class you will see the XML Graph that displays the query ID and additional information about the query that was killed and the one that processed successfully. Additionally there is information about the resources where the locks were in conflict, the type of lock being taken.
Don't forget to commit Script 1 in order to reset our transactions before the next section. Do this by running the commit transaction portion of the script. You only need to do this for Script 1, Script 2 was terminated when the deadlock occured.
EXTENDED EVENTS
While we could use Profiler remember that Profiler uses SQL Authentication and depending on your organization and your security policies that may not be available to every user. You have to parse the trace file for this. Profiler in this scenario is like casting a net to capture an event. A more precise and secure way to obtain the same information would be to use an Extended Event.
The Extended Events architecture enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem in SQL Server, Azure SQL Database, and Azure SQL Managed Instance. For more information on Extended Events see the overview on them or a Quickstart on Extended Events.
While we could deploy an event to capture Deadlocks, there is a default trace running on each instance of Azure SQL Managed Instance, Azure SQL Database, and SQL Server that will allow us to collect this information. To access this go back to SSMS and extend tree by the Management folder, Extended Events, Sessions, and we are looking for the system_health default extended event.
Right click on the system_health event and click Watch Live Data.
This will open up the Extended Event Live Data Viewer.
To trigger the deadlock execute Script 1 and Script 2 in the same order as earlier. After doing so the deadlock will occur and we will receive an entry for xml_deadlock_report.
Click on the xml_deadlock_report, then go down to the details and double click on the Value for the xml_report. This will open this data in XML format in the SSMS Window. We could parse the XML, but I prefer the graphical plan.
In order to get the graphical plan in SSMS go to File and save the the xml_deadlock_report.
Then change the Save as type to, All Files (*.*), change the file name to have the .xdl extension, then click Save.
Then Browse to the file and open the file we just saved in SSMS, select the file, and click open.
You should see the Deadlock Graph now in SSMS.
And with that Dear Reader you now know how to create a deadlock, detecct, and monitor that deadlock using Profiler or Extended Events. As always thank you for stopping by.
Thanks,
Brad