SQL2008中SQL应用之－锁定(locking)应用分析

2020-11-09 来源：步旅网

锁定(Locking)是一个关系型数据库系统的常规和必要的一部分，它防止对相同数据作并发更新或在更新过程中查看数据，从而保证被更新数据的完整性。它也能防止用户读取正在被修改的数据。

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

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

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

下面演示一个实例，它使用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实例中打开了哪些锁。它返回了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。
使用





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

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

 在表级别删除锁升级。注意，对于用了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列来验证这个选项。 
注意：如果表未分区，通常情况为表级别升级。如果你指定了Disable选项，将不会出现表级别的锁升级。这会提高并发性，但如果你请求访问大量的行或页，会增加内存的消耗。 
邀月 来自 http://www.cnblogs.com/downmoon

显示全文

全部栏目

SQL2008中SQL应用之－锁定(locking)应用分析