SQL Server 中的锁定介绍

SQL Server 中的锁定介绍

当多个用户或应用程序同时访问同一数据时，锁定可防止这些用户或应用程序同时对数据进行更改。锁由 Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile) 数据库引擎 在内部管理。根据用户采取的操作，会自动获取和释放锁。如果在没有使用锁定时多个用户同时更新同一数据，则数据库内的数据会出现逻辑错误。如果出现这种情况，则对这些数据执行的查询可能会产生意外的结果。应用程序从不直接请求锁。相反，当事务开始并在事务内以查询语言、数据操作语言 (DML) 或数据定义语言 (DDL) 执行命令时，SQL Server Mobile 会锁定任何所需的资源以帮助保护所需隔离级别的资源。SQL Server Mobile 自动确定何时为所有资源授予和释放锁。默认情况下，行级锁定用于数据页，页级锁定用于索引页。为保留系统资源，当超过行锁数的可配置阈值时，锁管理器将自动执行锁升级。在锁管理器中可以为每个会话分配的最大锁数是 262143。您可以选择更改某些操作的锁定粒度。请慎用此操作，因为更改锁定粒度可能对并发性有负面影响。

自动锁定

SQL Server Mobile 自动锁定所需的数据以保护事务的所有操作，包括：

·         数据操纵语言 (DML)

·         数据定义语言 (DDL)

·         所需隔离级别上的查询语句

SQL Server Mobile 自动锁定数据行和所有相关的索引页，以确保最大限度的并发性能。随着事务锁定更多的资源，锁的粒度将自动增大到整个表，以便减少锁的维护工作量。

锁粒度和层次结构

Microsoft SQL Server Database Engine 具有多粒度锁定，允许一个事务锁定不同类型的资源。为了尽量减少锁定的开销，数据库引擎 自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。但其开销较低，因为需要维护的锁较少。数据库引擎 通常必须获取多粒度级别上的锁才能完整地保护资源。这组多粒度级别上的锁称为锁层次结构。例如，为了完整地保护对索引的读取，数据库引擎 实例可能必须获取行上的共享锁以及页和表上的意向共享锁。下表列出了数据库引擎 可以锁定的资源。

资源

说明

RID

用于锁定堆中的单个行的行标识符。

KEY

索引中用于保护可序列化事务中的键范围的行锁。

PAGE

数据库中的 8 KB 页，例如数据页或索引页。

EXTENT

一组连续的八页，例如数据页或索引页。

HOBT

堆或 B 树。保护索引或没有聚集索引的表中数据页堆的锁。

TABLE

包括所有数据和索引的整个表。

FILE

数据库文件。

APPLICATION

应用程序专用的资源。

METADATA

元数据锁。

ALLOCATION_UNIT

分配单元。

DATABASE

整个数据库。

 锁模式

共享 (S)

用于不更改或不更新数据的读取操作，如 SELECT 语句。共享锁（S 锁）允许并发事务在封闭式并发控制（请参阅并发控制的类型）下读取 (SELECT) 资源。资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁）。

更新 (U)

用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。更新锁（U 锁）可以防止常见的死锁。在可重复读或可序列化事务中，此事务读取数据 [获取资源（页或行）的共享锁（S 锁）]，然后修改数据 [此操作要求锁转换为排他锁（X 锁）]。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排他锁（X 锁）以进行更新。由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新锁（U 锁）。一次只有一个事务可以获得资源的更新锁（U 锁）。如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。

排他 (X)

用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。排他锁（X 锁）可以防止并发事务对资源进行访问。使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。

数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。语句在执行所需的修改操作之前首先执行读取操作以获取数据。因此，数据修改语句通常请求共享锁和排他锁。例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。

意向

用于建立锁的层次结构。意向锁的类型有：意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。数据库引擎 使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。

意向锁有两种用途：

·         防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。

·         提高数据库引擎 在较高的粒度级别检测锁冲突的效率。

例如，在该表的页或行上请求共享锁（S 锁）之前，在表级请求共享意向锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁（X 锁）。意向锁可以提高性能，因为数据库引擎 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。

锁模式

说明

意向共享 (IS)

保护针对层次结构中某些（而并非所有）低层资源请求或获取的共享锁。

意向排他 (IX)

保护针对层次结构中某些（而并非所有）低层资源请求或获取的排他锁。IX 是 IS 的超集，它也保护针对低层级别资源请求的共享锁。

意向排他共享 (SIX)

保护针对层次结构中某些（而并非所有）低层资源请求或获取的共享锁以及针对某些（而并非所有）低层资源请求或获取的意向排他锁。顶层资源允许使用并发 IS 锁。例如，获取表上的 SIX 锁也将获取正在修改的页上的意向排他锁以及修改的行上的排他锁。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其他事务对资源进行更新，但是其他事务可以通过获取表级的 IS 锁来读取层次结构中的低层资源。

意向更新 (IU)

保护针对层次结构中所有低层资源请求或获取的更新锁。仅在页资源上使用 IU 锁。如果进行了更新操作，IU 锁将转换为 IX 锁。

共享意向更新 (SIU)

S 锁和 IU 锁的组合，作为分别获取这些锁并且同时持有两种锁的结果。例如，事务执行带有 PAGLOCK 提示的查询，然后执行更新操作。带有 PAGLOCK 提示的查询将获取 S 锁，更新操作将获取 IU 锁。

更新意向排他 (UIX)

U 锁和 IX 锁的组合，作为分别获取这些锁并且同时持有两种锁的结果。

 

架构

在执行依赖于表架构的操作时使用。架构锁的类型有：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。 执行表的数据定义语言 (DDL) 操作（例如添加列或删除表）时使用架构修改锁（Sch-M 锁）。在架构修改锁（Sch-M 锁）起作用的期间，会防止对表的并发访问。这意味着在释放架构修改锁（Sch-M 锁）之前，该锁之外的所有操作都将被阻止。

当编译查询时，使用架构稳定性锁（Sch-S 锁）。架构稳定性锁（Sch-S 锁）不阻塞任何事务锁，包括排他锁（X 锁）。因此在编译查询时，其他事务 [包括在表上有排他锁（X 锁）的事务] 都能继续运行。但不能在表上执行 DDL 操作。

大容量更新 (BU)

在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新锁（BU 锁）。大容量更新锁（BU 锁）允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。

键范围

当使用可序列化事务隔离级别时保护查询读取的行的范围。确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。在使用可序列化事务隔离级别时，对于 Transact-SQL 语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。键范围锁可防止幻读。通过保护行之间键的范围，它还防止对事务访问的记录集进行幻像插入或删除。

 锁升级

锁升级就是将大量较细粒度的锁（例如行）转换为少量较粗粒度的锁（例如表）。利用锁升级可以减少系统开销。 当事务超出其升级阈值时，Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile) 会自动将行锁和页锁升级为表锁。在 SQL Server Mobile 中，锁升级可以是从行到表或从页到表，但不能是从行到页。在表级别上升级时，不能请求低于表的锁。

例如，在行上执行表事务时，SQL Server Mobile 会自动在受影响的行上获取锁，同时将更高级别的意向锁置于包含这些行的页和表上。所有相关的索引页也将同时锁定。当事务所持有的锁的数量超出其阈值时，SQL Server Mobile 就会尝试将表上的意向锁更改为更强的锁。例如，意向排他 (IX) 锁可以更改为排他 (X) 锁。得到更强的锁后，事务在表上所持有的所有页级及行级锁都将予以释放。如果请求锁时超出特定的锁升级阈值，锁就会按各个表分别进行升级。对于所有子表级的锁而言，不管类型如何，都将计入阈值统计。升级阈值应仅视为估计值，这是因为内部操作所请求的锁都已计入阈值统计中。升级过程可能会早于预期时间。如果由于锁冲突而导致无法进行升级，事务仍将继续，但可能会在以后尝试重新进行升级。