use TSQLFundamentals2008;
go
--9.3 隔离级别
--隔离级别用于决定如何并发控制用户读写数据的操作。读操作可以是任何检索数据的语句，默认使用共享锁。
--写操作是指任何对表做出修改的语句，需要使用排他锁。
--可以设置的隔离级别有6个read uncommitted(未提交读)、read committed(提交读)、repeatable read
--(可重复读)、serializable(可序列化)、snapshot(快照)以及read committed snapshot(已经提交读隔离)。
--隔离级别越高，读操作请求的锁定就越严格，锁持有的时间也更长；因此，隔离级别越高，一致性也就越高，
--并发性就越低。
--9.3.1 read uncommitted 未提交读
--最低级的隔离级别。读操作不会请求共享锁。如果读操作不请求共享锁，就绝对不会和持有排他锁的写操作
--发生冲突。这意味着读操作可以读取未提交的修改(也称为脏读)。同时也意味着读操作不会妨碍写操作请求排他锁。
--Connection 1 
begin tran
update Production.Products
set unitprice+=1.00 --new value 21.00
where productid=2;
--Connection 2 
set transaction isolation level read uncommitted;
select productid, unitprice --21.00
from Production.Products 
where productid=2;
--9.3.2 read committed 已提交读
--能够防止脏读的最低隔离级别是read committed，这也是所有版本的SQL Server默认使用的隔离级别。这个
--隔离级别只允许读取已经提交过的修改。他要求读操作必须获得共享锁才能进行操作，从而防止读取未提交的修改。这
--意味着，如果写操作持有排他锁，读操作做出的共享锁请求就会和写操作发生冲突，所以读操作不得不等待。
--与read uncommitted不同，在read committed隔离级别下，不会读取脏数据。相反，
--只能读取已经提交过的修改。
--按照锁的持有时间来说，在read committed隔离级别中，读操作一完成，就立即释
--放资源上的共享锁。读操作不会在事务持续时间内保留共享锁；实际上设置在语句
--结束前也不能一直保留共享锁。这意味着在一个事务处理内部对相同的数据资源的
--两个读操作之间，没有共享锁会锁定该资源。因此，其他事务可以在两个读操作之
--间更改数据资源，读操作因而可能每次得到不同的取值。这种现象称为不可重复读
--(non-repeatable read)或不一致的分析(inconsistent analysis)。
--9.3.3 Repeatable Read可重复读
--如果想保证在事务内部进行的两个操作之间，其他任何事务都不能修改由当前事务读取
--的数据，则需要把隔离级别升级为repeatable read。在这种隔离级别下，事务中的读
--操作不但需要获得共享锁才能读取数据，而且获得的共享锁将一直保持到事务完成为止。
--这意味着一旦获得数据资源上的共享锁以读取数据，在事务完成之前，没有其他事务能够
--获得排他锁以更改这一数据资源。这样，就可以保证实现可重复的读取，或一致的分析。
--Connection 1
set transaction isolation level repeatable read;

begin tran;
select productid, unitprice
from Production.Products
where productid = 2;
--Connection 2
update Production.Products
set unitprice = unitprice + 10
where productid = 2;
--repeatable read隔离级别能够防止的另一种并发负面影响是丢失更新(lost update)，
--而较低的隔离级别则不能防止这种问题。丢失更新是指当两个事务读取了同一个值，然
--后基于最初读取的值进行计算，接着再更新该值，就会发生丢失更新的问题。
--9.3.4 SERIALIZABLE 可序列化
--事务只锁定查询第一次运行时找到的那些数据资源，而不会锁定查询结果范围以外的其他
--行。因此，在同一事物中进行第二次读取之前，如果其他事务插入了新行，而且新行也
--能满足读操作的查询过滤条件，那么这些新行也会出现在第二次读操作返回的结果中。这些
--新行称为幻影(phantom)，这种读操作也称为幻读(phantom read)。
--Serializable隔离级别比repeatable read增加了一个新内容———逻辑上，这个隔离级别会
--让读操作锁定满足查询搜索条件的键的整个范围.这就意味着读操作不仅锁定了满足查询
--搜索条件的现有的那些行,还锁定可未来可能满足查询搜索条件的行.或者更准确地说,如果
--其他事务视图增加能够满足读操作的查询搜索条件的新行,当前事务就会阻塞这样的新行.
--Connection 1
set transaction isolation level serializable 
begin tran;
select productid, unitprice
from Production.Products;
commit tran
--Connection 2
insert into Production.Products
(productname, supplierid,categoryid,unitprice,discontinued)
values('Product ABCDE', 1, 1, 20.00, 0);
--9.3.5 snapshot快照 隔离级别
--基于快照的隔离级别下,读操作不需要使用共享锁,所以即使请求的数据被其他事务以排他
--锁锁定,读操作也不会等待.
--在snapshot隔离级别下,当读取数据时,可以保证操作读取的行是事务开始时可用的最后提交的版本.
--快照隔离级别会带来性能上的开销.
--repeatable read和serializable隔离级别是通过产生死锁状态而避免更新冲突的，snapshot
--隔离级别也能够避免更新冲突，但与前两种隔离级别不同，当检测到更新冲突时，snapshot隔离
--级别的快照事务将因失败而终止。snapshot使用行版本控制检查更新。