[SQL Server][DeakLock]觀察死結的工具(四)擴充事件(Extended events)

2017-01-28

10728
0
SqlDeadlock

除了Trace flag及SQL Profiler能野生捕獲DeadLock資訊，在SQL Server 2008開始，也多了一個擴充事件(Extended events)的工具可以幫助我們，到了SQL Server 2016，雖然Trace flag及SQL Profiler都還能運作，但考慮到SQL Profiler對於Server負載的衝擊以及將來SQL產品的支援，我們就是慢慢把系統診斷的工作轉移到擴充事件中嚕~

建立及啟動擴充事件(Extended events)

(1)建立擴充事件(Event Session)

CREATE EVENT SESSION DeadLockEvent ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(
    SET FILENAME = N'C:\temp\db\DeadLockEvent.xet',
    METADATAFILE = 'C:\temp\db\DeadLockEvent.xem'
)

(2)啟動擴充事件(Event Session)

ALTER EVENT SESSION DeadLockEvent
      ON SERVER
    --ON DATABASE
STATE = START;   -- STOP;

執行測試死結的語法

只要讓兩個交易互斥鎖定物件時間長 (15秒)而且存取Table資源的順序不同。

查詢視窗1(先更新t1再更新t2)

BEGIN Tran Tran_one
UPDATE t1 
    set c2 = 'lock'

WAITFOR DELAY '00:00:15';  

UPDATE t2 
    set c3 = 'lock'

ROLLBACK

查詢視窗2(先更新t2再更新t1):必須在15秒內執行

BEGIN Tran Tran_two

UPDATE t2 
    set c3 = 'lock'

WAITFOR DELAY '00:00:15';  

UPDATE t1
    set c2 = 'lock'

ROLLBACK

查詢視窗1 x 2的執行結果:

查詢視窗2出現了以下的錯誤訊息:

訊息 1205，層級 13，狀態 45，行 15 交易 (處理序識別碼 55) 在鎖定資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。

查詢擴充事件結果有兩個方式，一個是直接透過SSMS GUI在物件總管下的管理直接查詢，另一個則是查詢.xet event 檔案，有時候也會權限管控，也許就可以透過這個方式取出Deadlock xml內容。

查詢擴充事件結果:透過SSMS GUI

SSMS物件總管 > 管理 > 擴充事件 > 點開DeadLockEvent > 點擊兩下event file

點開錄製到的死結，噹噹! Deadlock Graph本人

查詢擴充事件結果:透過T-SQL

查詢Event結果，將event data轉換成xml type再下去用xml.query查Deadlock Graph所需的結構及資料(.xdl)

SELECT object_name
      ,file_name
,convert(xml,event_data).query('/event/data/value/deadlock') as DeadLockGraph
FROM sys.fn_xe_file_target_read_file('C:\temp\db\*.xet', null , null, null);  


DECLARE @xml xml
SELECT @xml = event_data      
 FROM sys.fn_xe_file_target_read_file('C:\temp\db\*.xet', null , null, null);  

SELECT @xml.query('/event/data/value/deadlock')

查詢結果，點擊xml超連結