SQL Server 鎖升級機制演進：從直接升級表鎖到分區鎖與可控制選項

迷思：鎖升級是行鎖 → 頁鎖 → 表鎖

事實： SQL Server 2005 及之前版本，鎖升級直接從行鎖升級為表鎖，不經過頁鎖。

SQL Server 2005/2008 可用的追蹤旗標控制

• TF 1211： 完全停用鎖升級。但鎖記憶體限制為動態記憶體 60%，超過則報記憶體不足錯誤。
• TF 1224： 停用鎖升級，但當鎖記憶體超過 40% 時自動啟用升級。
• 若兩者同時啟用，僅 TF 1211 生效。

SQL Server 2008 新增：資料表層級 LOCK_ESCALATION 選項

ALTER TABLE <table_name> SET (LOCK_ESCALATION = <option>);

選項：

• TABLE（預設）：直接從行鎖升級為表鎖。
• AUTO：若資料表有分區，升級為分區鎖（HoBT 鎖），不再進一步升級為表鎖。
• DISABLE：停用鎖升級（但不禁止表鎖，如 SERIALIZABLE 隔離掃描仍需表鎖）。

為何 AUTO 非預設？

早期測試發現 AUTO（分區鎖升級）在某些工作負載下更容易導致死結。同樣，啟用 TF 1211/1224 或設定 AUTO 都需謹慎測試。

參考資源

• Books Online：ALTER TABLE ... SET (LOCK_ESCALATION ...)
• 部落格範例：〈SQL Server 2008: Partition-level lock escalation details and examples〉