本文將為大家講的是SQL Server Profiler分析死鎖幾大步驟，這里也是為了大家更好的做好數(shù)據(jù)庫的管理工作。

在兩個或多個SQL Server進程中，每一個進程鎖定了其他進程試圖鎖定的資源，就會出現(xiàn)死鎖，例如，進程process1對table1持有1個排它鎖(X),同時process1對table2請求1個排它鎖(X), 進程process2對table2持有1個排它鎖(X),同時process2對table1請求1個排它鎖(X) 類似這種情況，就會出現(xiàn)死鎖,除非當某個外部進程斷開死鎖，否則死鎖中的兩個事務都將無限期等待下去。

Microsoft SQL Server 數(shù)據(jù)庫引擎死鎖監(jiān)視器定期檢查陷入死鎖的任務。

如果監(jiān)視器檢測到循環(huán)依賴關(guān)系，將選擇其中一個任務作為犧牲品(通常是選擇占資源比較小的進程作為犧牲品)，然后終止其事務并提示錯誤1205。

這里我們通過SQL Server Profiler來監(jiān)視分析死鎖的發(fā)生過程，那樣我們就會深刻理解死鎖的成因。

1.創(chuàng)建測試表。

在 Microsoft SQL Server Management Studio上，新建一個查詢，寫創(chuàng)建表DealLockTest_1 & DealLockTest_2兩個表：

腳本：

useTest 
--創(chuàng)建分析死鎖使用到的兩個表DealLockTest_1&DealLockTest_2 
go 
SetNocountOn 
Go 
ifobject_id('DealLockTest_1')IsNotNull 
DropTableDealLockTest_1 
go 
CreateTableDealLockTest_1 
( 
IDintIdentity(1,1)PrimaryKey, 
Namenvarchar(512) 
) 
ifobject_id('DealLockTest_2')IsNotNull 
DropTableDealLockTest_2 
go 
CreateTableDealLockTest_2 
( 
IDintIdentity(1,1)PrimaryKey, 
Namenvarchar(512) 
) 
Go

InsertIntoDealLockTest_1(Name) 
SelectnameFromsys.all_objects 
InsertIntoDealLockTest_2(Name) 
SelectnameFromsys.all_objects 
Go

創(chuàng)建好表和插入測試數(shù)據(jù)后，先執(zhí)行腳本代碼（因為我們不需要跟蹤該代碼），緊接著，我們就模擬兩個會話，一個會話里面包含一個事務。這里我們就新建兩個查詢，其中***個會話，是更新DealLockTest_1表后，等待5秒鐘，更新DealLocktest_2.

UseTest 
Go 
--***個會話 
BeginTran 
UpdateDealLockTest_1 
SetName=N'test1'
WhereID>0 
/*這里的Waitfor等待，是為了容易獲取死鎖的發(fā)生*/ 
WaitforDelay'00:00:05'
UpdateDealLockTest_2 
SetName=N'test2'
WhereID>0 
CommitTran 
Go

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

UseTest 
Go 
--第二個會話 
BeginTran 
UpdateDealLockTest_2 
SetName=N'test1'
WhereID>0 
/*這里的Waitfor等待，是為了容易獲取死鎖的發(fā)生*/ 
WaitforDelay'00:00:05' 
UpdateDealLockTest_1 
SetName=N'test2'
WhereID>0 
CommitTran 
Go

第二個會話代碼，也先不要執(zhí)行。

2.啟動SQL Server Profiler，創(chuàng)建Trace(跟蹤).

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

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

點執(zhí)行按鈕，啟動Trace。

3.執(zhí)行測試代碼&監(jiān)視死鎖。

轉(zhuǎn)到 Microsoft SQL Server Management Studio界面，執(zhí)行***個會話&第二個會話的代碼，稍稍等待5秒鐘，我們就會發(fā)現(xiàn)其中一個會話收到報錯消息

我們再切換到SQL Server Profiler界面，就能發(fā)現(xiàn)SQL Server Profiler收到執(zhí)行腳本過程發(fā)生死鎖的信息。

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

4.分析死鎖

如下圖，我們可以看到***個會話在SPID 54,第二個會話在SPID 55,一旦SQL Server發(fā)現(xiàn)死鎖，它就會確定一個優(yōu)勝者，可成功執(zhí)行，和另一個作為犧牲品，要回滾。

可以到看到EventClass列中，兩條SQL:BatchCompleted事件緊跟在Lock:DealLock后面，其中一條，它就是作為犧牲品，它會被回滾.而另一條SQL:BatchCompleted將會是優(yōu)勝者，成功執(zhí)行。

那么，誰是優(yōu)勝者，誰是犧牲品呢? 不用著急，通過DealLock graph事件，所返回來的信息，我們可以知道結(jié)果。

我們雖然不能明白DealLock graph圖示的含義，但通過圖中描述的關(guān)系，我們知道一些有用的信息。圖中左右兩旁橢圓形相當一個處理節(jié)點(Process Node),當鼠標移動到上面的時候，可以看到內(nèi)部執(zhí)行的代碼，如Insert,UPdate,Delete.有打叉的左邊橢圓形就是犧牲者，沒有打叉的右邊橢圓形是優(yōu)勝者。中間兩個長方形就是一個資源節(jié)點(Resource Node)，描述數(shù)據(jù)庫中的對象,如一個表、一行或一個索引。在我們當前的實例中，資源節(jié)點描述的是，在聚集索引請求獲得排它鎖(X)。橢圓形與長方形之間，帶箭頭的連線表示，處理節(jié)點與資源節(jié)點的關(guān)系，包含描述鎖的模式.

接下來我們更詳細的看圖里面的數(shù)據(jù)說明。

先看右邊作為優(yōu)勝者的這橢圓形，我們可以看到內(nèi)容包含有：

服務器進程 ID： 服務器進程標識符 (SPID)，即服務器給擁有鎖的進程分配的標識符。服務器批 ID： 服務器批標識符 (SBID)。執(zhí)行上下文 ID： 執(zhí)行上下文標識符 (ECID)。與指定 SPID 相關(guān)聯(lián)的給定線程的執(zhí)行上下文 ID。ECID = {0,1,2,3, ...n}，其中 0 始終表示主線程或父線程，并且 {1,2,3, ...n} 表示子線程。死鎖優(yōu)先級： 進程的死鎖優(yōu)先級有關(guān)可能值的詳細信息，請參閱 SET DEADLOCK_PRIORITY (Transact-SQL)。已用日志： 進程所使用的日志空間量。所有者 ID： 正在使用事務并且當前正在等待鎖的進程的事務 ID。事務描述符： 指向描述事務狀態(tài)的事務描述符的指針。

這些數(shù)據(jù)描述，對于我們理解死鎖，只需要知道其中的一些就夠，除非我們在專門SQL Server機構(gòu)工作，才可能要深入理解它們。

下面我們來看左邊作為犧牲品的這橢圓形處理節(jié)點，它告訴我們以下信息：

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

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

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

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

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

這也就讓我們明白死鎖發(fā)生的原因。

這里說明下資源節(jié)點的一些信息：

HoBT：　　堆或 B 樹。 用于保護沒有聚集索引的表中的 B 樹（索引）或堆數(shù)據(jù)頁的鎖

associated objid: 關(guān)聯(lián)的對象ID,這里只是索引關(guān)聯(lián)的對象ID.

Index name:索引名

讓我們再對SQL Server Profiler監(jiān)視到的數(shù)據(jù)，作一次整理：

回顧圖：

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

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

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

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

同時，

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

這里就出現(xiàn)了進程阻塞，從而發(fā)生死鎖。

4.SQL Server 檢查到這兩個進程(***個&第二個會話)發(fā)生死鎖，并對占用資源比較少的進程，列入犧牲品名單，將它終止（Kill）。通過左右橢圓形進程節(jié)點顯示，可以發(fā)現(xiàn)已用日志最少的是左邊的進程節(jié)點。

5. SPID 54 （***個會話）被回滾(Rollback)，SPID 55 （第二個會話）執(zhí)行成功。

到這里我們已算完成了，對死鎖的監(jiān)視和分析。

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

【編輯推薦】

1. SQL Server 2008數(shù)據(jù)庫設計與實現(xiàn)
2. SQL Server 2008中的9種數(shù)據(jù)挖掘算法
3. SQL Server 2008服務器安裝設置向?qū)?/font>
4. SQL Server 2008重新創(chuàng)建表的方法
5. SQL Server 2008中的創(chuàng)建格式化日期