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

SQL Server 2008中SQL应用之二“锁定(locking)” 锁是关系型数据库系统中的常规和必要的一部分。那么在SQL中锁是什么样子的？如何查看锁得活动？怎么控制表的锁升级行为？在下文中笔者会为我们一一做出解答。 AD：  一、锁的基本概念： 锁定(Locking)是一个关系型数据库系统的常规和必要的一部分，它防止对相同数据作 并发更新 或在更新过程中查看数据， 从而保证被更新数据的完整性。它也能防止用户读取正在被修改的数据 。Sql Server动态地管理锁定，然而，还是很有必要了解Transact- SQL查询如何影响SQL Server中的锁定。在此，简单介绍下锁的基本常识。 锁定有助于防止并发问题的发生。当一个用户试图读取另一个用户正在修改的数据，或者修改另一个用户正在读取的数据时，或者尝试修改另一个事务正在尝试修改的数据时，就会出现并发问题。 SQL Server资源会被锁定，资源的锁定方式称作它的锁定模式(lock mode)，下表列出SQL Server处理的主要锁定模式： 名称 描述共享 (S) 用于不更改或不更新数据的读取操作，如 SELECT 语句。 更新 (U) 用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 排他 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。意向 用于建立锁的层次结构。 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。 架构 在执行依赖于表架构的操作时使用。 架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。 大容量更新 (BU) 在向表进行大容量数据复制且指定了TABLOCK 提示时使用。 键范围 当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。可以锁定SQL Server中的各种对象，既可以是一个行，也可以是一个表或数据库。可以锁定的资源在粒度（granularity）上差异很大。从细（行）到粗（数据库）。细粒度锁允许更大的数据库并发，因为用户能对某些未锁定的行执行查询。然而，每个由SQL Server产生的锁都需要内存，所以数以千计独立的行级别的锁也会影响SQL Server的性能。粗粒度的锁降低了并发性，但消耗的资源也较少。下表介绍SQL Server可以锁定的资源： 资源说明KEY索引中用于保护可序列化事务中的键范围的行锁。 PAGE数据库中的 8 KB 页，例如数据页或索引页。EXTENT一组连续的八页，例如数据页或索引页。HoBT 堆或B 树。 用于保护没有聚集索引的表中的 B 树（索引）或堆数据页的锁。TABLE 包括所有数据和索引的整个表。FILE 数据库文件。RID 用于锁定堆中的单个行的行标识符。APPLICATION 应用程序专用的资源。METADATA 元数据锁。 ALLOCATION_UNIT分配单元。DATABASE 整个数据库。不是所有的锁都能彼此兼容。例如，一个被排他锁锁定的资源不能被再加其他锁。其他事务必须等待或超时，直到排他锁被释放。被更新锁锁定的资源只能接受其他事务的共享锁。被共享锁锁定的资源还能接受其他的共享锁或更新锁。 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(