如果兩個用戶進程分別鎖定了不同的資源
接著又試圖鎖定對方所鎖定的資源
就會產生死鎖
此時
SQL Server將自動地選擇並中止其中一個進程以解除死鎖
使得另外一個進程能夠繼續處理
系統將回退被中止的事務
並向被回退事務的用戶發送錯誤信息
大多數設計良好的應用都會在接收到這個錯誤信息之後重新提交該事務
此時提交成功的可能性是很大的
但是
如果服務器上經常出現這種情況
就會顯著地降低服務器性能
為避免死鎖
設計應用應當遵循一定的原則
包括
▲ 讓應用每次都以相同的次序訪問服務器資源
▲ 在事務期間禁止任何用戶輸入
應當在事務開始之前收集用戶輸入
▲ 盡量保持事務的短小和簡單
▲ 如合適的話
為運行事務的用戶連接指定盡可能低的隔離級別
[適用於
]
此外
對於SQL Server的死鎖問題
下面是幾則實踐中很有用的小技巧
■ 使用SQL Server Profiler的Create Trace Wizard運行
Identify The Cause of a Deadlock
跟蹤來輔助識別死鎖問題
它將提供幫助查找數據庫產生死鎖原因的原始數據
[適用於
]
■ 如果無法消除應用中的所有死鎖
請確保提供了這樣一種程序邏輯
它能夠在死鎖出現並中止用戶事務之後
以隨機的時間間隔自動重新提交事務
這裡等待時間的隨機性非常重要
這是因為另一個競爭的事務也可能在等待
我們不應該讓兩個競爭的事務等待同樣的時間
然後再在同一時間執行它們
這樣的話將導致新的死鎖
[適用於
]
■ 盡可能地簡化所有T
SQL事務
此舉將減少各種類型的鎖的數量
有助於提高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更詳細的信息）來確定可能是哪些用戶阻塞了其他用戶
[適用於
]
■ 試試下面的一個或多個有助於避免阻塞鎖的建議
）對於頻繁使用的表使用集簇化的索引
）設法避免一次性影響大量記錄的T
SQL語句
特別是INSERT和UPDATE語句
）設法讓UPDATE和DELETE語句使用索引
）使用嵌套事務時
避免提交和回退沖突
[適用於
]
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22222.html
