转SQL Server 的事务和锁

最近在项目中进行压力测试遇到了数据库的死锁问题，简言之，如下的代码在 SERIALIZABLE 隔离级别造成了死锁：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

SELECT @findCount=COUNT(id)FROM MyTable

WHERE [fk_related_id]=@Argument

 

IF (@findCount > 0)

BEGIN

    ROLLBACK TRANSACTION

    RETURN ERROR_CODE

END

 

INSERT INTO MyTable ([fk_related_id],…)

VALUES (@Argument,…)

 

COMMIT TRANSACTION

RETURN SUCCESS_CODE

 

在搞清楚这个问题的过程中做了不少的实验，与各位共享。这一篇是开篇，主要说明的是 SQL Server 的四种（其实还有别的）经典的事务隔离级别，以及在不同的隔离级别下锁的使用手段，以及所带来的不同的数据一致性。

SQL Server 中锁的种类（Schema操作就暂时不涉及了）

锁类型描述（Shared Lock）用于只读操作数据锁定（Update Lock）用于数据的更新，在数据真正的需要更新的时候会申请升级为X锁。X（Exclusive Lock）独占锁，用于数据的更改。Key-Range Lock（稍后讨论）仅仅在 Serializable 隔离级别保护数据，以避免任何有可能使得本事务第二次读取信息产生错误的数据插入操作

各个事务隔离级别下锁的使用

SQL Server 中有四种事务隔离级别，具体的大家去参建 MSDN。下面列出在不同的事务隔离级别下这些锁是如何使用的：

隔离级别读数据锁状态写数据锁状态锁持有时间Read Uncommitted不获得任何锁不获得任何锁 Read Committed数据获得S锁对于 INSERT、DELETE、UPDATE的执行，获得X锁；对于UPDATE的标记，获得U锁；读完即释放，并不持有至事务结束。Repeatable Read数据获得S锁对于 INSERT、DELETE、UPDATE的执行，获得X锁；对于UPDATE的标记，获得U锁；持有至事务结束Serializable数据获得S锁，同时获得Key-Range锁。对于 INSERT、DELETE、UPDATE的执行，获得X锁；对于UPDATE的标记，获得U锁，同时获得Key-Range锁。持有至事务结束

我们可以利用这些知识形象说明各个隔离级别下的数据一致性：

Read Uncommitted 级别

（1）脏读

（2）更新丢失

（3）不可重复读

（4）幻读

Read Committed 级别

（1）脏读

（2）更新丢失

（3）不可重复读

（4）幻读

Repeatable Read 级别

（1）脏读

（2）更新丢失

（3）不可重复读

（4）幻读

Serializable 级别

（1）脏读

（2）更新丢失

（3）不可重复读

（4）幻读

我们从上图可以比较直观的看到以下的结论

 脏读更新丢失不可重复读幻读Read Uncommitted可能可能可能可能Read Committed不可能可能可能可能Repeatable Read不可能不可能不可能可能Serializable不可能不可能不可能不可能

这一篇到此为止，下一篇详细介绍 Key-Range Lock 并分析开篇提到的死锁问题。

 

 

在这篇随笔中，我们的主要关注点在 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 的声明。

我们假设有以下的表：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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 CLUSTEREDINDEX [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]

并假设我们有如下的数据：

1

2

3

4

5

6

7

8

9

10

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出来了，以便咱们做实验）。

idindex_columndataindex hashrow page111(8194443284a0)78222(61a06abd401c)78333(98ec012aa510)78444(a0c936a3c965)78555(59855d342c69)786156(f1de2a205d4a)787167(f07ed88b2b23)788188(e9069d930a93)789259(b81181109ebc)78103010(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 这下会死锁了，有好戏看了。

 

在上一篇中忘记了一个细节。Range T-K 到底代表了什么？Range T-K Lock 代表了在 SERIALIZABLE 隔离级别中，为了保护范围内的数据不被并发的事务影响而使用的一类锁模式（避免幻读）。它由两个部分构成：

第一个部分代表了他锁定了一个索引范围，在这个范围内，所有索引使用 T 锁进行锁定；

第二个部分是而这个范围内已经命中的Key，这些 Key 将使用 K 锁进行锁定。

合并在一起我们说在这个范围内，索引范围和特定的row的锁定模式为 Range T-K。

举上一篇的一个例子吧：

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

的锁的使用情况是：

实际上，上述语句产生的锁有两个部分，第一个是 Range S 锁，范围是 20-40 的索引范围，第二是 Key 上使用的 S 锁，在图中可以看到有三个 Key 被命中了，分别是“无限远”，“25”对应的索引以及“30”对应的索引。其 Mode 为 Range S-S，其 Type 为 KEY，也就是，他们的范围锁为 Range S，Key 锁为 S 锁。

更新和插入操作涉及的锁

涉及的锁主要是两种，一种是 Range S-U 锁，另一种是 Range X-X 锁。

Range S-U，这个选定索引范围会获得 S 锁而命中的 Key 使用 U 锁锁定，以便将来转换为 X 锁。而在更新时，则彻底成为 X 锁，这个范围内的锁模式也就成了 Range X-X。由于更新的数据列不同（有可能是索引列，有可能不是），使用的索引也不同（聚集，非聚集，唯一，等），因此其情况就不容易像 Range S-S 锁那么容易得出规律了。总的来说有几种情况还是一致的，这里就不再逐个实验了（这里强烈推荐阅读 SQL Server 2008 Internals 这本书关于锁的章节，讲述的很清楚）：

首先，在相等判断（例如“=”），且索引为唯一索引的情况下。如果该索引命中，不会有 Range T-K 锁锁定记录范围，而相应的记录直接获得 U 锁或者 X 锁；

其次，在相等判断，不论索引是否为唯一索引，如果该索引没有命中记录，则 Range T-K 锁锁定 “下一个”记录。（关于“下一个”的解释请参见上一篇）；

第三，在范围条件（>、<、BETWEEN），不论索引是否唯一，如果该索引命中，不但该范围会获得 Range T-K 锁，而该范围的“下一个”记录也会获得 Range T-K 锁。

为什么 Serializable 隔离级别更容易死锁

我们从第一篇的图可以看到，SERIALIZABLE 级别能够保证最严格的数据一致性，但是这些保卫的手段只要稍稍变化就可以发展为死锁。事实上，在各种隔离级别中，数据一致性越高，则越容易发生死锁；数据一致性越低，则发生死锁的概率就越小。

在这些隔离级别中，SERIALIZABLE 是最容易死锁的，这得益于 Range T-K 锁使锁定的范围不仅仅限于现有数据，还有未来数据；不仅仅限定现有的若干数据页，而是一个广大的范围。

这其中，最恐怖的问题莫过于“下一个”数据的锁定。这非常容易造成大范围死锁。我们以第一篇的例子来说明：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

SELECT @findCount=COUNT(id)FROM MyTable

WHERE [fk_related_id]=@Argument

 

IF (@findCount > 0)

BEGIN

    ROLLBACK TRANSACTION

    RETURN ERROR_CODE

END

 

INSERT INTO MyTable ([fk_related_id],…)

VALUES (@Argument,…)

 

COMMIT TRANSACTION

RETURN SUCCESS_CODE

在这个例子中，表 MyTable 的列 fk_related_id 是一个唯一索引（非聚集），事务隔离级别为 SERIALIZABLE。不同的存储过程执行会传入不同的 @Argument，表面看来，这不会有任何的问题，但是由于“下一个”数据的锁定，在稍高水平的并发上，就出现了大约 80% 的失败情况，这些失败都来源于死锁。我们挑选了其中的一次：

我们试图以每秒钟 15 个的压力在 @Argument 属于 [1, 1000] 的范围内进行存储过程调用。在这个过程中，有一个 @Argument 为 115 的记录首先成功的插入了进去！

idfk_related_iddata1115…

接下来有一个 @Argument 为 74 的记录获得了机会，我们假设它的 Session Id 为 A。它执行了 SELECT 语句：

idfk_related_iddata1115 (A 获得了Range S-S Lock)…

接下来有一个 @Argument 为 4 的记录获得了机会，我们假设它的 Session Id 为 B。它执行了 SELECT 语句：

idfk_related_iddata 115 (A 、B获得了Range S-S Lock)…

接下来，Session A 执行到了 INSERT 语句，那么 Range S-S 锁会试图进行一个转换测试（Range I-N 锁），但这显然是行不通的，因为 Session B 也获得了 Range S-S Lock，因此 Session A 陷入了等待；

而 Session B 也执行到了 INSERT 语句，相同的，它也陷入了等待；这样，Session A 等待 Session B 放弃 Range 锁，Session B 等待 Session A 放弃锁，这是一个死锁了。

而更糟糕的事情是，凡是 @Argument 小于 115 的记录，他都会试图令下一个记录获得新的 Range S-S 锁，从而进入无限的等待中，至少，1-115 号记录死锁，并且最终 114 个需要放弃，1个成功。这就是为什么 SERIALIZABLE 隔离级别不但会发生死锁，而且在某些时候，是大面积死锁。

总之：在 SERIALIZABLE 隔离级别下，只要有类似同一索引为条件先读后写的状况的，在较大并发下发生死锁的概率很高，而且如果碰巧既有的记录索引按照排序规则在非常靠后的位置，则很可能发生大面积死锁。

那么如何解决这个问题呢，呃，降低隔离级别当然是一个方法，例如，如果你能接受幻读，那么 REPEATABLE READ 是一个不错的选择。但是我突然在某篇博客中看到了使用 SELECT WITH UPDLOCK 的方法。事实上，这种东西让死锁更容易了。

例如，一个存储过程 SELECT B，而后 SELECT A；而另外的存储过程先 SELECT A，再 SELECT B，那么由于顺序不同，排他锁仅仅是 Read 的情况就可能发生死锁了。

那么为什么 REPEATABLE READ 会好得多呢？因为 REPEATABLE READ 紧紧锁定现有记录，而不会使用 Range 锁。我们仍然以上述存储过程为例，这样，只有两个被锁定的行数据在同一个页上（因为默认情况下使用页级锁），或者说挨得足够近，才有可能死锁，并且这个死锁仅仅限于这个数据页上的记录而不会影响其他记录，因此死锁的概率大大降低了。

我们实际测试中，在相同的测试条件下，并发提高到 100 的情况下时才有不到 0.1% 的死锁失败几率。当然我们付出了允许幻读的代价。