DATABASE SYSTEM CONCEPTS6.10

事务隔离级别

 

 由韦恩谢菲尔德，2014/02/13

 

 

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

 

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

•读取数据时是否进行锁定，以及请求何种类型的锁。

•读锁持续了多长时间。

•引用由另一个事务修改的行的读操作：◦阻塞，直到该行的排它锁被释放。

◦检索在语句或事务开始时存在的行的提交版本。

◦读取未提交的数据修改。

 

 

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

 

ISO隔离级别

 

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

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

 

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

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

 

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

 

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

 

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

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

2.减少阻塞（读取事务）。

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

4.增加tempdb中的活动（存储行版本信息）。一个。所有数据库数据修改都将具有行版本。

 

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

 

并发效果

 

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

•当第二个事务选择另一个事务正在更新的行时，发生脏读（在ISO中称为“未提交的相关性”）。在另一个事务实际上提交正在修改数据的事务之前正在读取修改的数据时，会发生脏读。如果这个事务被回滚，那么第二个事务刚刚返回一行数据库中不存在的数据。通过防止读取正在改变的数据可以避免这种影响。

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

•正在读取数据的事务正在读取一系列数据，而另一个事务插入或删除一行时发生幻像读取。如果读取事务发出的语句再次发布，则会返回额外的行（对于插入事务）或返回更少的行（对于删除事务）。通过防止事务在读取数据时插入或删除数据，可以避免这种影响。

•在下列情况下发生丢失/双重读取：◦读取事务正在读取索引扫描操作中的一系列行，并且在读取期间，行由第二个事务更新，将索引键列更改为其位置扫描。如果更新从扫描结束开始移动一行，读取事务可能会错过读取该行;相反，如果更新将行从扫描开始移动到结束，那么该行可以被读取两次。

◦如果READ UNCOMMITTED隔离级别中的读取事务正在执行分配顺序扫描（使用IAM页面）并且另一个事务导致分页，则可能会错过由读取事务读取的行。

 

 

在您仔细阅读这些效果时，您应该能够看到，在您工作以防止出现任何这些并发效应时，您正在数据库中创建更多锁定（并因此可能产生更多的阻塞）。

 

并发效应的例子

 

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

 

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

 

阅读未提交

 

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

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

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

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

 

阅读已提交

 

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

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

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

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

 

可重复阅读

 

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

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

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

3.请注意，查询窗口2等待查询窗口1结束，因为查询窗口1处于可重复读取状态。

4.重新运行步骤1-3：a。将查询窗口1更改为使用READ COMMITTED隔离级别，然后运行代码。

湾在查询窗口2中运行代码。

 

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

 

序列化

 

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

1. 在第一个查询窗口中，运行这些语句

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

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

4.重新运行步骤1-3：a。更改查询窗口1以使用REPEATABLE READ隔离级别，然后运行代码。

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

 

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

 

快照

 

我们看过的读取已提交/未提交的事务级别也存在丢失/双重读取的问题。可重复读/序列化隔离级别消除了这个问题，但是在严重阻塞其他事务的处罚下这样做。快照隔离级别消除了与可序列化隔离级别相同的所有相同并发副作用，并且在不引入锁定的情况下执行此操作（从而消除阻塞）。在这个测试中，我们将首先显示快照隔离级别中没有阻塞，然后展示如何在使用可序列化隔离级别时阻止这些相同的语句。

1.在第一个查询窗口中，运行这些语句

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

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

4.如果要更改查询窗口1以利用可序列化隔离级别并重新运行测试，则会看到查询窗口2现在将被阻塞，现在将等待查询窗口1完成，然后才能插入该行。

 

NOLOCK查询提示如何适合它？

 

2. 表提示NOLOCK（与表提示READUNCOMMITTED相同）与指定SET TRANSACTION LEVEL READUNCOMMITTED相同。您可以通过运行READ UNCOMMITTED的代码来查看该代码，而对于查询窗口2，请运行以下代码：

3. 如果您决定实施快照隔离，并且当前代码正在使用NOLOCK（或READUNCOMMITTED）表提示，那么这些指定的提示将具有优先权 - 您将需要更改代码以获得利用快照隔离级别的好处。

4. 

5. 概要

6. 

7. 在ISO隔离级别下，当我们改变查询运行的隔离级别而不是SQL COMMERCE的SQL Server默认级别时，我们要么减少锁定（但允许读取脏数据），要么增加锁定以尽量减少并发效应。快照隔离级别消除了所有并发性影响，同时在读取事务中保持零阻塞，但由于没有任何东西可以免费使用，因此可以通过增加tempdb活动和增加用户数据库和tempdb中的存储空间要求来支付代价。这就是说，我觉得如果你使用read未提交（或nolock），你应该切换到使用读取提交的快照隔离级别来实现你试图实现的那个查询。

8. 

9. 参考

10. •数据库引擎中的隔离级别（BOL） - http://technet.microsoft.com/zh-cn/library/ms189122%28v=sql.105%29.aspx

11. •SET TRANSACTION ISOLATION LEVEL（BOL） - http://technet.microsoft.com/zh-cn/library/ms173763%28v=sql.105%29.aspx

12. •并发效果（BOL） - http://technet.microsoft.com/en-us/library/ms190805%28v=sql.105%29.aspx

13. •行版本控制资源使用（BOL） - http://technet.microsoft.com/zh-cn/library/ms175492%28v=sql.105%29.aspx