問題引入
在日常運維阿裡雲RDS SQL Server産品過程中，經常會被客戶問道：“應用程式被死鎖報錯啦？影響很大，到底是哪個程序導緻了死鎖發生的啊？怎麼解決啊？怎麼辦呀？”。從客戶一連串的問題中，我們深刻體會到了死鎖問題的緊迫性和影響之大。授人予魚而不如授人予漁，RDS SQL Server死鎖系列文章就是為了幫助客人徹底解決死鎖問題為初衷而誕生的。本篇文章是系列文章的開篇，主要是讨論如何使用DBCC來捕獲死鎖資訊，内容包括：
DBCC捕獲死鎖
死鎖測試
死鎖分析
解決方法
DBCC捕獲死鎖
DBCC捕獲死鎖是利用了SQL Server死鎖自動監測機制（預設每5秒運作一次）的傳回資訊，來将死鎖資訊記錄到資料庫日志記錄中，我們可以事後從錯誤日中來檢視這些有用的死鎖資訊，包括：
死鎖的犧牲程序
死鎖發生時的程序資訊
死鎖發生時争搶的資源
其實，DBCC捕獲死鎖資訊的方法本身非常簡單，隻需要使用DBCC指令打開兩個跟蹤标記（1222和1204）即可。方法如下：
USE master
GO
DBCC TRACEON(1222,-1)
GO
--also write like this, that’s fine to use any one
DBCC TRACEON (1204, 1222, -1)
GO 跟蹤标記打開後，我們可以使用下面的語句再次檢查，確定标記打開成功：
DBCC TRACESTATUS(-1)
GO 截圖如下所示：
在這裡也順便把如何關閉死鎖跟蹤标記的方法寫到這裡：
DBCC TRACEOFF (1204, 1222, -1)
GO
--split into two stats
DBCC TRACEOFF (1204,-1)
GO
DBCC TRACEOFF (1222,-1)
GO 死鎖測試
擷取死鎖資訊的跟蹤标記已經打開，接下來進行死鎖測試。首先，在Test資料庫下建立兩個測試表，表名分别為：dbo.test_deadlock1和dbo.test_deadlock2，代碼如下：
IF DB_ID('Test') IS NULL
CREATE DATABASE Test;
GO
USE Test
GO
-- create two test tables
IF OBJECT_ID('dbo.test_deadlock1','u') IS NOT NULL
DROP TABLE dbo.test_deadlock1
GO
CREATE TABLE dbo.test_deadlock1(
id INT IDENTITY(1,1) not null PRIMARY KEY
,name VARCHAR(20) null
);
IF OBJECT_ID('dbo.test_deadlock2','u') IS NOT NULL
DROP TABLE dbo.test_deadlock2
GO
CREATE TABLE dbo.test_deadlock2(
id INT IDENTITY(1,1) not null PRIMARY KEY
,name VARCHAR(20) null
);
INSERT INTO dbo.test_deadlock1
SELECT 'AA'
UNION ALL
SELECT 'BB';
INSERT INTO dbo.test_deadlock2
SELECT 'AA'
UNION ALL
SELECT 'BB';
GO 接下來，我們使用SSMS打開一個新的連接配接，我們假設叫session 1，執行如下語句：
--session 1
USE Test
GO
BEGIN TRAN
UPDATE dbo.test_deadlock1
SET name = 'CC'
WHERE id = 1
;
WAITFOR DELAY '00:00:05'
UPDATE dbo.test_deadlock2
SET name = 'CC'
WHERE id = 1
;
ROLLBACK 緊接着，我們使用SSMS打開第二個連接配接，假設叫Session 2，執行下面的語句：
--session 2
USE Test
GO
BEGIN TRAN
UPDATE dbo.test_deadlock2
SET name = 'CC'
WHERE id = 1
;
UPDATE dbo.test_deadlock1
SET name = 'CC'
WHERE id = 1
;
COMMIT 一段時間以後，你會發現Session 2執行的語句會被死鎖，做為了死鎖的犧牲品，錯誤資訊如下：
Msg 1205, Level 13, State 51, Line 11
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 截圖為證：
死鎖分析
死鎖場景，我們已經模拟出來了，接下來就是分析死鎖的時候了。讓我們檢視錯誤日志：
EXEC sys.sp_readerrorlog 從這個死鎖資訊中，我們不難發現幾個非常有用的資訊：
參與死鎖的程序（process-list）：鎖住其他程序的程序和死鎖犧牲者程序（會有deadlock victim标記）。
死鎖發生時，程序執行的語句（inputbuf）：這個很重要，找到了語句就可以針對死鎖的語句進行針對性的優化解決。
程序争搶的資源（resource-list）：死鎖發生時，到底程序之間在争搶什麼資源，死鎖的類型是什麼？本例資源争搶發生在表Test.dbo.test_deadlock1 的主鍵上indexname=PK__test_dea__3213E83F07020F21，死鎖類型為X鎖（排他鎖）。
解決方法
通過SQL Server錯誤日志中死鎖資訊的分析，我們可以從死鎖發生時程序執行的語句發現，死鎖發生的原因是兩個UPDATE程序操作的表順序不一緻導緻的。我們隻需要調整其中一個程序的UPDATE表順序即可解決這個死鎖問題。比如，調整Session 2的執行語句，如下：
--session 2
USE Test
GO
BEGIN TRAN
UPDATE dbo.test_deadlock1
SET name = 'CC'
WHERE id = 1
;
UPDATE dbo.test_deadlock2
SET name = 'CC'
WHERE id = 1
;
COMMIT 最後總結
本篇分享講解了使用DBCC命名捕獲SQL Server死鎖資訊，是RDS SQL Server死鎖系列文章的開篇，我們還會在後續系列文章分享更多的方法來捕獲死鎖資訊，敬請期待。
原文位址https://yq.aliyun.com/articles/73856