詳解SQL Server Profiler分析死鎖幾大步驟
本文將為大家講的是SQL Server Profiler分析死鎖幾大步驟，這里也是為了大家更好的做好數(shù)據(jù)庫的管理工作。
在兩個(gè)或多個(gè)SQL Server進(jìn)程中，每一個(gè)進(jìn)程鎖定了其他進(jìn)程試圖鎖定的資源，就會(huì)出現(xiàn)死鎖，例如，進(jìn)程process1對(duì)table1持有1個(gè)排它鎖(X),同時(shí)process1對(duì)table2請(qǐng)求1個(gè)排它鎖(X), 進(jìn)程process2對(duì)table2持有1個(gè)排它鎖(X),同時(shí)process2對(duì)table1請(qǐng)求1個(gè)排它鎖(X) 類似這種情況，就會(huì)出現(xiàn)死鎖,除非當(dāng)某個(gè)外部進(jìn)程斷開死鎖，否則死鎖中的兩個(gè)事務(wù)都將無限期等待下去。
Microsoft SQL Server 數(shù)據(jù)庫引擎死鎖監(jiān)視器定期檢查陷入死鎖的任務(wù)。
如果監(jiān)視器檢測到循環(huán)依賴關(guān)系，將選擇其中一個(gè)任務(wù)作為犧牲品(通常是選擇占資源比較小的進(jìn)程作為犧牲品)，然后終止其事務(wù)并提示錯(cuò)誤1205。
這里我們通過SQL Server Profiler來監(jiān)視分析死鎖的發(fā)生過程，那樣我們就會(huì)深刻理解死鎖的成因。
1.創(chuàng)建測試表。
在 Microsoft SQL Server Management Studio上，新建一個(gè)查詢，寫創(chuàng)建表DealLockTest_1 & DealLockTest_2兩個(gè)表：
腳本：
- useTest
- --創(chuàng)建分析死鎖使用到的兩個(gè)表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í)行腳本代碼（因?yàn)槲覀儾恍枰櫾摯a），緊接著，我們就模擬兩個(gè)會(huì)話，一個(gè)會(huì)話里面包含一個(gè)事務(wù)。這里我們就新建兩個(gè)查詢，其中***個(gè)會(huì)話，是更新DealLockTest_1表后，等待5秒鐘，更新DealLocktest_2.
- UseTest
- Go
- --***個(gè)會(huì)話
- BeginTran
- UpdateDealLockTest_1
- SetName=N'test1'
- WhereID>0
- /*這里的Waitfor等待，是為了容易獲取死鎖的發(fā)生*/
- WaitforDelay'00:00:05'
- UpdateDealLockTest_2
- SetName=N'test2'
- WhereID>0
- CommitTran
- Go
代碼寫好后，我們先不要執(zhí)行代碼，接下來就寫第二個(gè)會(huì)話代碼； 第二個(gè)會(huì)話更新表的順序，剛好與***個(gè)會(huì)話相反，是更新DealLockTest_2表后，等待5秒鐘，更新DealLocktest_1.
- UseTest
- Go
- --第二個(gè)會(huì)話
- BeginTran
- UpdateDealLockTest_2
- SetName=N'test1'
- WhereID>0
- /*這里的Waitfor等待，是為了容易獲取死鎖的發(fā)生*/
- WaitforDelay'00:00:05'
- UpdateDealLockTest_1
- SetName=N'test2'
- WhereID>0
- CommitTran
- Go
第二個(gè)會(huì)話代碼，也先不要執(zhí)行。
2.啟動(dòng)SQL Server Profiler，創(chuàng)建Trace(跟蹤).
啟動(dòng)SQL Server Profiler工具(在Microsoft SQL Server Management Studio的工具菜單上就發(fā)現(xiàn)它)，創(chuàng)建一個(gè)Trace,Trace屬性選擇主要是包含:
Deadlock graph Lock: Deadlock Lock: Deadlock Chain RPC:Completed SP:StmtCompleted SQL:BatchCompleted SQL:BatchStarting
點(diǎn)執(zhí)行按鈕，啟動(dòng)Trace。
3.執(zhí)行測試代碼&監(jiān)視死鎖。
轉(zhuǎn)到 Microsoft SQL Server Management Studio界面，執(zhí)行***個(gè)會(huì)話&第二個(gè)會(huì)話的代碼，稍稍等待5秒鐘，我們就會(huì)發(fā)現(xiàn)其中一個(gè)會(huì)話收到報(bào)錯(cuò)消息
我們?cè)偾袚Q到SQL Server Profiler界面，就能發(fā)現(xiàn)SQL Server Profiler收到執(zhí)行腳本過程發(fā)生死鎖的信息。
OK,這里就先停止SQL Server Profiler上的“暫停跟蹤” Or "停止跟蹤"按鈕，下面我們具體分析死鎖發(fā)生過程。
4.分析死鎖
如下圖，我們可以看到***個(gè)會(huì)話在SPID 54,第二個(gè)會(huì)話在SPID 55,一旦SQL Server發(fā)現(xiàn)死鎖，它就會(huì)確定一個(gè)優(yōu)勝者，可成功執(zhí)行，和另一個(gè)作為犧牲品，要回滾。
可以到看到EventClass列中，兩條SQL:BatchCompleted事件緊跟在Lock:DealLock后面，其中一條，它就是作為犧牲品，它會(huì)被回滾.而另一條SQL:BatchCompleted將會(huì)是優(yōu)勝者，成功執(zhí)行。
那么，誰是優(yōu)勝者，誰是犧牲品呢? 不用著急，通過DealLock graph事件，所返回來的信息，我們可以知道結(jié)果。
我們雖然不能明白DealLock graph圖示的含義，但通過圖中描述的關(guān)系，我們知道一些有用的信息。圖中左右兩旁橢圓形相當(dāng)一個(gè)處理節(jié)點(diǎn)(Process Node),當(dāng)鼠標(biāo)移動(dòng)到上面的時(shí)候，可以看到內(nèi)部執(zhí)行的代碼，如Insert,UPdate,Delete.有打叉的左邊橢圓形就是犧牲者，沒有打叉的右邊橢圓形是優(yōu)勝者。中間兩個(gè)長方形就是一個(gè)資源節(jié)點(diǎn)(Resource Node)，描述數(shù)據(jù)庫中的對(duì)象,如一個(gè)表、一行或一個(gè)索引。在我們當(dāng)前的實(shí)例中，資源節(jié)點(diǎn)描述的是，在聚集索引請(qǐng)求獲得排它鎖(X)。橢圓形與長方形之間，帶箭頭的連線表示，處理節(jié)點(diǎn)與資源節(jié)點(diǎn)的關(guān)系，包含描述鎖的模式.
接下來我們更詳細(xì)的看圖里面的數(shù)據(jù)說明。
先看右邊作為優(yōu)勝者的這橢圓形，我們可以看到內(nèi)容包含有：
服務(wù)器進(jìn)程 ID： 服務(wù)器進(jìn)程標(biāo)識(shí)符 (SPID)，即服務(wù)器給擁有鎖的進(jìn)程分配的標(biāo)識(shí)符。服務(wù)器批 ID： 服務(wù)器批標(biāo)識(shí)符 (SBID)。執(zhí)行上下文 ID： 執(zhí)行上下文標(biāo)識(shí)符 (ECID)。與指定 SPID 相關(guān)聯(lián)的給定線程的執(zhí)行上下文 ID。ECID = {0,1,2,3, ...n}，其中 0 始終表示主線程或父線程，并且 {1,2,3, ...n} 表示子線程。死鎖優(yōu)先級(jí)： 進(jìn)程的死鎖優(yōu)先級(jí)有關(guān)可能值的詳細(xì)信息，請(qǐng)參閱 SET DEADLOCK_PRIORITY (Transact-SQL)。已用日志： 進(jìn)程所使用的日志空間量。所有者 ID： 正在使用事務(wù)并且當(dāng)前正在等待鎖的進(jìn)程的事務(wù) ID。事務(wù)描述符： 指向描述事務(wù)狀態(tài)的事務(wù)描述符的指針。
這些數(shù)據(jù)描述，對(duì)于我們理解死鎖，只需要知道其中的一些就夠，除非我們?cè)趯ｉTSQL Server機(jī)構(gòu)工作，才可能要深入理解它們。
下面我們來看左邊作為犧牲品的這橢圓形處理節(jié)點(diǎn)，它告訴我們以下信息：
1.它是一個(gè)失敗的事務(wù)。（藍(lán)色的交叉表示）
2.它是作為犧牲品的T-SQL代碼。
3.它對(duì)右下方的資源節(jié)點(diǎn)有一個(gè)排它鎖(X).
4.它對(duì)右上方的資源節(jié)點(diǎn)請(qǐng)求 一個(gè)排它鎖（X）.
我們?cè)賮砜粗虚g兩個(gè)長方形的資源節(jié)點(diǎn),兩個(gè)處理節(jié)點(diǎn)對(duì)它們各自都使用權(quán)，來執(zhí)行它們各自的代碼，同時(shí)又有對(duì)對(duì)方使用資源請(qǐng)求的動(dòng)作，從而發(fā)生了資源的競爭。
這也就讓我們明白死鎖發(fā)生的原因。
這里說明下資源節(jié)點(diǎn)的一些信息：
HoBT： 堆或 B 樹。 用于保護(hù)沒有聚集索引的表中的 B 樹（索引）或堆數(shù)據(jù)頁的鎖
associated objid: 關(guān)聯(lián)的對(duì)象ID,這里只是索引關(guān)聯(lián)的對(duì)象ID.
Index name:索引名
讓我們?cè)賹?duì)SQL Server Profiler監(jiān)視到的數(shù)據(jù)，作一次整理：
回顧圖：
1.在第3行SQL:BatchStarting, SPID 54 （***個(gè)會(huì)話啟動(dòng)）,在索引PK__DealLock__3214EC274222D4EF獲得一個(gè)排它鎖，再處理等待狀態(tài)，(因?yàn)樵谶@個(gè)實(shí)例中我設(shè)置了Waitfor Delay '00:00:05')
2.在第6行SQL:BatchStarting, SPID 55 （第二個(gè)會(huì)話啟動(dòng)）,在索引PK__DealLock__3214EC2745F365D3獲得一個(gè)排它鎖，再處理等待狀態(tài)，(因?yàn)樵谶@個(gè)實(shí)例中我設(shè)置了Waitfor Delay '00:00:05')
3.兩個(gè)進(jìn)程都各自獲得一個(gè)排它鎖(X),幾秒過去，它們就開始請(qǐng)求排它鎖。
SPID 54 （***個(gè)會(huì)話）,先對(duì)PK__DealLock__3214EC2745F365D3請(qǐng)求一個(gè)排它鎖(X),但PK__DealLock__3214EC2745F365D3當(dāng)前已經(jīng)給SPID 55 （第二個(gè)會(huì)話）獲得。SPID 54要于等待。
同時(shí)，
SPID 55 （第二個(gè)會(huì)話）,開始對(duì)PK__DealLock__3214EC274222D4EF請(qǐng)求一個(gè)排它鎖(X),但PK__DealLock__3214EC274222D4EF當(dāng)前已經(jīng)給SPID 54 （***個(gè)會(huì)話）獲得。SPID 55要等待。
這里就出現(xiàn)了進(jìn)程阻塞，從而發(fā)生死鎖。
4.SQL Server 檢查到這兩個(gè)進(jìn)程(***個(gè)&第二個(gè)會(huì)話)發(fā)生死鎖，并對(duì)占用資源比較少的進(jìn)程，列入犧牲品名單，將它終止（Kill）。通過左右橢圓形進(jìn)程節(jié)點(diǎn)顯示，可以發(fā)現(xiàn)已用日志最少的是左邊的進(jìn)程節(jié)點(diǎn)。
5. SPID 54 （***個(gè)會(huì)話）被回滾(Rollback)，SPID 55 （第二個(gè)會(huì)話）執(zhí)行成功。
到這里我們已算完成了，對(duì)死鎖的監(jiān)視和分析。
（注：是于其他死鎖的定義，死鎖模式，死鎖避免&預(yù)防,等等，不是本文重點(diǎn)，我沒有提出,網(wǎng)上太多這方面的文章）
【編輯推薦】
