41
RDS SQL Server死鎖（Deadlock）係列之一使用DBCC捕獲死鎖
問題引入
在日常運維阿裏雲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死鎖係列文章的開篇，我們還會在後續係列文章分享更多的方法來捕獲死鎖信息，敬請期待。
最後更新：2017-04-19 11:30:40