SQL锁机制高级篇http://tamsun.spaces.live.com/blog/cns!549A39619D3FADA3!177.entry

来源：互联网发布：windows主题图片编辑：程序博客网时间：2024/04/29 19:18

SQL锁机制高级篇

在看这篇文章(翻译)之前，简单介绍一下锁，顺便也带出几个专用词汇的翻译。

什么是锁

SQL Server 2000使用锁来实现多用户同时修改数据库同一数据时的同步控制。

死锁

多个会话同时访问数据库一些资源时，当每个会话都需要别的会话正在使用的资源时，死锁就有可能发生。死锁在多线程系统中都有可能出现，并不仅仅局限于于关系数据库管理系统。

锁的类型

一个数据库系统在许多情况下都有可能锁数据项。其可能性包括：

Rows—数据库表中的一整行
Pages—行的集合（通常为几kb）
Extents—通常是几个页的集合
Table—整个数据库表
Database—被锁的整个数据库表

除非有其它的说明，数据库根据情况自己选择最好的锁方式。不过值得感谢的是，SQL Server提供了一种避免默认行为的方法。这是由锁提示来完成的。

锁提示

Tansact－SQL提供了一系列不同级别的锁提示，你可以在SELECT,INSERT,UPDATE和DELETE中使用它们来告诉SQL Server你需要如何通过重设锁。可以实现的提示包括：

FASTFIRSTROW—选取结果集中的第一行，并将其优化

HOLDLOCK—持有一个共享锁直至事务完成

NOLOCK—不允许使用共享锁或独享锁。这可能会造成数据重写或者没有被确认就返回的情况；因此，就有可能使用到脏数据。这个提示只能在SELECT中使用。

PAGLOCK—锁表格

READCOMMITTED—只读取被事务确认的数据。这就是SQL Server的默认行为。

READPAST—跳过被其它进程锁住的行，所以返回的数据可能会忽略行的内容。这也只能在SELECT中使用。

READUNCOMMITTED—等价于NOLOCK.

REPEATABLEREAD—在查询语句中，对所有数据使用锁。这可以防止其它的用户更新数据，但是新的行可能被其它的用户插入到数据中，并且被最新访问该数据的用户读取。

ROWLOCK—按照行的级别来对数据上锁。SQL Server通常锁到页或者表级别来修改行，所以当开发者使用单行的时候，通常要重设这个设置。

SERIALIZABLE—等价于HOLDLOCK.

TABLOCK—按照表级别上锁。在运行多个有关表级别数据操作的时候，你可能需要使用到这个提示。

UPDLOCK—当读取一个表的时候，使用更新锁来代替共享锁，并且保持一直拥有这个锁直至事务结束。它的好处是，可以允许你在阅读数据的时候可以不需要锁，并且以最快的速度更新数据。

XLOCK—给所有的资源都上独享锁，直至事务结束。微软将提示分为两类：granularity和isolation-level。Granularity提示包括PAGLOCK, NOLOCK, ROWLOCK和TABLOCK。而isolation-level提示包括HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD和SERIALIZABLE。

可以在Transact-SQL声明中使用这些提示。它们被放在声明的FROM部分中，位于WITH之后。WITH声明在SQL Server 2000中是可选部分，但是微软强烈要求将它包含在内。这就使得许多人都认为在未来的SQL Server发行版中，就可能会包含这个声明。下面是提示应用于FROM从句中的例子：

[ FROM { < table_source > } [ ,...n ] ]

< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
  < table_hint > ::=
  { INDEX ( index_val [ ,...n ] )
  | FASTFIRSTROW
  | HOLDLOCK
  | NOLOCK
  | PAGLOCK
  | READCOMMITTED
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK }

词汇表

会话 (session)

English Query 中由 English Query 引擎执行的操作序列。会话在用户登录时开始，在用户注销时结束。会话期间的所有操作构成一个事务作用域，并受由登录用户名和密码决定的权限的支配。堆表 (heap table)

如果一个表没有索引，数据行以随机的顺序存储，这种结构称为堆。这种表称为堆表。意向锁 (intent lock)

放置在资源层次结构的一个级别上的锁，以保护较低级别资源上的共享或排它锁。例如，在 SQL Server 2000 数据库引擎任务应用表内的共享或排它行锁之前，在该表上放置意向锁。如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。排它锁(exclusive lock)

一种锁，它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作（INSERT、UPDATE 或 DELETE）过程中始终应用排它锁。隔离级别 (isolation level)

控制隔离数据以供一个进程使用并防止其它进程干扰的程度的事务属性。设置隔离级别定义了 SQL Server 会话中所有 SELECT 语句的默认锁定行为。扩展(盘)区 (extent)

每当 SQL Server 对象（如表或索引）需要更多空间时分配给该对象的空间的单元。在 SQL Server 2000 中，一个扩展是八个邻接的页。锁粒度(lock granularity)

SQL Server中数据以8KB为一页(page)的单位保存，连续的8个页组成一个扩展(extent)。创建数据库时，按这种方式来分配磁盘空间。当数据库容量增加时，意味着要创建更多的页和扩展。按照数据的存储结构 (row,page,extent)进行加锁，就是锁粒度。

SQL Server 2000里，最低的锁粒度是行(row)锁。SQL Server可以单独锁行，数据页，扩展，表。假设在UPDATE操作中只影响一行记录，SQL Server会将该行记录锁定，其他用户只有等该行记录的更新操作完毕后才能修改。另一方面，对于没有锁定的行记录，其他用户是可以进行修改的。因此行级锁对于并发是最佳的。

现在假设UPDATE操作影响1000行记录，SQL Server是否一次锁定一行？那就意味着如果有一个这样的选项，在内存允许前提下，需要1000个锁。实际上，SQL Server会根据这些数据是否分布在连续的页，来决定是否用几个页面锁，或者扩展锁，或者是表锁。如果SQL Server加了页面锁，那么这些页面上的记录其它用户就无法访问或者修改，即使页面上有些数据并非属于这1000行记录。这就是一种追求并发性能和资源消耗之间的平衡策略。

SQL Server对锁需要的资源十分敏感，也就是说，SQL Server查询优化器检测到可用内存较低时，就会使用页锁来替代多个行锁。同样，在内存消耗更低的判断下，会优先选择表锁而几个扩展锁。锁信息的标识

锁类型:

RID :行标识符。用于在表中单独锁定一行。
KEY :键, 索引内部的行锁。用于保护可串行事务中的键范围。
PAG :数据或索引页。
EXT :相邻的八个数据页或索引页构成的一组。
TAB :包括所有数据和索引在内的整个表。
DB :数据库。

^_^，说是简单介绍，其实我觉得已经对锁介绍也蛮多了，也许有写得不对的地方，有心人帮忙指点一下。词汇的中文翻译是从SQL Server联机帮助(books online)上搬用的。下面开始正文，好歹人家也是发表在堂堂DBA大网站上的Article，呵呵。

原文：Advanced SQL Server Locking
来源：SQL-Server-Performance.com
作者：Andrés Taylor

I thought I knew SQL Server pretty well. I've been using the product for more than 6 years now, and I like to know my tools from the inside out.......

使用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中找到，它标识了那些锁之间是相互兼容的。我这里列出一个更复杂的表格，希望对大家有用：

at_sql_locking4

结束语

我的确找到了难以掌握的更新意向锁，对此进行了大量的研究。锁和锁行为在联机帮助中的资料很少，因此也增加了对此的研究。我在大量研究后写下此文，希望能和你们分享相关的知识。

个人介绍

email：Andrés Taylor
作为 Dotway 的.NET/SQL Server 顾问。