SQL Server 中的锁

锁是一种与表关联的机制，**用于限制对数据的未经授权的访问**。它主要用于解决事务中的并发问题。我们可以**在行级别、数据库级别、表级别和页面级别应用锁**。本文将讨论锁机制以及如何在 SQL Server 中监视它们。

我们知道多个用户可以同时访问数据库。因此，锁定对于成功的事务至关重要，可以防止在多个用户尝试读取、写入或更新数据库时数据被损坏或失效。通常，锁是一种**内存中的结构**，包含所有者、类型以及它们要保护的资源的哈希值。作为一种内存中的结构，锁的大小为**96 字节**。

最好理解的是，“锁**旨在确保数据完整性和一致性**，同时支持并发数据访问，因为它强制每个事务通过 ACID 测试。当多个用户同时访问数据库以更改其数据时，它会实现并发控制。”

ACID 测试包含以下使事务成功的要求

• 原子性：确保事务中包含的所有语句或操作都必须成功。否则，操作将回滚到其先前的状态。
• 一致性：确保在成功提交事务时，数据库只能构建一个有效状态。
• 隔离性：保证所有事务都独立于其他事务。它还确保语句对彼此透明。
• 持久性：保证即使在系统崩溃或发生故障的情况下，已提交事务的结果也会永久保存在数据库中。

锁在数据库中的位置在哪里？

现在，我们将了解锁实际上存在于数据库的何处，即它们锁定或不锁定哪些资源。下表显示了 SQL Server 可以放置锁的资源

锁模式

锁模式用于阻止其他人读取或更改被锁定的资源。它可以分为以下**六种类型**，如下所示

• 排他锁 (X)
• 共享锁 (S)
• 更新锁 (U)
• 意向锁 (I)
• 架构锁 (Sch)
• 批量更新锁 (BU)

排他锁

排他锁**在 INSERT、UPDATE 或 DELETE 语句等 DML 操作中非常有用**。此锁应用于事务时，会阻止其他用户访问被锁定的资源。这意味着**一个资源在同一时间只能由一个事务持有排他锁**。此锁的用户称为写入者。当事务想要修改页面或行数据时，将应用此锁。只有在目标上没有其他共享锁或排他锁时，才能由页面或行持有。

共享锁

一旦在页面或行上应用了共享锁，它们将被保留用于只读目的。这意味着在锁处于活动状态时，没有其他事务可以更改被锁定的资源。顾名思义，多个事务可以同时在此资源上持有此锁。此锁的用户称为读取者。此外，此锁也允许写入操作，但不允许进行 DDL 更改。

更新锁

更新锁与排他锁相同，但设计更为灵活。已持有共享锁的事务可以授予更新锁。在这种情况下，更新锁可以对目标页面或行持有另一个共享锁。当持有更新锁的事务即将更改数据时，此锁可以更改为排他锁。它通常用于服务器过滤记录以进行更新。此外，它是**循环死锁**问题的解决方案。

意向锁

意向锁是一种让事务通知其他事务其获取锁意图的方式。此锁旨在阻止另一个事务获取层次结构中下一个对象的锁，从而确保数据修改得以正确执行。它表明此锁用于创建锁层次结构。它是性能方面的一个重要锁类型。它可以分为三种类型

1. 意向共享 (IS)：如果页面或行持有此锁，则事务打算通过单独获取这些资源的共享锁 (S) 来读取较低层次结构中的资源。
2. 意向排他 (IX)：如果页面或行持有此锁，则事务打算通过单独获取这些资源的排他锁 (X) 来更改较低层次结构中的某些资源。
3. 意向更新 (IU)：此锁只能在页面级别获取，并且在更新操作完成后会转换为意向排他锁。

架构锁

架构锁在 SQL Server 中用于执行依赖于表架构的操作。它可以分为两种类型

1. 架构修改 (Sch-M)：在执行 DDL 语句时使用，并在更改对象结构时阻止访问锁定的对象数据。
2. 架构稳定性 (Sch-S)：在编译和执行架构相关查询以及生成执行计划时使用。

批量更新锁

此锁用于通过指定 **TABLOCK** 提示将批量数据复制到表中。用户通常在想要将大量数据插入数据库时使用它。

锁定层次结构

SQL Server 引入了锁定层次结构，用于在读取或修改数据时使用。锁层次结构从层次结构顶部的数据库开始，一直向下到表和页面，直到底部的行。下图更清楚地解释了这一点

锁兼容性矩阵

在理解了锁模式和锁层次结构之后，让我们看看锁模式如何转换为锁层次结构。主要有两种可用的锁定级别，锁模式应用于这些级别：行级别和表级别。

我们可以在行级别应用以下三种锁模式

• 排他 (X)
• 共享 (S)
• 更新 (U)

下表说明了这些模式的兼容性

其中 ✗ 表示不兼容，✓ 表示兼容。

我们可以在表级别应用以下五种锁模式

• 排他 (X)
• 共享 (S)
• 意向排他 (IX)
• 意向共享 (IS)
• 共享与意向排他 (SIX)

下表说明了这些模式的兼容性

示例

让我们通过一个例子来理解如何在 SQL Server 中使用锁定。我们将首先创建一个**演示表**，不包含任何索引，然后尝试更新该表。

在下一步中，我们将**创建一个事务来更新记录**，然后分析锁定的资源。

我们将获得以下输出

现在，我们将执行以下命令来检查 **sys.dm_tran_lock** 视图。

它将显示有关活动锁资源的许多数据。

然而，其中一些数据无法清楚理解。因此，我们将使用 sys.dm_tran_locks 视图与其他视图结合使用。这是代码脚本。

执行脚本将返回以下输出，其中我们可以看到锁定的资源和锁模式。