Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database?

  • Article

In Azure SQL Database we have already running an Extended Event that captures the deadlocks without any additionally action for customer side. In this post we are going to learn how to obtain the deadlocks, how to obtain the deadlock graphic and how to reproduce a deadlock for testing.

 

How to obtain the deadlocks

 

Basically, you need to run the query against master database the following TSQL (no results will be shown if ran against user database )

WITH CTE 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 CTE

 

How to obtain the deadlock graph

 

  • 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.

 

How to to reproduce a deadlock for testing

-- ==================================

-- Create the tables.

-- ==================================

CREATE TABLE [dbo].[Person](

[Id] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](20) NOT NULL,

[BusinessEntityID] [int] NOT NULL,

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

(

[Id] ASC

))

CREATE TABLE [dbo].[PersonPhone](

[PhoneNumber] [varchar](50) NOT NULL,

[BusinessEntityID] [int] NOT NULL,

CONSTRAINT [PK_PersonPhone] PRIMARY KEY CLUSTERED

(

[PhoneNumber] ASC

))

-- ==================================

-- Add some rows

-- ==================================

insert into [PersonPhone] values ( '999-555-1212',1)

insert into [Person] values( 'Chris',1)

-- ==================================

-- Open the SQL SERVER Management Studio and open two queries.

-- ==================================

-- ====================================

-- Execute this query using a query window for Deadlock Thread 1

-- ====================================

BEGIN TRANSACTION

UPDATE [PersonPhone] SET PhoneNumber = '999-555-1212' WHERE [BusinessEntityID] = 1

WAITFOR DELAY '00:00:50'

UPDATE [Person] SET [FirstName] = 'Chris' WHERE [BusinessEntityID] = 1

ROLLBACK TRANSACTION

-- ====================================

-- Very quickly execute this query using a query window for Deadlock Thread 2

-- ====================================

BEGIN TRANSACTION

UPDATE [Person] SET [FirstName] = 'Chris' WHERE [BusinessEntityID] = 1

UPDATE [PersonPhone] SET PhoneNumber = '999-555-1212' WHERE [BusinessEntityID] = 1

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION

Enjoy!