[轉載] 微軟SQL Server交易隔離等級執行個體簡介

源文： http://untitled.spaces.live.com/Blog/cns!86B82838704E0D5A!819.entry

 

資料庫的交易隔離等級（TRANSACTION ISOLATION LEVEL）是一個資料庫上很基本的一個概念。為什麼會有交易隔離等級，SQL Server上實現了哪些交易隔離等級？交易隔離等級的前提是一個多使用者、多進程、多線程的並發系統，在這個系統中為了保證資料的一致性和完整性，我們引入了交易隔離等級這個概念，對一個單使用者、單線程的應用來說則不存在這個問題。

首先，我們來看一下高並發的系統中會存在哪些問題，為了便於理解我們以張三在招商銀行的帳號和存款為例。

一、準備工作：

1. 建立一個銀行帳號Table（只是為了說明問題，不考慮表的設計範式）
CREATE TABLE dbo.BankAccount
(
   BankAccountId CHAR(16)       NOT NULL, -- 銀行帳號
   UserName      NVARCHAR(32)   NOT NULL, -- 使用者
   Balance       DECIMAL(19, 2) NOT NULL, -- 餘額
   LastUpdate    SMALLDATETIME  NOT NULL
)
GO

2. 準備資料
INSERT INTO dbo.BankAccount
     VALUES ('9555500100071120', N'張三', 10000.00, GETDATE()) -- 北京分行帳號
INSERT INTO dbo.BankAccount
     VALUES ('9555507551227787', N'張三', 20000.00, GETDATE()) -- 深圳分行帳號
GO

3. 查看資料
SELECT * FROM dbo.BankAccount

二、應用情境

假設張三在招商銀行開設了兩個帳號，一個是招商銀行北京分行，一個是招商銀行深圳分行，兩個帳號的餘額分別是10,000和20,000。

1. 張三在網上做了一筆交易，交易額100，買方小王通過銀行匯款100到張三的北京分行的帳號（見下面左圖），櫃檯操作人員向張三帳號存入100（事務一），然後系統些動作記錄（假設需要10秒，WAITFOR DELAY '00:00:10'）正在此時張三在ATM查了一下帳號上餘額（事務二），發現已經是10100，於是回去準備發貨，但是事務一在寫動作記錄時逾時，這是交易回復，存款交易被取消，錢退給了小王，這樣張三查到的帳號餘額事實上是事務一還沒有提交的資料，導致張三錯誤的認為已經收到交易款項。
 
一個事務讀到另外一個事務還沒有提交的資料，我們稱之為髒讀。
解決方案：把交易隔離等級調整到READ COMMITTED，即把右中的SET TRAN ISOLATION LEVEL READ UNCOMMITTED更改成中的SET TRAN ISOLATION LEVEL READ COMMITTED。這時我們重複上面的動作會發現事務二會一直等到事務一執行完畢再返回結果，因為此時事務以已經把自己的更改ROLLBACK了，所以事務二可以返回正確的結果。

2. 張三先後兩次查詢某一帳號的餘額，在兩次查詢期間，小王完成了銀行轉賬，導致兩次的查詢結果不同。
一個事務先後讀取同一條記錄，但兩次讀取的資料不同，我們稱之為不可重複讀取。
解決方案：把交易隔離等級調整到REPEATABLE READ。在中使用SET TRAN ISOLATION LEVEL REPEATABLE READ。這時我們重複上面的動作會發現事務二會一直等到事務一執行完畢再返回結果。

3. 張三妻子先後兩次查詢張三招商銀行所有帳號的總餘額，而在此期間張三在廣州招商銀行分行成功開設了一個帳號，並存入5000，導致張三妻子兩次查詢的總餘額不同，在此期間張三原有兩個帳號的餘額均未發生改變。
 
一個事務先後讀取一個範圍的記錄，但兩次讀取的紀錄數不同，我們稱之為幻象讀。
解決方案：把交易隔離等級調整到SERIALIZABLE。在中使用SET TRAN ISOLATION LEVEL SERIALIZABLE。這時我們重複上面的動作會發現事務二會一直等到事務一執行完畢再返回結果。

 

三、總結

交易隔離等級是通過資料庫的鎖機制來控制的，在不同的應用情境需要應用不同的交易隔離等級，SQL Server預設的交易隔離等級是READ COMMITTED，預設的隔離等級，已經可以滿足我們大部分應用的需求。