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

作者: OK_008 來源: 博客園發布時間: 2010-01-18 10:02 閱讀: 2010 次推薦: 0 原文鏈接 [收藏]

摘要：本文通過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機構工作，才可能要深入理解它們。