With Extended events you can monitor and analyse occurence of deadlocks. Like traces there is a possibility to get information about deadlock occured in system. Lets have a look on it in detail in this post.
Lets create event session to monitor our deadlock.
CREATE EVENT SESSION DeadLockSession ON SERVER
ADD EVENT sqlserver.xml_deadlock_report -- ,
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id,sqlserver.username)
/*Filter error no or db number*/
)
ADD TARGET package0.ring_buffer
(SET max_memory = 4096
)
WITH (max_dispatch_latency = 30 seconds)
Starting esssion:
ALTER EVENT SESSION DeadLockSession ON SERVER
STATE = START
Now I create structures, two tables, and fill them with data. I will use it to raise a deadlock.
CREATE TABLE dbo.ConcurentTest (id INT IDENTITY(1,1))
ALTER TABLE dbo.ConcurentTest ADD col INT
CREATE TABLE dbo.ConcurentTest2 (id INT IDENTITY(1,1))
ALTER TABLE dbo.ConcurentTest2 ADD col INT
INSERT INTO dbo.ConcurentTest(col) SELECT 1 FROM sys.objects
INSERT INTO dbo.ConcurentTest2 (col) SELECT 1 FROM sys.objects
To create deadlock open two query windows in management studio and run queries (updates on tables) in bellow mentioned scripts in order specified in table bellow. We cause that queries will block each other to complete its operation because of U and X locks on our objects.
Query1
BEGIN TRAN
UPDATE dbo.ConcurentTest SET col=4
WhERE id = 5
UPDATE dbo.ConcurentTest2 SET col=4
WhERE id = 8
COMMIT
Query2
BEGIN TRAN
UPDATE dbo.ConcurentTest2 SET col=4
WhERE id = 6
UPDATE dbo.ConcurentTest SET col=10
WhERE id = 7
COMMIT
Order to run queries.
| Order | Script1 | Script2 |
| 1 | BEGIN TRAN
UPDATE dbo.ConcurentTest SET col=4
WhERE id = 5 | |
| 2 | | BEGIN TRAN
UPDATE dbo.ConcurentTest2 SET col=4
WhERE id = 6 |
| 3 |
UPDATE dbo.ConcurentTest2 SET col=4
WhERE id = 8 | |
| 4 | |
UPDATE dbo.ConcurentTest SET col=10
WhERE id = 7
|
Now , in one of your query window you should see deadlock message. Query in this window was marked as deadlock victim and will not finish. Updates in the other query windows will be executed and data will be updated.
Now take a look on output from extended event xml_deadlock_report.
Put data to temporary table
IF OBJECT_ID('tempdb.dbo.#LogData') IS NOT NULL
DROP TABLE #LogData;
SELECT
CAST(LocksAcquired.TargetXml AS XML) AS XMLData
INTO
#LogData
FROM
(
SELECT
CAST(t.target_data AS XML) AS TargetXml
FROM
sys.dm_xe_session_targets AS t
JOIN sys.dm_xe_sessions AS s
ON s.address = t.event_session_address
WHERE
s.name = 'DeadLockSession'
)
LocksAcquired
Lets parse data.
SELECT p.XMLData.value('(inputbuf)[1]', 'varchar(1000)' ) AS [SqlText] ,
p.XMLData.value('(@lockMode)[1]', 'varchar(1000)' ) AS [LockMode],
p.XMLData.value('(@isolationlevel)[1]', 'varchar(1000)' ) AS [IsolationLevel],
p.XMLData.value('(@spid)[1]', 'varchar(1000)' ) AS [SPID],
p.XMLData.value('(@id)[1]', 'varchar(1000)' ) AS [processID]
FROM #LogData tt
CROSS APPLY XMLData.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS T(XMLData)
CROSS APPLY T.XMLData.nodes('data/value/deadlock/process-list') AS i(XMLData)
CROSS APPLY i.XMLData.nodes('process') AS p(XMLData)
SELECT p.XMLData.value('(@objectname)[1]', 'varchar(1000)' ) AS [OwnerObjectName]
,p.XMLData.value('(owner-list/owner/@id)[1]', 'varchar(1000)' ) AS [OwnerProcessId]
,p.XMLData.value('(waiter-list/waiter/@id)[1]', 'varchar(1000)' ) AS [WaiterProcessId]
FROM #LogData tt
CROSS APPLY XMLData.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS T(XMLData)
CROSS APPLY T.XMLData.nodes('data/value/deadlock/resource-list') AS i(XMLData)
CROSS APPLY i.XMLData.nodes('ridlock') AS p(XMLData)
SELECT i.XMLData.value('(victimProcess/@id)[1]', 'varchar(1000)' ) AS [VictimProcessId]
FROM #LogData tt
CROSS APPLY XMLData.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS T(XMLData)
CROSS APPLY T.XMLData.nodes('data/value/deadlock/victim-list') AS i(XMLData)
After running scipts above you should see which process was evaluated as deadlock victim, marked on picture bellow. You can get queries causing deadlocks with their SPIDSs. There are more interesting informations like deadlock type. In our case it was RID lock since we dont have any index on our structures and page the query touches.
As you can see extended events are very handful in deadlocks scenarios. Since I am still addicting to extended events deeper and deeper you can look forward to another posts with this topic.