通過SQL Server Profiler來監視分析死結

在兩個或多個SQL Server進程中，每一個進程鎖定了其他進程試圖鎖定資源，就會出現死結，例如，
進程process1對table1持有1個排它鎖(X),同時process1對table2請求1個排它鎖(X),
進程process2對table2持有1個排它鎖(X),同時process2對table1請求1個排它鎖(X)

類似這種情況，就會出現死結,除非當某個外部進程斷開死結，否則死結中的兩個事務都將無限期等待下去。

Microsoft SQL Server 資料庫引擎死結監視器定期檢查陷入死結的任務。

如果監視器檢測到循環相依性關係，將選擇其中一個任務作為犧牲品(通常是選擇佔資源比較小的進程作為犧牲品)，然後終止其事務並提示錯誤1205。

這裡我們通過SQL Server Profiler來監視分析死結的發生過程，那樣我們就會深刻理解死結的成因。

 

1.建立測試表。

在 Microsoft SQL Server Management  Studio上，建立一個查詢，寫建立表DealLockTest_1 & DealLockTest_2兩個表：

 

 指令碼：

代碼

use Test

--建立分析死結使用到的兩個表DealLockTest_1 & DealLockTest_2
go
Set Nocount On    
Go
if object_id('DealLockTest_1') Is Not Null
    Drop Table DealLockTest_1
go
Create Table DealLockTest_1
(
    ID int Identity(1,1) Primary Key,
    Name nvarchar(512)
)

if object_id('DealLockTest_2') Is Not Null
    Drop Table DealLockTest_2
go
Create Table DealLockTest_2
(
    ID int Identity(1,1) Primary Key,
    Name nvarchar(512)
)

Go

--插入一些測試資料
Insert Into DealLockTest_1(Name)
    Select name From sys.all_objects

    
Insert Into DealLockTest_2(Name)
    Select name From sys.all_objects
Go
    

建立好表和插入測試資料後，先執行指令碼代碼（因為我們不需要跟蹤該代碼），緊接著，我們就類比兩個會話，一個會話裡麵包含一個事務。這裡我們就建立兩個查詢，其中第一個會話，是更新DealLockTest_1表後，等待5秒鐘，更新DealLocktest_2.

 

 

代碼

Use Test
Go

    --第一個會話
    
    Begin Tran
    
    Update DealLockTest_1
        Set Name=N'test1'
        Where ID >0
    
    
    /*這裡的Waitfor等待，是為了容易擷取死結的發生*/        
    Waitfor Delay '00:00:05'
    
        
    Update DealLockTest_2
        Set Name=N'test2'
        Where ID >0        
    
    Commit Tran

Go

 

代碼寫好後，我們先不要執行代碼，接下來就寫第二個會話代碼； 第二個會話更新表的順序，剛好與第一個會話相反，是更新DealLockTest_2表後，等待5秒鐘，更新DealLocktest_1.

 

 

代碼

Use Test
Go
    --第二個會話
    
    Begin Tran
    
    Update DealLockTest_2
        Set Name=N'test1'
        Where ID >0

    /*這裡的Waitfor等待，是為了容易擷取死結的發生*/                
    Waitfor Delay '00:00:05'            
        
    Update DealLockTest_1
        Set Name=N'test2'
        Where ID >0        
    
    Commit Tran

Go

 

第二個會話代碼，也先不要執行。

 

 

2.啟動SQL Server Profiler，建立Trace(跟蹤).

 

啟動SQL Server Profiler工具(在Microsoft SQL Server Management  Studio的工具菜單上就發現它)，建立一個Trace,Trace屬性選擇主要是包含:

Deadlock graph
Lock: Deadlock
Lock: Deadlock Chain
RPC:Completed
SP:StmtCompleted
SQL:BatchCompleted
SQL:BatchStarting

 

 點執行按鈕，啟動Trace。

 

3.執行測試代碼&監視死結。

 轉到 Microsoft SQL Server Management  Studio介面，執行第一個會話&第二個會話的代碼，稍稍等待5秒鐘，我們就會發現其中一個會話收到報錯訊息

 

 

 

 我們再切換到SQL Server Profiler介面，就能發現SQL Server Profiler收到執行指令碼過程發生死結的資訊。

 

 

 OK,這裡就先停止SQL Server Profiler上的“暫停跟蹤” Or "停止跟蹤"按鈕，下面我們具體分析死結發生過程。

 

 

4.分析死結

 如，我們可以看到第一個會話在SPID 54,第二個會話在SPID 55,一旦SQL Server發現死結，它就會確定一個優勝者，可成功執行，和另一個作為犧牲品，要復原。

可以到看到EventClass列中，兩條SQL:BatchCompleted事件緊跟在Lock:DealLock後面，其中一條，它就是作為犧牲品，它會被復原.而另一條SQL:BatchCompleted將會是優勝者，成功執行。

 那麼，誰是優勝者，誰是犧牲品呢? 不用著急，通過DealLock graph事件，所返回來的資訊，我們可以知道結果。

 

 

 

 我們雖然不能明白DealLock graph圖示的含義，但通過圖中描述的關係，我們知道一些有用的資訊。圖中左右兩旁橢圓形相當一個處理節點(Process Node),當滑鼠移動到上面的時候，可以看到內部執行的代碼，如Insert,UPdate,Delete.有打叉的左邊橢圓形就是犧牲者，沒有打叉的右邊橢圓形是優勝者。中間兩個長方形就是一個資源節點(Resource Node)，描述資料庫中的對象,如一個表、一行或一個索引。在我們當前的執行個體中，資源節點描述的是，在叢集索引請求獲得排它鎖(X)。橢圓形與長方形之間，帶箭頭的連線表示，處理節點與資源節點的關係，包含描述鎖的模式.

 

 接下來我們更詳細的看圖裡面的資料說明。

 先看右邊作為優勝者的這橢圓形，我們可以看到內容包含有：

 

伺服器處理序 ID： 伺服器處理序標識符 (SPID)，即伺服器給擁有鎖的進程分配的標識符。
伺服器批 ID： 伺服器批標識符 (SBID)。
執行內容 ID： 執行內容標識符 (ECID)。與指定 SPID 相關聯的給定線程的執行內容 ID。ECID = {0,1,2,3, ...n}，其中 0 始終表示主線程或父線程，並且 {1,2,3, ...n} 表示子線程。
死結優先順序： 進程的死結優先順序有關可能值的詳細資料，請參閱 SET DEADLOCK_PRIORITY (Transact-SQL)。
已用日誌： 進程所使用的日誌空間量。
所有者 ID： 正在使用事務並且當前正在等待鎖的進程的事務 ID。
事務描述符： 指向描述事務狀態的事務描述符的指標。
 

這些資料描述，對於我們理解死結，只需要知道其中的一些就夠，除非我們在專門SQL Server機構工作，才可能要深入理解它們。

 

 

 

 下面我們來看左邊作為犧牲品的這橢圓形處理節點，它告訴我們以下資訊：

1.它是一個失敗的交易。（藍色的交叉表示）

2.它是作為犧牲品的T-SQL代碼。

3.它對右下方的資源節點有一個排它鎖(X).

4.它對右上方的資源節點請求 一個排它鎖（X）.

 

 

 

 

我們再來看中間兩個長方形的資源節點,兩個處理節點對它們各自都使用權，來執行它們各自的代碼，同時又有對對方使用資源請求的動作，從而發生了資源的競爭。

這也就讓我們明白死結發生的原因。

 

 這裡說明下資源節點的一些資訊：

 

HoBT：　　堆或 B 樹。 用於保護沒有叢集索引的表中的 B 樹（索引）或堆資料頁的鎖

associated objid: 關聯的對象ID,這裡只是索引關聯的對象ID.

Index name:索引名

 

 

 

 

 

 

 

 

 

 讓我們再對SQL Server Profiler監視到的資料，作一次整理：

 回顧圖：

 

1.在第3行SQL:BatchStarting, SPID 54 （第一個會話啟動）,在索引PK__DealLock__3214EC274222D4EF獲得一個排它鎖，再處理等待狀態，(因為在這個執行個體中我設定了Waitfor Delay '00:00:05')

2.在第6行SQL:BatchStarting, SPID 55 （第二個會話啟動）,在索引PK__DealLock__3214EC2745F365D3獲得一個排它鎖，再處理等待狀態，(因為在這個執行個體中我設定了Waitfor Delay '00:00:05')

3.兩個進程都各自獲得一個排它鎖(X),幾秒過去，它們就開始請求排它鎖。

SPID 54 （第一個會話）,先對PK__DealLock__3214EC2745F365D3請求一個排它鎖(X),但PK__DealLock__3214EC2745F365D3當前已經給SPID 55 （第二個會話）獲得。SPID 54要於等待。

同時，

SPID 55 （第二個會話）,開始對PK__DealLock__3214EC274222D4EF請求一個排它鎖(X),但PK__DealLock__3214EC274222D4EF當前已經給SPID 54 （第一個會話）獲得。SPID 55要等待。 

 

這裡就出現了進程阻塞，從而發生死結。

 

4.SQL Server  檢查到這兩個進程(第一個&第二個會話)發生死結，並對佔用資源比較少的進程，列入犧牲品名單，將它終止（Kill）。通過左右橢圓形進程節點顯示，可以發現已用日誌最少的是左邊的進程節點。

 

5. SPID 54 （第一個會話）被復原(Rollback)，SPID 55 （第二個會話）執行成功。

 

 到這裡我們已算完成了，對死結的監視和分析。

 

（注：是於其他死結的定義，死結模式，死結避免&預防,等等，不是本文重點，我沒有提出,網上太多這方面的文章）

 

 (完)