深入理解oracle的事務(wù)隔離性

轉(zhuǎn)： http://www.bkjia.com/oracle/925189.html

深入理解oracle的事務(wù)隔離性

在Oracle關(guān)系數(shù)據(jù)庫(kù)中，我們先來(lái)看下面這個(gè)問(wèn)題：
A事務(wù)：select <cols> from T where id > 10 and id < 10000;
B事務(wù)：update T set id = 45000 where id = 4501
兩個(gè)事務(wù)按下面的順序執(zhí)行：
A事務(wù)：|--------------------------------|commit
B事務(wù)： |-------------|commit
也就是A事務(wù)先開(kāi)始執(zhí)行，過(guò)一段時(shí)間B事務(wù)再開(kāi)始執(zhí)行，但是B事務(wù)先執(zhí)行完并commit提交了，A事務(wù)又過(guò)了一段時(shí)間才完成。那么問(wèn)題來(lái)了，在這種情況下，問(wèn)A事務(wù)能不能取得正確的結(jié)果，兩個(gè)事務(wù)之間會(huì)不會(huì)有干擾，怎么干擾？
這是一個(gè)典型的關(guān)系型數(shù)據(jù)庫(kù)事務(wù)的隔離性問(wèn)題，而且，針對(duì)不同的數(shù)據(jù)庫(kù)（存儲(chǔ)引擎），可能會(huì)有不同的表現(xiàn)。

根據(jù)上面的描述，以oracle為例，它的缺省數(shù)據(jù)庫(kù)隔離級(jí)別是讀已提交（read-committed），事務(wù)A持有一個(gè)讀鎖（瞬間共享讀鎖），B持有一個(gè)排它寫鎖。
Read Committed讀已提交的官方定義是，通過(guò)“瞬間共享讀鎖”和“排他寫鎖”實(shí)現(xiàn)。讀取數(shù)據(jù)的事務(wù)允許其他事務(wù)繼續(xù)訪問(wèn)該行數(shù)據(jù)，但是未提交的寫事務(wù)將會(huì)禁止其他事務(wù)訪問(wèn)該行。
按照讀已提交的定義，似乎按上題的條件，A，B兩個(gè)事務(wù)都能夠正確完成并commit提交。

但關(guān)系數(shù)據(jù)庫(kù)廠商，它們的產(chǎn)品往往不會(huì)完完全全的按照規(guī)范來(lái)實(shí)現(xiàn)，總會(huì)附加一些自己特有的東西在里面。那么我們接下來(lái)詳細(xì)分析一下，oracle是怎樣處理的，SQL語(yǔ)句執(zhí)行的內(nèi)部過(guò)程相當(dāng)復(fù)雜，大概比較顯式和通俗易懂的是，先運(yùn)行執(zhí)行計(jì)劃，然后執(zhí)行SQL優(yōu)化等策略，接著可能根據(jù)關(guān)鍵字，進(jìn)行加鎖處理，上下文切換等操作，比如select語(yǔ)句就會(huì)加一個(gè)讀鎖。

在執(zhí)行DML語(yǔ)句時(shí)，Oracle會(huì)給每一行增加一個(gè)sn序列號(hào)，比如select <cols> from T where id > 10 and id < 10000;這條語(yǔ)句，查詢出將近1w條數(shù)據(jù)，在執(zhí)行掃描的時(shí)候，發(fā)現(xiàn)符合條件的行就會(huì)加一個(gè)sn（實(shí)際操作時(shí)，可能是和內(nèi)存中某個(gè)sn數(shù)值關(guān)聯(lián)起來(lái)），這個(gè)sn序列號(hào)實(shí)際上被當(dāng)做樂(lè)觀鎖使用。
那么可能出現(xiàn)下面的情況，事務(wù)A的select語(yǔ)句還沒(méi)有執(zhí)行完，當(dāng)執(zhí)行到2000條的時(shí)候，B開(kāi)始了一個(gè)update T set id = 45000 where id = 4501的事務(wù)，由于在oracle中，寫鎖的級(jí)別高于讀鎖，因此這時(shí)候B事務(wù)的update語(yǔ)句取得寫鎖，成功執(zhí)行完并commit，交出寫鎖。
當(dāng)先開(kāi)始的select語(yǔ)句執(zhí)行到4501時(shí)，如果此時(shí)B事務(wù)已經(jīng)commit，那么A事務(wù)會(huì)接著執(zhí)行下去，成功commit，反之，當(dāng)A事務(wù)執(zhí)行到4501行時(shí)，B事務(wù)還未commit，那么二者的鎖在4501這條數(shù)據(jù)發(fā)生沖突,這時(shí)整個(gè)A事務(wù)就會(huì)出錯(cuò)。

這里插一句，對(duì)于DML的select語(yǔ)句來(lái)說(shuō)，只具有讀一致性，所以失敗了僅僅是報(bào)錯(cuò)放棄，不會(huì)回滾。

然而，上面的描述卻有一個(gè)知識(shí)缺失點(diǎn)，就是所謂的MVCC(Multi-Version Concurrency Control)---基于多版本的并發(fā)控制協(xié)議 (注：與MVCC相對(duì)的，是基于鎖的并發(fā)控制，Lock-Based Concurrency Control)。MVCC最大的好處是：讀不加鎖，讀寫不沖突。在讀多寫少的OLTP應(yīng)用中，讀寫不沖突是非常重要的，極大的增加了系統(tǒng)的并發(fā)性能，現(xiàn)階段幾乎所有的RDBMS，都支持了MVCC。

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

在oracle里，undo就是所謂的快照。如果undo夠大的話，A事務(wù)的select返回的是沒(méi)有執(zhí)行update的語(yǔ)句前的數(shù)據(jù)；如果undo不夠大，A事務(wù)的select會(huì)直接報(bào)錯(cuò)沒(méi)有返回值，因?yàn)槭请[式提交，所以并不會(huì)rollback回滾。
這就是oracle的經(jīng)典錯(cuò)誤ORA-01555快照過(guò)舊。

再回到一開(kāi)始的原題目中，當(dāng)執(zhí)行事務(wù)A的select語(yǔ)句時(shí)，并沒(méi)有明確指出是快照讀還是當(dāng)前讀。因此，為嚴(yán)密起見(jiàn)，我們最終的結(jié)果是：
1.如果A事務(wù)執(zhí)行的是快照讀，如果undo夠大的話，A,B事務(wù)都能夠正確commit提交，A事務(wù)的select返回的是沒(méi)有執(zhí)行update的語(yǔ)句前的數(shù)據(jù)；如果undo不夠大，B事務(wù)能夠正確commit，A事務(wù)的select會(huì)直接報(bào)錯(cuò)沒(méi)有返回值，事實(shí)上數(shù)據(jù)庫(kù)的讀寫事務(wù)，絕大多數(shù)都屬于這種情況；
2.如果A事務(wù)執(zhí)行的是當(dāng)前讀，那么當(dāng)A事務(wù)的select讀操作和B事務(wù)的update寫操作沒(méi)有沖突時(shí)（不會(huì)同時(shí)讀寫4501那一行），兩個(gè)事務(wù)都能正確執(zhí)行；反之，A事務(wù)是有可能出錯(cuò)的。并不是A事務(wù)只要先執(zhí)行，兩個(gè)事務(wù)就一定能成功commit提交。

快照讀：select 

當(dāng)前讀：select * from xx for update; update ; delete;