MS SQL Server 中的锁和事务持续时间

如今，开发者之间关于 SQL 锁的争论很常见。有人说“锁会在整个事务期间保持”。但也有人争辩说“锁只会保持在语句执行期间”。那么，谁是对的呢？

嗯，双方在某种程度上都是正确的。实际上，锁的持续时间取决于隔离级别。

正如 SQL-99 标准中所述，有 4 种事务隔离级别

• 读已提交 (默认)
• 读未提交
• 可重复读
• 串行化

SQL Server** 提供以下两个额外的隔离级别 (** SQL Server 2005 及更高版本)

• 快照
• 读已提交快照

当多个用户尝试访问同一数据时，DBMS 中可能会出现几种并发问题。每种隔离级别都可以防止特定的并发问题。

• 丢失更新
• 脏读
• 不可重复读
• 幻读

丢失更新 – 这种情况可能以两种方式发生。第一种场景：当一个事务 (事务 A) 更新的数据被另一个事务 (事务 B) 在 事务 A 提交或回滚之前覆盖时，就会发生这种情况。(但在任何事务隔离级别下，SQL Server** 都不会发生这种类型的丢失更新)

第二种场景是，一个事务 (事务 A) 读取一条记录并将其值检索到局部变量中，而同一条记录将被另一个事务 (事务 B) 更新。之后，事务 A 将使用局部变量中的值更新该记录。在这种情况下，事务 B 所做的更新可以被视为“丢失更新”。

脏读 – 这是一个事务 (未提交) 所更改的数据被另一个事务访问的情况。除“读未提交”外，所有隔离级别都可以防止“脏读”。

不可重复读 – 这是一个事务 (事务 A) 在一个事务中多次访问一组特定的数据，而在这些访问之间，该数据被另一个事务 (事务 B) 更新或删除。可重复读、序列化和快照隔离级别可以保护事务免受不可重复读的影响。

幻读 – 这是同一个事务中的两个查询，针对同一个表，使用相同的 'WHERE' 子句，而最后一个执行的查询返回的比第一个更多的行。只有序列化和快照隔离级别可以保护事务免受幻读的影响。

为了解决上述并发问题，SQL Server 使用以下类型的锁。

• 共享锁或 S 锁 - 共享锁有时也称为读锁。任何资源（例如行或页）都可以同时拥有多个共享锁。共享锁与其他共享锁兼容。
• 排他锁或 X 锁 - 排他锁也称为写锁。任何资源在任何时候只能有一个排他锁。排他锁与其他锁（包括共享锁）不兼容。
• 更新锁或 U 锁 - 更新锁可以看作是共享锁和排他锁的组合。更新锁用于在选择行进行更新之前锁定这些行，然后再实际更新。更新锁与共享锁兼容，但与其他更新锁不兼容。

有关锁类型的更多信息，请参阅以下链接

• http://msdn.microsoft.com/en-us/library/ms175519.aspx

正如我之前提到的，SQL Server 获取的锁类型取决于当前事务的隔离级别。我将简要进一步描述每个隔离级别。

读已提交隔离级别 – 这是 SQL Server 中新连接的默认隔离级别。这可以确保在事务中不会发生脏读。如果连接使用此隔离级别，并且在执行 DML 语句时遇到脏行，它将等待拥有该行的事务提交或回滚后，再继续执行。

读未提交隔离级别 - 虽然专家不建议使用，但最好也考虑一下。它可能会导致“脏读”，但如果使用得当，它可能会带来巨大的性能优势。

您应该只在脏读问题不构成问题的例程中使用此隔离级别。此类例程通常返回的信息不直接用作决策的基础。允许脏读的典型示例是仅在应用程序列表中（例如客户列表）使用的查询，或者数据库仅用于读取操作。

读未提交隔离级别是迄今为止性能最佳的隔离级别，因为它在读取其他事务修改过的数据时，不会等待其他连接完成其事务。在读未提交隔离级别中，读取操作不会获取共享锁；这就是允许脏读的原因。这一事实也减少了 SQL Server 锁管理器所需的工作量和内存。由于不获取共享锁，因此读取被排他锁锁定的资源不成问题。但是，当在读未提交隔离级别中执行查询时，会获取另一种称为“架构稳定性锁”(Sch-S) 的锁，以防止数据定义语言 (DDL) 语句更改表结构。下面是此隔离级别的行为示例。

可重复读隔离级别 - 在此隔离级别下，它保证在您的事务中不会发生脏读。它还可以确保如果您对具有相同 where 子句的同一表执行/发出两个 DML 语句，则两个查询都将返回相同的结果。但是，此隔离级别可以防止早期访问行的更新和删除，但不能防止插入，这就是所谓的“幻行”并发问题。请注意，使用聚合函数时也可能出现幻行，尽管不太容易检测。

序列化隔离级别 – 这可以保证上述任何并发问题都不会发生。它与“可重复读隔离级别”非常相似，只是它还可以防止“幻读”。但是，使用此隔离级别会比“可重复读”增加更多被阻塞事务和死锁的风险。然而，它会保证如果您对具有相同 WHERE 子句的同一表发出两个 DML 语句，这两个语句将返回完全相同的结果，包括相同数量的行数。为了保护事务免受插入的影响，SQL Server 需要使用共享锁来锁定 WHERE 子句中包含的列的索引范围。如果不存在这样的索引，SQL Server 将需要锁定整个表。

快照隔离级别 – 除了 SQL 的标准隔离级别外，SQL 2005 还引入了“快照隔离级别”。与“序列化隔离级别”一样，它可以防止上述所有并发问题。但其主要区别在于，它不是通过阻止其他事务访问行来实现的。它仅通过在事务处于活动状态时存储行的版本以及跟踪特定行何时被插入来实现。

为了说明这一点，我将使用一个测试数据库。它的名称是 'SampleDB'。首先，您必须在启用“快照隔离级别”之后才能使用它。

alter database SampleDB set allow_snapshot_isolation on;
alter database SampleDB set read_committed_snapshot off;

现在，我们将创建一个示例表并插入几条记录。

create table SampleIsolaion(
    id int,
    name varchar(20),
    remarks varchar(20) default ''
)

insert into SampleIsolaion (id,name,remarks)
select 1, 'Value A', 'Def' union
select 2, 'Value B', 'Def'

读已提交快照隔离级别 – 这可以被认为是“读已提交”隔离级别的新实现。当设置此选项时，它提供语句级别的读一致性，我们将在帖子中使用一些示例来展示这一点。使用此选项，读取操作不获取任何页锁或行锁（仅获取 SCH-S：架构稳定性锁），并通过读取 tempdb 中的数据来读取数据的版本。此选项通过 ALTER DATABASE 命令在数据库级别设置。

我将用一个示例来说明此隔离级别的用法。首先，启用所需的隔离级别。

alter database SampleDB set read_committed_snapshot on;
alter database SampleDB set allow_snapshot_isolation on;

现在，让我们创建一个表并填充一些示例数据。

create table sample_table(
    id int,
    descr varchar(20),
    remarks varchar(20)
)

insert into sample_table
select 1,'Val A','Def' union
select 2,'Val B','Def'

现在，在 SQL Server Management Studio 中打开两个查询窗口。

--Window 1
begin tran
    update sample_table set descr = 'Val P', remarks = 'Window 1' where id = 1

在不提交的情况下，在第二个窗口中执行以下操作

--Window 2
begin tran
    set transaction isolation level read committed    
    select * from sample_table

您可以看到，即使不提交，它也会从 tempdb 中创建的较旧行版本中读取。如果只有“读已提交”隔离级别而没有启用“读已提交快照”选项，那么这个 select 语句将会被锁定。