Transaction Isolation Levels

事务隔离级别
作者 韦恩·谢菲尔德 2014/02/13
我最近在SQL Server Central上发表了文章锁定、阻塞和死锁本文将继续讨论事务隔离级别，以及事务隔离级别的选择如何影响前面文章中讨论的锁定机制。
如果我们看一下数据库引擎中的在线（BOL）主题隔离级别，我们可以看到一个事务隔离级别控制：
1、是否在读取数据时获取锁，以及请求何种类型的锁。
2、读锁被保存了多长时间。
3、是否读操作引用另一个事务修改的行：
（1）直到该行的独占锁被释放为止。
（2）检索声明或交易开始时存在的行的提交版本。
（3）读取未提交的数据修改。
请注意，这些都只影响数据的读取。在编写数据时获得的锁不受影响——这些锁仍然需要保护数据修改。事务隔离级别控制如何保护读操作免受另一个（写）操作的保护。
ISO隔离级别
下表显示了不同的ISO隔离级别，以及它们的并发副作用：

当我们检查这个表时，我们可以看到不同的事务隔离级别被设计来消除并发性的影响。
SQL Server 2005增加了两个额外的事务隔离级别，这两个级别都处理快照：

读取提交的快照是一个数据库级的设置，如果它被打开并且事务隔离级别被读取，那么它将使用行版本控制在语句开始时呈现一个事务一致的数据视图。
快照隔离级别还利用行版本控制，在语句开始时呈现数据的事务一致性视图。这就要求开启允许快照隔离数据库设置，并要求查询发出SET事务隔离级别快照语句。
在这两种快照隔离级别中，其效果是读者不会阻塞作者，而作者也不会屏蔽读者。此外，读者将无法从其他事务中读取任何动态的数据修改。
正如我已经提到的，这两种方式都利用了行版本控制。当使用行版本控制，SQL Server中的数据库引擎将维护被交易额影响的行的版本。利用row-versioning将:
1、消除读取事务上的共享锁。
2、减少阻塞（在读取事务上）。
3、增加数据修改所需的资源。
4、增加临时数据库的活动（存储行版本信息的地方）。
a.所有的数据库数据修改都将有行版本控制。
5、每个数据记录将有一个14字节的记录后缀。
并发性的影响
上面的图表提到了几种不同的并发性副作用，所以让我们解释一下其中的每一个。这些效果在BOL中被定义为并发效果：
脏读（在ISO中称为“未提交依赖性”）发生在第二个事务选择由另一个事务更新的行时。当修改的数据被读取之前，另一个事务实际上提交了修改数据的事务，就会出现脏读数。如果这个事务被回滚，那么第二个事务就会返回一个与数据库中不存在的数据的行。通过防止读取正在被更改的数据，可以避免这种影响。
不可重复读（在ISO中称为“不一致分析”）发生在一个事务多次读取同一行时，结果在不同的读取之间是不同的。这可能发生在另一个事务修改并提交到一行更改时。虽然与脏读相似，但不同之处在于，在不可重复的读中，写事务成功地提交了事务，而在脏读中，写事务被回滚。在数据读取完成之前，可以避免数据的变化，从而避免这种影响。
幻读出现于当读取数据的事务读取一系列数据时，而另一个事务则插入或删除一行。如果读取事务发出的语句再次被发出，则会有额外的行返回（对于插入事务），或者返回的行数减少（对于删除事务）。可以通过防止在读取数据时插入或删除数据来避免这种影响。
缺失/双读发生在：
读取事务是在索引扫描操作中读取一系列行的行，并且在读取期间由第二个事务更新，改变索引键列（s），从而改变它在扫描中的位置。如果更新将一行从扫描的末尾移动到开始，那么读取事务就会错过读取该行；相反，如果更新从扫描的开始移动到结束，那么这一行可以被读取两次。
如果读未提交隔离级别的读取事务执行分配顺序扫描（使用IAM页面），另一个事务导致页面分割，读取事务可以忽略各行。
当您阅读这些效果时，您应该能够看到，当您努力防止这些并发性影响时，您正在数据库中创建更多的锁定（从而可能导致更多的阻塞）。
并发效果的例子
让我们运行一些示例，看看这些不同的并发效果如何在不同的事务隔离级别中表现出来。所有这些示例都使用两个查询窗口;其中一个将运行读事务，而另一个运行写事务。这些查询使用“等待延迟”来给您一点时间来启动一个事务，然后切换到另一个查询窗口来运行另一个事务。
首先是数据库初始化代码。这段代码需要在运行每个测试之前运行。它被放入一个存储过程中，以便在必要时可以轻松地运行。
IF DB_ID(‘IsolationLevelTest’) IS NOT NULL BEGIN
USE IsolationLevelTest;
ALTER DATABASE IsolationLevelTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master;
DROP DATABASE IsolationLevelTest;
END;
CREATE DATABASE IsolationLevelTest;
GO
USE IsolationLevelTest;
GO
CREATE PROCEDURE dbo.db_reset AS
IF OBJECT_ID(‘dbo.IsolationTests’,’U’) IS NOT NULL DROP TABLE dbo.IsolationTests; CREATE TABLE dbo.IsolationTests (
Id INTEGER IDENTITY,
ColA CHAR(1));
INSERT INTO dbo.IsolationTests(ColA)
SELECT ‘A’ UNION ALL
SELECT ‘A’ UNION ALL
SELECT ‘A’ UNION ALL
SELECT ‘A’ UNION ALL
SELECT ‘A’ UNION ALL
SELECT ‘A’ UNION ALL
SELECT ‘A’;
SELECT *
FROM dbo.IsolationTests;
IF EXISTS (SELECT 1 FROM sys.databases WHERE database_id = DB_ID(‘IsolationLevelTest’) AND snapshot_isolation_state = 1)
ALTER DATABASE IsolationLevelTest SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO EXECUTE dbo.db_reset;
GO
读未提交
在阅读未提交的隔离级别，我们将查看如何允许读取脏读数。这将通过在执行更新的一个查询窗口中启动一个事务来执行，同时，在第二个查询窗口中，在read未提交事务隔离级别上运行select语句，以便查询将读取正在修改的数据。一段时间后，第一个查询窗口中的事务回滚。您将看到第二个查询窗口返回了从未提交给该表的数据。
1、在第一个查询窗口中，运行以下语句：

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

3、正如您从结果中看到的，第二个查询立即返回，它返回随后在第一个查询窗口中回滚的值。
读取命令
在“读提交”测试中，我们将重新运行这些语句。第二个问询窗口被设置为使用读取提交的事务隔离级别。因此，在第二个查询窗口中运行的select语句将不得不等待第一个事务完成（交易要么提交，要么回滚），然后才能读取数据——它将被公开事务阻塞。
1.在第一个查询窗口中，运行以下语句：

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

3.正如您所看到的，查询窗口2中的语句必须等待查询窗口1中的事务完成才能运行，并且查询窗口2在查询窗口1完成后返回表中的值。
可重复读取
对于下一个隔离级别，可重复阅读，我们将展示如何在这个隔离级别中读取来自表的数据两次，在读取之间的一段时间内，将需要返回相同的数据。在这种隔离级别下，它必须读取所读取行的数据的完全相同的数据，因此它将阻塞另一个试图更新这些行的事务。然后，我们将把这个从可重复读改为读提交，以显示允许更新运行的效果。
1.在第一个查询窗口中，运行以下语句：

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

3.请注意，查询窗口2等待查询窗口1完成，因为查询窗口1是可重复读取的。
4.重新运行步骤1 - 3:
a.改变查询窗口1，以使用读取提交的隔离级别，并运行代码。
b.在查询窗口2中运行代码。
5.请注意，查询窗口2立即完成，并且在query window 1中第二个select语句返回了来自第一个select语句的不同结果。

可序列化的
在刚刚执行的可重复阅读测试中，我们看到了如何防止对数据的更新。可串行化隔离级别更进一步，并且防止插入或删除发生在该表中。为了测试这一点，我们将本质上从可重复读取重新运行测试，我们将把隔离级别更改为serializable，并尝试执行插入而不是更新。然后，我们将在可重复读隔离级别上运行这个测试，显示它如何允许插入运行。
1.在第一个查询窗口中，运行以下语句：

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

3.请注意，查询窗口2中的insert等待，直到查询窗口1中的事务完成为止。
4.重新运行步骤1 - 3:
a.改变查询窗口1，以使用可重复读取隔离级别，并运行代码。
b.在查询窗口2中运行代码。
5.请注意，查询窗口2中的插入立即运行，查询窗口1中的第二个select语句返回所插入的行。

快照
我们所研究的读提交/未提交的事务级别也存在丢失/双读的问题。可重复读/可串行化隔离级别消除了这个问题，但是在严重阻塞其他事务的情况下这样做。快照隔离级别消除了serializable隔离级别所做的所有并发副作用，并且在不引入锁定（从而消除阻塞）的情况下做到这一点。在这个测试中，我们将首先展示在快照隔离级别中缺少阻塞，然后展示在使用serializable隔离级别时如何阻塞这些语句。
1.在第一个查询窗口中，运行以下语句：

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

3.请注意，查询窗口2立即完成，但是数据修改没有反映在查询窗口1中。
4.如果您要更改问询窗口1以利用可串行化隔离级别并重新运行测试，您将看到查询窗口2将被阻塞，并将等待查询窗口1完成，然后才能插入该行。

NOLOCK查询提示如何适合它？
表提示NOLOCK（与表提示read未提交相同）与指定设定事务级别read未提交相同。您可以通过运行阅读未提交的代码来查看这一点，而查询窗口2则运行以下代码：

如果您决定实现快照隔离，并且您当前的代码正在使用NOLOCK（或read提交）表提示，这些指定的提示将具有优先级——您将需要更改代码以获得利用快照隔离级别的好处。
总结
在ISO隔离级别下，当我们改变一个查询在远离SQL Server默认值的情况下运行的隔离级别时，我们要么减少锁（但是允许读取脏数据），要么增加锁，以最小化并发性的影响。快照隔离级别消除了所有并发性的影响，同时在读取事务上保持零阻塞，但是由于没有任何东西是免费的，所以您需要在用户数据库和tempdb中增加tempdb活动和增加存储空间需求来支付价格。话虽如此，但我认为，如果您正在使用读未提交（或nolock），您应该切换到使用读提交的快照隔离级别，而不是实现您试图为该查询实现的无阻塞。