SQL Server 2008中SQL应用之二“锁定(locking)”

一、锁的基本概念：

锁定(Locking)是一个关系型数据库系统的常规和必要的一部分，它防止对相同数据作 并发更新 或在更新过程中查看数据， 从而保证被更新数据的完整性。它也能防止用户读取正在被修改的数据 。Sql Server动态地管理锁定，然而，还是很有必要了解Transact- SQL查询如何影响SQL Server中的锁定。在此，简单介绍下锁的基本常识。

锁定有助于防止并发问题的发生。当一个用户试图读取另一个用户正在修改的数据，或者修改另一个用户正在读取的数据时，或者尝试修改另一个事务正在尝试修改的数据时，就会出现并发问题。

SQL Server资源会被锁定，资源的锁定方式称作它的锁定模式(lock mode)，下表列出SQL Server处理的主要锁定模式：

可以锁定SQL Server中的各种对象，既可以是一个行，也可以是一个表或数据库。可以锁定的资源在粒度（granularity）上差异很大。从细（行）到粗（数据库）。细粒度锁允许更大的数据库并发，因为用户能对某些未锁定的行执行查询。然而，每个由SQL Server产生的锁都需要内存，所以数以千计独立的行级别的锁也会影响SQL Server的性能。粗粒度的锁降低了并发性，但消耗的资源也较少。下表介绍SQL Server可以锁定的资源：

不是所有的锁都能彼此兼容。例如，一个被排他锁锁定的资源不能被再加其他锁。其他事务必须等待或超时，直到排他锁被释放。被更新锁锁定的资源只能接受其他事务的共享锁。被共享锁锁定的资源还能接受其他的共享锁或更新锁。

SQL Server自动分配和升级锁。升级意味着细粒度的锁（行或页锁）被转化为粗粒度的表锁。当单个T－SQL语句在单个表或索引上获取5000多个锁,或者SQL Server实例中的锁数量超过可用内存阈值时，SQL Server会尝试启动锁升级。锁占用系统内存，因此把很多锁转化为一个较大的锁能释放内存资源。然而，在释放内存资源的同时会降低并发性。

SQL Server 2008带来了新的表选项，可以禁用锁升级或在分区（而不是表）范围启用锁升级。

二、查看锁的活动

下面演示一个实例，它使用sys.dm_tran_locks动态视图监视数据库中锁的活动。

打开一个查询窗口，执行如下语句：

USE AdventureWorks  
BEGIN TRAN  
SELECT ProductID, ModifiedDate  
FROM Production.ProductDocument  
WITH (TABLOCKX) 

打开另一个查询窗口，执行：

SELECT request_session_id sessionid,  
resource_type type,  
resource_database_id dbid,  
OBJECT_NAME(resource_associated_entity_id,   
resource_database_id) objectname,  
request_mode rmode,  
request_status rstatus  
FROM sys.dm_tran_locks  
WHERE resource_type IN ('DATABASE', 'OBJECT') 

执行结果：

/*sessionid    type    dbid    objectname    rmode    rstatus  
     51      DATABASE    4        NULL         S       GRANT 
     52      DATABASE    4        NULL         S       GRANT 
     53      DATABASE    8        NULL         S       GRANT   
     56      DATABASE    8        NULL         S       GRANT 
     53      OBJECT      8    ProductDocument  X       GRANT 
*/  
 

解析：本示例中，我们首先启动了一个新事务，并使用TABLOCKX锁提示（这个提示对表放置了排他锁），对Production.ProductDocument表执行了一个查询。查询sys.dm_tran_locks动态管理视力可以监视当前SQL Server实例中打开了哪些锁。它返回了AdventureWorks数据库中活动锁的列表。可以在结果中的***一行看到ProductDocument表上的排他锁。

前三列定义了会话锁、资源类型和数据库ID。第四列使用了Object_Name函数，注意它使用了两个参数（对象ID和数据库ID）来指定访问哪个名称（第二个参数是SQL Server 2005 SP2引入的，它用来指定为了转换对象名称而使用哪个数据库）。同时也查询锁定请求模式和状态，***，From子句引用DMV，用Where子句指定了两个资源类型。Resource_Type指定了锁定的资源类型，如Database\Object\File\Page\Key\RID\Extent\Metadata\Application\Allocation_Unit或HOBT类型。依赖资源类型的resource_associated_entity_id，确定ID是object ID, allocation unit ID, 或Hobt ID。

如果resource_associated_entity_id列包含Object ID(资源类型为Object),可以使用sys.objects目录视图来转换名称。

如果resource_associated_entity_id列包含allocation unit ID(资源类型为Allocation_Unit),可以引用sys.allocatiion_units和contain_id联结到sys.partitions上，就可以确定object ID。

如果resource_associated_entity_id列包含Hobt ID(资源类型为Key\page\Row或HOBT),可以直接引用sys.partitions,然后查找相应的Object ID。

对于Database、Extent、 Application或MetaData的资源类型，resource_associated_entity_id列将为0。

使用sys.dm_tran_locks能对无法预料的并发问题进行故障调试。例如，一个查询会话占用锁的时间可能比预期时间长而被锁，或者锁的粒度或锁模式不是我们所期望的（可能是希望使用表锁而不是更小粒度的行锁或页锁）。理解锁处于的锁定级别有助于我们更有效地对查询的并发问题进行故障调试。

三、控制表的锁升级行为

每个在SQL Server中创建的锁都会消耗内存资源。当锁的数量增加时，内存就会减少。如果锁的内存使用百分比超过一个特定阈值，SQL Server会将细粒度锁（页或行）转换为粗粒度锁（表锁）。这个过程称为锁升级。锁升级可以减少SQL Server实例占有的锁数量，减少锁内存的使用。

虽然细粒度会消耗更多的内存，但由于多个查询可以访问未锁定的行，因此也会改善并发性。引入表锁可能会减少内存的消耗，但也会带来阻塞，这是因为一条查询锁住了整个表。根据使用数据库的应用程序，这个行为可能是不希望发生的，而且你可能希望当SQL Server实施锁升级时尽量获得更多的控制。

SQL Server 2008引入了使用ALter table命令在表级别控制锁升级的功能。现在可以从如下3个设置中选择：

Table 这是SQL Server 2005中使用的默认行为。当设置为该值时，在表级别启用了锁升级，不论是否为分区表。

Auto 如果表已分区，则在分区级别（堆或B树）启用锁升级。如果表未分区，锁升级将发生在表级别上。

Disable 在表级别删除锁升级。注意，对于用了TABLOCK 提示或使用可序列化隔离级别下Heap的查询时，你仍然可能看到表锁。

下面示例演示了修改表的新设置：

ALTER TABLE Person.Address  
SET (LOCK_ESCALATION = AUTO)--注意这句在SQL Server 2005下会出错  
SELECT lock_escalation,lock_escalation_desc  
FROM sys.tables  
WHERE name='Address' 
 
/*lock_escalation    lock_escalation_desc  
2    AUTO  
*/ 

下来，我们禁用锁升级：

ALTER TABLE Person.Address  
SET ( LOCK_ESCALATION = DISABLE)  
SELECT lock_escalation,lock_escalation_desc  
FROM sys.tables  
WHERE name='Address' 
 
/*lock_escalation    lock_escalation_desc  
1    DISABLE  
*/ 

说明：在更改了这个配置后，可以通过查询sys.tables目录视图的lock_escalation_desc列来验证这个选项。

 原文出处：http://www.cnblogs.com/downmoon/archive/2011/02/18/1916117.html

【编辑推荐】

1. SQL Server 2008中SQL应用之一“死锁(Deadlocking)”
2. 浅析SQL Server 2008中的代码安全之一：存储过程加密与安全上下文
3. 浅析SQL Server 2008中的代码安全之二：DDL触发器与登录触发器
4. 浅析SQL Server 2008中的代码安全之三：通过PassPhrase加密
5. 浅析SQL Server 2008中的代码安全之四：主密钥