如何減少SQL Server死結發生

　　死結是指在某組資源中，兩個或兩個以上的線程在執行過程中，在爭奪某一資源時而造成互相等待的現象，若無外力的作用下,它們都將無法推進下去，死時就可能會產生死結,這些永遠在互相等待的進程稱為死結線程。簡單的說，進程A等待進程B釋放他的資源，B又等待A釋放他的資源，這樣互相等待就形成死結。

　　如在資料庫中，如果需要對一條資料進行修改，首先資料庫管理系統會在上面加鎖，以保證在同一時間只有一個事務能進行修改操作。如事務1的線程 T1具有表A上的排它鎖，事務2的線程T2 具有表B上的排它鎖，並且之後需要表A上的鎖。事務2無法獲得這一鎖，因為事務1已擁有它。事務2被阻塞，等待事務1。然後，事務1需要表B的鎖，但無法獲得鎖，因為事務2將它鎖定了。事務在提交或復原之前不能釋放持有的鎖。因為事務需要對方控制的鎖才能繼續操作，所以它們不能提交或復原，這樣資料庫就會發生死結了。

　　如在編寫預存程序的時候，由於有些預存程序事務性的操作比較頻繁，如果先鎖住表A，再鎖住表B，那麼在所有的預存程序中都要按照這個順序來鎖定它們。如果無意中某個預存程序中先鎖定表B，再鎖定表A，這可能就會導致一個死結。而且死結一般是不太容易被發現的。

　　如果伺服器上經常出現這種死結情況，就會降低伺服器的效能，所以應用程式在使用的時候，我們就需要對其進行跟蹤，使用sp_who和sp_who2來確定可能是哪些使用者阻塞了其他使用者，我們還可以用下面的預存程序來跟蹤具體的死結執行的影響：

　　create procedure sp_who_lock

　　as

　　begin

　　declare @spid int,@bl int,@intTransactionCountOnEntry

　　int,@intRowcount

　　int,@intCountProperties

　　int,@intCounter

　　int create table

　　#tmp_lock_who

　　(id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 RETURN

　　@@ERRORinsert into

　　#tmp_lock_who(spid,bl) select

　　0 ,blockedfrom (select * from sysprocesses where

　　blocked>0 )

　　a where not exists(select * from (select * from sysprocesses where blocked>0 )

　　b where a.blocked=spid)union select spid,blocked from sysprocesses where

　　blocked>0IF

　　@@ERROR<>0 RETURN @@ERROR -- 找到暫存資料表的記錄數select

　　@intCountProperties = Count(*),@intCounter = 1from #tmp_lock_whoIF

　　@@ERROR<>0 RETURN @@ERROR if @intCountProperties=0select

　　'現在沒有阻塞和死結資訊'

　　as message-- 迴圈開始while @intCounter <= @intCountPropertiesbegin-- 取第一條記錄select

　　@spid = spid,@bl = blfrom #tmp_lock_who where id = @intCounter beginif @spid =0 select

　　'引起資料庫死結的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,

　　其執行的SQL文法如下'elseselect

　　'進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' +

　　'進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,

　　當前進程執行的SQL文法如下'DBCC INPUTBUFFER (@bl )end --

　　迴圈指標下移set @intCounter = @intCounter + 1enddrop table #tmp_lock_who

　　return 0

　　我們只需要通過在查詢分析器裡面執行sp_who_lock，就可以具體捕捉到執行的堵塞進程，這時我們就可以對對應的SQL語句或者預存程序進行效能上面的改進及設計。

　　所以我們在資料庫設計的時候，雖然不能完全避免死結，但可以使死結的數量盡量減少。增加事務的輸送量並減少系統開銷，因為只有很少的事務，所以就得遵循下面的原則：

　　按同一順序訪問對象

　　如果所有並發事務按同一順序訪問對象，則發生死結的可能性會降低。在寫SQL語句或預存程序的時候，就需要按照順序在兩個並發事務中先獲得表A上的鎖，然後獲得表B上的鎖，當第一個事務完成之前，另一個事務被阻塞在表A上。第一個事務提交或復原後，第二個事務繼續進行，而不能在語句裡面寫先獲得表B上的鎖，然後再獲得表A的鎖。

　　避免事務中的使用者互動

　　避免編寫包含使用者互動的事務，因為運行沒有使用者互動的批處理的速度要遠遠快於使用者手動響應查詢的速度，例如回覆應用程式請求參數的提示。例如，如果事務正在等待使用者輸入，而使用者就去做別的事了，則使用者將此事務掛起使之不能完成。這樣將降低系統的輸送量，因為事務持有的任何鎖只有在事務提交或復原時才會釋放。即使不出現死結的情況，訪問同一資源的其它事務也會被阻塞，等待該事務完成。

　　保持事務簡短並在一個批處理中

　　在同一資料庫中並發執行多個需要長時間啟動並執行事務時通常發生死結。事務已耗用時間越長，其持有排它鎖或更新鎖定的時間也就越長，從而堵塞了其它活動並可能導致死結。保持事務在一個批處理中，可以最小化事務的網路通訊往返量，減少完成事務可能的延遲並釋放鎖。

　　使用低隔離等級

　　確定事務是否能在更低的隔離等級上運行。執行提交讀允許事務讀取另一個事務已讀取(未修改)的資料，而不必等待第一個事務完成。使用較低的隔離等級(例如提交讀)而不使用較高的隔離等級(例如可串列讀)可以縮短持有共用鎖定的時間，從而降低了鎖定爭奪。

　　使用綁定串連

　　使用綁定串連使同一應用程式所開啟的兩個或多個串連可以相互合作。次級串連所獲得的任何鎖可以象由主串連獲得的鎖那樣持有，反之亦然，因此不會相互阻塞。

　　下面有一些對死結發生的一些建議：

　　1)對於頻繁使用的表使用集簇化的索引;

　　2)設法避免一次性影響大量記錄的T-SQL語句，特別是INSERT和UPDATE語句;

　　3)設法讓UPDATE和DELETE語句使用索引;

　　4)使用嵌套事務時，避免提交和回退衝突;

　　5)對一些資料不需要及時讀取更新值的表在寫SQL的時候在表後台加上(nolock)，如：Select * from tableA(nolock)