SQL Server 2005+ 依赖性和一致性问题备忘

这段时间在思考流程控制器的一致性和并发问题，把 SQLServer 2005 存储引擎这本书

又找出来翻了翻，把其中相关的部分手K了一遍，加深理解，备忘。

依赖性问题或一致性问题
1）丢失更新（Lost updates）
当两个（或更多）进程读取相同数据并且都处理该数据（修改它的值），然后都尝试更新
原来的数据成新的值时，这种行为就会产生了。第二个进程可能完全覆盖掉第一个所完成
的更新。如：在接收室中有两个职员 A 和 B 负责接收部件并在存货数据库中添加新的装
运记录。他们都检查了当前库存量并且看到当前储存着 25 个小部件，职员 A 的装运量
为 50 个小部件，因些他将 25 加上 50 以后更新了当前的值为 75。职员 B 的装运量为
20 个小部件，因此她将 25 加上 20 以后更新了当前值为 45，把职员 A 所处理的 50
个新部件给完全覆盖掉了。职员 A （在哭）的更新丢失了。丢失更新是这些行为当中惟
一一个用户可能在所有情况下都想避免的行为。

2）脏读（Dirty reads）
这种行为在一个进程读取未提交数据时会产生。如果一个进程修改了数据但是尚未提交修
改，而另一个正在读取数据的进程会读到这个修改从而导致一种不一致的状态发生。如：
职员 A 已经将原来的 25 个小部件值更新为 75，但在提交之前，一个销售员看到了当前
值为 75 并在第二天发送了 60 个小部件给客户。如果职员 A 意识到小部件存在缺陷并
将其送回生产商的话，销售员其实就是完成了一次脏读，并且基于这个未提交数据采取了
行动。默认情况下，脏读是不允许的。谨记：更新数据的进程是无法控制别的进程在它提
交之前读取其数据的。这是由读取数据的进程来决定是否想要读取未必会被提交的数据。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

3）不可重复读（Non-repeatable reads）
这种行为又被叫做不一致分析（inconsistent analysis）。如果一个进程在同一事务中
分别以两个读操作读取相同资源时可能会得到不同的值，这就是不可重复读。这种情况可
能发生在第一个进程执行两次读操作的间隔内别的进程修改数据的时候。在接收室的例子
中，假设一个主管进来对当前库存做了一次抽查，并且在她的计算器上做加法。当她完成
以后，为了再一次确认结果，她返回到第一个职员那里。然而，如果职员 A 在主管的两
次查询之间接收了新的小部件的话，两次得到的总额就会不同，而这样的读就是不可重复
的。

4）幻影（Phantoms）
这种行为产生于一个数据集内的部分数据被修改过。这只发生在查询中带有一个谓词的时
候。如：where count_of_widgets < 10 这样的。如果在同一事务中使用相同谓词的两次
select 操作返回不同数量的结果集，幻影就产生了。再例：主管仍在对库存进行抽查，
这次她在接收室里来回踱步（？）并且留意哪个职员的小部件数量少于 10 个。当完成这
个名单以后，她回来并告诉每个人这个低数量的统计。然而，如果在她第一次漫游时一个
拥有少于 10 个部件的职员在休息以后回到工作岗位但却没有被主管抽查到，即使她符合
谓词中的标准，这个职员也不会成为主管名单中的一员了。这个额外的职员（或者行）就
被看作是一个幻影。

隔离级别（Lsolation Levels）
1）未提交读（Uncommitted Read）
对于未提交读隔离级别来域，除了丢失更新之外，以上提到的所有行为都有可能发生。用
户的查询可能读到未提交的数据，并且不可重复读及幻影都是有可能存在的。未提交读是
通过使读操作不占有任何锁来实现的，而且由于 SQL Server 不再尝试获取锁，也就不会
被其它进程所占用的想冲突的锁阻塞住了。该模式通常并不是理想的选择，但是由于未提
交读，用户就不会卡在等待锁上，并且读操作不占用任何锁而可能影响到其他正在读取或
者写入数据的进程。
当采用未提交读时，用户是放弃了对于高一致性数据（strongly consistent data）的把
握而趋向于支持系统的高并发能力，使用户不会再互相锁定住对方。这样，何时才应该选
择未提交读呢？显示，每笔数据都须保证平衡的金融交易是不适合的。而对于某些决策支
持分析（decision support analysis）来域可能会很合适（譬如域，当需要察看销售走
势时），国为没有必要做到完全精确而且更高的并发性所带来的均衡也是相当值得的。
未提交读是针对阻塞太频繁问题的一种悲观的解决方案，国为它只是忽略了锁而不保障事
务的一致性。
2）已提交读（Read Committed）
SQL Server 2005+ 支持两种已提交读的隔离级别（数据库引擎的默认级别）。可以是乐
观的也可以是悲观的，取决于 READ_COMMITED_SNAPSHOT 设置，这个选项默认是关闭的，
所以该隔离级别是在默认情况下是采用悲观并发控制。（即 SQL Server 2005+ 默认情况
下是使用的悲观的已提交读隔离级别）。
a）悲观已提交读（锁定）
已提交读隔离级别保证了一个操作不会读到别的进程已经修改但尚未提交的数据。如果别
的事务正在更新数据并因此在数据行上持有排他锁（exclusive locks），用户的事务就
必须等待这些锁释放以后才能使用这个数据（无论是读取还是修改）。同样地，事务必须
至少在要被访问的数据上加上共享锁（share locks），这意味着该数据无法被其它人使
用。共享锁不会妨碍其他人读取数据，但是它会其他人需要等待才能更新数据。
b）乐观已提交讯（快照）
已提交读（快照）也能确保一个操作不会读到未提交数据，但不是通过迫使其他进程等待
的方式。对于已提交读（快照），每当一行数据被更新以后，SQL Server 就会生成该行
数据前一次已提交值的一个版本（version）。被修改的数据仍旧被锁定着，但是其它进
程可以看到该数据在更新操作开始之前的版本。
3）可重复读（Repeatable Read）
可重复读是一种悲观的隔离级别。它在已提交读的基础上增加了新的属性：确保当事务重
新访问数据或查询被再一次执行时数据将不再发生改变。换句话说，在一个事务中执行相
同的查询两次是不会看到由其他用户的事务所造成的任何数据上的改变的。然而，可重复
读隔离级别还是允许幻影行的出现。
在某些情况下，防止不可重复读是用户向往的一种安全措施。但这种额外的安全措施所带
来的开销是事务中所有的共享锁必须保留到事务完成（Commit 或者 Rollback）为止。只
要事务是打开的，没有其他用户可以修改被该事务所访问的数据。显示，这会严重降低并
发性和性能。如果事务不操持简短或者如果编写应用程序时没有能够注意到这样潜在的锁
竞争问题，当一个进程正在等待锁被释放时，SQL Server 就会表现出“挂起”（hand）的
状态。
4）快照（Snaphhot）
快照隔离是一种乐观的隔离级别。类似于已提交读（快照），如果当前版本被锁定住时，
它允许进程读取已提交数据的早期版本。快照隔离和已提交读（快照）的区别与早期版本
该有多早这个问题相关。尽管快照隔离所避免的行为和可串行化所避免的是相同的，但是
快照隔离并不是真正意义上的可串行化隔离级别。对于快照隔离，可能会有两个事务在同
时执行，并引起一个在任何序列化执行中都不可能产生的结果。
如：
------------------------------------------------------------------------------
时刻 事务一 事务二
----- ------------------------------------- ----------------------------------
1 declare @price money declare @price money
begin tran begin tran
----- ------------------------------------- ----------------------------------
2 select @price = price from titles select @price = price from titles
where title_id = 'BU1032' where title_id = 'PS7777'
----- ------------------------------------- ----------------------------------
3 update titles set price = @price update titles set price = @price
where title_id = 'PS7777' where title_id = 'BU1032'
----- ------------------------------------- ----------------------------------
3 commit tran commit tran
------------------------------------------------------------------------------
快照隔离级别可能导致两本书拥有相同的价格而不交换价格！
5）可串行化（Serializable）
可串行化也是一种悲观隔离级别。可串行化隔离级别在可重复读基础上增加了新的属性：
确保在重新执行查询时，SQL Server 不会在蹭的过渡时间（interim）增加新行。换句话
说，如果在同一事务中相同的查询被执行两次的话，幻影就不会出现了。
可串行化是最健壮的悲观隔离级别，因为它防止了之前所述的所有可能的不合人意的行为
也就是说，它不允许未提交读，不允许重复读和幻影，并且还保证了事务的依次执行。
防止幻影是另一种合人所愿的安全措施，但木有免费午餐，事务中的所有共享锁必须保留
到事务完全完成为止。另外，执行可串行化隔离级别不仅需要锁定已读数据，还需要锁定
那些不存在的数据！

总结隔离级别
--------------------------------------------------------------------------------
隔离级别 脏读 不可重复读 幻影读 并发控制模型
-------------------- --------- ------------------ -------------- ---------------
未提交读 Yes Yes Yes 悲观
-------------------- --------- ------------------ -------------- ---------------
已提交读 No Yes Yes 悲观
-------------------- --------- ------------------ -------------- ---------------
已提交读（快照） No Yes Yes 乐观
-------------------- --------- ------------------ -------------- ---------------
可重复读 No No Yes 悲观
-------------------- --------- ------------------ -------------- ---------------
快照 No No No 乐观
-------------------- --------- ------------------ -------------- ---------------
可串行化 No No No 悲观
--------------------------------------------------------------------------------