可序列化隔離等級裡的鎖定擴大

標籤：

在今天的文章裡我會討論下可序列化（SERIALIZABLE）隔離等級裡會有的鎖定擴大（Lock Escalations），還有你如何避免。在上個月的7月14日，我已經介紹了SQL Server裡鎖定擴大（Lock Escalations）的基本概念還有為什麼需要它們。因此請你回到這個文章來理解下這個非常重要的概念。

可序列化（SERIALIZABLE）隔離等級

可序列化（SERIALIZABLE）隔離等級用來阻止所謂的幻影記錄（Phantom Records）。為了阻止它們，SQL Server使用關鍵範圍鎖定定（Key-Range Locking）技術。我們來看下面的SELECT語句： 

1 SELECT * FROM Person.Address2 WHERE StateProvinceID BETWEEN 10 AND 123 GO

這個語句請求StateProvinceID在10到12之間的所有記錄。如果你在可序列化（SERIALIZABLE）隔離等級運行這個語句，這些範圍內的IDs會被鎖定，保護它不會被資料修改：

• 你不能INSERT新的記錄到保護範圍
• 你不能從保護範圍DELETE現有記錄
• 你不能通過UPDATE語句移動現有資料到保護範圍

這個範圍之外的修改是允許的，因為SQL Server只鎖定了那個特定範圍。 

鎖定擴大（Lock Escalations）

關鍵範圍鎖定定（Key-Range Locking）技術最重要的是在你的查詢謂語上需要支援的非叢集索引。在我們的例子裡是StateProvinceID列。如果在它上面沒有支援的索引定義，在執行計畫裡查詢最佳化工具會選擇叢集索引掃描/表掃描（Clustered Index Scan/Table Scan）運算子。這意味著你必須掃描你的整個表（用殘留謂語（residual predicate））來找匹配的記錄。

 

當你在可序列化（SERIALIZABLE）隔離等級裡運行你的SELECT語句，在你掃描期間獲得超過5000個鎖時，你會觸發鎖定擴大（Lock Escalations）。下面代碼示範了當沒有支援的非叢集索引時，你如何觸發鎖定擴大（Lock Escalations）。

 1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 2 GO 3  4 BEGIN TRANSACTION 5  6 -- The following statement causes a Lock Escalation, because there is no 7 -- supporting Non-Clustered index on the column "StateProvinceID" 8 SELECT * FROM Person.Address 9 WHERE StateProvinceID BETWEEN 10 AND 1210 11 -- There is only a S lock on the table itself!12 SELECT * FROM sys.dm_tran_locks13 WHERE request_session_id = @@SPID14 15 ROLLBACK16 GO

現在讓我們建立支援的非叢集索引。

1 -- Create a supporting Non-Clustered Index2 CREATE NONCLUSTERED INDEX idx_StateProvinceID ON Person.Address(StateProvinceID)3 GO

現在當你查看執行計畫時，你會看到查詢最佳化工具引用了這個新建立的索引並與書籤尋找（Bookmark Lookup）進行了組合。

當你在可序列化（SERIALIZABLE）隔離等級裡再次運行SELECT語句，你不會觸發鎖定擴大了，因為物理上你唯讀20條請求行。

小結

可序列化（SERIALIZABLE）隔離等級是最有限制的一個，它會阻止幻影記錄（Phantom Records）。SQL Server內部使用關鍵範圍鎖定定（Key-Range Locking）技術來保持請求範圍行的穩定。這裡你要記住最重要的是你在你搜尋謂語（search predicate）上要有支援的非叢集索引。不然的話你需要掃描你的整個表，如果你讀取超過5000行，你就會觸發鎖定擴大（Lock Escalations）。

感謝關注！

可序列化隔離等級裡的鎖定擴大