SQL锁机制高级篇------二

使用SQL Server 6年多了，在下自认为对SQL Server还是比较熟悉的，而且我喜欢将SQL Server内部的一些 东西搞清楚。 当我在教一门SQL Server编程课程时，我注意到微软MSDN中提到了锁兼容性，在MSDN列举了一个兼容性关系的表格。 看过这张关系表格，我就想知道是否存在用于更新的意向锁(Intent Update lock)？于是我开始阅读相关的资料。 这篇文章也是我研究的结果。这篇文章的适用读者是那些对隔离级别(isolation level)，意向锁，死锁和锁粒度有所了解的。 如果你对这些领域还不了解，那么我建议你在读这篇文章前，应该先去了解和阅读相关资料。 希望这篇文章能够加深你对SQL Server锁的理解，也许有些技巧还能够在SQL Server编程中带来帮助。 必须指出，即使不知道锁是如何工作的，你也能长时间愉快地使用SQL Server，并且能创建高质量的代码和数据库设计。 不过如果你象我那样喜欢探究事情的内部机理，或者你的工作需要你掌握一些性能方面的知识，我很乐意能教你一些有用的东西。
更新锁(Update Locks)死锁的典型情况是SPID X锁住了资源A，并在等待对资源B进行加锁，而SPID Y锁住了资源B，在等待对资源A加锁，如此就 形成了死锁。如果不理解，查询MSDN 或者相关的资料。 现在来假想更多情形下的死锁。假设：SPID X在资源A上加了共享锁，SPID Y也在资源A上加了共享锁，因为是共享锁， 所以这样没有问题。现在X想把共享锁升级为排它锁(exclusive lock)以用于更新资源。X就必须等Y释放共享锁才能办到， 当X在等待时，Y也想做同样的事情。这样，X在等Y释放，Y同时在等待X释放，死锁产生了。这种死锁被称为转换死锁(conversion deadlock)。 这种情况会很常见，为避免这种死锁，就引入了更新锁机制。更新锁允许连接读取资源，同时宣告它因为要编辑数据而要开始锁住资源了。SQL Server并无法提前知道一个事务要把共享锁转换成排它锁了，当然有一个情况特殊，即只在一个SQL语句中 完成读取然后更新的操作，比如说UPDATE XXX (SELECT YYY ....)这种类型。对于一般的SELECT语句，我们必须显示地 使用UPDLOCK提示。 下面是代码示例： USE Northwind
GO
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
GO
BEGIN TRAN
SELECT *
FROM Orders (UPDLOCK)
WHERE OrderID = 10633 注意到我打开了事务，但并没有关闭事务。这样锁就始终存在。如果另外一个连接视图在相同的记录上获取更新锁，就只有等待第一个事务结束后才行。这样就可以演示，在相同资源上， 两个更新锁不相容的效果。 运行SP_LOCK，会显示和上面的操作相关的记录行，字段以及锁的情况： 如我们预想那样，主键OrderID被更新锁锁住了。图中Resource列里面那个(89003da47679)的值，表示的是 主键10633的哈希值。SQL Server使用哈希表的方式来存储锁信息。 包含那行的记录行，如我们所期望的那样，被更新意向锁锁住了。在resource那列的数值(1:242)表示该数据页面是 数据库的第1个文件，页面编号是#242。而意外的是，SQL Server添加了一个IX的表锁。由于SQL Server不会在 表锁上使用U/IU类型锁，所以在表锁级别上，只能看到X/IX类型锁。 当更新操作中带有where语法，SQL Server会扫描整个表，并且/或者扫描索引，以决定那些记录会被改变。 在从表/索引读取信息之前，SQL Server首先把对象锁住。既然SQL Server知道你提交的是更新事务，那么它 就会选择更新锁，而不是共享锁。这样做就是为了避免前面所提到的死锁情况－－转换死锁(conversion deadlock)。 当SQL Server确定那些记录行需要改变后，在这些记录上，它会把更新锁进一步升级为排它锁，如果是堆表(heap table)，那么锁加 在RID(行标识符)上，如果是聚集索引表，锁加在主键上。这就意味着更新锁会立刻升级为排它锁，因此当你执行UPDATE 操作时，几乎不可能看到这个更新过程。 不过，也有例外。如果SQL Server使用一个索引来定位记录行，它就会锁住索引页，在索引上加的就是更新锁。 如果不改变任何包含在该索引中的数据列，更新锁不会升级为排它锁。下面是一个例子： BEGIN TRAN
UPDATE Region
SET RegionDescription = 'South'
WHERE RegionID = 4 Region是一个堆表，在RegionId上只有非聚集唯一索引主键。因此完成上面查询时，SQL Server在RegionId上扫描索引， 锁住索引页和索引键。当发现要改变得记录行后，因为更新查询并不改变RegionId的值，因此不会升级到排它锁。 运行SP_LOCK后可以得到以下信息： 我们看到，在RID上有一个IX锁。该锁位于RegionId索引上。还可以看到在表上有一个IX锁，RID上有一个X锁。 KEY锁在RegionId索引上，证据可以从Indid列上可以得到。在索引上还有一个更新锁，这是更新锁激活的一个瞬间之一。 当查询结束后，仍然存在两个页面锁 –- 一个在索引页 (1:306)上, 另一个在堆(heap) (1:300)上。这是因为 堆的Indid(Index id)为0。
锁粒度(Lock Granularity)SQL Server有几种锁类型，每种类型都可以选择不同的粒度。 如果运行SP_LOCK，或者查看企业管理器中"当前激活"信息，就可以看到至少四，五中不同的锁类型。下面简单回顾一下这些类型： Database (DB): 这是一种会话(session)锁。例如，它不涉及任何事务，仅仅是一个用户和数据库之间的连接。 这样就可以防止有用户连接到数据库时，该数据库被卸载了。值得注意的是，虽然SQL Server的master和tempdb是 不能卸载的，但是在这两个数据库上是没有DB锁的。 Table (TAB): 这是SQL Server中最粗略的逻辑锁。在表级别上经常加的是意向锁 (觉得意向锁不安全吗？这里 有更详细的信息。) Extent (EXT): 这种锁一般发生在SQL Server创建新表，或者已有表容量增加时，而并非用于锁住记录行。因此 当文件容量变化时，经常会看到这种锁的存在。 Page (PAG): 当SQL Server要同时锁住很多记录行，而可用的锁槽(slot)较少时，页面锁将会被采用。页面级别 上的意向锁更常见。目前为止的SQL Server版本（包括SQL Server 6.5在内)，这种类型的锁是最佳性能的。 Key (KEY): 和RID锁一样，可能是SQL Server中最佳级别的锁。KEY锁用于索引上，而RID锁用在堆表上。 (译者注：行锁包括KEY锁和RID锁，从锁的级别上 考虑对并发是最佳的，但是从性能考虑，行锁会大量占用资源，相关资料可见前面的blog)。
在研究SQL Server 2000的锁行为中，我认为SQL Server在大多数情况下，和速度相比，更看重并发性能。 较高的并发性能，意味着很多用户能同时对数据库进行操作。所以锁尽可能的小，不必要地锁住别人也需要的数据的可能性就越小；另一方面，如果使用较大的锁，将获取更高的速度。（译者注：这句话的理解应该以平衡性能的前提下考查。) 当SQL Server 2000发现操作将锁住越来越多的记录行时，就会提高锁的级别。 例如SQL Server 2000会升级到表锁，丢掉单独的pages/keys/RIDs级别锁。注意：提高锁的级别肯定是升级到表锁，而不会将RID/KEY锁升级到页面锁。 那么SQL Server2000什么时候升级锁呢？它无法知道你将锁住的表的比例，因此它唯一在意的就是产生的锁的数量。 当锁使用了较高比例的内存时，SQL Server2000就开始升级所有连接事务上的锁了。当锁槽使用将尽时，也会开始 升级工作。你可以用SP_CONFIGURE来配置SQL Server可用的锁槽数，例如降低该数值，从而来观察锁的升级情况。 SQL Server会尽可能使用较小的锁来保证较高的并发性能。但是有时候SQL Server并不知道数据将会发生怎么样的改变， 从而它会按照它的规则来改变锁的级别，而这种改变并非你想要的。例如一个很大的查找表(lookup table)，仅仅是读取 数据。那么你可以直接用一个表锁来替代很多KEY锁。使用的方法是使用锁提示或者SP_INDEXOPTION。 锁提示很普通，在联机帮助(BOL) 有大量关于此的文档，因此在本文就不重复介绍了。系统存储过程SP_INDEXOPTION是强迫SQL Server使用特定大小的锁 的好办法。 使用SP_INDEXOPTION，你可以关闭行或者页面级别的锁。也就是说，你可以不需要锁提示－－所有表或者索引上的锁都是你指定的大小。即使BOL宣称，该存储过程用于索引上的锁粒度，其实它也能用户堆上。一个好的实现 方法是使用表名来替代@IndexNamePattern变量，这种方法很少人知道。 关于这方面的研究并没有结束。如果你使用了两个更高隔离级别中的一个，且在检索规则中没有任何可用的索引，那么 SQL Server即使不锁住整个表，也会尽可能多的记录来满足你的查询。下面是一个例子： USE Northwind
GO
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
GO
BEGIN TRAN
UPDATE dbo.Orders
SET Freight = Freight * 1.25
WHERE Freight BETWEEN 100 AND 200在另一个窗口运行SP_LOCK。在我这里运行的时候，我看到该连接上有853个锁。数据库Northwind中的Orders表上 有830行，每行上都有一个锁。回滚该UPDATE事务，然后进行改写，在UPDATE前先创建索引，如下所示： USE Northwind
GO
CREATE NONCLUSTERED INDEX
FreightTest ON
Orders(Feight)
GO
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
GO
BEGIN TRAN
UPDATE dbo.Orders
SET Freight = Freight * 1.25
WHERE Freight BETWEEN 100 AND 200现在，运行SP_LOCK只显示25个锁。这在性能调试时经常被忽视。即使你是用缺省的READ COMMITED隔离级别， 和创建索引相比，也是巨大的差别－－136个锁和24个锁。
锁的跟踪标记(Locking Trace Flags)有一些跟踪标记可以帮助我们调试锁，发现死锁问题。 跟踪标记用于打开或者关闭SQL Server的行为方式。DBCC TRACEON命令来设置跟踪标记，如果希望SQL Server启动 时就打开跟踪标记，只要在启动参数加'-T'就可以了。 1200: 显示所有连接的所有的锁。这个选项将会有巨量的输出信息，因此我建议只在可控制的环境下使用，例如在 同一时刻只有一个连接在工作。 1204: 输入和死锁相关的信息。下面是这种信息的一个示例：
Node:1
KEY: 6:885578193:2 (010086470766) CleanCnt : 1 Mode : U Flags : 0x0
Grant List 0:
Owner: 0x42c0b2e0 Mode: U Flg: 0x0 Ref: 2 Life: 02000000 SPID: 53 ECID: 0
SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update Region
set RegionDescription = 'aa'
where RegionID = 1
Requested By
ResType: LockOwner Stype : 'OR' Mode: U SPID: 51 ECID: 0 Ec:(0x42E25568)
Value : 0x42c0b220 Cost: (0/0)
Node: 2
RID: 6:1:300:0 CleanCnt: 1 Mode: U Flags: 0x2
Grant List: 0
Owner: 0x42c0b320 Mode: S Flg: 0x0 Ref: 1 Life: 02000000 SPID: 51 ECID: 0
SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update Region
set RegionDescription = 'aa'
where RegionID = 1
Requested By
ResType: LockOwner Stype : 'OR' Mode: X SPID: 53 ECID: 0 Ec:(0x434A1568)
Value : 0x42c0b240 Cost: (0/0)
Victim Resource Owner:
ResType: LockOwner Stype: 'OR' Mode: X SPID: 53 ECID: 0 Ec(0x434A1568)
Value: 0x42c0b240 Cost: (0/0)KEY: 表示死锁中涉及到的索引信息。当然你也可以用类似的参数来指定任何其它的锁信息，例如page，RID，table等等。 ECID从master.dbo.sysprocesses得到，用于区分不同线程产生的锁。Mode是死锁的请求模式，例如S, X 或者 U。 字符串"6:885578193:2"表示：数据库id为6，对象id为885578193，索引id为2。后面圆括号内的数值是标识锁的哈希值，该值存储在master.dbo.syslockinfo表的rsc_text列内。遗憾的是，这个数值是单向哈希，也就是说仅靠它是无法找出被 锁住的记录行。Spid是锁的系统进程ID。 Node 1 & 2显示进入了死锁状态。两个锁都处于等待队列中，“Requested By:”说明了这一点。 1205: 打印锁管理工作的相关信息。每次死锁搜索工作初始化后，跟踪标记就通知锁管理打印出搜索的信息。该 选项工作的前提示跟踪标记1024必须给出。 1211: 禁止所有锁的升级。这个标记通知锁管理不要升级任何锁，即使锁资源被用完也一样。
列锁(Column Locks)正如你所知道的，SQL Server 2000中最下的锁是行锁。SQL Server 并不直接提供列锁。下面我们通过索引锁来模拟 实现列级别的锁。 列锁通常被认为在某些情况下会很慢，SQL Server也不例外。但是既然行锁并不自动锁表的索引，因此你总是可以 在索引页上使用那些被锁住的数据。我们再用数据库Northwind的Region表来举例。 表Region是堆表，有两个字段：RegionDescription和RegionId。RegionId字段上有一个唯一性非聚集索引。 我们用一个简单的UPDATE操作，来更新RegionDescription字段的内容。 USE Northwind
GO BEGIN TRAN
UPDATE Region
SET RegionDescription = 'South'
WHERE RegionDescription = 'Southern'该查询，SQL Server不会用到索引，因为在RegionDescription字段上并没有索引。 因此SQL Server会扫描整表以找到需要更新的记录行。一旦找到，那些记录上的更新锁就会升级到排它锁。要确定这点，可以在另一个窗口运行SP_LOCK即可。因此那些对应数据上应该有RID锁。在运行SP_LOCK的那个窗口中 输入一个SELECT查询： SELECT * FROM Region此时，我们不会进入等待状态。如果你象我，就会喜欢去看一下执行计划，因为执行计划会告诉我们为什么此时我们不会进入等待状态。 正如上面看到的，SQL Server要完成上面的SELECT，需要选择一个索引扫描以获取数据。既然SLECT *可以用索引来完成， 因此它就没有必要去读取堆上的数据了。我们称这种查询为覆盖查询(covering query)。 需要注意上面过程中的两个准则。第一个准则是查询中涉及到的数据必须是索引能照顾到的。记住如果表有一个聚集索引，所有的非聚集索引会有一个index字段，字段内就是那个聚集索引字段的值 第二个准则是早先的那个UPDATE操作不能改变任何索引包含的字段的值。如果被改变了(即索引值也被改变了)，它就会 升级到排它锁，因此上面的技巧也失效了。
扩展锁能力的表格(Extended Lock Capability Table)该表可以在联机帮助和MSDN中找到，它标识了那些锁之间是相互兼容的。我这里列出一个更复杂的表格，希望 对大家有用：
结束语我的确找到了难以掌握的更新意向锁，对此进行了大量的研究。锁和锁行为在联机帮助中的资料很少，因此也增加了对此的研究。我在大量研究后写下此文，希望能和你们分享相关的知识。