如何減少SQL Server數據庫中死鎖發生

這篇文章主要針對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 @@ERROR
 
 insert into #tmp_lock_who(spid,bl) select  0 ,blocked
   from (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>0

 IF @@ERROR<>0 RETURN @@ERROR 
  
-- 找到臨時表的記錄數
 select  @intCountProperties = Count(*),@intCounter = 1
 from #tmp_lock_who
 
 IF @@ERROR<>0 RETURN @@ERROR 
 
 if @intCountProperties=0
  select '現在沒有阻塞和死鎖信息' as message

-- 循環開始
while @intCounter <= @intCountProperties
begin
-- 取第一條記錄
  select  @spid = spid,@bl = bl
  from #tmp_lock_who where id = @intCounter 
 begin
  if @spid =0 
    select '引起數據庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '
進程號,其執行的SQL語法如下'
 else
    select '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '

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

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

-- 循環指針下移
 set @intCounter = @intCounter + 1
end


drop table #tmp_lock_who

return 0
end

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

關鍵詞：

• 1
• 2
• 3
• 下一頁