解決在 SQL Server 中鎖定擴大所造成的封鎖問題

發行項
03/20/2024

摘要

鎖定擴大是將許多細微鎖定轉換 (的程式，例如) 數據列或頁面鎖定轉換成數據表鎖定。 Microsoft SQL Server 會動態決定何時執行鎖定擴大。進行這項決策時，SQL Server 會考慮特定掃描時保留的鎖定數目、整個交易所持有的鎖定數目，以及用於整個系統鎖定的記憶體。一般而言，SQL Server的預設行為只會在改善效能或必須將過多的系統鎖定記憶體降低到更合理的層級時，才會發生鎖定擴大。不過，某些應用程式或查詢設計可能會在此動作不想要時觸發鎖定擴大，而呈報的數據表鎖定可能會封鎖其他使用者。本文討論如何判斷鎖定擴大是否造成封鎖，以及如何處理不想要的鎖定擴大。

原始產品版本： SQL S
原始 KB 編號： 323630

判斷鎖定擴大是否造成封鎖

鎖定擴大不會造成大部分的封鎖問題。若要判斷鎖定擴大發生在您遇到封鎖問題的時間或接近時間，請啟動包含事件的 lock_escalation 擴充事件會話。如果您沒有看到任何 lock_escalation 事件，您的伺服器上就不會發生鎖定擴大，而且本文中的資訊不適用於您的情況。

如果發生鎖定擴大，請確認呈報的數據表鎖定封鎖其他使用者。

如需如何識別標頭封鎖程式所持有的標頭封鎖程式和鎖定資源，以及封鎖其他伺服器進程標識碼 (SPID) 的詳細資訊，請參閱 INF：瞭解和解決 SQL Server 封鎖問題。

如果封鎖其他用戶的鎖定不是 TAB (數據表層級) 鎖定，其鎖定模式為 S (共用) 或 X (獨佔) ，則鎖定擴大不是問題所在。特別是，如果 TAB 鎖定是意圖鎖定 (例如 IS、IU 或 IX) 的鎖定模式，這並不是由鎖定擴大所造成。如果您的封鎖問題不是由鎖定擴大所造成，請參閱 INF：瞭解和解決 SQL Server 封鎖問題疑難解答步驟。

防止鎖定擴大

防止鎖定擴大的最簡單且最安全的方法是讓交易保持簡短，並減少昂貴查詢的鎖定使用量，以免超過鎖定擴大閾值。有數種方法可以達成此目標，包括下列策略：

將大型批次作業分成數個較小的作業。例如，您執行下列查詢，從稽核數據表中移除 100,000 個以上的舊記錄，然後判斷查詢造成鎖定擴大，導致其他使用者遭到封鎖：
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
藉由一次移除數百筆記錄，您可以大幅減少每個交易累積的鎖定數目。這會防止鎖定擴大。例如，您會執行下列查詢：
```
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;
```
讓查詢盡可能有效率，以減少查詢的鎖定使用量。大型掃描或許多書簽查閱可能會增加鎖定擴大的機會。此外，這些會增加死結的機會，並會對並行和效能造成負面影響。識別造成鎖定擴大的查詢之後，請尋找建立新索引或將數據行新增至現有索引的機會，以移除索引或數據表掃描，並將索引搜尋的效率最大化。檢閱執行計劃，並可能建立新的非叢集索引，以改善查詢效能。如需詳細資訊，請參閱 SQL Server 索引架構和設計指南。

這項優化的目標之一是讓索引搜尋傳回的數據列盡可能少，以將書籤查閱的成本降到最低， (最大化查詢) 索引的選取性。如果 SQL Server 估計書籤查閱邏輯運算符會傳回許多數據列，則可能會使用子PREFETCH句來進行書籤查閱。如果 SQL Server 用於PREFETCH書籤查閱，則必須將部分查詢的交易隔離等級增加為查詢的一部分「可重複讀取」。這表示，在「讀取認可」隔離等級的語句看起來 SELECT 可能會在叢集索引和一個非叢集索引) 上取得數千個密鑰鎖定 (。這可能會導致這類查詢超過鎖定擴大閾值。如果您發現呈報的鎖定是共用數據表鎖定，這特別重要，雖然這些通常不會出現在預設的「讀取認可」隔離等級。如果 Bookmark Lookup WITH PREFETCH 子句造成擴大，請考慮將數據行新增至出現在索引搜尋中的非叢集索引，或是查詢計劃中書籤查閱邏輯運算符下方的索引掃描邏輯運算符。您可以建立涵蓋索引 (包含查詢) 中所使用之數據表中所有數據行的索引;或者，如果「選取數據行」清單中包含所有數據行不切實際，則至少可以建立涵蓋聯結準則或 WHERE 子句中之數據行的索引。

巢狀循環聯結可能也會使用 PREFETCH，這會導致相同的鎖定行為。
如果不同的SPID目前保有不相容的數據表鎖定，則無法發生鎖定擴大。鎖定擴大一律會呈報至數據表鎖定，且永遠不會呈報到頁面鎖定。此外，如果因為另一個SPID保留不相容的TAB鎖定而導致鎖定擴大嘗試失敗，則嘗試擴大的查詢不會在等候TAB鎖定時封鎖。相反地，它會繼續在其原始、更細微的層級 (數據列、索引鍵或頁面) 取得鎖定，並定期進行額外的擴大嘗試。因此，防止特定數據表鎖定擴大的其中一個方法，是取得與呈報鎖定類型不相容的不同聯機並保留鎖定。數據表層級的 IX (意圖獨佔) 鎖定不會鎖定任何數據列或頁面，但仍然與呈報的 S (共用) 或 X (獨佔) TAB 鎖定不相容。例如，假設您必須執行批次作業，以修改 mytable 資料表中的許多數據列，並因鎖定擴大而造成封鎖。如果此作業一律在一小時內完成，您可能會建立包含下列程式代碼的 Transact-SQL 作業，並將新作業排程為在批次作業開始時間之前幾分鐘啟動：
```
BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
此查詢會取得並保留 mytable 上的 IX 鎖定一小時。這可防止在該時間內鎖定數據表的擴大。除非其他查詢使用 TABLOCK 提示強制表鎖，或系統管理員已使用 ALTER INDEX) 停用頁面或數據列鎖定，否則此批次不會修改任何數據或封鎖其他查詢 (。
消除缺少 SARGability 所造成的鎖定擴大，這是一個關係資料庫詞彙，用來描述查詢是否可以使用述詞和聯結數據行的索引。如需SARGability的詳細資訊，請參閱設計指南內查詢考慮。例如，看似不會要求許多數據列或單一數據列的相當簡單的查詢，最後可能仍會掃描整個數據表/索引。如果 WHERE 子句左側有函式或計算，可能會發生這種情況。這類缺少 SARGability 的範例包括隱含或明確的數據類型轉換、ISNULL () 系統函式、具有做為參數傳遞之數據行的使用者定義函式，或數據行上的計算，例如 WHERE CONVERT(INT, column1) = @a 或 WHERE Column1*Column2 = 5。在這種情況下，即使查詢包含適當的數據行，也無法搜尋現有的索引，因為所有數據行值都必須先擷取並傳遞至函式。這會導致掃描整個數據表或索引，並導致取得大量鎖定。在這種情況下，SQL Server 可以達到鎖定計數擴大閾值。解決方法是避免對 WHERE 子句中的數據行使用函式，以確保 SARGable 條件。

停用鎖定擴大

雖然可以在 SQL Server 中停用鎖定擴大，但我們不建議這麼做。請改用防止鎖定擴大一節中所述的預防策略。

數據表層級： 您可以在資料表層級停用鎖定擴大。請參閱 ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE)。若要判斷要以哪個數據表為目標，請檢查 T-SQL 查詢。如果無法這麼做，請使用擴充事件、啟用 lock_escalation 事件，並檢查 object_id 數據行。或者，使用 Lock：Escalation 事件，並使用 SQL Profiler 檢查 ObjectID2 數據行。
實例層級： 您可以為實例啟用其中一個追蹤旗標 1211 或 1224 或兩者，以停用鎖定擴大。不過，這些追蹤旗標會在 SQL Server 實例中全域停用所有鎖定擴大。鎖定擴大有助於 SQL Server，方法是將因取得和釋放數千個鎖定的額外負荷而變慢的查詢效率最大化。鎖定擴大也有助於將所需的記憶體降到最低，以追蹤鎖定。 SQL Server 可動態配置給鎖定結構的記憶體是有限的。因此，如果您停用鎖定擴大，而且鎖定記憶體變得夠大，則任何嘗試為任何查詢配置其他鎖定都可能會失敗，併產生下列錯誤專案：

錯誤：1204，嚴重性：19，狀態：1
SQL Server 目前無法取得LOCK資源。當作用中使用者較少，或要求系統管理員檢查 SQL Server 鎖定和記憶體設定時，請重新執行您的語句。

注意事項

發生 1204 錯誤時，它會停止處理目前的語句，並導致作用中交易復原。如果您重新啟動 SQL Server 服務，復原本身可能會封鎖使用者，或導致資料庫復原時間過長。

您可以使用 SQL Server 組態管理員，將這些追蹤旗標新增至 (-T1211 或 -T1224 ) 。您必須重新啟動 SQL Server 服務，新的啟動參數才會生效。如果您執行 DBCC TRACEON (1211, -1) 或 DBCC TRACEON (1224, -1) 查詢，追蹤旗標會立即生效。
不過，如果您未將 -T1211 或 -T1224 新增為啟動參數，則會在重新啟動 SQL Server 服務時失去命令的效果DBCC TRACEON。開啟追蹤旗標可防止任何未來的鎖定擴大，但不會反轉任何已在使用中交易中發生的鎖定擴大。

如果您使用 ROWLOCK 之類的鎖定提示，這隻會改變初始鎖定計劃。鎖定提示不會防止鎖定擴大。

鎖定擴大閾值

鎖定擴大可能會在下列其中一種情況下發生：

已達到記憶體閾值 - 達到 40% 鎖定記憶體的記憶體閾值。當鎖定記憶體超過緩衝池的 24% 時，可以觸發鎖定擴大。鎖定記憶體限制為可見緩衝池的 60%。鎖定擴大閾值設定為鎖定記憶體的 40%。這是緩衝池 60% 的 40%，或 24%。如果鎖定記憶體超過 60% 的限制 (如果停用鎖定擴大) ，則所有配置額外鎖定的嘗試都會失敗，而且 1204 會產生錯誤。
達到鎖定閾值 - 檢查記憶體閾值之後，會評估在目前數據表或索引上取得的鎖定數目。如果數目超過 5,000，則會觸發鎖定擴大。

若要瞭解已達到的閾值，請使用擴充事件、啟用 lock_escalation 事件，以及檢查 escalated_lock_count 和 escalation_cause 數據行。或者，使用 Lock：Escalation 事件並檢查 EventSubClass 值，其中 “0 - LOCK_THRESHOLD” 表示語句超過鎖定閾值，而 “1 - MEMORY_THRESHOLD” 表示語句超過記憶體閾值。此外，請檢查 IntegerData 和 IntegerData2 數據行。

建議

相較於在數據表或實例層級停用擴大，在防止鎖定擴大一節中討論的方法是更好的選項。此外，預防方法通常會產生比停用鎖定擴大更好的查詢效能。 Microsoft 建議您只啟用此追蹤旗標，以減輕鎖定擴大所造成的嚴重封鎖，而其他選項，例如本文中所討論的選項，正在進行調查。

共用方式為

解決在 SQL Server 中鎖定擴大所造成的封鎖問題

摘要

判斷鎖定擴大是否造成封鎖

防止鎖定擴大

停用鎖定擴大

鎖定擴大閾值

建議

另請參閱

意見反應

意見反應

其他資源