減少SQL Server資料庫死結的技巧

    如果兩個使用者進程分別鎖定了不同的資源，接著又試圖鎖定對方所鎖定資源，就會產生死結。此時，SQL Server將自動地選擇並中止其中一個進程以解除死結，使得另外一個進程能夠繼續處理。系統將回退被中止的事務，並向被回退事務的使用者發送錯誤資訊。

    大多數設計良好的應用都會在接收到這個錯誤資訊之後重新提交該事務，此時提交成功的可能性是很大的。但是，如果伺服器上經常出現這種情況，就會顯著地降低伺服器效能。為避免死結，設計應用應當遵循一定的原則，包括：

▲ 讓應用每次都以相同的次序訪問伺服器資源。

▲ 在事務期間禁止任何使用者輸入。應當在事務開始之前收集使用者輸入。

▲ 盡量保持事務的短小和簡單。

▲ 如合適的話，為運行事務的使用者串連指定儘可能低的隔離等級。[適用於6.5，7.0，2000]

    此外，對於SQL Server的死結問題，下面是幾則實踐中很有用的小技巧。

■ 使用SQL Server Profiler的Create Trace Wizard運行“Identify The Cause of a
Deadlock”跟蹤來輔助識別死結問題，它將提供協助尋找資料庫產生死結原因的未經處理資料。[適用於7.0，2000]

■
如果無法消除應用中的所有死結，請確保提供了這樣一種程式邏輯：它能夠在死結出現並中止使用者事務之後，以隨機的時間間隔自動重新提交事務。這裡等待時間的
隨機性非常重要，這是因為另一個競爭的事務也可能在等待，我們不應該讓兩個競爭的事務等待同樣的時間，然後再在同一時間執行它們，這樣的話將導致新的死
鎖。[適用於6.5，7.0，2000]

■ 儘可能地簡化所有T-SQL事務。此舉將減少各種類型的鎖的數量，有助於提高SQL Server應用的整體效能。如果可能的話，應將較複雜的事務分割成多個較簡單的事務。[適用於6.5，7.0，2000]

■ 所有條件邏輯、變數賦值以及其他相關的預備設定作業應當在事務之外完成，而不應該放到事務之內。永遠不要為了接受使用者輸入而暫停某個事務，使用者輸入應當總是在事務之外完成。[適用於6.5，7.0，2000]

■ 在預存程序內封裝所有事務，包括BEGIN TRANSACTION和COMMIT
TRANSACTION語句。此舉從兩個方面協助減少阻塞的鎖。首先，它限制了事務運行時客戶程式和SQL
Server之間的通訊，從而使得兩者之間的任何訊息只能出現於非事務已耗用時間（減少了事務啟動並執行時間）。其次，由於預存程序強制它所啟動的事務或者完
成、或者中止，從而防止了使用者留下未完成的事務（留下未撤銷的鎖）。[適用於6.5，7.0，2000]

■
如果客戶程式需要先用一定的時間檢查資料，然後可能更新資料，也可能不更新資料，那麼最好不要在整個記錄檢查期間都鎖定記錄。假設大部分時間都是檢查資料
而不是更新資料，那麼處理這種特殊情況的一種方法就是：先選擇出記錄（不加UPDATE子句。UPDATE子句將在記錄上加上共用鎖定），然後把它發送給客
戶。

    如果使用者只查看記錄但從來不更新它，程式可以什麼也不做；反過來，如果使用者決定更新某個記錄，那麼他可以通過一個WHERE子句檢查當前的資料是否和以前提取的資料相同，然後執行UPDATE。

    類似地，我們還可以檢查記錄中的時間識別欄位（如果它存在的話）。如果資料相同，則執行UPDATE操作；如果記錄已經改變，則應用應該提示使用者以便使用者
決定如何處理。雖然這種方法需要編寫更多的代碼，但它能夠減少加鎖時間和次數，提高應用的整體效能。[適用於6.5，7.0，2000]

■ 儘可能地為使用者串連指定具有最少限制的交易隔離等級，而不是總是使用預設的READ COMMITTED。為了避免由此產生任何其他問題，應當參考不同隔離等級將產生的效果，仔細地分析事務的特性。[適用於6.5，7.0，2000]

■
使用遊標會降低並發性。為避免這一點，如果可以使用唯讀遊標則應該使用READ_ONLY遊標選項，否則如果需要進行更新，嘗試使用
OPTIMISTIC遊標選項以減少加鎖。設法避免使用SCROLL_LOCKS遊標選項，該選項會增加由於記錄鎖定引起的問題。[適用於6.5，
7.0，2000]

■
如果使用者抱怨說他們不得不等待系統完成事務，則應當檢查伺服器上的資源鎖定是否是導致該問題的原因。進行此類檢查時可以使用SQL Server
Locks Object: Average Wait Time (ms)，用該計數器來度量各種鎖的平均等待時間。

    如果可以確定一種或幾種類型的鎖導致了事務延遲，就可以進一步探究是否可以確定具體是哪個事務產生了這種鎖。Profiler是進行這類具體分析的最好工具。[適用於7.0，2000]

■ 使用sp_who和sp_who2（SQL Server Books Online沒有關於sp_who2的說明，但sp_who2提供了比sp_who更詳細的資訊）來確定可能是哪些使用者阻塞了其他使用者。[適用於6.5，7.0，2000]

■
試試下面的一個或多個有助於避免阻塞鎖的建議：1）對於頻繁使用的表使用集簇化的索引；2）設法避免一次性影響大量記錄的T-SQL語句，特別是
INSERT和UPDATE語句；3）設法讓UPDATE和DELETE語句使用索引；4）使用嵌套事務時，避免提交和回退衝突。[適用於6.5，
7.0，2000]

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
原文網址如下:原文網址