SQLServer事务隔离级别的选择如何影响锁定机制
事务隔离级别
最近我在sqlserver cub上发布了锁定、阻止和死锁的文章。本文将继续讨论事务隔离级别，以及事务隔离级别的选择如何影响先前文中所讨论的锁定机制。
如果我们查看引擎中的联机（bol）主题隔离级别，可以看到事务隔离级别控制：
在读取数据时是否锁定，以及请求什么类型的锁。
读取锁保持多长时间。
是否引用另一个事务修改的行的读取操作：
块，直到释放行上的排他锁。
检索语句或事务启动时存在的行的提交版本。
读取未提交的数据修改。
注意，这些都只影响数据的读取。在写入数据时获取的锁不受影响——这些仍然需要保护数据修改。事务隔离级别控制读取操作如何免受其他（写入）操作的影响。
so隔离级别
下表显示了不同的iso隔离级别，以及它们的并发性副作用：
|
isolation level |
dirty reads |
nonrepeatable reads |
phantom reads |
missing / double reads |
|
read uncommitted |
yes |
yes |
yes |
yes |
|
read committed |
no |
yes |
yes |
yes |
|
repeatable read |
no |
no |
yes |
no |
|
serializable |
no |
no |
no |
no |
在检查此表时，我们可以看到不同的事务隔离级别被设计为消除并发效应。
sql server 2005添加了两个额外的事务隔离级别，它们都涉及利用快照：
|
isolation level |
dirty reads |
nonrepeatable reads |
phantom reads |
missing / double reads |
|
read committed snapshot |
no |
no |
no |
no |
|
snapshot |
no |
no |
no |
no |
read sypretype快照是一个数据库级设置，如果它被打开并且事务隔离级别被读取，那么它将使用行版本控制在语句开始时呈现一个事务一致的数据视图。
快照隔离级别还利用行版本控制在语句开始时呈现数据的事务一致视图。这要求打开asvixsnashothotion数据库设置，并为查询发出set事务隔离级别快照语句。
在这两种快照隔离级别中，效果是读者不会阻止作者，作家不会阻止读者。此外，读者将无法从其他事务中读取任何飞行数据修改。
正如我已经提到的，这两个都使用行版本控制。当使用行版本控制时，sqlserver中的数据库引擎将维护受事务影响的行的版本。利用行版本控制将：
消除读取事务上的共享锁。
减少阻塞（在读事务上）。
增加数据修改所需的资源。
增加tempdb中的活动（存储行版本信息的地方）。
所有数据库数据修改都将具有行版本控制。
每个数据记录将附加一个14字节的记录后缀。
并发效应
上面的图表提到了几种不同的并发副作用，所以让我们来解释其中的每一个。这些效应在bol中被定义为并发效应：
当第二个事务选择另一个事务正在更新的行时，脏读取（在iso中称为“未提交依赖项”）发生。当其他事务实际提交修改数据的事务之前，正在读取修改后的数据时会出现脏读取。如果要回滚该事务，则第二个事务刚刚返回了一个行，该行中存在不存在于数据库中的数据。可以通过防止读取正在改变的数据来避免这种影响。
当事务多次读取同一行时，不可重复读取（在iso中称为“不一致分析”），并且不同读取之间的结果是不同的。当另一个事务修改并提交对行的更改时，可能发生这种情况。虽然类似于脏读取，但不同之处在于，在不可重复读取中，写入事务已成功提交事务，而在脏读取中，写入事务被回滚。通过防止数据的变化，直到数据读完为止，可以避免这种影响。
当读取数据的事务正在读取数据范围时，将发生幻像读取，而另一事务则插入或删除行。如果将再次发布读事务的语句，则将返回（插入事务）的额外行，或返回的行数较少（对于删除事务）。通过防止事务在读取数据时插入或删除数据，可以避免这种影响。
缺失/双读发生在：
读取事务在索引扫描操作中读取行的范围，并且在读取期间，通过第二事务更新行，改变索引键列，从而改变其在扫描中的位置。如果更新将行从扫描结束移动到开始，则读取事务可能错过读取该行；反之，如果更新从扫描的开始移动到结束，则该行可以被读取两次。
如果读取未提交隔离级别中的读取事务正在执行分配顺序扫描（使用iam页），而另一事务导致页拆分，则读取事务将丢失行。
当你这些效果时，你应该能够看到，当你工作以防止这些并发效应时，你在数据库中创建了更多的锁定（从而潜在地阻止更多的阻塞）。
并发效应的示例
让我们运行一些示例，看看这些不同的并发效应如何在不同的事务隔离级别中表现出来。所有这些示例都是通过使用两个查询窗口来工作的：一个将运行一个读事务，另一个运行写事务。查询利用“waitfor delay”给你一点时间来启动一个事务，并切换到另一个查询窗口来运行另一个查询窗口。
首先是数据库初始化代码。此代码需要在运行每个测试之前运行。它被放入一个存储过程中，以便可以在必要时轻松运行。
if db_id('isolationleveltest')isnotnullbegin
use isolationleveltest;
alterdatabase isolationleveltest set single_user withrollback immediate;
use master;
dropdatabase isolationleveltest;end;createdatabase isolationleveltest;
gouse isolationleveltest;
go
createprocedure dbo.db_reset asif object_id('dbo.isolationtests','u')isnotnulldroptable dbo.isolationtests;createtable dbo.isolationtests (
id integer identity,
cola char(1));insertinto dbo.isolationtests(cola)select'a'unionallselect'a'unionallselect'a'unionallselect'a'unionallselect'a'unionallselect'a'unionallselect'a';
select *from dbo.isolationtests;
ifexists(select 1 from sys.databases where database_id = db_id('isolationleveltest')and snapshot_isolation_state = 1)
alterdatabase isolationleveltest set allow_snapshot_isolation off;
goexecute dbo.db_reset;
go
未提交读
在未读的隔离级别中，我们将查看如何允许脏读取。这将通过在正在执行更新的一个查询窗口中启动事务，并在读取未提交事务隔离级别中运行select语句的第二查询窗口中执行，以便查询将读取正在修改的数据。经过一段时间后，第一个查询窗口中的事务被回滚。你将看到第二个查询窗口返回了从未提交给表的数据。
在第一个查询窗口中，运行这些语句：
use isolationleveltest;
goexecute dbo.db_reset;
go
begintransaction;update dbo.isolationtestsset cola ='z';--simulate having some intensive processing here with a waitwaitfor delay '00:00:10';rollback;
go
select*from dbo.isolationtests;
go
在第二个查询窗口中，运行这些语句：
use isolationleveltest;
go-- read uncommitted-- run this in query window 2 while the 1st query is runningsettransaction isolation level read uncommitted;select*from dbo.isolationtests;
从结果中可以看出，第二个查询立即返回，并返回随后在第一个查询窗口中回滚的值。
读提交
在read提交的测试中，我们将重新运行这些语句。设置第二个查询窗口以使用已提交的事务隔离级别。因此，在第二个查询窗口中运行的select语句必须等到第一个事务完成（事务被提交或回滚）之后才能读取数据——它被打开的事务阻塞。
在第一个查询窗口中，运行这些语句：
use isolationleveltest;
goexecute dbo.db_reset;
go
begintransaction;update dbo.isolationtestsset cola ='y';--simulate having some intensive processing here with a waitwaitfor delay '00:00:10';rollback;
在第二个查询窗口中，运行这些语句：
use isolationleveltest;
go-- read committed-- run this in query window 2 while the 1st query is runningsettransaction isolation level read committed;select*from dbo.isolationtests;
正如你所看到的，查询窗口2中的语句必须等待查询窗口1中的事务在运行之前完成，查询窗口2在查询窗口1完成后返回表中的值。
可重复读取
对于下一个隔离级别，可重复读取，我们将展示如何在这个隔离级别中从一个表中读取数据两次，在读取之间的一段时间内，如何返回相同的数据。在这个隔离级别下，它必须读取所读取的行的完全相同的数据，因此它将阻塞试图更新这些行中的某些行的第二个事务。然后，我们将从可重复读取更改为read read，以显示允许更新运行的效果。
在第一个查询窗口中，运行这些语句：
use isolationleveltest;
goexecute dbo.db_reset;
go
settransaction isolation level repeatable read;begintransaction;select*from dbo.isolationtests;waitfor delay '00:00:10';select*from isolationtests;rollback;
在第二个查询窗口中，运行这些语句：
use isolationleveltest;
goupdate dbo.isolationtests set col1 = -1;
注意，查询窗口2等待直到查询窗口1完成，因为查询窗口1是可重复读取的。
重新运行步骤1-3：
更改查询窗口1以使用已提交的隔离级别，并运行代码。
在查询窗口2中运行代码。
请注意，查询窗口2立即完成，而在查询窗口1中，第二select语句返回第一个select语句的不同结果。
可串行化的
在刚才执行的可重复读取测试中，我们看到了如何防止对数据的更新。可序列化隔离级别需要进一步的步骤，并且还可以防止插入或删除发生在该表中。为了测试这一点，我们将基本上从可重复读取重新运行测试，我们将将隔离级别更改为可序列化，并尝试执行插入而不是更新。然后，我们将在可重复读取隔离级别运行此测试，说明如何允许插入运行。
在第一个查询窗口中，运行这些语句：
use isolationleveltest;
goexecute dbo.db_reset;
go
-- serializable-- run this in query window 1settransaction isolation level serializable;--set transaction isolation level repeatable read; -- what will happen if this is set insteadbegintransaction;select*from dbo.isolationtests;waitfor delay '00:00:10';select*from dbo.isolationtests;rollback;
在第二个查询窗口中，运行这些语句：
use isolationleveltest;
goinsertinto dbo.isolationtests(cola)values('w');
注意，查询窗口2中的插入等待，直到查询窗口1中的事务完成。
重新运行步骤1-3：
更改查询窗口1以使用可重复读取隔离级别，并运行代码。
在查询窗口2中运行代码。
注意，查询窗口2中的插入立即运行，查询窗口1中的第二个select语句返回插入的行。
快照
我们已经看到的读提交/未提交事务级别也有丢失/双读的问题。可重复读取/可串行化隔离级别消除了这个问题，但是在严重阻塞其他事务的情况下这样做。快照隔离级别消除了可序列化隔离级别所执行的所有并发并发副作用，并且在不引入锁定（从而消除阻塞）的情况下另外进行了这种副作用。在这个测试中，我们将首先显示快照隔离级别中没有阻塞，然后显示在使用可序列化隔离级别时如何阻止这些相同的语句。
在第一个查询窗口中，运行这些语句：
use isolationleveltest;
goexecute dbo.db_reset;
go
-- snapshotalterdatabase isolationleveltest set allow_snapshot_isolation on;
go-- run this in query window 1use isolationleveltest;
gosettransaction isolation level snapshot;begintransaction;select*from dbo.isolationtests;waitfor delay '00:00:10';select*from dbo.isolationtests;rollback;
在第二个查询窗口中，运行这些语句：
use isolationleveltest;
goinsertinto dbo.isolationtests(cola)values('x');select*from dbo.isolationtests;
注意，查询窗口2立即完成，但是数据修改未反映在查询窗口1中。
如果要更改查询窗口1以使用可序列化隔离级别并重新运行测试，则会发现查询窗口2现在将被阻塞，并且现在将等待直到查询窗口1完成之后才能插入行。
nolok查询提示如何适合它呢？
表提示nolock（与表提示reununread相同）与指定set事务级别reunun承诺相同。你可以通过运行未提交的读代码来看到这一点，而对于查询窗口2，运行以下代码来代替：
select*from dbo.isolationtests with(nolock);
如果你决定实现快照隔离，并且当前的代码使用nolock（或reununrebug）表提示，这些指定的提示将具有优先权——你需要更改代码以获得利用快照隔离级别的好处。
总结
在iso隔离级别下，当我们改变查询的隔离级别时，远离sqlserver默认的read提交，我们要么减少锁（但允许读取脏数据），要么增加涉及的锁定，以最小化并发效应。快照隔离级别消除了所有并发效应，同时在读取事务上保持零阻塞，但是由于没有任何免费的，你支付的代价是在用户数据库和tempdb中增加了tempdb活动和增加的存储空间需求。这就是说，我觉得如果你使用read unundor（或nolock），你应该切换到使用read提交的快照隔离级别来实现无阻塞，你正试图为该查询实现。