史丹利好熱

 

啟動Trace 1222 1204

適用範圍:SQL Server 7.0-2016

啟動Trace flag的方式也有幾種: QUERYTRACEON、啟動時啟動選項及DBCC TRACEON，這邊直接使用 DBCC TRACEON 命令來啟用全域追蹤(Global Trace)，下次SQL Server執行個體重新啟動時會自動失效。

 

1.首先開啟SSMS，在查詢視窗中輸入:

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

DBCC TRACESTATUS;
GO

 

確認1204、1222 Trace flag已經啟動了。

 

---

執行製造交易死結的語法

可以透過這一篇產生簡單的資料庫、資料表及資料。

 

查詢視窗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

死結發生

 

---

查詢SQL Server 紀錄檔

 

產生死結後，接著可以查看錯誤紀錄檔error log，查看log有兩個方式:

1. 使用系統預存程序xp_readerrorlog查詢。
2. 使用SQL Server管理工具SSMS中的物件總管。

 

1.使用系統預存程序xp_readerrorlog查詢。

DECLARE @table TABLE(LogDate datetime, ProcessInfo nvarchar(30),Text nvarchar(4000))
INSERT @table EXEC master.dbo.xp_readerrorlog 0,1
--第一個參數0表示目前的錯誤記錄檔
--第二個參數1表示SQL Server log
SELECT * FROM @table ORDER BY LogDate desc 

 

執行結果集:

2.使用SQL Server管理工具SSMS

打開SQL管理工具SSMS > 物件總管 > 管理 > SQL Server紀錄檔 > 目前

接下來準備分析Trace 1204及Trace 1222 Output

 

---

Trace 1204 Output

Scope: global only

 

Trace 1204 紀錄檔內容:

RID。識別在資料表內保留或要求鎖定的單一資料列。

RID格式: db_id:file_id:page_no:row_no 表示。

 

觀察兩個交易已經鎖定的資源

鎖定的資源分別為RID(19:1:79:0)及RID(19:1:89:0)

輸入以下指令查詢Page內容: 

DBCC TRACEON (3604);
DBCC PAGE(19, 1, 79, 0);
DBCC TRACEOFF (3604);

DBCC TRACEON (3604);
DBCC PAGE(19, 1, 89, 0);
DBCC TRACEOFF (3604);

可以分別查詢object id 為245575913及261575970

接著使用object_name方法取得object名稱

USE Deadlockdb
SELECT OBJECT_NAME(245575913);

USE Deadlockdb
SELECT OBJECT_NAME(261575970);

物件查詢結果

Spid :60已經持有table T1的鎖定，而Spid持有Table T2的鎖定。

另外從Trace 1204 log中也能觀察到回復成本:

• Spid :60回復成本244
• Spid :52回復成本240

 

總結: 兩個交易都準備在取得更新鎖定(U)時出現衝突，SQL Server選擇回復成本較小的spid 52犧牲。

 

---

Trace 1222 Output

Scope: global only

 

繼續觀察Trace 1222 Log: 

Trace 1222也能觀察到兩個交易鎖定的物件以及使用的SQL指令，最後SQL Server選擇回復成本較小的Tran_two犧牲。

 

---

小結:

如果搶不到DBA大人的時間，.NET工程師解讀上會有些困難(自己就超多的)，不過也可以使用trace flag的功能先找到衝突的交易，再肉眼下去分析T-SQL的內容，

推薦大家使用接下來的Profiler或是Extended events(最佳)來觀察deadlock graph會比較輕鬆。

 

另外msdn也有提到，未來不一定支援Trace flag的警示。

 

---

參考:

technet偵測與結束死結

https://technet.microsoft.com/zh-tw/library/ms178104(v=sql.105).aspx

Deadlocking

https://technet.microsoft.com/en-us/library/ms177433(v=sql.105).aspx

trace flag

https://msdn.microsoft.com/en-us/library/ms188396.aspx