Azure SQL – How to Get Deadlock Information

azure-sql-databasedeadlocksql server

My application has been logging dead lock exceptions return from sql azure to my .net application: Transaction (Process ID 152) was deadlocked on lock resources with another process....

I would like to investigate this further to see if I can prevent those deadlocks from happening.

How do I get the details about those past and future deadlocks? I remember that the sql server profiler is able to extract some deadlock events with some xml that shows all the queries involved in the dead lock. That is what I'm after.

However, since this runs on Sql Azure, there is no profiler.

Best Answer

You can use extended events on Azure to capture deadlock details.

Following article explains in details.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/21/lesson-learned-19-how-to-obtain-the-deadlocks-of-your-azure-sql-database/

Based on the comment I am adding this section. I just tested this and it works.

You need to run the following query in Master database (Not user database where your deadlock happened) in order to get the XML of the deadlock. 

WITH deadlockcte 
     AS (SELECT Cast(event_data AS XML) AS [target_data_XML] 
         FROM   sys.Fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL 
                )) 
SELECT 
target_data_xml.value('(/event/@timestamp)[1]', 'DateTime2') 
AS Timestamp, 
target_data_xml.query('/event/data[@name="xml_report"]/value/deadlock') 
AS deadlock_xml, 
target_data_xml.query('/event/data[@name="database_name"]/value').value('(/value)[1]', 'nvarchar(100)') AS db_name 
FROM   deadlockcte

Which will have the details about the deadlock processes and you can get a deadlock graph by:

  • Copy the deadlock_xml column results from the previous query and load into a text file. If more than one row is returned, you will want to do each row result separate.
  • Save the file as a ‘.xdl’ extension, (e.g. deadlock.xdl) which can be viewed in tools such as SQL Server Management Studio as a deadlock report/graphic.

Based on the last comment I am adding my test code which does show text in inputbuffer.

 /*

Deadlock simulation code was copied from:
http://stackoverflow.com/questions/22825147/how-to-simulate-deadlock-on-sql-server

*/

--Simulating deadlock

IF OBJECT_ID('Employees') IS NOT NULL
    DROP TABLE Employees

CREATE TABLE Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO



IF OBJECT_ID('Suppliers') IS NOT NULL
    DROP TABLE Suppliers

CREATE TABLE Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO


INSERT INTO Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO
INSERT INTO Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

/*
--Window 1
BEGIN TRAN; 
UPDATE Employees
SET EmpName = 'Mary'
WHERE empid = 1


--Window=2
BEGIN TRAN; 
UPDATE Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1

Window 1
UPDATE Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1

window 2
UPDATE Employees
SET phone = N'555-9999'
WHERE empid = 1
--commit
*/


DROP TABLE Employees
GO
DROP TABLE Suppliers
GO
Related Question