第十四周翻译（Transaction Isolation Levels）



事务隔离级别

最近，我在SQL Server Central上发布了锁定和死锁的文章。本文继续讨论事务隔离级别，以及事务隔离级别的选择如何影响前面文章中前面讨论的锁定机制。

如果我们看一下数据库引擎中的联机丛书（BOL）主题隔离级别，我们可以看到事务隔离级别控制着：

数据读取时是否进行锁定，以及请求何种类型的锁。 读锁持续了多长时间。 是否引用由另一个事务修改的读操作： 阻塞，直到该行的排它锁被释放。 检索在语句或事务开始时存在的行的提交版本。 读取未提交的数据修改。

注意这些都只影响数据的读取。写入数据时获得的锁不受影响 - 仍然需要这些锁来保护数据修改。事务隔离级别控制读操作如何受到其他（写）操作的保护。

ISO隔离级别

下表显示了不同的ISO隔离级别以及它们的并发副作用：

 

在我们检查这个表时，我们可以看到不同的事务隔离级别被设计用来消除并发效应。

SQL Server 2005增加了两个额外的事务隔离级别，这两个级别处理利用快照：

 

READ_COMMITTED_SNAPSHOT是一个数据库级设置，如果它打开并且提交事务隔离级别，那么它将使用行版本控制在语句启动时呈现数据的事务一致视图。

快照隔离级别还利用行版本控制在语句启动时呈现数据的事务一致视图。这要求打开ALLOW_SNAPSHOT_ISOLATION数据库设置，并使查询发出SETTRANSACTION ISOLATION LEVEL SNAPSHOT语句。

在这两种快照隔离级别中，效果是读者不会阻止作者，作者也不会阻止读者。此外，读者将无法阅读任何来自其他交易的飞行数据修改。

正如我已经提到的，这两个都利用行版本控制。使用行版本控制时，SQL Server中的数据库引擎将维护受事务影响的行版本。利用行版本化将会：

消除读取事务的共享锁定。 减少阻塞（读取事务）。 增加数据修改所需的资源。 增加tempdb中的活动（存储行版本信息的位置）。 所有数据库数据修改都将具有行版本。 每个数据记录都会附加一个14字节的记录后缀。

 

 

 

并发效应              

上面的图表提到了几种不同的并发副作用，所以让我们来解释其中的每一个。这些效应在BOL中被定义为并发效应：             

脏读取（在ISO中称为“未提交依赖项”）发生在第二个事务选择另一个事务正在更新的行时。当其他事务实际提交修改数据的事务之前，正在读取修改后的数据时会出现脏读取。如果要回滚该事务，则第二个事务刚刚返回了一个行，该行中存在不存在于数据库中的数据。可以通过防止读取正在改变的数据来避免这种影响。

不可重复读取（在ISO中称为“不一致分析”）发生在事务多次读取同一行时，不同读取之间的结果不同。当另一个事务修改并提交对行的更改时，可能发生这种情况。虽然类似于脏读取，但不同之处在于，在不可重复读取中，写入事务已成功提交事务，而在脏读取中，写入事务被回滚。通过防止数据的变化，直到数据读完为止，可以避免这种影响。             

当读取数据的事务正在读取数据范围时，幻读发生，另一事务插入或删除行。如果将再次发布读事务的语句，则将返回（插入事务）的额外行，或返回的行数较少（对于删除事务）。通过防止事务在读取数据时插入或删除数据，可以避免这种影响。

丢失/双重读取发生在：              

读事务是在索引扫描操作中读取行的范围，并且在读取期间，通过第二事务更新行，改变索引键列，从而改变其在扫描中的位置。如果更新将行从扫描结束移动到开始，则读取事务可能错过读取该行；反之，如果更新从扫描的开始移动到结束，则该行可以被读取两次。            

如果读未提交隔离级别中的读取事务正在执行分配顺序扫描（使用IAM页），而另一事务导致页拆分，则读取事务无法读取行。             

当您阅读这些效果时，您应该能够看到，当您工作以防止这些并发效应时，您在数据库中创建了更多的锁定（从而潜在地阻止更多的阻塞）。

并发效应的示例             

让我们运行一些示例，看看这些不同的并发效应如何在不同的事务隔离级别中表现出来。所有这些示例都是通过使用两个查询窗口来工作的：一个将运行一个读事务，另一个运行写事务。查询利用“WAITFOR DELAY”给你一点时间来启动一个事务，并切换到另一个查询窗口来运行另一个查询窗口。             

首先是数据库初始化代码。此代码需要在运行每个测试之前运行。它被放入一个存储过程中，以便可以在必要时轻松运行。

 

 

未提交读             

在未读的隔离级别中，我们将查看如何允许脏读取。这将通过在正在执行更新的一个查询窗口中启动事务，并在读取未提交事务隔离级别中运行SELECT语句的第二查询窗口中执行，以便查询将读取正在修改的数据。经过一段时间后，第一个查询窗口中的事务被回滚。您将看到第二个查询窗口返回了从未提交给表的数据。             

在第一个查询窗口中运行这些语句：

 

在第二个查询窗口中运行这些语句：

 

从结果中可以看出，第二个查询立即返回，并返回在第一个查询窗口中回滚的值。

 

 

已提交读

在已提交读测试中，我们将重新运行这些语句。第二个查询窗口设置为使用已提交的事务隔离级别。因此，在第二个查询窗口中运行的select语句必须等到第一个事务完成(事务被提交或回滚)之后才能读取数据——它被打开的事务阻塞。

在第一个查询窗口中运行这些语句：

 

在第二个查询窗口中运行这些语句：

 

从结果中可以看出，查询窗口2中的语句必须等待查询窗口1中的事务完成后才能运行，查询窗口2在查询窗口1完成后返回表中的值。

 

可重复读取

对于下一个可重复读取的隔离级别，我们将展示在这个隔离级别上的事务如何返回相同的数据，该事务正在从表中读取数据两次，每次读取间隔一段时间。在这个隔离级别下，它必须为被读取的行读取完全相同的数据，因此它将阻塞第二个试图更新其中一些行的事务。然后，我们将从可重复读取更改为已提交读取，以显示允许更新运行的效果。

 

在第一个查询窗口中运行这些语句：

 

在第二个查询窗口中运行这些语句：

 

注意，查询窗口2一直等到查询窗口1完成，因为查询窗口1是可重复读取的。

重新运行步骤1 - 3:

更改查询窗口1以使用已提交的隔离级别，并运行代码。

运行查询窗口2中的代码。

注意查询窗口2立即完成，在查询窗口1中第二个select语句从第一个select语句返回不同的结果。

 

可序列化的

在刚刚执行的可重复读测试中，我们看到如何防止对数据的更新。可序列化的隔离级别进一步实现了这一点，并防止对该表进行插入或删除。为了测试它，我们将从可重复读取中重新运行测试，并将隔离级别更改为可序列化，并尝试执行插入操作而不是更新。然后，我们将在可重复读隔离级别中运行这个测试，展示如何允许插入运行。

在第一个查询窗口中运行这些语句：

 

在第二个查询窗口中运行这些语句：

 

请注意，查询窗口2中的插入要等待查询窗口1中的事务完成。

重新运行步骤1 - 3:

更改查询窗口1以使用可重复读取隔离级别，并运行代码。

运行查询窗口2中的代码。

注意，查询窗口2中的insert立即运行，查询窗口1中的第二个select语句返回插入的行。

快照

我们所研究的读提交/未提交事务级别也存在丢失/双读的问题。可重复读/可串行隔离级别消除了这个问题，但这样做的代价是严重阻塞其他事务。快照隔离级别消除了与serializable隔离级别相同的并发性副作用，并且在不引入锁(因此消除了阻塞)的情况下也实现了这一点。在这个测试中，我们将首先显示快照隔离级别中没有阻塞，然后显示在使用serializable隔离级别时，如何阻塞这些相同的语句。

在第一个查询窗口中，运行以下语句:

 

在第二个查询窗口中运行这些语句：

 

注意，查询窗口2立即完成，但是数据修改没有反映在查询窗口1中。

如果您要更改查询窗口1以使用serializable隔离级别并重新运行测试，您将会看到查询窗口2现在将被阻塞，并且现在将等到查询窗口1完成后才能插入行。

 

NOLOCK查询提示如何适用呢?

 

表提示NOLOCK(与表提示READUNCOMMITTED相同)与指定设置的事务级别READUNCOMMITTED相同。您可以通过运行未提交的READ代码来看到这一点，而对于查询窗口2，则运行以下代码:

 

如果您决定实现快照隔离，并且您的当前代码正在使用NOLOCK(或READUNCOMMITTED)表提示，这些指定的提示将具有优先级——您将需要更改代码以获得利用快照隔离级别的好处。

总结

在ISO隔离级别中，当我们更改查询运行的隔离级别时，我们要么减少锁(但允许读取脏数据)，要么增加所涉及的锁以最小化并发性影响。快照隔离级别消除了所有并发影响，同时在读取事务上保持零阻塞，但是由于没有免费的东西，因此需要付出代价，即增加了tempdb活动，增加了用户数据库和tempdb中的存储空间需求。话虽如此，我认为，如果您使用的是未提交读(或nolock)，那么您应该切换到使用已提交读隔离级别，以实现您试图为该查询实现的无阻塞。