Tag: Extended Events

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.