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資料型別控制併發性時

例2. READ COMMITTED 事務隔離級別

通過僅允許一個事務讀取另一個事務中已經提交的資料，READ COMMITTED 事務隔離級別防止了“髒讀”問題。這是SQL Server中預設的事務隔離級別。

例3. REPEATABLE READ 事務隔離級別

正如你在前一個事務隔離級別的步驟2所看到的，Session 2中的事務能夠修改已經被Session 1中的事務讀取的資料。正像真實場景中所描述的，這可能導致“LOST UPDATE”。REPEATABLE READ 事務隔離級別不允許這種情況發生，因為它違背了REPEATABLE READ原則。換句話說，Session 1中的事務讀取同一資料可能會產生不同的結果。

例4. SERIALIZABLE 事務隔離級別

為了向你展示SERIALIZABLE 事務隔離級別防止的併發性問題，本例我們從REPEATABLE READ 事務隔離級別開始。

例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事務隔離級別。

例6. READ COMMITTED SNAPSHOT 隔離級別

到目前為止，所有的隔離級別都是將事務相互間隔離開來。一旦初始事務完成了，對其他事務變得不可用的資源才又變得可用。READ COMMITTED SNAPSHOT 隔離級別在這點上有所不同，它能夠讀取其已經被他事務提交的資料。

READ COMMITTED SNAPSHOT 隔離級別也是通過資料庫開關來開啟的。然後，任何使用READ COMMITTED SNAPSHOT 隔離級別的事務將通過版本化起作用。


希望通過以上具體例項，能夠幫助你理解如何正確地隔離事務和語句從而防止併發性問題。

四、小結

隔離級別	解決的併發性問題	存在的並發性問題
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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13651903/viewspace-1082730/，如需轉載，請註明出處，否則將追究法律責任。