Oracle 資料庫隔離等級，特性，問題和解決方案

　　“……老手根本就不會照著指示去做，他邊做邊取捨，因此必須全神貫注於手上的工作，即使他沒有刻意這樣做，他的動作和機器之間也自然有一種和諧的感覺。他不需要遵照任何書面的指示，因為手中的機器給他的感覺決定他的思路和動作，同時也影響他手中的工作。所以機器和他的思想同時不斷地改變，一直到把事情做好了，他的內心才真正地安寧下來。”
　　“聽起來好像藝術一樣。”
　　　　　　　　　　　　　　　　　　——羅伯特·M.波西格 《禪與>機車維修藝術》

　　如果沒有任何資料庫隔離策略，在多使用者（多事務）並發時，會產生下列問題：
　　- 丟失更新（lost update）：兩個事務同時更新同一條資料時，會發生更新丟失。
　　　　例如：使用者A讀取學號為107的學生（學號=107，姓名=“小明”，年齡=28）
　　　　=> 使用者B讀取學號為107的學生（學號=107，姓名=“小明”，年齡=28）
　　　　=> 使用者A把姓名更改為“王小明”（學號=107，姓名=“王小明”，年齡=28）
　　　　=> 使用者B把年齡更改為33（學號=107，姓名=“小明”，年齡=33）
　　　　=> 使用者A提交（學號=107，姓名=“王小明”，年齡=28）
　　　　=> 使用者B提交（學號=107，姓名=“小明”，年齡=33）
　　　　使用者A對學生姓名的更新丟失了。
　　- 髒讀（dirty read）：當一個事務讀取另一個事務尚未提交的修改時，產生髒讀。
　　　　例如：使用者A讀取學號為107的學生（學號=107，姓名=“小明”，年齡=28）
　　　　=> 使用者A把姓名更改為“王小明”（學號=107，姓名=“王小明”，年齡=28）
　　　　=> 使用者B讀取學號為107的學生（學號=107，姓名=“王小明”，年齡=28）
　　　　=> 使用者A撤銷更改，交易回復（學號=107，姓名=“小明”，年齡=28）
　　　　這樣使用者B相當於讀取了一個從未存在過的資料“王小明”。如果涉及到金額的話問題更為嚴重，因為使用者B讀取了一個金額之後，很可能把這個金額與其它金額累加，再把結果儲存到摘要資料之中，這樣在月底對不上賬的時候，由於使用者A復原了事務，資料庫內不會有任何操作記錄，這樣使用者B是何時、從哪裡讀取了錯誤資料根本無從查起。
　　- 不可重現的讀取（nonrepeatable read）：同一查詢在同一事務中多次進行，在此期間，由於其他事務提交了對資料的修改或刪除，每次返回不同的結果。
　　　　例如：假設學生表裡只有“小明”和“小麗”2條記錄（小明.年齡=20，小麗.年齡=30）。如果使用者A把“小明.年齡”更改為40、把“小麗.年齡”更改為50並提交事務。在使用者A修改資料並提交前，學生的平均年齡為(20+30)/2=25；在使用者A修改資料並提交後，學生的平均年齡為(40+50)/2=45。
現在考慮在使用者A更新 2 名學生的年齡時，使用者B執行了一個計算平均年齡的事務：

如前所述，在使用者A修改資料並提交前，學生的平均年齡為(20+30)/2=25；在使用者A修改資料並提交後，學生的平均年齡為(40+50)/2=45。使用者B計算得出的平均年齡是 25 或 45 都是可以接受的，但是在上例中使用者B計算得出的平均年齡 35 是從未出現在系統中的錯誤數值。
　　- 幻讀（phantom read）：同一查詢在同一事務中多次進行，由於其他提交事務所做的插入操作，雖然查詢條件相同，每次返回的結果集卻不同。

事務B使用相同的條件進行了2次查詢/篩選，一次是為了向費用結算表插入摘要資料，一次為了確定對費用明細表的修改範圍。在這兩次篩選之間，事務A提交了一條新的費用詳細資料，導致兩次篩選的結果不一致。

隔離等級

　　為避免上述並發問題，ANSI/ISO SQL92標準定義了一些隔離等級：

　　- 讀取未提交資料（read uncommitted）

　　- 讀取已提交資料（read committed）

　　- 可重現的讀取（repeatable read）

　　- 序列化（serializable）

通過指定不同的隔離等級，可避免上述一種或多種並發問題，見。

細心的讀者可能已經注意到不包括“丟失更新（lost update）”，這是因為“丟失更新”問題需要使用樂觀鎖或悲觀鎖來解決，超出本文範圍，先不詳述。

Oracle 的隔離等級

　　SQL92定義的隔離等級在理論上很完善，但是 Oracle 顯然認為在實際實現的時候並不應該完全照搬SQL92的模型。 
　　- Oracle不支援 SQL92 標準中的“讀取未提交資料（read uncommitted）”隔離等級，想要髒讀都沒可能。
　　- Oracle 支援 SQL92 標準中的“讀取已提交資料（read committed）”隔離等級，（這也是Oracle預設的隔離等級）。
　　- Oracle不支援 SQL92 標準中的“可重現的讀取（repeatable read）”隔離等級，要想避免“不可重現的讀取（nonrepeatable read）”可以直接使用“序列化（serializable）”隔離等級。
　　- Oracle 支援 SQL92 標準中的“序列化（serializable）”隔離等級，但是並不真正阻塞事務的執行（這一點在後文還有詳述）。
　　- Oracle 還另外增加了一個非SQL92標準的“唯讀(read-only)”隔離等級。

Oracle的序列化（serializable）隔離等級

　　序列化，顧名思義，是讓並發的事務感覺上是一個挨一個地串列執行的。之所以說是“感覺上”，是因為當2個事務並發時，Oracle並不會阻塞其中一個事務去等待另一個事務執行完畢再執行，而是仍然讓2個事務同時並行，那麼如何能“感覺”是串列的呢？請看的實驗。

使用者B的事務因為指定了serializable隔離等級，所以雖然在查詢費用明細表之前，使用者A提交了對費用明細表的更改，但是因為使用者A提交的更改是在使用者B的事務開始之後才提交的，所以這個更改對使用者B的事務不可見。也就是說，使用者B的事務開始之後，其它事務提交的更改都不會再影響事務內的查詢結果，這樣感覺上使用者A的事務好像是在使用者B的事務結束之後才執行的似的。這本來是非常好的一個特性，極大地提高了並行性，但是也會造成問題。

　　問題1：Oracle的這種“假串列”會讓嚴格依賴於時間的程式產生混亂。

　　請看這個例子，對費用結算的例子稍稍做了一點改動。

程式員的本意是統計2012-3-4這天從零點至運行程式之時的費用總額。如果他以為 Oracle 的 serializable 會像 C# 的 lock 一樣阻塞其它事務的話，就會對結果非常吃驚：在2012-3-4 0:00 ~ 2012-3-4 10:02 實際有3條費用明細，總額為20+30+100=150，而不是使用者B的事務統計得出的50。

　　問題2：ORA-08177 Can't serialize access for this transaction (無法序列化訪問)錯誤。

　　如果你使用了 serialize 隔離等級，沒準你的客戶會經常抱怨這個隨機出現的錯誤。兄弟，你並不孤獨！
　　導致這個錯誤的原因有2個：
　　(1) 兩個事務同時更新了同一條資料。你可以這樣重現這個錯誤：事務B開始（使用serialize 隔離等級) => 事務A開始，更新 表1.RowA 但不提交 => 事務B更新表1.RowA，因為行鎖定而被阻塞 => 事務A提交 => 事務B報 ORA-08177 錯誤。
　　(2) 事務所更新的表的 initrans 參數太小。Oracle 官方文檔的說法是，如果使用了 serialize 隔離等級，表的 initrans 參數最小要設定成3（預設是1）。

alter table 費用明細表 initrans 3;

　　原文：“Oracle Database stores control information in each data block to manage access by concurrent transactions. Therefore, if you set the transaction isolation level to SERIALIZABLE, then you must use the ALTER TABLE command to set INITRANS to at least 3. This parameter causes Oracle Database to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. Higher values should be used for tables that will undergo many transactions updating the same blocks.”
　　注意，人家說的是“最小是3”。我用自己筆記本裡的 32 位 Oracle10g 測試的結果是設定成 3 也會頻繁地報 ORA-08177 錯誤。後來改成5 和 10，都不行。改成50，終於不報錯了。但是都說了這個錯誤是隨機的，有時候3也沒問題的——反過來說，設定成50也未必保險。坑爹啊！真坑爹！！這就像菜譜裡面寫的“放入適量的油……”，他喵的到底多少算是“適量”啊？！！！
　　有興趣的讀者可以使用的語句實際測試一下。

　　我的建議是，還是盡量不要用 serialize 隔離等級吧，使用者是不會理解什麼叫“無法序列化訪問”的，他只會覺得你的“XX功能會隨機地不好用”倒是真的。稍後我們再簡單討論一下不用 serialize 隔離等級如何避免幻讀。現在先來看一下 Oracle 官方文檔建議的適合使用 serialize 隔離等級的3種情況。

　　(1) With large databases and short transactions that update only a fewrows（大資料庫、只更新幾條資料的短事務）

　　(2) Where the chance that two concurrent transactions will modify thesame rows is relatively low（2個並發事務更新同一條資料的幾率不大）

　　(3) Where relatively long-running transactions are primarily read only（相對已耗用時間較長的事務主要用來讀取資料）

使用預設的 read committed 隔離等級，如何避免幻讀產生的問題

　　使用預設的 read committed 隔離等級，如何編寫程式才能避免幻讀產生的問題呢？首先，無論是“不可重現的讀取（nonrepeatable read）”還是“幻讀（phantom read）”，都是因為程式反覆讀取資料產生的。所以首先需要做的是，在一個事務裡確保唯讀取資料一次。最好用C#而不是預存程序實現商務邏輯，這樣很容易做到唯讀取一次，然後把結果存放到IList或IDictionary裡。比較難辦的是需要更新資料的情況。回顧一下前面所舉的幻讀的例子。

事務B使用相同的條件進行了2次查詢/篩選，一次是為了向費用結算表插入摘要資料，一次為了確定對費用明細表的修改範圍。在這兩次篩選之間，事務A提交了一條新的費用詳細資料，導致兩次篩選的結果不一致。要避免這個問題，還是要貫徹“唯讀取一次”的原則，或者更廣義地說，是“只確定一次篩選範圍”。大致有2種方法。
　　<法一> 可以先把合格費用明細讀取出來儲存到一個列表裡，然後無論統計還是更新，都局限於這個列表裡的資料。下面的C#代碼與的功能相同，但是沒有幻讀的問題。

// 使用者B的事務開始

IList<費用明細> chargeList = 費用明細Repository.擷取未結算列表();
費用結算 balance = new 費用結算 
{
    總金額 = chargeList.Sum(t => t.金額),
    結算編號 = "J122"
};
費用結算Repository.Save(balance);

// 這時候使用者A提交了一條新的費用明細，不過沒關係

foreach(費用明細 charge in chargeList)
{
    charge.是否已結算 = 1;
    charge.結算編號 = "J122";
    費用明細Repository.Update(charge);
}

這個方法的缺點是要對 chargeList 裡的每個實體 Update 一次，如果資料量較大可能會有效能問題。這時候可以用<法二>。
注 本文為了表述的方便使用了中文和英文混雜的代碼，實際編程的時候不要這樣做。
　　<法二> 使用事務B專屬的方法標識出操作資料的範圍。

注 雖然是用SQL語句來示範的，使用C#（實體+ORM）同樣可以用這種方法。

嚴格依賴時間的程式

嚴格來說這並不是幻讀造成的問題——事務A還沒提交呢。這種設計十分危險，無論使用 read committed 還是 serializable 隔離等級都不足以避免並發造成的不一致，應該盡量避免這樣的設計。依賴時間很危險，因為系統時間是隨時可能被系統管理員更改的，更別提有些國家和地區會實行夏時制，想想看，事務B提交了之後，系統時間被回撥了1小時！
　　然而世事往往不盡如人意，你可能不幸遇到了這樣一個遺留系統，或者使用者有很多其它的業務或與你互動的系統嚴格依賴於時間而逼得你不得不這麼做的時候，該怎麼辦呢？
　　<法一> 在商務邏輯層面，可以把使用者B和使用者A的兩個方法使用C#提供的線程同步技術序列化——理論上行的通，但是操作費用明細實體的方法那麼多，很容易有所遺漏。
　　<法二> 在Repository層面，為費用明細實體設定一個令牌，並且可以設定是否進入令牌模式。在令牌模式下，費用明細Repository裡面的所有持久化操作都必須拿到令牌才能操作，拿不到令牌直接拋異常。平時的業務操作都在非令牌模式下工作。在使用者B想要進行結算操作時，事務開始之後，馬上設定成令牌模式，然後擷取令牌，這樣就能確保此時只有使用者B才能操作費用明細表了。此法雖然並發性很差，但是既簡單又保險。而且很多時候像結算這樣的操作一個月（或一天）只進行一次，並發性差一些也可以忍受。值得注意的是下面這種情況。

雖然發生的機率不高，但是讓令牌法徹底失效了。綜合考慮系統時間被管理員改變的可能性，僅僅在結算事務裡獨佔令牌也是不夠的，還必須在費用明細Repository.Save()方法裡驗證費用明細.建立時間必須大於最近一次的結算時間。