深入理解oracle的事務隔離性

轉： http://www.bkjia.com/oracle/925189.html

深入理解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;