死鎖是指在某組資源中兩個或兩個以上的線程在執行過程中在爭奪某一資源時而造成互相等待的現象若無外力的作用下它們都將無法推進下去死時就可能會產生死鎖這些永遠在互相等待的進程稱為死鎖線程簡單的說進程A等待進程B釋放他的資源B又等待A釋放他的資源這樣互相等待就形成死鎖
如在數據庫中如果需要對一條數據進行修改首先數據庫管理系統會在上面加鎖以保證在同一時間只有一個事務能進行修改操作如事務的線程 T具有表A上的排它鎖事務的線程T 具有表B上的排它鎖並且之後需要表A上的鎖事務無法獲得這一鎖因為事務已擁有它事務被阻塞等待事務然後事務需要表B的鎖但無法獲得鎖因為事務將它鎖定了事務在提交或回滾之前不能釋放持有的鎖因為事務需要對方控制的鎖才能繼續操作所以它們不能提交或回滾這樣數據庫就會發生死鎖了
如在編寫存儲過程的時候由於有些存儲過程事務性的操作比較頻繁如果先鎖住表A再鎖住表B那麼在所有的存儲過程中都要按照這個順序來鎖定它們如果無意中某個存儲過程中先鎖定表B再鎖定表A這可能就會導致一個死鎖而且死鎖一般是不太容易被發現的
如果服務器上經常出現這種死鎖情況就會降低服務器的性能所以應用程序在使用的時候我們就需要對其進行跟蹤使用sp_who和sp_who來確定可能是哪些用戶阻塞了其他用戶我們還可以用下面的存儲過程來跟蹤具體的死鎖執行的影響
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()spid smallintbl smallint)IF @@ERROR<> RETURN
@@ERRORinsert into
#tmp_lock_who(spidbl) select
blockedfrom (select * from sysprocesses where
blocked> )
a where not exists(select * from (select * from sysprocesses where blocked> )
b where ablocked=spid)union select spidblocked from sysprocesses where
blocked>IF
@@ERROR<> RETURN @@ERROR 找到臨時表的記錄數select
@intCountProperties = Count(*)@intCounter = from #tmp_lock_whoIF
@@ERROR<> RETURN @@ERROR if @intCountProperties=select
現在沒有阻塞和死鎖信息
as message 循環開始while @intCounter <= @intCountPropertiesbegin 取第一條記錄select
@spid = spid@bl = blfrom #tmp_lock_who where id = @intCounter beginif @spid = select
引起數據庫死鎖的是: + CAST(@bl AS VARCHAR()) + 進程號
其執行的SQL語法如下elseselect
進程號SPID+ CAST(@spid AS VARCHAR())+ 被 +
進程號SPID+ CAST(@bl AS VARCHAR()) +阻塞
當前進程執行的SQL語法如下DBCC INPUTBUFFER (@bl )end
循環指針下移set @intCounter = @intCounter + enddrop table #tmp_lock_who
return
我們只需要通過在查詢分析器裡面執行sp_who_lock就可以具體捕捉到執行的堵塞進程這時我們就可以對對應的SQL語句或者存儲過程進行性能上面的改進及設計
所以我們在數據庫設計的時候雖然不能完全避免死鎖但可以使死鎖的數量盡量減少增加事務的吞吐量並減少系統開銷因為只有很少的事務所以就得遵循下面的原則
按同一順序訪問對象
如果所有並發事務按同一順序訪問對象則發生死鎖的可能性會降低在寫SQL語句或存儲過程的時候就需要按照順序在兩個並發事務中先獲得表A上的鎖然後獲得表B上的鎖當第一個事務完成之前另一個事務被阻塞在表A上第一個事務提交或回滾後第二個事務繼續進行而不能在語句裡面寫先獲得表B上的鎖然後再獲得表A的鎖
避免事務中的用戶交互
避免編寫包含用戶交互的事務因為運行沒有用戶交互的批處理的速度要遠遠快於用戶手動響應查詢的速度例如答復應用程序請求參數的提示例如如果事務正在等待用戶輸入而用戶就去做別的事了則用戶將此事務掛起使之不能完成這樣將降低系統的吞吐量因為事務持有的任何鎖只有在事務提交或回滾時才會釋放即使不出現死鎖的情況訪問同一資源的其它事務也會被阻塞等待該事務完成
保持事務簡短並在一個批處理中
在同一數據庫中並發執行多個需要長時間運行的事務時通常發生死鎖事務運行時間越長其持有排它鎖或更新鎖的時間也就越長從而堵塞了其它活動並可能導致死鎖保持事務在一個批處理中可以最小化事務的網絡通信往返量減少完成事務可能的延遲並釋放鎖
使用低隔離級別
確定事務是否能在更低的隔離級別上運行執行提交讀允許事務讀取另一個事務已讀取(未修改)的數據而不必等待第一個事務完成使用較低的隔離級別(例如提交讀)而不使用較高的隔離級別(例如可串行讀)可以縮短持有共享鎖的時間從而降低了鎖定爭奪
使用綁定連接
使用綁定連接使同一應用程序所打開的兩個或多個連接可以相互合作次級連接所獲得的任何鎖可以象由主連接獲得的鎖那樣持有反之亦然因此不會相互阻塞
下面有一些對死鎖發生的一些建議
)對於頻繁使用的表使用集簇化的索引;
)設法避免一次性影響大量記錄的TSQL語句特別是INSERT和UPDATE語句;
)設法讓UPDATE和DELETE語句使用索引;
)使用嵌套事務時避免提交和回退沖突;
)對一些數據不需要及時讀取更新值的表在寫SQL的時候在表後台加上(nolock)如Select * from tableA(nolock)
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22240.html
