SqlServer并发事务：死锁跟踪（三）3种跟踪死锁的方法总结

方法一：打开profiler跟踪事件 locks: deadlock graph

方法二：打开1222或者1204标志记录死锁,在sqlserver日志查看

 

1
2
DBCC TRACEON(1222,-1)
DBCC TRACEON(1204,-1)

 

 

1
2
3
4
5
6
7
8
如果日志太多就不好找了，这时可以用系统扩展存储过程筛选！


--查看是否死锁，确定死锁的的时间
exec xp_readerrorlog 0,1,'DeadLock',NULL,'2015-01-01','2015-01-10','DESC'

--按时间查看sqlserver日志，即为死锁信息
exec xp_readerrorlog 0,1,NULL,NULL,'2015-01-07 22:13:10.300','2015-01-07 22:13:10.330','DESC'

 

方法三：系统扩展事件会话system_health自动记录

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--扩展事件会话的信息
select * from sys.dm_xe_sessions where name = 'system_health'


SELECT
    xed.value('@timestamp','datetime')as Creation_Date,
    xed.query('.')AS Extend_Event
FROM
(
    SELECT CAST([target_data] AS XML)AS Target_Data
    FROM sys.dm_xe_session_targets AS xt
    INNER JOIN sys.dm_xe_sessions AS xs
    ON xs.address= xt.event_session_address
    WHERE xs.name=N'system_health'
    AND xt.target_name=N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')AS XEventData(xed)
ORDER BY Creation_Date DESC