如何在SQL Server的Extended Events中查詢死結(Deadlock)

在電腦領域裡，死結（Deadlock）是指：
兩個（或多個）執行緒/程式在互相等待對方釋放資源，結果誰也無法繼續執行的狀況。例如：

• 程式A鎖住資源1，等待資源2；
• 程式B鎖住資源2，等待資源1；

➯ 結果兩者都卡住，系統「死結」。

死結有四個基本要件：

1. 互斥(Mutual Exclusion)

   同時間一項資源(Resource)只能被一個程序(Process)所佔用

2. 不可強佔(No Preemption)

   資源被佔用時，其他程序必須等待資源被釋放才能佔用這個資源

3. 持有並等待(Hold and Wait)

   一個程序佔用一項資源，也等待另一項資源被釋放

4. 循環式等待(Circular Wait)

   A程序等待B程序釋放資源
   B程序等待C程序釋放資源
   C程序等待D程序釋放資源
   ...
   X程序等待A程序釋放資源

SQL Server中的鎖定監控程序(Lock Monitor Process)負責偵測死結。依據以下方式週期式執行：

• 預設狀態下，每5秒鐘偵測死結的發生
• 發現死結時，立刻進行偵測程序
• 確定死結的發生，偵測的頻率改為每0.1秒執行一次

偵測到死結時，鎖定監控程序會選定一個程序為犧牲者(Victim)，終止這個程序並發送錯誤邊碼為1205的錯誤訊息。程序終止後，鎖定被解除，其他程序就可以繼續工作。死結犧牲者會依下列方式進行選定：

• 如發生死結的交易deadlock priority相同，會優先選定復原成本較低的交易。
• deadlock priority設定不相同，則優先選定deadlock priority較低者。

接著我們在SQL Server中分析死結，首先產生死結：

先建立資料庫及資料表

USE master;
IF EXISTS(SELECT * FROM sys.databases WHERE name=N'db01')
BEGIN
  ALTER DATABASE db01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE db01;
END
GO
CREATE DATABASE db01;
GO

USE db01;

CREATE TABLE dbo.Test(id int PRIMARY KEY);
GO

執行以下T-SQL語法

BEGIN TRAN;

INSERT INTO dbo.Test(id) VALUES(1);

WAITFOR DELAY '0:0:10';

INSERT INTO dbo.Test(id) VALUES(10);

ROLLBACK TRAN;

在10秒內執行另一段T-SQL語法

BEGIN TRAN;

INSERT INTO dbo.Test(id) VALUES(10);

INSERT INTO dbo.Test(id) VALUES(1);

ROLLBACK TRAN;

等待一段時間(大約十多秒)，產生錯誤訊息1205如下圖

在Object Explorer(F8)，展開Management
➔Extended Events➔Sessions➔system_health
雙擊package0.event_file

Ctrl+F快速搜尋中尋找deadlock

選擇xml_deadlock_report可以在下方的Detail的xml_report分析deadlock

或是切換到Deadlock頁籤查看Deadlock視圖