SQL Server 2008 R2 事务与隔离级别实例讲解

binghai03

SQL Server 2008 R2 事务与隔离级别实例讲解
　　上班途中，你在一处ATM机前停了下来。正当你在敲入密码的时候，你的一位家人也正在镇上的另一处TAM机上输入密码。你打算从某个还有500元余额的账户上转出400元，而你的家人想从同一账户取走300元。倘若没有隔离级别的存在，麻烦就要来了......
　　SQL Server 实现了6个隔离级别来防止并发情况下，类似上面例子中企图并发的访问或修改同一数据时问题的发生。本文将带你体验全部6个隔离级别。正如你接下来将看到的，你将理解每个隔离级别所能达成的效果以及何时使用它。
　　本文假定你对事务和隔离级别的基本概念有所了解。本文所使用的例子基于AdventureWorks数据库而运行。
一、事务简介
　　SQL Server的6个隔离级别中有5个是用于隔离事务的，它们因而被称作事务隔离级别。另外的一个工作于语句级别。我们先从什么是事务开始吧！
　　让我们再次回到ATM机的例子。你打算在2个账户间转账。注意，转账至少涉及2个数据更改的操作：一个账户需要减少余额，而另一个账户需要增加余额。好，假定借方账户（debit account）转出资金操作成功了，而贷方账户（credit account）转入资金操作失败了。你不想要这一幕发生，因为那样的话你的钱就没了。你希望两个账户的数据更新作为一个完整的工作单元来处理--要么完全成功，要么完全失败。我们称这样的工作单元为“事务”。事务就是数据库级别的工作单元，包括一个或多个数据更改，必须整体地被视为一个单一的单元，它们要么全都成功，要么什么也不曾发生。
　　这里再列举几个要求多个数据修改操作必须要么完全成功要么什么也没发生的例子。当数据被合并到数据库时，可能有多个表需要更新。当顾客下订单时，Order表、Invoice Line Item表和Product表的数据可能都需要更新。购买机票也许要求更新Passenger表和Reservations表。无论何时当一个操作要求多个数据更改操作整体地作为单一的单元来处理，这就是需要使用事务的时候。
　　现在是时候引入2个有用的概念了--提交和回滚。如果事务中所有的数据更改操作都成功了，那么这些数据更改就可以被提交（也就是持久化到数据库）。否则，截止到失败点事务中所发生的所有数据更改必须被回滚（也就是撤销操作，什么也不曾发生）。
　　现在让我们来看一看使用事务的一些实际命令。在本文的例子中，我们将使用 Explicit Transaction Mode。在这种模式下，我们使用BEGIN TRANSACTION 命令开启一个事务，视具体情况，使用COMMIT TRANSACTION 或者 ROLLBACK TRANSACTION 命令终止一个事务。
二、 隔离级别简介

　　必须小心对待并发情况，因为它们可能引发已知的并发性问题，包括“脏读”、“不可重复读”和“幻像读”，这些问题可能反过来导致数据的不良后果，就像我们在ATM机例子中感受的一样。正如我们已经知道的，为了防止并发性问题，隔离级别用于将事务或语句相互间隔离开来。 下面是SQL Server 2008中定义的隔离级别名称：

A. Transaction Isolation Level
　　1. READ UNCOMMITTED
　　2. READ COMMITTED (Default)
　　3. REPEATABLE READ
　　4. SERIALIZABLE
　　5. SNAPSHOT

B. Statement Isolation Level
　　6. READ COMMITTED SNAPSHOT
　　正如你在下面的例子中即将看到的，隔离级别越高，提供的保护级别也越高（防止更多的并发性问题）。并且，每个隔离级别包括了前一个级别所提供的保护，因此，每个后续的更高隔离级别以避免更多并发性问题的形式提供了额外的保护。但是，世上没有免费的午餐，隔离级别越高，数据可用性就越低。选择合适的隔离级别是一种在高度安全的并发性和数据的高可用性之间寻求平衡的行为。
　　让我们来看看具体实例。
三、引入实例
　　所有例子都运行于AdventureWorks数据库，你可以下载AdventureWorks数据库(AdventureWorks2008_SR4.exe, 包含了除2008R2以外的所有版本AdventureWorks数据库)。
　　下载本文所有实例的脚本文件
　　为了创建并发环境，所有例子使用2个SQL Server Session，每个会话运行一个不同的事务，每个事务访问相同的资源。在SQL Server Management Studio中，每个查询窗口代表了一个不同的Session，因此，你可以在SQL Server Management Studio中为不同的事务使用不同的查询窗口。
　　所有例子包含了真实场景以便你将这一切建立在现实的基础上。
例1. READ UNCOMMITTED 事务隔离级别
　　READ UNCOMMITTED 事务隔离级别根本就没有提供事务间的隔离，它允许违反并发性原则的最基本形式之一 -- 脏读。当一个事务能够读取另一个事务中已经Update但尚未Commit的数据时，“脏读”就发生了。
　　READ UNCOMMITTED 事务隔离级别应用于：

• 　　单用户系统
  
• 　　系统中两个事务同时访问同一资源的可能性为零或几乎为零
  
• 　　当使用Rowversion数据类型控制并发性时
  

http://blog.itpub.net/attachment/201402/17/13651903_139262787987r2.png

例2. READ COMMITTED 事务隔离级别
　　通过仅允许一个事务读取另一个事务中已经提交的数据，READ COMMITTED 事务隔离级别防止了“脏读”问题。这是SQL Server中默认的事务隔离级别。

http://blog.itpub.net/attachment/201402/17/13651903_1392627993ZLd0.png

例3. REPEATABLE READ 事务隔离级别
　　正如你在前一个事务隔离级别的步骤2所看到的，Session 2中的事务能够修改已经被Session 1中的事务读取的数据。正像真实场景中所描述的，这可能导致“LOST UPDATE”。REPEATABLE READ 事务隔离级别不允许这种情况发生，因为它违背了REPEATABLE READ原则。换句话说，Session 1中的事务读取同一数据可能会产生不同的结果。

http://blog.itpub.net/attachment/201402/17/13651903_1392628028161o.png

例4. SERIALIZABLE 事务隔离级别
　　为了向你展示SERIALIZABLE 事务隔离级别防止的并发性问题，本例我们从REPEATABLE READ 事务隔离级别开始。

http://blog.itpub.net/attachment/201402/17/13651903_13926280544l5p.png

例5. SNAPSHOT事务隔离级别
　　也许你已经注意到，在上述例1到例4中，防止并发性问题的同时也降低了数据的可访问性。先是不允许Read，然后是不允许Update，不允许Insert。SNAPSHOT事务隔离级别防止了之前那些隔离级别所能防止的许多并发性问题，同时降低了与之相关的成本。它允许更高的数据可用性。
　　通过在事务开始前在TempDB中使用row versions创建一份数据库的虚拟快照，SNAPSHOT事务隔离级别完成了此壮举。此后它只允许事务访问该数据库虚拟快照。这种方法被称做“基于版本控制的隔离”(versioning-based isolation，背后细节的完整介绍请参考Understanding Row Versioning-Based Isolation Levels。
　　使用versioning-based isolation，事务仅能看到虚拟快照中的数据。因此，其他事务仍然能够访问同一数据，只要它们不去修改已经被第一个事务修改过的数据就好。如果那样做了（企图修改数据），那么，那些事务将会被回滚并以错误消息终止。
　　只有当数据库中启用SNAPSHOT事务隔离级别的开关打开后，才能使用它。打开此开关将告知数据库去设置版本化环境。理解这一点很重要，因为，一旦版本化开启，数据库会有维护版本化的开销，无论是否有事务正在使用SNAPSHOT事务隔离级别。

http://blog.itpub.net/attachment/201402/17/13651903_1392628624CO47.png

例6. READ COMMITTED SNAPSHOT 隔离级别
　　到目前为止，所有的隔离级别都是将事务相互间隔离开来。一旦初始事务完成了，对其他事务变得不可用的资源才又变得可用。READ COMMITTED SNAPSHOT 隔离级别在这点上有所不同，它能够读取其已经被他事务提交的数据。
　　READ COMMITTED SNAPSHOT 隔离级别也是通过数据库开关来打开的。然后，任何使用READ COMMITTED SNAPSHOT 隔离级别的事务将通过版本化起作用。

http://blog.itpub.net/attachment/201402/17/13651903_1392628664oO55.png

　　希望通过以上具体实例，能够帮助你理解如何正确地隔离事务和语句从而防止并发性问题。
四、小结
　　隔离级别
　　解决的并发性问题
　　存在的并发性问题
　　READ UNCOMMITTED
　　不适用于并发场合
　　Dirty Reads, Non-repeatable Reads, Phantom Reads
　　READ COMMITTED
　　Dirty Reads
　　Lost Update , Non-repeatable Reads, Phantom Reads
　　REPEATABLE READ
　　Non-repeatable Reads
　　Phantom Reads, potentially Deadlocking
　　SERIALIZABLE
　　Phantom Reads
　　Less Data Availability, potentially Deadlocking
　　SNAPSHOT
　　上述所有并发性问题
　　事务访问的是虚拟快照，其他事务Committed的数据对当前事务仍然不可见，也不允许Update被其他事务Updated的数据。
　　READ COMMITTED SNAPSHOT
　　上述所有并发性问题
　　无
　　参考：
　　http://blogs.interfacett.com/understanding-isolation-levels-sql-server-2008-r2-2012-examples