1

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.

3

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
  • I tried this actually but the results were actually empty. – Clement May 29 '17 at 5:02
  • however when I query SELECT * FROM sys.event_log WHERE event_type = 'deadlock', I can 'see' the deadlocks but the additional_data column is null – Clement May 29 '17 at 5:08
  • thanks I will try this as soon as I can and get back to you – Clement May 30 '17 at 6:42
  • by the way, to connect to the master db do you just change the default db? the USE statement appears to be forbidden... – Clement May 30 '17 at 6:42
  • Thanks it worked great. I think I wasn't querying the master db. – Clement May 30 '17 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.