提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  工具

当前栏目

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

应用SQL 分析 锁定 sql2008 Locking
2023-06-13 09:14:28 时间

一、锁的基本概念：

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

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

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

名称

描述

共享(S) 用于不更改或不更新数据的读取操作，如SELECT语句。 更新(U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 排他(X) 用于数据修改操作，例如INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新。 意向 用于建立锁的层次结构。意向锁包含三种类型：意向共享(IS)、意向排他(IX)和意向排他共享(SIX)。 架构 在执行依赖于表架构的操作时使用。架构锁包含两种类型：架构修改(Sch-M)和架构稳定性(Sch-S)。 大容量更新(BU) 在向表进行大容量数据复制且指定了TABLOCK提示时使用。 键范围 当使用可序列化事务隔离级别时保护查询读取的行的范围。确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。

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

资源

说明

KEY 索引中用于保护可序列化事务中的键范围的行锁。 PAGE 数据库中的8KB页，例如数据页或索引页。 EXTENT 一组连续的八页，例如数据页或索引页。 HoBT 堆或B树。用于保护没有聚集索引的表中的B树（索引）或堆数据页的锁。 TABLE 包括所有数据和索引的整个表。 FILE 数据库文件。 RID 用于锁定堆中的单个行的行标识符。 APPLICATION 应用程序专用的资源。 METADATA 元数据锁。 ALLOCATION_UNIT 分配单元。 DATABASE 整个数据库。

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

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

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


二、查看锁的活动

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

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

复制代码代码如下:

USEAdventureWorks
BEGINTRAN
SELECTProductID,ModifiedDate
FROMProduction.ProductDocument
WITH(TABLOCKX)

打开另一个查询窗口，执行：
复制代码代码如下:

SELECTrequest_session_idsessionid,
resource_typetype,
resource_database_iddbid,
OBJECT_NAME(resource_associated_entity_id,resource_database_id)objectname,
request_modermode,
request_statusrstatus
FROMsys.dm_tran_locks
WHEREresource_typeIN("DATABASE","OBJECT")

执行结果：
复制代码代码如下:
/*
sessionidtypedbidobjectnamermoderstatus
51DATABASE4NULLSGRANT
52DATABASE4NULLSGRANT
53DATABASE8NULLSGRANT
56DATABASE8NULLSGRANT
53OBJECT8ProductDocumentXGRANT
*/

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

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

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

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

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

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

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


三、控制表的锁升级行为

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

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

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

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

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

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

下面示例演示了修改表的新设置：
复制代码代码如下:
ALTERTABLEPerson.Address
SET(LOCK_ESCALATION=AUTO)
--注意这句在SQLServer2005下会出错
SELECTlock_escalation,lock_escalation_desc
FROMsys.tables
WHEREname="Address"

/*
lock_escalationlock_escalation_desc
2AUTO
*/

下来，我们禁用锁升级：
复制代码代码如下:
ALTERTABLEPerson.Address
SET(LOCK_ESCALATION=DISABLE)
SELECTlock_escalation,lock_escalation_desc
FROMsys.tables
WHEREname="Address"

/*
lock_escalationlock_escalation_desc
1DISABLE
*/

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

注意：如果表未分区，通常情况为表级别升级。如果你指定了Disable选项，将不会出现表级别的锁升级。这会提高并发性，但如果你请求访问大量的行或页，会增加内存的消耗。
邀月来自http://www.cnblogs.com/downmoon

相关文章