SQL Server中如何減少死鎖

SQL Server中如何減少死鎖，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

　　減少SQLServer死鎖的方法有哪些

　　如事務1的線程T1具有表A上的排它鎖，事務2的線程T2具有表B上的排它鎖，并且之后需要表A上的鎖。事務2無法獲得這一鎖，因為事務1已擁有它。事務2被阻塞，等待事務1。然后，事務1需要表B的鎖，但無法獲得鎖，因為事務2將它鎖定了。事務在提交或回滾之前不能釋放持有的鎖。因為事務需要對方控制的鎖才能繼續操作，所以它們不能提交或回滾，這樣數據庫就會發生死鎖了。

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

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

　　createproceduresp_who_lock

　　as

　　begin

　　declare@spidint,@blint,

　　@intTransactionCountOnEntryint,

　　@intRowcountint,

　　@intCountPropertiesint,

　　@intCounterint

　　createtable#tmp_lock_who(idintidentity(1,1),spidsmallint,blsmallint)

　　IF@@ERROR<>0RETURN@@ERROR

　　insertinto#tmp_lock_who(spid,bl)select0,blocked

　　from(select*fromsysprocesseswhereblocked>0)a

　　wherenotexists(select*from(select*fromsysprocesseswhereblocked>0)b

　　wherea.blocked=spid)

　　unionselectspid,blockedfromsysprocesseswhereblocked>0

　　IF@@ERROR<>0RETURN@@ERROR

　　--找到臨時表的記錄數

　　select@intCountProperties=Count(*),@intCounter=1

　　from#tmp_lock_who

　　IF@@ERROR<>0RETURN@@ERROR

　　if@intCountProperties=0

　　select’現在沒有阻塞和死鎖信息’asmessage

　　--循環開始

　　while@intCounter<=@intCountProperties 　　begin 　　--取第一條記錄 　　select@spid=spid,@bl=bl 　　from#tmp_lock_whowhereid=@intCounter 　　begin 　　if@spid=0 　　select’引起數據庫死鎖的是:’+CAST(@blASVARCHAR(10))+’進程號,其執行的SQL語法如下’ 　　else 　　select’進程號SPID：’+CAST(@spidASVARCHAR(10))+’被’+’進程號SPID：’+CAST(@blASVARCHAR(10))+’阻塞,其當前進程執行的SQL語法如下’ 　　DBCCINPUTBUFFER(@bl) 　　end 　　--循環指針下移 　　set@intCounter=@intCounter+1 　　end 　　droptable#tmp_lock_who 　　return0 　　end 　　減少SQLServer死鎖的方法有哪些 　　我們只需要通過在查詢分析器里面執行sp_who_lock，就可以具體捕捉到執行的堵塞進程，這時我們就可以對對應的SQL語句或者存儲過程進行性能上面的改進及設計。[Page] 　　所以我們在數據庫設計的時候，雖然不能完全避免死鎖，但可以使死鎖的數量盡量減少。增加事務的吞吐量并減少系統開銷，因為只有很少的事務，所以就得遵循下面的原則： 　　按同一順序訪問對象 　　假如所有并發事務按同一順序訪問對象，則發生死鎖的可能性會降低。在寫SQL語句或存儲過程的時候，就需要按照順序在兩個并發事務中先獲得表A上的鎖，然后獲得表B上的鎖，當第一個事務完成之前，另一個事務被阻塞在表A上。第一個事務提交或回滾后，第二個事務繼續進行，而不能在語句里面寫先獲得表B上的鎖，然后再獲得表A的鎖。 　　避免事務中的用戶交互 　　避免編寫包含用戶交互的事務，因為運行沒有用戶交互的批處理的速度要遠遠快于用戶手動響應查詢的速度，例如答復應用程序請求參數的提示。例如，假如事務正在等待用戶輸入，而用戶就去做別的事了，則用戶將此事務掛起使之不能完成。這樣將降低系統的吞吐量，因為事務持有的任何鎖只有在事務提交或回滾時才會釋放。即使不出現死鎖的情況，訪問同一資源的其它事務也會被阻塞，等待該事務完成。 　　保持事務簡短并在一個批處理中 　　在同一數據庫中并發執行多個需要長時間運行的事務時通常發生死鎖。事務運行時間越長，其持有排它鎖或更新鎖的時間也就越長，從而堵塞了其它活動并可能導致死鎖。保持事務在一個批處理中，可以最小化事務的網絡通信往返量，減少完成事務可能的延遲并釋放鎖。 　　使用低隔離級別 　　確定事務是否能在更低的隔離級別上運行。執行提交讀允許事務讀取另一個事務已讀取(未修改)的數據，而不必等待第一個事務完成。使用較低的隔離級別(例如提交讀)而不使用較高的隔離級別(例如可串行讀)可以縮短持有共享鎖的時間，從而降低了鎖定爭奪。 　　使用綁定連接 　　使用綁定連接使同一應用程序所打開的兩個或多個連接可以相互合作。次級連接所獲得的任何鎖可以象由主連接獲得的鎖那樣持有，反之亦然，因此不會相互阻塞。 　　

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

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

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

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

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

5)對一些數據不需要及時讀取更新值的表在寫SQL的時候在表后臺加上(nolock)，如：Select*fromtableA(nolock)。

看完上述內容，你們掌握SQL Server中如何減少死鎖的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！