共用方式為

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

  • 發行項

摘要

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

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

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

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

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

如需如何識別前端封鎖程式和前端封鎖程式所持有的鎖定資源，以及封鎖其他伺服器進程標識碼的詳細資訊，請參閱 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 取得數千個索引鍵鎖定（在叢集索引和一個非叢集索引上）。 這可能會導致這類查詢超過鎖定擴大閾值。 如果您發現呈報的鎖定是共用數據表鎖定，但通常不會在預設的「讀取認可」隔離等級中看到這些鎖定，這特別重要。 如果書籤查閱 WITH PREFETCH 子句造成擴大，請考慮將數據行加入至出現在索引搜尋中的非叢集索引，或查詢計劃中書簽查閱邏輯運算元下方的索引掃描邏輯運算元。 您可以建立涵蓋索引 （索引，其中包含查詢中使用的數據表中的所有數據行），或至少涵蓋聯結準則或 WHERE 子句中用於聯結準則之數據行的索引，如果不切實際地將所有專案包含在「選取數據行」清單中。

    巢狀循環聯結也可能使用 PREFETCH，這會導致相同的鎖定行為。

  • 如果不同的SPID目前持有不相容的數據表鎖定，則無法發生鎖定擴大。 鎖定擴大一律會呈報至數據表鎖定，且永遠不會升級為頁面鎖定。 此外，如果鎖定擴大嘗試失敗，因為另一個SPID保存不相容的TAB鎖定，則嘗試擴大的查詢不會在等候TAB鎖定時封鎖。 相反地，其會繼續在其更細微的原始層級 (資料列、索引鍵或分頁) 取得鎖定，並定期進行其他擴大嘗試。 因此，其中一種防止在特定資料表上鎖定擴大的方法便是取得並保留與擴大鎖定類型不相容的不同連線鎖定。 資料表層級的 IX (意圖獨佔) 鎖定不會鎖定任何資料列或分頁，但其仍然與擴大的 S (共用) 或 X (獨佔) TAB 鎖定不相容。 例如，假設您必須執行批次作業，以修改 mytable 資料表中的許多數據列，而且因為鎖定擴大而造成封鎖。 如果此作業一律在不到一小時內完成，您可以建立包含下列程式代碼的 Transact-SQL 作業，並將新作業排程在批次作業開始時間前幾分鐘開始：

    BEGIN TRAN;
SELECT * FROM mytable WITH (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。 在這種情況下，即使查詢包含適當的數據行，查詢也無法 SEEK 現有的索引，因為必須先擷取所有數據行值並傳遞至函式。 這會導致掃描整個數據表或索引，並導致取得大量的鎖定。 在這種情況下，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% 或 24% 的 40%。 如果鎖定記憶體超過 60% 的限制（如果停用鎖定擴大，這更有可能），則所有配置其他鎖定的嘗試都會失敗，併 1204 產生錯誤。

  • 達到 鎖定閾值 - 檢查記憶體閾值之後，會評估目前數據表或索引上取得的鎖定數目。 如果數字超過5,000，則會觸發鎖定擴大。

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

建議

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

另請參閱