关于SQLServer事务隔离级别控制知识探讨

事务隔离级别

我最近在sqlservercentral上发表了一篇文章：锁定、阻塞和死锁。本文将从这里继续讨论事务隔离级别，以及是如何进行选择的。在事务隔离级别上，会影响前面文章中讨论的锁定机制。如果我们查看引擎中的联机丛书(bol)主题隔离级别，我们将看到事务隔离级别控制：

1.读取数据时是否使用锁，以及请求的锁类型。

2.如何保持读锁的时间。

3.读取操作是否引用由另一个事务修改的行：

1.阻止直到释放行上的独占锁为止。

2.检索语句或事务启动时存在的行的已提交版本。

3.读取未提交的数据修改。

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

iso隔离级别

下表显示了不同的iso隔离级别，以及它们的并发性副作用（？？？）

隔离级别	脏读？	不重读？	幻读？	误读、重读？
未提交读	是	是	是	是
已提交读	否	是	是	是
重复读	否	否	是	否
可串行读	否	否	否	否

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

sqlserver 2005添加了两个额外的事务隔离级别，这两个级别都处理利用快照：

隔离级别	脏读？	不重读？	幻读？	误读？重读？
已提交读快照	否	否	否	否
快照	否	否	否	否

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

快照隔离级别还使用行版本控制在被打开时显示数据的事务一致性视图。这需要打开allow_shopasth_isolation数据库设置，并要求查询发出set事务隔离级别快照语句。

在两种快照的隔离级别里，其效果是读者不锁定制作人，制作人也不锁定读者。此外，读取器将无法从其他读取任何在轨数据或修改。

正如我已经提到的，这两种方法都利用行版本控制。当使用行版本控制时，sqlserver中的数据库引擎将维护受处理结果的影响的行（？？）的版本。利用行版本控制将：

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

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

3.增加数据修改所需的资源。增加temdb中的活动(行版本控制通知)。

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

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

并发效应

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

脏读(引用在iso中，ed作为“未提交的依赖项”出现，当第二个事务选择由另一个事务更新的行时发生。当修改后的数据在处理之前读取时，就会发生脏读取。因为其他事务实际上提交了正在修改数据的事务。如果要回滚此事务，则第二个事务刚刚返回了一行，其中包含的数据没有，数据库中不存在。可以通过防止读取正在更改的数据来避免这种影响。

不重读当数据处理发生时，将发生不可重复的读取(在iso中称为“不一致分析”)。 对同一行进行多次读取，不同读取之间的结果不同。当另一个事务修改并提交对行的更改时，就会发生这种情况。虽然类似于脏读，但不同在于，在不可重复读取中，写入事务已成功提交事务，而在脏读中，写入事务被回滚。这种效应 在数据读取完成之前，要防止数据的更改。幻读当正在读取数据的事务正在读取一系列数据和另一个数据处理时，就会发生幻取。在数据里插入或删除行。如果将再次发布读取事务的语句，则将返回额外的行（对于插入事务），或更少的行返回(用于删除事务)。可以通过在读取数据时阻止事务插入或删除数据来避免这种影响。

误读、重复读出现在

索引扫描操作中读取一系列行，并在读取期间由第二事务更新一行，从而更改索引键列及其在扫描中的位置。如果需要更新，将一行从扫描结束移动到开始，读取事务可能错过读取该行；

反之，如果更新将行从扫描开始移动到末尾，如果读取未提交隔离级别中的读取事务执行分配顺序扫描(使用iam页)，而另一个事务导致page split（真的不知道怎么翻译），则可以读取该行两次。

当这些效果时，应该能够看到，为了努力防止任何这些并发效果时，就会在在数据库创造更多锁（也因此会有更多阻碍）。

并发效应的例子

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

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

ifdb_id('isolationleveltest')isnotnullbegin

useisolationleveltest;

alterdatabaseisolationleveltest setsingle_user withrollbackimmediate;

usemaster;

dropdatabaseisolationleveltest;end;createdatabaseisolationleveltest;

gouseisolationleveltest;

go

createproceduredbo.db_reset asifobject_id('dbo.isolationtests','u')isnotnulldroptabledbo.isolationtests;createtabledbo.isolationtests (

id integer identity,

cola char(1));insertintodbo.isolationtests(cola)select'a'unionallselect'a'unionallselect'a'unionallselect'a'unionallselect'a'unionallselect'a'unionallselect'a';

select*fromdbo.isolationtests;

ifexists(select1 fromsys.databases wheredatabase_id =db_id('isolationleveltest')andsnapshot_isolation_state =1)

alterdatabaseisolationleveltest setallow_snapshot_isolation off;

goexecutedbo.db_reset;

go

未提交读

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

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

useisolationleveltest;

goexecutedbo.db_reset;

go

begintransaction;updatedbo.isolationtestssetcola ='y';--simulate having some intensive processing here with a waitwaitfordelay '00:00:10';rollback;

在第二个查询窗口中，我们运行：

useisolationleveltest;

go-- read committed-- run this in query window 2 while the 1st query is runningsettransactionisolation level readcommitted;select*fromdbo.isolationtests;

如此所见，查询窗口2中的语句必须等待查询窗口1中的事务完成后才能运行，查询窗口2在查询窗口后返回表中的值。其中查询窗口1是完整的，包含全部内容的.

可重复读

对于下一个隔离级别，可重复读取，我们将展示这个隔离级别中的事务是如何两次从表中读取数据的，其中有一段是在两次读取之间进行的，并将需要返回相同的数据。在这个隔离级别下，它必须读取与所读取的行完全相同的数据，因此它将阻塞试图执行更新的第二个事务。 对其中一些行进行分页。然后，我们将从可重复读取更改为已读提交，以显示允许运行更新的效果。在第一个查询窗口中，运行以下语句：

useisolationleveltest;

goexecutedbo.db_reset;

go

settransactionisolation level repeatable read;begintransaction;select*fromdbo.isolationtests;waitfordelay '00:00:10';select*fromisolationtests;rollback;

在第二个窗口，我们运行：

useisolationleveltest;

goupdatedbo.isolationtests setcol1 =-1;

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

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

注意，查询窗口2立即完成，在查询窗口1中，第二个select语句返回与第一个选择不同的结果声明。

在刚才执行的可重复读取测试中，我们看到了如何防止对数据的更新。可序列化的隔离级别更进一步，还可以防止插入或删除。 发生在这张桌子上。为了测试这一点，我们将从可重复读取中重新运行测试，并将隔离级别更改为可序列化，并尝试执行插入而不是 最新消息。然后，我们将在可重复读取隔离级别上运行此测试，展示如何允许insert运行。

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

useisolationleveltest;

goexecutedbo.db_reset;

go

-- serializable-- run this in query window 1settransactionisolation level serializable;--set transaction isolation level repeatable read; -- what will happen if this is set insteadbegintransaction;select*fromdbo.isolationtests;waitfordelay '00:00:10';select*fromdbo.isolationtests;rollback;

在第二个查询窗口中，我们运行：

useisolationleveltest;

goinsertintodbo.isolationtests(cola)values('w');

请注意，在查询窗口2中的插入等待到查询窗口1中的事务完成为止。

重新运行步骤1-3：更改查询窗口1以使用可重复读取隔离级别，并运行密码。

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

快照

我们已经看过的已读提交/未提交事务级别也存在丢失/双读的问题。可重复读取/可序列化隔离级别消除这一问题，但在严重阻碍其他交易的情况下这样做。

快照隔离级别消除了可序列化隔离级别的所有相同并发性副作用，并且在不引入锁定的情况下(从而消除了锁定)也这样做了。在这个测试中，我们将首先显示快照隔离级别中没有锁定，然后显示缺失。现在，在使用可序列化隔离级别时，这些语句将被锁定。

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

useisolationleveltest;

goexecutedbo.db_reset;

go

-- snapshotalterdatabaseisolationleveltest setallow_snapshot_isolation on;

go-- run this in query window 1useisolationleveltest;

gosettransactionisolation level snapshot;begintransaction;select*fromdbo.isolationtests;waitfordelay '00:00:10';select*fromdbo.isolationtests;rollback;

在第二个查询窗口中，我们运行：

useisolationleveltest;

goinsertintodbo.isolationtests(cola)values('x');select*fromdbo.isolationtests;

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

运行测试，您将看到查询窗口2现在将被阻塞，并等待查询窗口1完成后才能插入行。

nolock查询提示如何适合它？

表显示nolock(与表提示readuncommitted相同)与指定select事务级别的readuncommitted相同。通过运行未提交的读代码可以看到这一点，对于查询窗口2，运行以下代码，而不是：

select*fromdbo.isolationtests with(nolock);

如果您决定实现快照隔离，并且您的当前代码正在使用nolock(或readuncommitted)表提示，则这些指定的提示将具有优先级-您将需要更改代码，以获得利用快照隔离级别的优势。

总结

在iso隔离级别下，当我们更改查询运行的隔离级别时，除了sql server默认的read提交之外，我们还可以减少锁(但允许读取 ，或者增加所涉及的锁以最小化并发效果。快照隔离级别消除了所有并发性效果，同时保持了对数据读取的零阻塞。但是由于没有白痴的午餐，所以您要付出更多的代价，增加了对用户数据库和temdb中的存储空间的需求。话虽如此，我觉得如果你使用读未提交(或nolock)时，应该切换到使用读取提交的快照隔离级别，以实现为该查询实现的无阻塞。