熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

減少SQL Server數據庫死鎖的技巧

2013-11-15 14:42:48  來源: SQL Server 

  如果兩個用戶進程分別鎖定了不同的資源接著又試圖鎖定對方所鎖定的資源就會產生死鎖此時SQL Server將自動地選擇並中止其中一個進程以解除死鎖使得另外一個進程能夠繼續處理系統將回退被中止的事務並向被回退事務的用戶發送錯誤信息
  
  大多數設計良好的應用都會在接收到這個錯誤信息之後重新提交該事務此時提交成功的可能性是很大的但是如果服務器上經常出現這種情況就會顯著地降低服務器性能為避免死鎖設計應用應當遵循一定的原則包括
  
  
  
  ▲ 讓應用每次都以相同的次序訪問服務器資源
  
  ▲ 在事務期間禁止任何用戶輸入應當在事務開始之前收集用戶輸入
  
  ▲ 盡量保持事務的短小和簡單
  
  ▲ 如合適的話為運行事務的用戶連接指定盡可能低的隔離級別[適用於]
  
  
  
  此外對於SQL Server的死鎖問題下面是幾則實踐中很有用的小技巧
  
  
  ■ 使用SQL Server Profiler的Create Trace Wizard運行Identify The Cause of a Deadlock跟蹤來輔助識別死鎖問題它將提供幫助查找數據庫產生死鎖原因的原始數據[適用於]
  
  ■ 如果無法消除應用中的所有死鎖請確保提供了這樣一種程序邏輯它能夠在死鎖出現並中止用戶事務之後以隨機的時間間隔自動重新提交事務這裡等待時間的隨機性非常重要這是因為另一個競爭的事務也可能在等待我們不應該讓兩個競爭的事務等待同樣的時間然後再在同一時間執行它們這樣的話將導致新的死鎖[適用於]
  
  ■ 盡可能地簡化所有TSQL事務此舉將減少各種類型的鎖的數量有助於提高SQL Server應用的整體性能如果可能的話應將較復雜的事務分割成多個較簡單的事務[適用於]
  
  ■ 所有條件邏輯變量賦值以及其他相關的預備設置操作應當在事務之外完成而不應該放到事務之內永遠不要為了接受用戶輸入而暫停某個事務用戶輸入應當總是在事務之外完成[適用於]
  
  ■ 在存儲過程內封裝所有事務包括BEGIN TRANSACTION和COMMIT TRANSACTION語句此舉從兩個方面幫助減少阻塞的鎖首先它限制了事務運行時客戶程序和SQL Server之間的通信從而使得兩者之間的任何消息只能出現於非事務運行時間(減少了事務運行的時間)其次由於存儲過程強制它所啟動的事務或者完成或者中止從而防止了用戶留下未完成的事務(留下未撤銷的鎖)[適用於]
  
  ■ 如果客戶程序需要先用一定的時間檢查數據然後可能更新數據也可能不更新數據那麼最好不要在整個記錄檢查期間都鎖定記錄假設大部分時間都是檢查數據而不是更新數據那麼處理這種特殊情況的一種方法就是先選擇出記錄(不加UPDATE子句UPDATE子句將在記錄上加上共享鎖)然後把它發送給客戶
  
  如果用戶只查看記錄但從來不更新它程序可以什麼也不做反過來如果用戶決定更新某個記錄那麼他可以通過一個WHERE子句檢查當前的數據是否和以前提取的數據相同然後執行UPDATE
  
  類似地我們還可以檢查記錄中的時間標識列(如果它存在的話)如果數據相同則執行UPDATE操作如果記錄已經改變則應用應該提示用戶以便用戶決定如何處理雖然這種方法需要編寫更多的代碼但它能夠減少加鎖時間和次數提高應用的整體性能[適用於]
  
  ■ 盡可能地為用戶連接指定具有最少限制的事務隔離級別而不是總是使用默認的READ COMMITTED為了避免由此產生任何其他問題應當參考不同隔離級別將產生的效果仔細地分析事務的特性[適用於]
  
  ■ 使用游標會降低並發性為避免這一點如果可以使用只讀的游標則應該使用READ_ONLY游標選項否則如果需要進行更新嘗試使用OPTIMISTIC游標選項以減少加鎖設法避免使用SCROLL_LOCKS游標選項該選項會增加由於記錄鎖定引起的問題[適用於]
  
  ■ 如果用戶抱怨說他們不得不等待系統完成事務則應當檢查服務器上的資源鎖定是否是導致該問題的原因進行此類檢查時可以使用SQL Server Locks Object: Average Wait Time (ms)用該計數器來度量各種鎖的平均等待時間
  
  如果可以確定一種或幾種類型的鎖導致了事務延遲就可以進一步探究是否可以確定具體是哪個事務產生了這種鎖Profiler是進行這類具體分析的最好工具[適用於]
  
  ■ 使用sp_who和sp_who(SQL Server Books Online沒有關於sp_who的說明但sp_who提供了比sp_who更詳細的信息)來確定可能是哪些用戶阻塞了其他用戶[適用於]
  
  ■ 試試下面的一個或多個有助於避免阻塞鎖的建議)對於頻繁使用的表使用集簇化的索引)設法避免一次性影響大量記錄的TSQL語句特別是INSERT和UPDATE語句)設法讓UPDATE和DELETE語句使用索引)使用嵌套事務時避免提交和回退沖突[適用於]

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22222.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.