数据库



事务隔离LevelsBy WayneSheffield，2014/02/13I
最近在SQL Server Central上发表了一篇文章“锁定、阻塞和死锁”。本文从这里继续讨论事务隔离级别，以及事务隔离级别的选择如何影响前面文章中讨论的锁定机制。如果我们查看数据库引擎中的联机丛书(BOL)主题隔离级别，我们可以看到事务隔离级别控制：在读取数据时是否使用锁，以及需要何种类型的锁。使用锁。通过引用由另一个事务修改的行的读取操作：阻塞，直到该行上的独占锁被释放。检索语句或事务启动时存在的行的已提交版本。读取未提交的数据修改。请注意，所有这些都只影响数据的读取。写入数据时获得的锁不受影响-这些仍然是保护数据修改所必需的。事务隔离级别控制读取操作如何受到其他(写)操作的保护。
ISO隔离级别
下表显示了不同的ISO隔离级别，以及它们的并发性副作用：

当我们检查这个表时，我们可以看到不同的事务隔离级别是为了消除并发效应而设计的。SQLServer 2005添加了两个额外的事务隔离级别，这两个级别都处理利用快照：

READ_COMMIT_快照是数据库级别的设置，如果打开该设置并提交事务隔离级别，则将使用行版本控制在语句启动时显示数据的事务一致性视图。快照隔离级别还利用行版本控制在语句启动时显示数据的事务一致性视图。这需要打开ALLOASTSHOPASTION_ISOLATION数据库设置，查询要发出SET事务隔离级别快照语句，在这两个快照隔离级别上，结果都是读取器不阻塞写入器，而写入器不阻塞读取器。此外，读者将无法从其他事务中读取任何在途数据修改，正如我已经提到的，这两种方法都利用行版本控制。当使用行版本控制时，SQLServer中的数据库引擎将维护受事务影响的行的版本。

1利用行版本控制将：消除读事务上的共享锁。减少阻塞(在读事务上)。
2增加数据修改所需的资源。在temdb中增加活动(存储行版本控制信息)。
3所有数据库数据修改都将有行版本。每个数据记录将有一个14字节的记录后缀。
并发效应-上面的图表提到了几个不同的并发副作用，所以让我们解释一下其中的每一个。这些影响在BOL中被定义为并发效应：当第二个事务选择由另一个事务更新的行时，就会发生脏读(在ISO中称为“未提交的依赖项”)。当修改后的数据在另一个事务实际提交正在修改数据的事务之前被读取时，就会发生脏读。如果要回滚此事务，则第二个事务刚刚返回了一个包含数据库中不存在的数据的行。可以通过防止读取正在更改的数据来避免这种影响。当事务多次读取同一行时，就会发生不可重复的读取(在ISO中称为“不一致分析”)，并且不同读取之间的结果是不同的。当另一个事务修改并提交对行的更改时，就会发生这种情况。虽然类似于Dirtyread，但区别在于在不可重复读取中，写入事务成功地提交了事务，而在Dirtyread中，写入事务被回滚。在数据读取完成之前，可以通过防止数据的变化来避免这种影响。
当正在读取数据的事务正在读取一系列数据，而另一个事务插入或删除一行时，就会发生幻影读取。如果要再次发出由读取事务发出的语句，则将返回更多的行(用于插入事务)，或者返回的行较少(用于删除事务)。在读取数据时，可以通过防止事务插入或删除数据来避免这种影响。当：读取事务正在索引扫描操作中读取一系列行时，并且在读取期间一行被第二事务更新，更改索引键列，从而改变其在扫描中的位置时，就会发生误读/双读。如果更新将一行从扫描结束移动到开始，读取事务可能会忽略读取该行；反之，如果更新将行从扫描开始移动到末尾，则该行可以两次读取。如果读取未提交隔离级别中的读取事务正在执行分配顺序扫描(使用IAM页)，而另一个事务导致当您读取这些效果时，您应该能够看到，当您努力防止任何这些并发效果时，您将在数据库中创建更多的锁定(从而可能造成更多阻塞)。
并发效果的示例-让我们运行一些示例，看看这些不同的并发效果是如何在不同的事务隔离级别中表现出来的。所有这些示例都通过使用两个查询窗口来工作；一个是运行读取事务，另一个是运行写事务。

查询利用“WAITFORDERE”来给您一点时间来启动一个事务，然后切换到另一个查询窗口来运行另一个查询窗口，首先是数据库初始化代码。此代码需要在运行每个测试之前运行。正在将其放入存储过程中，以便在必要时方便地运行。
读未提交在读未提交隔离级别，我们将查看如何允许脏读。这将通过在一个正在执行更新的查询窗口中启动一个事务来执行，并在第二个查询窗口中同时在读未提交事务隔离级别上运行一个SELECT语句，以便该查询将读取被修改的数据。一段时间后，第一个查询窗口中的事务将被回滚。
您将看到第二个查询窗口返回了从未提交到该表的数据，在第一个查询窗口中，运行以下语句：

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

从结果中可以看到，第二个查询立即返回，并返回随后在第一个查询窗口中回滚的值。
在Read提交的测试中，我们将重新运行这些语句。第二个查询窗口被设置为使用已提交的事务隔离级别。因此，运行在第二个查询窗口中的SELECT语句必须等到第一个事务完成(事务已提交或回滚)之后才能读取数据-它正在被打开的事务阻塞。在第一个查询窗口中，运行以下语句：

请注意，查询窗口2等待查询窗口1完成，因为查询窗口1处于可重复读取状态。Rerun步骤1-3：更改查询窗口1以使用读取提交的隔离级别，并运行代码。

在查询窗口2中运行代码。注意查询窗口2立即完成，查询窗口1中的第二个SELECT语句返回来自第一个SELECT语句的不同结果。
在刚才执行的可重复读取测试中，我们看到了如何防止对数据的更新。可序列化的隔离级别进一步提高了这一步，还防止了对此表进行插入或删除。为了测试这一点，我们将从可重复读取中重新运行测试，并将隔离级别更改为可序列化，并尝试执行INSERT而不是更新。然后，我们将在可重复读取隔离级别上运行此测试，展示如何允许INSERT运行，在第一个查询窗口中，运行以下语句：

请注意，查询窗口2中的插入等待到查询窗口1中的事务完成为止。Rerun步骤1：更改查询窗口1以使用可重复读取隔离级别，并运行代码。在查询窗口2中运行代码。请注意查询窗口2中的INSERT立即运行，查询窗口1中的第二个SELECT语句返回插入的row.Snapail已提交/未提交的事务级别。我们已经看过的，也有丢失/双读的问题。可重复读取/可序列化隔离级别消除了此问题，但在严重阻塞其他事务的情况下进行此操作。快照隔离级别消除了与可序列化隔离级别相同的并发副作用，此外，在不引入锁定(从而消除阻塞)的情况下也会这样做。在这个测试中，我们将首先显示快照隔离级别中没有阻塞，然后显示在使用可序列化隔离级别时这些相同的语句是如何被阻塞的。在第一个查询窗口中，运行以下语句

请注意，查询窗口2立即完成，但查询窗口1中没有反映数据修改。如果要更改查询窗口1以利用可序列化的隔离级别并重新运行测试，则会看到查询窗口2现在将被阻塞，并等待查询窗口1完成后才能插入行。NOLOCK查询提示如何适合它？表提示NOLOCK(与表提示READUNCOMMITTED相同)与指定SET事务级别的READUNCOMMITTED相同。您可以通过运行Read Unitted的代码和查询窗口2的代码来看到这一点，然后运行以下代码：

如果您决定实现快照隔离，并且您的当前代码正在使用NOLOCK(或READUNCOMMITTED)表提示，则这些指定的提示将具有优先级-您将需要更改代码以获得使用快照隔离级别的好处。
总之，在ISO隔离级别下，当我们更改查询运行的隔离级别时，我们要么减少锁(但允许读取脏数据)，要么增加所涉及的锁定以最小化并发效应。快照隔离级别消除了所有并发效果，同时在读取事务上保持零阻塞，但由于没有任何东西是免费的，因此您要付出的代价是增加了temdb活动并增加了对用户数据库和temdb中存储空间的需求。That being said，I feel that if you are using read uncommitted(or nolock)，that you should switch to using the read committed snapshot isolation level instead to achieve the no blocking you are trying to achieve for that query.ReferencesIsolation Levels in the Database Engine(BOL)-http：//technet.microsoft.com/en-us/library/ms189122%28v=sql.105%29.aspxSET TRANSACTION ISOLATION LEVEL(BOL)-http：//technet.microsoft.com/en-us/library/ms173763%28v=sql.105%29.aspxConcurrency Effects(BOL)-http：/technet.microsoft.com/en-us/library/ms190805%28v=sql.105%29.aspxRow Versioning Resource Usage(BOL)-http：//technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx