SQL Server 的事務和鎖（二）-Range S-S鎖

在這篇隨筆中，我們的主要關注點在 Key-Range Lock。Key-Range Lock有 S-S、S-U、I-N、X-X幾種情況。我們一個一個來說，力求明白。遺憾的是，這裡可能會比較冗長，那麼死結分析只好依次順延了。

Range S-S鎖的擷取規則

MSDN 對 Range 鎖的規則有部分描述，但是言簡意賅，以下我們會將各種情況分解開來，理清MSDN中涉及的或者未涉及的規則，這些規則適用於SQL Server 2000/2005/2008/2008 R2。關於MSDN的描述，請參見：http://technet.microsoft.com/zh-cn/library/ms191272(en-us,SQL.110).aspx。

在描述規則之前需要聲明的是，我們的叢集索引就建立在 WHERE 字句之上，這很重要，否則是不會獲得 Range 鎖的，也就達不到 SERIALIZABLE 的要求了；另外，為了討論簡便，以下的 SQL 全部省略 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 的聲明。

我們假設有以下的表：

CREATE TABLE [dbo].[MyTable]([id] [int] IDENTITY(1,1) NOT NULL,[index_column] [int] NOT NULL,[data] [int] NOT NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED ([id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]CREATE UNIQUE CLUSTERED INDEX [IX_MyTable] ON [dbo].[MyTable] ([index_column] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

並假設我們有如下的資料：

INSERT INTO [MyTable] ([index_column],[data]) VALUES (1, 1)INSERT INTO [MyTable] ([index_column],[data]) VALUES (2, 2)INSERT INTO [MyTable] ([index_column],[data]) VALUES (3, 3)INSERT INTO [MyTable] ([index_column],[data]) VALUES (4, 4)INSERT INTO [MyTable] ([index_column],[data]) VALUES (5, 5)INSERT INTO [MyTable] ([index_column],[data]) VALUES (15, 6)INSERT INTO [MyTable] ([index_column],[data]) VALUES (16, 7)INSERT INTO [MyTable] ([index_column],[data]) VALUES (18, 8)INSERT INTO [MyTable] ([index_column],[data]) VALUES (25, 9)INSERT INTO [MyTable] ([index_column],[data]) VALUES (30, 10)

那麼這張表看起來應該是這樣的（我另外還將Index的Hash值以及row所在的資料頁Dump出來了，以便咱們做實驗）。

id	index_column	data	index hash	row page
1	1	1	(8194443284a0)	78
2	2	2	(61a06abd401c)	78
3	3	3	(98ec012aa510)	78
4	4	4	(a0c936a3c965)	78
5	5	5	(59855d342c69)	78
6	15	6	(f1de2a205d4a)	78
7	16	7	(f07ed88b2b23)	78
8	18	8	(e9069d930a93)	78
9	25	9	(b81181109ebc)	78
10	30	10	(8034b699f2c9)	78

對於WHERE子句中的條件命中現有記錄的情況

規則一：如果 WHERE 子句使用的是“相等”條件，例如“WHERE [index_column]=6”，並且該索引是唯一索引，則該索引不會獲得Key-Range S-S鎖，僅僅是Key上獲得普通S鎖；

假設我們執行

SELECT [data] FROM [MyTable] WHERE [index_column]=1

那麼我們使用 sp_lock 得到鎖的情況：

可以發現第一個索引上獲得了S鎖，但並不是 Range S-S 鎖。

規則二：如果 WHERE 子句使用的是“範圍”條件，例如“>、<、BETWEEN、IN”等。不論該索引是否唯一，WHERE子句規定都會成為 Range S-S 鎖作用的範圍，除此之外，在索引定序之下，這個作用範圍的“下一個”索引項目也會獲得Range S-S鎖。

我們必須首先解釋一下“下一個”是怎麼一回事，“下一個”索引項目有兩種情況：

第一：如果在索引定序下，作用範圍之外按照資料排布的方向能夠找到一個存在的，或者是“殘存的”索引項目（已經提交刪除，資料庫中再也看不到了，但是還沒有從B樹資料頁中刪除），那麼這個索引項目就是“下一個”索引項目；

第二：如果在索引定序下，作用範圍之外按照資料排布的方向找不到任何殘存的索引項目，那麼無限遠（Resource Hash為0xffffffff）的索引項目就是“下一個”索引項目。

我們結合規則二進行說明，例如我們執行

SELECT [data] FROM [MyTable] WHERE [index_column]>=1 AND [index_column]<=4

那麼 index_column 中的值為 1、2、3、4的索引會獲得 Range S-S 鎖，除此以外，4之後的下一個索引值，也就是5對應的索引會獲得 Range S-S鎖。這和我們的實驗結果剛好一致。

 

我們再來看著一個，例如我們執行：

SELECT [data] FROM [MyTable] WHERE [index_column]>=20 AND [index_column]<=40

那麼 index_column 為 25、30的索引會獲得 Range S-S 鎖，除此以外，30之後的下一個索引值，也就是“無限遠”會獲得 Range S-S 鎖，請看實際Dump的鎖的使用方式：

 

我們最後練一個稍稍複雜點兒情況：

SELECT [data] FROM [MyTable]

WHERE ([index_column]>=2 AND [index_column]<=4) OR ([index_column]>=10 AND [index_column]<=16) OR ([index_column]>=30 AND [index_column]<=40)

這裡想說明的問題是，我們的“範圍”是指一個個的閉合的範圍，要一個個套用規則進行分析，我們現在有3塊兒閉合的範圍，分別是 [2,4]、[10,16]、[30,40]。我們一個個的來，對於[2,4]，在這個範圍內2，3，4，5獲得 Range S-S鎖；

對於[10,16]範圍，15，16，18獲得 Range S-S鎖；對於[30,40]範圍，30，無限遠獲得 Range S-S鎖，一共9個。

 

規則一補充：如果 WHERE 子句使用的是“相等”條件，但是該索引不是唯一索引，那麼除了WHERE命中的索引獲得 Range S-S鎖之外，“下一個”索引也會獲得 Range S-S鎖。

我今天仔細的做了關於這個規則的驗證。另外查閱了 SQL Server 2000 - 2008 Internals 的圖書中關於這個問題的記載。在不是唯一索引的情況下，沒有以上這種固定的選擇規則。以上規則只有在一些特定情況下才出現。而其他規則是沒有問題的。

對於WHERE子句中的條件不能命中任何記錄的情況

規則三：如果 WHERE 子句使用的是“相等”條件，不論索引是否為唯一索引，若不能夠命中任何記錄，除該 WHERE 子句規定的那個不存在的記錄作為 Range S-S的一部分之外，該記錄的“下一個”索引值也將會獲得 Range S-S 鎖。

例如，我們執行

SELECT [data] FROM [MyTable] WHERE [index_column]=6

那麼下一條索引記錄為15所對應的索引，因此這個索引將會獲得 Range S-S 鎖。

 

又例如，我們執行

SELECT [data] FROM [MyTable] WHERE [index_column]=31

那麼下一索引記錄應該是“無限遠”對應的索引，則這個索引將會獲得 Range S-S 鎖。

 

規則四：如果WHERE子句中使用“範圍”條件，不論索引是否為唯一索引，若不能夠命中任何記錄，除該 WHERE 子句規定的那個不存在的範圍作為 Range S-S的一部分外，該範圍的“下一個”索引值也將會獲得 Range S-S鎖。

例如，我們執行

SELECT [data] FROM [MyTable] WHERE [index_column]>=6 AND [index_column]<=10

我實在是寫不動了，請各位開動腦筋吧，這裡直接給結果：

 

再來一個例子吧，我們執行

SELECT [data] FROM [MyTable] WHERE [index_column]>30 AND [index_column]<40

結果是：

 

好了，這一篇終於搞定了。下一篇我們到了 Range S-U 以及 Range I-N 這下會死結了，有好戲看了。