翻译：Transaction Isolation Levels

最新推荐文章于 2020-04-17 18:50:15 发布

emmm00

最新推荐文章于 2020-04-17 18:50:15 发布

阅读量171

点赞数

ISO隔离级别

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

当我们检查这个表时，我们可以看到不同的事务隔离级别被设计为消除并发性影响。

SQL Server 2005增加了两个额外的事务隔离级别，这两个级别都处理使用快照的问题:

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

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

在这两个快照隔离级别中，结果是读者不会阻止作者，作者不会阻止读者。此外，读者将不能阅读任何飞行数据修改从其他事务。

正如我已经提到的，这两种方法都使用行版本控制。在使用行版本控制时，SQL Server中的数据库引擎将维护受事务影响的行的版本。利用row-versioning将:

1. 消除读取事务上的共享锁。

2. 减少阻塞(读取事务)。

3. 增加数据修改所需的资源。

4. 增加tempdb中的活动(其中存储行版本控制信息)。

a.所有数据库数据修改都有行版本控制。

5. 每个数据记录都有一个14字节的记录后缀。

并发性的影响

上面的图表提到了几种不同的并发副作用，让我们来解释一下它们。这些效应在BOL中定义为并发效应:

脏读”(在ISO中称为“不提交依赖”)发生在第二个事务选择正在被另一个事务更新的行时。当修改后的数据在另一个事务提交正在修改数据的事务之前被读取时，会发生脏读。如果要回滚该事务，则第二个事务只返回与数据库中不存在的数据的行。通过防止读取正在更改的数据，可以避免这种影响。

当事务多次读取同一行时，就会发生不可重复读取(在ISO中称为“不一致分析”)，并且不同的读取之间的结果是不同的。当另一个事务修改并将更改提交给行时，就会发生这种情况。与脏读类似，不同之处在于，在不可重复读中，写事务已经成功提交了事务，而在脏读中，写事务被回滚。可以通过防止数据更改，直到完成数据读取，从而避免这种影响。

当读取数据的事务正在读取数据范围，而另一个事务插入或删除一行时，就会发生虚读。如果再次发出读取事务发出的语句，将返回额外的行(对于insert事务)，或者返回更少的行(对于delete事务)。通过防止事务在读取数据时插入或删除数据，可以避免这种影响。

缺失/双读发生在:

² 读取事务在索引扫描操作中读取一系列行，在读取行期间由第二个事务更新，从而更改索引键列，从而更改其在扫描中的位置。如果更新将一行从扫描结束移动到开始，则读取事务可能会错过该行;相反，如果更新将行从扫描的开始移动到末尾，那么该行可以被读取两次。

² 如果读未提交隔离级别中的读取事务正在执行分配顺序扫描(使用IAM页面)，而另一个事务导致页面分割，则读取事务可能会错过正在读取的行。

当您阅读这些效果时，您应该能够看到，当您在努力防止任何这些并发效果时，您正在数据库中创建更多的锁定(从而可能产生更多的阻塞)。

并发效果的示例

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

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

"未提交"读

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

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

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

3. 从结果中可以看到，第二个查询立即返回，它返回随后在第一个查询窗口中回滚的值。

"已提交"读

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

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

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

3.如您所见，查询窗口2中的语句必须等待查询窗口1中的事务完成后才能运行，查询窗口2在查询窗口1完成后返回表中的值。

可重复读

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

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

2.在第二个查询窗口中，运行以下语句

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

4.重新运行步骤1 - 3:

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

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

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

可序列化的

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

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

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

3.注意，查询窗口2中的插入将等待直到查询窗口1中的事务完成。

4.重新运行步骤1 - 3:

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

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

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

快照

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

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

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

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

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

NOLOCK查询提示如何适用呢?

表提示NOLOCK(与表提示READUNCOMMITTED相同)与指定设置的事务级别READUNCOMMITTED相同。您可以通过运行READ UNCOMMITTED代码看到这一点，而对于query window 2，您可以运行以下代码:

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

总结

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