SQL Server 锁
标签：SQL SERVER/MSSQL SERVER/数据库/死锁/
锁资源
Microsoft SQL Server 数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。 为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。 锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。 锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。 但其开销较低，因为需要维护的锁较少。
数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。 这组多粒度级别上的锁称为锁层次结构。 例如，为了完整地保护对索引的读取，数据库引擎实例可能必须获取行上的共享锁以及页和表上的意向共享锁。
数据库引擎可以锁定的资源。
| 资源 | 说明 |
|---|---|
|
RID |
用于锁定堆中的单个行的行标识符。 |
|
KEY |
索引中用于保护可序列化事务中的键范围的行锁。 |
|
PAGE |
数据库中的 8 KB 页，例如数据页或索引页。 |
|
EXTENT |
一组连续的八页，例如数据页或索引页。 |
|
HoBT |
堆或 B 树。 用于保护没有聚集索引的表中的 B 树（索引）或堆数据页的锁。 |
|
TABLE |
包括所有数据和索引的整个表。 |
|
FILE |
数据库文件。 |
|
APPLICATION |
应用程序专用的资源。 |
|
METADATA |
元数据锁。 |
|
ALLOCATION_UNIT |
分配单元。 |
|
DATABASE |
整个数据库。 |
锁模式
数据库引擎使用的资源锁模式。
| 锁模式 | 说明 |
|---|---|
|
共享 (S) |
用于不更改或不更新数据的读取操作，如 SELECT 语句。 |
|
更新 (U) |
用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 |
|
排他 (X) |
用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。 |
|
意向 |
用于建立锁的层次结构。 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。 |
|
架构 |
在执行依赖于表架构的操作时使用。 架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。 |
|
大容量更新 (BU) |
在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。 |
|
键范围 |
当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。 |
共享锁
共享锁（S 锁）允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 有关详细信息，请参阅并发控制的类型。 资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。 读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁）。
更新锁
更新锁（U 锁）可以防止常见的死锁。 在可重复读或可序列化事务中，此事务读取数据 [获取资源（页或行）的共享锁（S 锁）]，然后修改数据 [此操作要求锁转换为排他锁（X 锁）]。 如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。 共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。 第二个事务试图获取排他锁（X 锁）以进行更新。 由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
若要避免这种潜在的死锁问题，请使用更新锁（U 锁）。 一次只有一个事务可以获得资源的更新锁（U 锁）。 如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。
排他锁
排他锁（X 锁）可以防止并发事务对资源进行访问。 使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。
数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此，数据修改语句通常请求共享锁和排他锁。 例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。
意向锁
数据库引擎使用意向锁来保护共享锁（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 锁的组合，作为分别获取这些锁并且同时持有两种锁的结果。 |
架构锁
数据库引擎在表数据定义语言 (DDL) 操作（例如添加列或删除表）的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间，Sch-M 锁将阻止对表进行并发访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。
某些数据操作语言 (DML) 操作（例如表截断）使用 Sch-M 锁阻止并发操作访问受影响的表。
数据库引擎在编译和执行查询时使用架构稳定性 (Sch-S) 锁。 Sch-S 锁不会阻止某些事务锁，其中包括排他 (X) 锁。 因此，在编译查询的过程中，其他事务（包括那些针对表使用 X 锁的事务）将继续运行。 但是，无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。
大容量更新锁
数据库引擎在将数据大容量复制到表中时使用了大容量更新 (BU) 锁，并指定了 TABLOCK 提示或使用 sp_tableoption 设置了 table lock on bulk load 表选项。 大容量更新锁（BU 锁）允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。
键范围锁
在使用可序列化事务隔离级别时，对于 Transact-SQL 语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。 键范围锁可防止幻读。 通过保护行之间键的范围，它还防止对事务访问的记录集进行幻像插入或删除
锁兼容性控制
锁兼容性控制多个事务能否同时获取同一资源上的锁。 如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。 如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。 例如，没有与排他锁兼容的锁模式。 如果具有排他锁（X 锁），则在释放排他锁（X 锁）之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。 另一种情况是，如果共享锁（S 锁）已应用到资源，则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁（U 锁）。 但是，在释放共享锁之前，其他事务无法获取排他锁。
最常见的锁模式的兼容性。
| 现有授予模式 | ||||||
|---|---|---|---|---|---|---|
|
请求模式 |
IS |
S |
U |
IX |
SIX |
X |
|
意向共享 (IS) |
是 |
是 |
是 |
是 |
是 |
否 |
|
共享 (S) |
是 |
是 |
是 |
否 |
否 |
否 |
|
更新 (U) |
是 |
是 |
否 |
否 |
否 |
否 |
|
意向排他 (IX) |
是 |
否 |
否 |
是 |
否 |
否 |
|
意向排他共享 (SIX) |
是 |
否 |
否 |
否 |
否 |
否 |
|
排他 (X) |
否 |
否 |
否 |
否 |
否 |
否 |
使用下表可以确定 Microsoft SQL Server 中所有可用的锁模式的兼容性。
|
备注： 作者：pursuer.chen 博客：http://www.cnblogs.com/chenmh 本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。 《欢迎交流讨论》 |
