SQL Server 事務(wù)與隔離級(jí)別實(shí)例講解

上班途中，你在一處ATM機(jī)前停了下來(lái)。正當(dāng)你在敲入密碼的時(shí)候，你的一位家人也正在鎮(zhèn)上的另一處TAM機(jī)上輸入密碼。你打算從某個(gè)還有500元余額的賬戶上轉(zhuǎn)出400元，而你的家人想從同一賬戶取走300元。倘若沒(méi)有隔離級(jí)別的存在，麻煩就要來(lái)了......

SQL Server 實(shí)現(xiàn)了6個(gè)隔離級(jí)別來(lái)防止并發(fā)情況下，類似上面例子中企圖并發(fā)的訪問(wèn)或修改同一數(shù)據(jù)時(shí)問(wèn)題的發(fā)生。本文將帶你體驗(yàn)全部6個(gè)隔離級(jí)別。正如你接下來(lái)將看到的，你將理解每個(gè)隔離級(jí)別所能達(dá)成的效果以及何時(shí)使用它。

本文假定你對(duì)事務(wù)和隔離級(jí)別的基本概念有所了解。本文所使用的例子基于AdventureWorks數(shù)據(jù)庫(kù)而運(yùn)行。

一、事務(wù)簡(jiǎn)介 SQL Server的6個(gè)隔離級(jí)別中有5個(gè)是用于隔離事務(wù)的，它們因而被稱作事務(wù)隔離級(jí)別。另外的一個(gè)工作于語(yǔ)句級(jí)別。我們先從什么是事務(wù)開始吧！

讓我們?cè)俅位氐紸TM機(jī)的例子。你打算在2個(gè)賬戶間轉(zhuǎn)賬。注意，轉(zhuǎn)賬至少涉及2個(gè)數(shù)據(jù)更改的操作：一個(gè)賬戶需要減少余額，而另一個(gè)賬戶需要增加余額。好，假定借方賬戶（debit account）轉(zhuǎn)出資金操作成功了，而貸方賬戶（credit account）轉(zhuǎn)入資金操作失敗了。你不想要這一幕發(fā)生，因?yàn)槟菢拥脑捘愕腻X就沒(méi)了。你希望兩個(gè)賬戶的數(shù)據(jù)更新作為一個(gè)完整的工作單元來(lái)處理--要么完全成功，要么完全失敗。我們稱這樣的工作單元為“事務(wù)”。事務(wù)就是數(shù)據(jù)庫(kù)級(jí)別的工作單元，包括一個(gè)或多個(gè)數(shù)據(jù)更改，必須整體地被視為一個(gè)單一的單元，它們要么全都成功，要么什么也不曾發(fā)生。

這里再列舉幾個(gè)要求多個(gè)數(shù)據(jù)修改操作必須要么完全成功要么什么也沒(méi)發(fā)生的例子。當(dāng)數(shù)據(jù)被合并到數(shù)據(jù)庫(kù)時(shí)，可能有多個(gè)表需要更新。當(dāng)顧客下訂單時(shí)，Order表、Invoice Line Item表和Product表的數(shù)據(jù)可能都需要更新。購(gòu)買機(jī)票也許要求更新Passenger表和Reservations表。無(wú)論何時(shí)當(dāng)一個(gè)操作要求多個(gè)數(shù)據(jù)更改操作整體地作為單一的單元來(lái)處理，這就是需要使用事務(wù)的時(shí)候。

現(xiàn)在是時(shí)候引入2個(gè)有用的概念了--提交和回滾。如果事務(wù)中所有的數(shù)據(jù)更改操作都成功了，那么這些數(shù)據(jù)更改就可以被提交（也就是持久化到數(shù)據(jù)庫(kù)）。否則，截止到失敗點(diǎn)事務(wù)中所發(fā)生的所有數(shù)據(jù)更改必須被回滾（也就是撤銷操作，什么也不曾發(fā)生）。

現(xiàn)在讓我們來(lái)看一看使用事務(wù)的一些實(shí)際命令。在本文的例子中，我們將使用Explicit Transaction Mode。在這種模式下，我們使用BEGIN TRANSACTION 命令開啟一個(gè)事務(wù)，視具體情況，使用COMMIT TRANSACTION 或者 ROLLBACK TRANSACTION 命令終止一個(gè)事務(wù)。

二、 隔離級(jí)別簡(jiǎn)介 必須小心對(duì)待并發(fā)情況，因?yàn)樗鼈兛赡芤l(fā)已知的并發(fā)性問(wèn)題，包括“臟讀”、“不可重復(fù)讀”和“幻像讀”，這些問(wèn)題可能反過(guò)來(lái)導(dǎo)致數(shù)據(jù)的不良后果，就像我們?cè)贏TM機(jī)例子中感受的一樣。正如我們已經(jīng)知道的，為了防止并發(fā)性問(wèn)題，隔離級(jí)別用于將事務(wù)或語(yǔ)句相互間隔離開來(lái)。 下面是SQL Server 2008中定義的隔離級(jí)別名稱：
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

正如你在下面的例子中即將看到的，隔離級(jí)別越高，提供的保護(hù)級(jí)別也越高（防止更多的并發(fā)性問(wèn)題）。并且，每個(gè)隔離級(jí)別包括了前一個(gè)級(jí)別所提供的保護(hù)，因此，每個(gè)后續(xù)的更高隔離級(jí)別以避免更多并發(fā)性問(wèn)題的形式提供了額外的保護(hù)。但是，世上沒(méi)有免費(fèi)的午餐，隔離級(jí)別越高，數(shù)據(jù)可用性就越低。選擇合適的隔離級(jí)別是一種在高度安全的并發(fā)性和數(shù)據(jù)的高可用性之間尋求平衡的行為。

讓我們來(lái)看看具體實(shí)例。

三、引入實(shí)例 所有例子都運(yùn)行于AdventureWorks數(shù)據(jù)庫(kù)，你可以下載AdventureWorks數(shù)據(jù)庫(kù)(AdventureWorks2008_SR4.exe, 包含了除2008R2以外的所有版本AdventureWorks數(shù)據(jù)庫(kù))。
下載本文所有實(shí)例的腳本文件

為了創(chuàng)建并發(fā)環(huán)境，所有例子使用2個(gè)SQL Server Session，每個(gè)會(huì)話運(yùn)行一個(gè)不同的事務(wù)，每個(gè)事務(wù)訪問(wèn)相同的資源。在SQL Server Management Studio中，每個(gè)查詢窗口代表了一個(gè)不同的Session，因此，你可以在SQL Server Management Studio中為不同的事務(wù)使用不同的查詢窗口。

所有例子包含了真實(shí)場(chǎng)景以便你將這一切建立在現(xiàn)實(shí)的基礎(chǔ)上。

例1. READ UNCOMMITTED 事務(wù)隔離級(jí)別 READ UNCOMMITTED 事務(wù)隔離級(jí)別根本就沒(méi)有提供事務(wù)間的隔離，它允許違反并發(fā)性原則的最基本形式之一 -- 臟讀。當(dāng)一個(gè)事務(wù)能夠讀取另一個(gè)事務(wù)中已經(jīng)Update但尚未Commit的數(shù)據(jù)時(shí)，“臟讀”就發(fā)生了。

READ UNCOMMITTED 事務(wù)隔離級(jí)別應(yīng)用于：

單用戶系統(tǒng) 系統(tǒng)中兩個(gè)事務(wù)同時(shí)訪問(wèn)同一資源的可能性為零或幾乎為零 當(dāng)使用Rowversion數(shù)據(jù)類型控制并發(fā)性時(shí)

例2. READ COMMITTED 事務(wù)隔離級(jí)別 通過(guò)僅允許一個(gè)事務(wù)讀取另一個(gè)事務(wù)中已經(jīng)提交的數(shù)據(jù)，READ COMMITTED 事務(wù)隔離級(jí)別防止了“臟讀”問(wèn)題。這是SQL Server中默認(rèn)的事務(wù)隔離級(jí)別。


例3. REPEATABLE READ 事務(wù)隔離級(jí)別 正如你在前一個(gè)事務(wù)隔離級(jí)別的步驟2所看到的，Session 2中的事務(wù)能夠修改已經(jīng)被Session 1中的事務(wù)讀取的數(shù)據(jù)。正像真實(shí)場(chǎng)景中所描述的，這可能導(dǎo)致“LOST UPDATE”。REPEATABLE READ 事務(wù)隔離級(jí)別不允許這種情況發(fā)生，因?yàn)樗`背了REPEATABLE READ原則。換句話說(shuō)，Session 1中的事務(wù)讀取同一數(shù)據(jù)可能會(huì)產(chǎn)生不同的結(jié)果。


例4. SERIALIZABLE 事務(wù)隔離級(jí)別 為了向你展示SERIALIZABLE 事務(wù)隔離級(jí)別防止的并發(fā)性問(wèn)題，本例我們從REPEATABLE READ 事務(wù)隔離級(jí)別開始。


例5. SNAPSHOT事務(wù)隔離級(jí)別 也許你已經(jīng)注意到，在上述例1到例4中，防止并發(fā)性問(wèn)題的同時(shí)也降低了數(shù)據(jù)的可訪問(wèn)性。先是不允許Read，然后是不允許Update，不允許Insert。SNAPSHOT事務(wù)隔離級(jí)別防止了之前那些隔離級(jí)別所能防止的許多并發(fā)性問(wèn)題，同時(shí)降低了與之相關(guān)的成本。它允許更高的數(shù)據(jù)可用性。

通過(guò)在事務(wù)開始前在TempDB中使用row versions創(chuàng)建一份數(shù)據(jù)庫(kù)的虛擬快照，SNAPSHOT事務(wù)隔離級(jí)別完成了此壯舉。此后它只允許事務(wù)訪問(wèn)該數(shù)據(jù)庫(kù)虛擬快照。這種方法被稱做“基于版本控制的隔離”(versioning-based isolation，背后細(xì)節(jié)的完整介紹請(qǐng)參考Understanding Row Versioning-Based Isolation Levels。

使用versioning-based isolation，事務(wù)僅能看到虛擬快照中的數(shù)據(jù)。因此，其他事務(wù)仍然能夠訪問(wèn)同一數(shù)據(jù)，只要它們不去修改已經(jīng)被第一個(gè)事務(wù)修改過(guò)的數(shù)據(jù)就好。如果那樣做了（企圖修改數(shù)據(jù)），那么，那些事務(wù)將會(huì)被回滾并以錯(cuò)誤消息終止。

只有當(dāng)數(shù)據(jù)庫(kù)中啟用SNAPSHOT事務(wù)隔離級(jí)別的開關(guān)打開后，才能使用它。打開此開關(guān)將告知數(shù)據(jù)庫(kù)去設(shè)置版本化環(huán)境。理解這一點(diǎn)很重要，因?yàn)椋坏┌姹净_啟，數(shù)據(jù)庫(kù)會(huì)有維護(hù)版本化的開銷，無(wú)論是否有事務(wù)正在使用SNAPSHOT事務(wù)隔離級(jí)別。


例6. READ COMMITTED SNAPSHOT 隔離級(jí)別 到目前為止，所有的隔離級(jí)別都是將事務(wù)相互間隔離開來(lái)。一旦初始事務(wù)完成了，對(duì)其他事務(wù)變得不可用的資源才又變得可用。READ COMMITTED SNAPSHOT 隔離級(jí)別在這點(diǎn)上有所不同，它能夠讀取其已經(jīng)被他事務(wù)提交的數(shù)據(jù)。

READ COMMITTED SNAPSHOT 隔離級(jí)別也是通過(guò)數(shù)據(jù)庫(kù)開關(guān)來(lái)打開的。然后，任何使用READ COMMITTED SNAPSHOT 隔離級(jí)別的事務(wù)將通過(guò)版本化起作用。


希望通過(guò)以上具體實(shí)例，能夠幫助你理解如何正確地隔離事務(wù)和語(yǔ)句從而防止并發(fā)性問(wèn)題。

四、小結(jié) 隔離級(jí)別 ??解決的并發(fā)性問(wèn)題
存在的并發(fā)性問(wèn)題
READ UNCOMMITTED
不適用于并發(fā)場(chǎng)合
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
上述所有并發(fā)性問(wèn)題
事務(wù)訪問(wèn)的是虛擬快照，其他事務(wù)Committed的數(shù)據(jù)對(duì)當(dāng)前事務(wù)仍然不可見，也不允許Update被其他事務(wù)Updated的數(shù)據(jù)。
READ COMMITTED SNAPSHOT
上述所有并發(fā)性問(wèn)題
無(wú)