428
RDS SQL Server死鎖（Deadlock）係列之二使用Profiler捕獲死鎖
問題引入
不管是RDS SQL Server還是自建SQL Server數據庫，死鎖的確是一個非常頭疼的問題，上一篇文章我們已經談到了使用DBCC捕獲死鎖。這篇文章是以阿裏雲RDS客戶遇到的死鎖問題為背景，分享死鎖文章係列之二使用Profiler捕獲死鎖。
Profiler捕獲死鎖
使用Profiler工具的Deadlock graph事件，可以非常方便直觀的捕獲死鎖信息。方法是：
開啟MSSQL Profiler：開始 -> 運行 -> 鍵入profiler
新建Deadlock Graph Trace：在Profiler窗體中，開啟一個Trace -> 顯示所有事件 -> 依次找到Locks -> DeadLocak Graph -> 運行（詳情參見下麵的截圖，按照字母標號依次點擊）。
注意這裏我們僅Trace這一個事件就好了，取消其他多餘與死鎖無關的事件。
死鎖測試
死鎖測試的方法和上一篇文章一致，參見上一篇文章RDS SQL Server死鎖（Deadlock）係列之一使用DBCC捕獲死鎖中死鎖測試部分，在此不在累述。
死鎖分析
當死鎖狀況發生時，Profiler捕獲到死鎖信息，繪製成Deadlock Graph圖，非常直觀的展示了死鎖的進程、犧牲的進程和爭搶的資源。
分析Deadlock graph圖
接下來就是分析死鎖發生時的情況，參加如下截圖：
通過死鎖關係圖的展示，我們可以分析如下：
犧牲進程：圖中最左邊被×掉的64號進程是死鎖犧牲品，它申請到了test_deadlock2的X鎖，再申請test_deadlock1的X鎖時，被做為了犧牲品。
死鎖進程：圖中最右邊63號進程首先獲取到了test_deadlock1的X鎖，然後申請test_deadlock2的X鎖，但這個時候64號進程已經拿走了test_deadlock2的X鎖。係統選擇殺死64號進程（即64做為了犧牲品），讓63號進程成功獲取到test_deadlock2的X鎖，他是本次資源爭搶的獲勝者。
爭搶的資源：圖中中部是兩個進程爭搶的資源，我們可以通過圖中資源的HoBt ID獲取表和索引名，方法如下：
分析Deadlock Trace文件
雖然通過Deadlock Graph圖可以很清楚的分析出死鎖的關係，找到資源的爭搶點，但是我個人推薦分析Deadlock Trace文件的方式，這種方式更加簡單明了。我們需要首先保存Deadlock Graph監控信息到文件，比如保存到C:\Temp\Deadlock_testing.trc，方法如下：
文件保存完畢以後的.trc為後綴的文件其實就是xml類型的文件，我們可以使用接下來的語句進行分析XML文件：
use master
go
-- declare variables.
declare
@file nvarchar(256)
;
select
@file = N'C:\Temp\Deadlock_testing.trc'
;
WITH DATA
AS
(
SELECT
--[TraceID] = @trace_id ,
RowNumber = row_number() OVER (ORDER BY StartTime), -- assign a row number to each deadlock
[DeadlockGraph]=case when TextData like '<deadlock-list%' then convert(xml, TextData) else null end,
*
from ::fn_trace_gettable(@file, default)
where TextData like '<deadlock-list%'
)
,
deadlock
AS
(
SELECT
RowNumber
,OwnerID = T.C.value('@id', 'varchar(50)')
,SPid = T.C.value('(./@spid)[1]','int')
,status = T.C.value('(./@status)[1]','varchar(10)')
--,PagelockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)')
--,DeadlockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)')
--,KeylockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)')
--,DeadlockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)')
,Victim = case when T.C.value('@id', 'varchar(50)') = T.C.value('./../../@victim','varchar(50)') then 1 else 0 end
,LockMode = T.C.value('@lockMode', 'varchar(20)')
,DeadlockGraph
,Inputbuf = T.C.value('(./inputbuf/text())[1]','varchar(max)')
,Code = T.C.value('(./executionStack/frame/text())[1]','VARCHAR(max)')
,SPName = T.C.value('(./executionStack/frame/@procname)[1]','sysname')
,Hostname = T.C.value('(./@hostname)[1]','sysname')
,Clientapp = T.C.value('(./@clientapp)[1]','varchar(max)')
,LoginName = T.C.value('@loginname', 'varchar(20)')
,Action = T.C.value('(./@transactionname)[1]','varchar(max)')
,StartTime
,TransactionTime = T.C.value('@lasttranstarted', 'datetime')
--,*
FROM DATA AS A
CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/process-list/process') AS T(C)
)
,
keylock
AS
(
SELECT
OwnerID = T.C.value('./owner[1]/@id', 'varchar(50)')
,KeylockObject = T.C.value('./../@objectname', 'varchar(200)')
,Indexname = T.C.value('./../@indexname', 'varchar(200)')
,IndexLockMode = T.C.value('./../@mode', 'varchar(20)')
--,owner = T.C.query('.')
FROM DATA AS A
CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/resource-list/keylock/owner-list') AS T(C)
)
SELECT
--A.OwnerID
A.SPid
,is_Vitim = A.Victim
--,A.DeadlockGraph
,A.SPName
,A.Code
,A.LockMode
,A.StartTime
,B.Indexname
,B.KeylockObject
,B.IndexLockMode
,A.Inputbuf
,A.Hostname
,A.LoginName
,A.Clientapp
,A.Action
,status
,A.TransactionTime
FROM deadlock AS A
LEFT JOIN keylock AS B
ON A.OwnerID = B.OwnerID
ORDER BY A.RowNumber,A.Victim
執行的結果如下截圖：
從這個分析結果來看，我們可以非常清晰明了得到如下信息：
死鎖與被死鎖進程：63和64號進程
死鎖發生時，兩個進程執行的語句
死鎖的類型：本例是X鎖
鎖定資源的對象和索引名
死鎖的兩個進程執行的語句塊是什麼
進程執行所在的主機
......
分析Deadlock Trace表
我們既可以將Deadlock Graph保存為Trace文件，還可以將其保存到Trace表中，假如我們將這個捕獲到的死鎖信息保存到本地數據庫表test.dbo.Deadlock_testing中，方法如下：
分析Deadlock Trace Table方法與分析Deadlock Trace File類似，隻需要將分析語句中的DATA公用表示稍微修改即可：
WITH DATA
AS
(
SELECT
RowNumber = row_number() OVER (ORDER BY StartTime)
,DeadlockGraph = CAST(TextData AS xml)
,StartTime
,spid
FROM test.dbo.Deadlock_testing WITH (NOLOCK)
WHERE EventClass = 148
)
阿裏雲RDS SQL Server
如果你是阿裏雲RDS SQL Server 2008R2用戶，請工單聯係阿裏雲，申請實例的Profiler權限，然後即可按照本方法來自行排查；如果你是阿裏雲RDS SQL Server 2012用戶，默認已經具備Profiler權限，無需申請權限。
最後總結
使用Profiler捕獲死鎖信息的方法比使用DBCC的方式更加靈活，直觀，一目了然。希望阿裏雲RDS SQL Server客戶借助本係列文章都可以自己動起手來，分析死鎖，解決死鎖的問題。
最後更新：2017-04-21 10:00:52