第十四周翻译

最新推荐文章于 2024-02-04 14:09:23 发布

sd990620

最新推荐文章于 2024-02-04 14:09:23 发布

阅读量166

点赞数

事务隔离级别
韦恩·谢菲尔德,2014/02/13
我最近在SQL Server Central上发布了文章锁定、阻塞和死锁。本文将继续讨论事务隔离级别，以及事务隔离级别的选择如何影响先前文章中讨论的锁定机制。
如果我们查看数据库引擎中的Books Online (BOL)主题隔离级别，我们可以看到事务隔离级别控件:
是否在读取数据时获取锁，以及请求什么类型的锁。
读取锁的保存时间。
引用另一个事务修改的行的读取操作:
直到行上的独占锁被释放为止。
检索语句或事务启动时存在的行提交版本。
读取未提交的数据修改。
注意，这些都只影响数据的读取。写入数据时获得的锁不受影响——仍然需要这些锁来保护数据修改。事务隔离级别控制如何保护读操作不受其他(写)操作的影响。
ISO隔离级别

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

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

、

READ_COMMITTED_SNAPSHOT是数据库级别的设置，如果打开它并提交事务隔离级别，那么它将使用行版本控制在语句开始时显示数据的事务一致视图。
快照隔离级别还利用行版本控制在语句开始时显示数据的事务一致视图。这要求打开ALLOW_SNAPSHOT_ISOLATION数据库设置，并让查询发出SET事务隔离级别快照语句。
在这两个快照隔离级别中，结果是读者不会阻止作者，作者不会阻止读者。此外，读者将不能阅读任何飞行数据修改从其他事务。
正如我已经提到的，这两种方法都使用行版本控制。在使用行版本控制时，SQL Server中的数据库引擎将维护受事务影响的行的版本。利用row-versioning将:
1 消除读取事务上的共享锁。
2 减少阻塞(读取事务)。
3 增加数据修改所需的资源。
4 增加tempdb中的活动(其中存储行版本控制信息)。
所有数据库数据修改都有行版本控制。
5 每个数据记录都有一个14字节的记录后缀。

并发性的影响
上面的图表提到了几种不同的并发副作用，让我们来解释一下它们。这些效应在BOL中定义为并发效应:
当第二个事务选择由另一个事务更新的行时，会发生脏读(在ISO中称为“未提交依赖项”)。当修改后的数据在另一个事务提交正在修改数据的事务之前被读取时，会发生脏读。如果要回滚该事务，则第二个事务刚刚返回了包含数据库中不存在的数据的行。通过阻止正在更改的数据的读取，可以避免这种影响。
当事务多次读取同一行时，就会发生不可重复读取(在ISO中称为“不一致分析”)，并且不同的读取之间的结果是不同的。当另一个事务修改并将更改提交给行时，就会发生这种情况。与脏读类似，不同之处在于，在不可重复读中，写事务已经成功提交了事务，而在脏读中，写事务被回滚。可以通过防止数据更改，直到完成数据读取，从而避免这种影响。
当读取数据的事务正在读取数据范围，而另一个事务插入或删除一行时，就会发生虚读。如果再次发出读取事务发出的语句，将返回额外的行(对于insert事务)，或者返回更少的行(对于delete事务)。通过防止在读取数据时插入或删除数据，可以避免这种影响。
缺失/双读发生在:
读取事务在索引扫描操作中读取一系列行，在读取行期间由第二个事务更新，从而更改索引键列，从而更改其在扫描中的位置。如果更新将一行从扫描结束移动到开始，则读取事务可能会错过该行;相反，如果更新将行从扫描的开始移动到末尾，那么该行可以被读取两次。
如果读取未提交隔离级别的读取事务执行分配顺序扫描(使用IAM页面)和另一个事务导致页拆分，则可以忽略读取事务读取的行。
当您阅读这些效果时，您应该能够看到，当您在努力防止任何这些并发效果时，您正在数据库中创建更多的锁定(从而可能产生更多的阻塞)。
并发效果的示例
让我们运行一些示例，看看这些不同的并发效果如何在不同的事务隔离级别中表现出来。所有这些示例都使用两个查询窗口;一个运行读事务，另一个运行写事务。查询使用“WAITFOR DELAY”给您一点时间来启动一个事务，然后切换到另一个查询窗口来运行另一个事务。
首先是数据库初始化代码。在运行每个测试之前，需要运行此代码。它被放入一个存储过程中，以便在必要时可以轻松运行。

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

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

3 从结果中可以看到，第二个查询立即返回，它返回随后在第一个查询窗口中回滚的值。
读过承诺
1 在read committed测试中，我们将重新运行这些语句。第二个查询窗口设置为使用已提交的事务隔离级别。因此，在第二个查询窗口中运行的select语句将不得不等到第一个事务完成(事务被提交或回滚)，然后才能读取数据——它被开放事务阻塞。
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:
更改查询窗口1以使用可重复读取隔离级别，并运行代码。
运行查询窗口2中的代码。
5 注意，查询窗口2中的insert立即运行，查询窗口1中的第二个select语句返回插入的行。
快照
我们所研究的读提交/未提交事务级别也存在丢失/双读的问题。可重复读/可串行隔离级别消除了这个问题，但这样做的代价是严重阻塞其他事务。快照隔离级别消除了与serializable隔离级别相同的并发性副作用，并且在不引入锁(因此消除了阻塞)的情况下也实现了这一点。在这个测试中，我们将首先显示快照隔离级别中没有阻塞，然后显示在使用serializable隔离级别时，如何阻塞这些相同的语句。
在第一个查询窗口中，运行以下语句:

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

3 注意，查询窗口2立即完成，但是数据修改没有反映在查询窗口1中。
4 如果您要更改查询窗口1以使用serializable隔离级别并重新运行测试，您将会看到查询窗口2现在将被阻塞，并且现在将等到查询窗口1完成后才能插入行。
NOLOCK查询提示如何适用呢?
表提示NOLOCK(与表提示READUNCOMMITTED相同)与指定设置的事务级别READUNCOMMITTED相同。您可以通过运行未提交的READ代码来看到这一点，而对于查询窗口2，则运行以下代码:

如果您决定实现快照隔离，并且您的当前代码正在使用NOLOCK(或READUNCOMMITTED)表提示，那么这些指定的提示将具有优先级——您将需要修改代码以获得利用快照隔离级别的好处。
总结
在ISO隔离级别中，当我们更改查询运行的隔离级别时，我们要么减少锁(但允许读取脏数据)，要么增加所涉及的锁以最小化并发性影响。快照隔离级别消除了所有并发影响，同时在读取事务上保持零阻塞，但是由于没有免费的东西，因此需要付出代价，即增加了tempdb活动，增加了用户数据库和tempdb中的存储空间需求。话虽如此，我认为，如果您使用的是read uncommitted(或nolock)，那么您应该切换到使用read committed snapshot隔离级别，以实现您试图为该查询实现的无阻塞。
引用
数据库引擎(BOL)中的隔离级别——http://technet.microsoft.com/en-us/library/ms189122%28v=sql.105%29.aspx
设置事务隔离级别(BOL)——http://technet.microsoft.com/en-us/library/ms173763% %28
并发效果(BOL)——http://technet.microsoft.com/en-us/library/ms190805%28v=sql.105% %29.aspx
行版本控制资源使用(BOL) - http://technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx