Analyze deadlock graph

Deadlocks on page resource

Select
AlertTime,
DeadlockGraph,
ObjectName = ResList.pagelock.value(‘@objectname’, ‘sysname’),
IndexName = ResList.pagelock.value(‘@indexname’, ‘sysname’),
ResultingLockMode = ResList.pagelock.value(‘@mode’, ‘varchar(10)’),
OwnerID = OwnList.Own.value(‘@id’, ‘sysname’),
OwnerLockMode = OwnList.Own.value(‘@mode’, ‘varchar(10)’),
WaiterID = WaitList.Wait.value(‘@id’, ‘sysname’),
WaiterLockMode = WaitList.Wait.value(‘@mode’, ‘varchar(10)’)
From msdb..DeadlockEvents
— Shred pagelock node of the resource-list
Cross Apply DeadlockGraph.nodes(‘//resource-list/pagelock’) ResList(pagelock)
— Shred the owner node beneath the resource-list/pagelock node
Cross Apply ResList.pagelock.nodes(‘./owner-list/owner’) OwnList(Own)
— Shred the waiter node beneath the resource-list/pagelock node
Cross Apply ResList.pagelock.nodes(‘./waiter-list/waiter’) WaitList(Wait)
where AlertTime>=’2013-01-31 01:18:16.720′

Deadlocks on key resource

Select AlertTime, DeadlockGraph,
ObjectName = ResList.KeyLock.value(‘@objectname’, ‘sysname’),
IndexName = ResList.KeyLock.value(‘@indexname’, ‘sysname’),
ResultingLockMode = ResList.KeyLock.value(‘@mode’, ‘varchar(10)’),
OwnerID = OwnList.Own.value(‘@id’, ‘sysname’),
OwnerLockMode = OwnList.Own.value(‘@mode’, ‘varchar(10)’),
WaiterID = WaitList.Wait.value(‘@id’, ‘sysname’),
WaiterLockMode = WaitList.Wait.value(‘@mode’, ‘varchar(10)’)
From msdb..DeadlockEvents
— Shred keylock node of the resource-list
Cross Apply DeadlockGraph.nodes(‘//resource-list/keylock’) ResList(KeyLock)
— Shred the owner node beneath the resource-list/keylock node
Cross Apply ResList.KeyLock.nodes(‘./owner-list/owner’) OwnList(Own)
— Shred the waiter node beneath the resource-list/keylock node
Cross Apply ResList.KeyLock.nodes(‘./waiter-list/waiter’) WaitList(Wait)
where AlertTime=’2013-01-30 02:17:57.810′

 

Source: http://www.sqlsoldier.com/wp/sqlserver/tsqltuesday17usingapplytodemystifydeadlocks