Below is the Code from the Video for Tracing Deadlock with
SQL Server Profiler

--=====================================
--=====================================
--=====================================
-- 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

свернуть