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 这下会死锁了，有好戏看了。