在今天的文章里，我想談下SQL Server里鎖升級（Lock Escalations）。鎖升級是SQL Server使用的優化技術，用來控制在SQL Server鎖管理里把持鎖的數量。我們首先用SQL Server里所謂的鎖層級（Lock Hierarchy）開始，因為那是在像SQL Server的關系數據庫里，為什么有鎖升級概念存在的原因。

下圖展示了SQL Server使用的鎖層級：

從圖里可以看到，鎖層級開始于數據庫層級，向下至行層級。在數據庫本身層級，你一直有一個共享鎖（Shared Lock (S)）。當你的查詢連接到一個數據庫（例如USE MyDatabase），共享鎖會阻止數據庫刪除，或者在那個數據庫上還原備份。當你進行一個操作時，在數據庫層級下，在表上，在頁上，在記錄上都會有鎖。

當你執行一個SELECT語句，在表和頁上會有一個意向共享鎖（Intent Shared Lock (IS)），在記錄本身上有共享鎖（Shared Lock (S)）。當你進行數據修改語句（INSERT，UPDATE，DELETE），在表和頁上會有一個意向排它或更新鎖（Intent Exclusive or Update Lock (IX or IU)），在改變的記錄上有排它或更新鎖（Exclusive or Update Lock (X or U)）。當多個線程嘗試在鎖層級里并發獲取鎖時，SQL Server會一直獲取從頭到腳的鎖來阻止所謂的競爭危害。當你對表進行20000條記錄的刪除操作時，現在想象下這個鎖層級會是什么樣的？我們來假定記錄是400 bytes長，這就意味這在8kb的頁里剛好有20條記錄：

在數據庫上你有1個共享鎖（S），在表上有1個意向排它鎖（IX），在頁上有1000個意向排它鎖（IX）（20000條記錄散布在1000個頁上），最后在記錄本身你有20000個排它鎖（X）。對于DELETE操作總計你獲取了21002個鎖。在SQL Server里每個鎖需要96 bytes的內存，因此對這個簡單的查詢需要1.9MB的鎖。當你并行執行多個查詢時，這個不會無限擴展。因此，SQL Server現在用所謂的鎖升級（Lock Escalation）實現。

在你的鎖層級里一旦有超過5000個鎖，SQL Server會升級這么多的精細粒度（fine-granularity）的鎖為簡單的粗粒度（coarse-granularity）的鎖。默認情況下，SQL Server“總是”升級到表層級。這意味著你的鎖層級從剛才例子的樣子，在鎖升級成功執行后，變成如下的樣子：

如你所見，在表本身上你只有一個大鎖。在DELETE操作的情況下，在表層級你有一個排它鎖（X）。這會以負面傷及你數據庫的并發。在表層級把持一個排它鎖（X）意味者沒有其他回話可以訪問那個表——每個其它查詢會阻塞。當你在可重讀隔離級別（Repeatable Read Isolation Level）運行你的SELECT語句，你也在把持你的共享鎖（S）直到事務結束，這也就是說只要你讀超過5000條記錄就會發生鎖升級。這里的結果是一個共享鎖（S）在表本身！你的表只是暫時只讀，因為在表上每個其它數據修改都會阻塞！

這里還有個誤解——SQL Server會鎖升級從行層級到頁層級，最后到表層級。錯了！在SQL Server里沒有這樣的代碼路徑存在！默認情況下，SQL Server總是會直接升級到表層級。到頁層級的升級策略不存在。如果你的表被分區了（只針對企業版本的SQL Server），那樣的話，你可以配置升級到分區層級。但這里你必須非常仔細測試你的數據訪問模式，因為鎖升級到分區層級會引起死鎖。因此這個選項默認是沒啟用的。

自SQL Server 2008開始，你可以控制SQL Server如何進行鎖升級——通過ALTER TABLE語句和LOCK_ESCALTATION屬性。有3個可用選項：

• TABLE
• AUTO
• DISABLE

1 -- Controllling Lock Escalation2 ALTER TABLE Person.Person3 SET4 (5     LOCK_ESCALATION = AUTO -- or TABLE or DISABLE6 )7 GO

默認選項是TABLE，意味著SQL Server總是執行鎖升級到表層級——即使這個表已被分區。如果你的表已被分區，你想設置分區層級的鎖升級（因為你已經測試了你的數據訪問模式，用它你不會引起死鎖），那么你可以修改選項為AUTO。AUTO意味著你的鎖升級會執行到分區層級，如果表被分區的話，否則就到表層級。使用DISABLE選項你可以完全禁用那個表的鎖升級。但是禁用鎖升級并不是最好的選項，因為SQL Server的鎖管理器會消耗大量的內存，如果你對你的查詢和索引設計不深思熟慮的話。

在SQL Server里鎖升級基本是個噩夢。你如何才能從表里刪除5000行記錄而不產生鎖升級？你可以臨時禁用鎖升級，但這里你要非常仔細。另外一個方法（我推薦的）是讓你的DELETE/UPDATE語句在一個循環里，作為不同，獨立的事務：DELETE/UPDATE少于5000行記錄，這樣的話你可以阻止鎖升級。這樣做的好處，你龐大的事務會分解為多個小事務，但也會讓你的事務日志更多，帶來自動增長問題。

感謝關注！