First published on MSDN on Jan 21, 2017, Updated Dec 2022
In Azure SQL Database we have already running an Extended Event that captures the deadlocks without any additionally action
for the 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.
Assuming your system experiences a high volume of deadlocks using the above technique might cause an issue.
When querying the system function it fetch all the XEL files at once
This process uses the memory clerk "XE Engine" - MEMORYCLERK_SQLQUERYPLAN and it might impact your master database availability and eventually lead to availability issues with your managed instance / SQL Database.
Once fetching the data is completed, we need to go and parse the XML information for each deadlock event.
which is also an intensive resource consuming process.
Having that in mind we strongly suggest customers which have more than just a few deadlock events to avoid using this method
Instead, we suggest customers create XE session to track deadlock information on their database.
When setting customer XE session you may choose destination as Ring Buffer or blob storage to host the captured events.
To get the current number of deadlock events happening on your database please run the following query.
Depending on the number you make, take a decision about the next steps.
SELECT
#OfDeadlockEvents = format(sum(event_count),'#,#')
FROM
sys.event_log
WHERE
event_type = 'deadlock'
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
-- ==================================
-- 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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.