--=====================================
--=====================================
--=====================================
-- DEAD LOCK QUERY for EXTENDED EVENTS
--=====================================
--=====================================
--=====================================
DBCC TRACEON (1222,-1)
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), DeadlockEventXML.value('(event/@timestamp)[1]', 'datetime2')) AS [EventTime],
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,
DB_NAME(DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS [DatabaseName],
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@transactionname)[1]', 'nvarchar(max)') AS VictimTransactionName,
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,
DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadLockGraph,
DeadlockEventXML
FROM
(
SELECT
XEvent.query('.') AS DeadlockEventXML,
Data.TargetData
FROM
(
SELECT
CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health' AND
st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS DeadlockInfo
--=====================================
-- Windows/Session #1
--=====================================
SELECT @@SPID
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ParentTable')
DROP TABLE [ParentTable]
CREATE TABLE [ParentTable]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Value] [varchar](100) NULL,
[DateChanged] [datetime] DEFAULT(GETDATE()) NULL,
CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ChildTable')
DROP TABLE [ChildTable]
CREATE TABLE [ChildTable]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Value] [varchar](100) NULL,
[DateChanged] [datetime] DEFAULT(GETDATE()) NULL,
CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)
INSERT INTO [ParentTable](Name, Value)
SELECT 'Name1', 'Value1'
UNION ALL
SELECT 'Name2', 'Value2'
UNION ALL
SELECT 'Name3', 'Value3'
INSERT INTO [ChildTable](Name, Value)
SELECT 'Name1', 'Value1'
UNION ALL
SELECT 'Name2', 'Value2'
UNION ALL
SELECT 'Name3', 'Value3'
SELECT * FROM ParentTable
SELECT * FROM ChildTable
--=====================================
-- Windows/Session #2
--=====================================
-----------------------------------------------------
-- This window/session is default CASE DEADLOCK --
-----------------------------------------------------
SET DEADLOCK_PRIORITY LOW
BEGIN TRAN
UPDATE ParentTable SET Name = Name + Name WHERE ID=2
WAITFOR DELAY '00:00:10'
UPDATE ParentTable SET Name = Name + Name WHERE ID=1
COMMIT TRAN
--=====================================
-- Windows/Session #3
--=====================================
-----------------------------------------------------
-- This window/session is default CASE DEADLOCK --
-----------------------------------------------------
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE ParentTable SET Name = Name + Name WHERE ID=1
WAITFOR DELAY '00:00:10'
UPDATE ParentTable SET Name = Name + Name WHERE ID=2
COMMIT TRAN
