深入理解oracle的事務隔離性

轉：

深入理解oracle的事務隔離性

在Oracle關聯式資料庫中，我們先來看下面這個問題：
A事務：select <cols> from T where id > 10 and id < 10000;
B事務：update T set id = 45000 where id = 4501
兩個事務按下面的順序執行：
A事務：|--------------------------------|commit
B事務： |-------------|commit
也就是A事務先開始執行，過一段時間B事務再開始執行，但是B事務先執行完並commit提交了，A事務又過了一段時間才完成。那麼問題來了，在這種情況下，問A事務能不能取得正確的結果，兩個事務之間會不會有干擾，怎麼干擾？
這是一個典型的關係型資料庫事務的隔離性問題，而且，針對不同的資料庫（儲存引擎），可能會有不同的表現。

根據上面的描述，以oracle為例，它的預設資料庫隔離級別是讀已提交（read-committed），事務A持有一個讀鎖（瞬間共享讀鎖），B持有一個排它寫鎖。
Read Committed讀已提交的官方定義是，透過“瞬間共享讀鎖”和“排他寫鎖”實現。讀取資料的事務允許其他事務繼續訪問該行資料，但是未提交的寫事務將會禁止其他事務訪問該行。
按照讀已提交的定義，似乎按上題的條件，A，B兩個事務都能夠正確完成並commit提交。

但關聯式資料庫廠商，它們的產品往往不會完完全全的按照規範來實現，總會附加一些自己特有的東西在裡面。那麼我們接下來詳細分析一下，oracle是怎樣處理的，SQL語句執行的內部過程相當複雜，大概比較顯式和通俗易懂的是，先執行執行計劃，然後執行SQL最佳化等策略，接著可能根據關鍵字，進行加鎖處理，上下文切換等操作，比如select語句就會加一個讀鎖。

在執行DML語句時，Oracle會給每一行增加一個sn序列號，比如select <cols> from T where id > 10 and id < 10000;這條語句，查詢出將近1w條資料，在執行掃描的時候，發現符合條件的行就會加一個sn（實際操作時，可能是和記憶體中某個sn數值關聯起來），這個sn序列號實際上被當做樂觀鎖使用。
那麼可能出現下面的情況，事務A的select語句還沒有執行完，當執行到2000條的時候，B開始了一個update T set id = 45000 where id = 4501的事務，由於在oracle中，寫鎖的級別高於讀鎖，因此這時候B事務的update語句取得寫鎖，成功執行完並commit，交出寫鎖。
當先開始的select語句執行到4501時，如果此時B事務已經commit，那麼A事務會接著執行下去，成功commit，反之，當A事務執行到4501行時，B事務還未commit，那麼二者的鎖在4501這條資料發生衝突,這時整個A事務就會出錯。

這裡插一句，對於DML的select語句來說，只具有讀一致性，所以失敗了僅僅是報錯放棄，不會回滾。

然而，上面的描述卻有一個知識缺失點，就是所謂的MVCC(Multi-Version Concurrency Control)---基於多版本的併發控制協議 (注：與MVCC相對的，是基於鎖的併發控制，Lock-Based Concurrency Control)。MVCC最大的好處是：讀不加鎖，讀寫不衝突。在讀多寫少的OLTP應用中，讀寫不衝突是非常重要的，極大的增加了系統的併發效能，現階段幾乎所有的RDBMS，都支援了MVCC。

在MVCC併發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。快照讀，讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖。當前讀，讀取的是記錄的最新版本，並且，當前讀返回的記錄，都會加上鎖(一種就是上面提及的sn序列號方式的樂觀鎖)，保證其他事務不會再併發修改這條記錄。

在oracle裡，undo就是所謂的快照。如果undo夠大的話，A事務的select返回的是沒有執行update的語句前的資料；如果undo不夠大，A事務的select會直接報錯沒有返回值，因為是隱式提交，所以並不會rollback回滾。
這就是oracle的經典錯誤ORA-01555快照過舊。

再回到一開始的原題目中，當執行事務A的select語句時，並沒有明確指出是快照讀還是當前讀。因此，為嚴密起見，我們最終的結果是：
1.如果A事務執行的是快照讀，如果undo夠大的話，A,B事務都能夠正確commit提交，A事務的select返回的是沒有執行update的語句前的資料；如果undo不夠大，B事務能夠正確commit，A事務的select會直接報錯沒有返回值，事實上資料庫的讀寫事務，絕大多數都屬於這種情況；
2.如果A事務執行的是當前讀，那麼當A事務的select讀操作和B事務的update寫操作沒有衝突時（不會同時讀寫4501那一行），兩個事務都能正確執行；反之，A事務是有可能出錯的。並不是A事務只要先執行，兩個事務就一定能成功commit提交。

快照讀：select 

當前讀：select * from xx for update; update ; delete;