sqlplus之多版本

oracle采用了一種多版本、讀一致(read-consistent)的并發(fā)模型。

讀一致查詢：對于一個時間點(point in time)，查詢會產(chǎn)生一致的結(jié)果

非阻塞查詢：查詢不會被寫入器阻塞

多版本(multi-versioning)，oracle能夠同時物化多個版本的數(shù)據(jù)。

-----------------------------------------------------------------------------------------------

創(chuàng)建一個測試表T，并把all_users表的一些數(shù)據(jù)加載到T表中

scott@ORCL>create?table?t
??2??as
??3??select?*
??4??from?all_users;

表已創(chuàng)建。

然后在這個表上 打開一個游標(biāo)。在此沒有從該游標(biāo)讀取數(shù)據(jù)，只是打開游標(biāo)而已。

打開游標(biāo)時，oracle不復(fù)制任何數(shù)據(jù)，它會邊行進邊回答查詢。

只在獲取數(shù)據(jù)時它才從表中讀取數(shù)據(jù)。

scott@ORCL>variable?x?refcursor
scott@ORCL>begin
??2??open?:x?for?select?*?from?t;
??3??end;
??4??/

PL/SQL?過程已成功完成。

在同一會話中，再從表中刪除所有數(shù)據(jù)，甚至commit提交了刪除所做的工作。

scott@ORCL>delete?from?t;
已刪除37行。

scott@ORCL>commit;
提交完成。

記錄行都沒有了，但是，還是可以通過游標(biāo)獲取數(shù)據(jù)

scott@ORCL>print?x

USERNAME??????????????????????????USER_ID?CREATED
------------------------------?----------?--------------
PERFSTAT???????????????????????????????93?15-3月?-18
BI?????????????????????????????????????90?14-3月?-18
PM?????????????????????????????????????89?14-3月?-18
SH?????????????????????????????????????88?14-3月?-18
IX?????????????????????????????????????87?14-3月?-18
OE?????????????????????????????????????86?14-3月?-18
HR?????????????????????????????????????85?14-3月?-18
SCOTT??????????????????????????????????84?30-3月?-10
OWBSYS_AUDIT???????????????????????????83?30-3月?-10
OWBSYS?????????????????????????????????79?30-3月?-10
APEX_030200????????????????????????????78?30-3月?-10
APEX_PUBLIC_USER???????????????????????76?30-3月?-10
FLOWS_FILES????????????????????????????75?30-3月?-10
MGMT_VIEW??????????????????????????????74?30-3月?-10
SYSMAN?????????????????????????????????72?30-3月?-10
SPATIAL_CSW_ADMIN_USR??????????????????70?30-3月?-10
SPATIAL_WFS_ADMIN_USR??????????????????67?30-3月?-10
MDDATA?????????????????????????????????65?30-3月?-10
MDSYS??????????????????????????????????57?30-3月?-10
SI_INFORMTN_SCHEMA?????????????????????56?30-3月?-10
ORDPLUGINS?????????????????????????????55?30-3月?-10
ORDDATA????????????????????????????????54?30-3月?-10
ORDSYS?????????????????????????????????53?30-3月?-10
OLAPSYS????????????????????????????????61?30-3月?-10
ANONYMOUS??????????????????????????????46?30-3月?-10
XDB????????????????????????????????????45?30-3月?-10
CTXSYS?????????????????????????????????43?30-3月?-10
EXFSYS?????????????????????????????????42?30-3月?-10
XS$NULL????????????????????????2147483638?30-3月?-10
WMSYS??????????????????????????????????32?30-3月?-10
APPQOSSYS??????????????????????????????31?30-3月?-10
DBSNMP?????????????????????????????????30?30-3月?-10
ORACLE_OCM?????????????????????????????21?30-3月?-10
DIP????????????????????????????????????14?30-3月?-10
OUTLN???????????????????????????????????9?30-3月?-10
SYSTEM??????????????????????????????????5?30-3月?-10
SYS?????????????????????????????????????0?30-3月?-10

已選擇37行。

open命令返回的結(jié)果集那一刻(時間點)就已經(jīng)確定了。

打開時，我們根本沒有碰過表中的任何數(shù)據(jù)塊，但答案是固定的。

獲取數(shù)據(jù)之前，我們無法知道答案會是什么；但從游標(biāo)角度看，結(jié)果則是固定不變了。

打開游標(biāo)時，并非oracle將所有數(shù)據(jù)復(fù)制到另外某個位置；實際上是delete命令為我們把數(shù)據(jù)保留下來，把它放在一個稱之為undo段(undo segment)的數(shù)據(jù)區(qū)，也稱之為回滾段(rollback segment)。

-------------------------------------------------------------------------------

1.多版本和閃回

SCN(System Change Number or System Commit Number)

是oracle的內(nèi)部時鐘：每次發(fā)生提交時，這個時鐘就會向上滴答(遞增)

授權(quán)給Scott：

sys@ORCL>grant?"CONNECT"?to?scott;
授權(quán)成功。

sys@ORCL>grant?"DBA"?to?scott;
授權(quán)成功。

sys@ORCL>grant?execute?on?dbms_flashback?to?scott;
授權(quán)成功。

scott@ORCL>variable?SCN?number
scott@ORCL>exec?:scn?:=?dbms_flashback.get_system_change_number
PL/SQL?過程已成功完成。

scott@ORCL>print?scn

???????SCN
----------
???1364868

現(xiàn)在可以讓oracle提供SCN值所表示時間點的數(shù)據(jù)。以后再查詢oracle，就能看看這一時刻表中的內(nèi)容。

首先看看emp表：

scott@ORCL>select?count(1)?from?emp;

??COUNT(1)
----------
????????14

下面把這些信息都刪除，并驗證數(shù)據(jù)是否確實"沒有了"：

scott@ORCL>delete?from?emp;

已刪除14行。

scott@ORCL>select?count(1)?from?emp;

??COUNT(1)
----------
?????????0

此外，使用閃回查詢(即 AS OF SCN 或 AS OF TIMESTAMP子句)，可以讓oracle告訴我們SCN值為1364868的時間點上表中有什么：

scott@ORCL>select?count(1)?from?emp?AS?OF?SCN?:scn;

??COUNT(1)
----------
????????14

不僅如此，這個功能還能跨事務(wù)邊界。我們甚至可以在統(tǒng)一查詢中得到同一個對象在"兩個時間點"上的結(jié)果：

scott@ORCL>commit;
提交完成。

scott@ORCL>select?*
??2??from?(select?count(*)?from?emp),
??3??(select?count(*)?from?emp?as?of?scn:scn)
??4??/

??COUNT(*)???COUNT(*)
----------?----------
?????????0?????????14

oracle 10g以及以上版本，有個“閃回”(flashback)命令，它使用了這種底層多版本技術(shù)，可以把對象返回到以前某個時間點的狀態(tài)：

scott@ORCL>flashback?table?emp?to?scn?:scn;
flashback?table?emp?to?scn?:scn
????????????????*
第?1?行出現(xiàn)錯誤:
ORA-08189:?因為未啟用行移動功能,?不能閃回表


scott@ORCL>alter?table?emp?enable?row?movement;????#允許oracle修改分配給行的rowid
表已更改。

scott@ORCL>flashback?table?emp?to?scn?:scn;
閃回完成。

scott@ORCL>select?*
??2??from?(select?count(*)?from?emp),
??3??(select?count(*)?from?emp?as?of?scn:scn)
??4??/

??COUNT(*)???COUNT(*)
----------?----------
????????14?????????14

在oracle中，插入一行時就會為它分配一個rowid,而且這一行永遠(yuǎn)擁有這個rowid。

閃回表處理 會對emp完成delete，并且重新插入行，這樣就會為這些行分配一個新的rowid。

要支持閃回必須允許oracle執(zhí)行這個操作。

2.讀一致性和非阻塞讀

假設(shè)我們讀取的表在每個數(shù)據(jù)庫塊(數(shù)據(jù)庫中最小的存儲單元)中只存放一行

scott@ORCL>create?table?accounts
??2??(account_number?number?primary?key,
??3??account_balance?number
??4??);

表已創(chuàng)建。

scott@ORCL>insert?into?accounts?values(123,500);
已創(chuàng)建?1?行。

...

scott@ORCL>select?*?from?accounts;

ACCOUNT_NUMBER?ACCOUNT_BALANCE
--------------?---------------
???????????123?????????????500
???????????234?????????????250
???????????345?????????????400
???????????456?????????????100

scott@ORCL>select?sum(account_balance)?from?accounts;

SUM(ACCOUNT_BALANCE)
--------------------
????????????????1250

場景：如果我們現(xiàn)在讀了第1行，準(zhǔn)備讀第2行和第3行時，一臺ATM針對這個表發(fā)生了一個事務(wù)，將400從賬號123轉(zhuǎn)到賬戶456，結(jié)果會怎么樣？讀一致性 就是oracle為了避免發(fā)生這種情況所采用的方法。

在幾乎所有的其他數(shù)據(jù)庫中，如果想得到"一致"和"正確"的查詢答案，就必須在計算總額時鎖定整個表，或者在讀取記錄行時對其鎖定。這樣一來，獲取結(jié)果時 就可以防止別人再做修改。如果提前鎖定表，就會得到查詢開始時數(shù)據(jù)庫中的結(jié)果。如果在讀取數(shù)據(jù)時鎖定(共享讀鎖，可以防止更新，但不妨礙讀取器讀取數(shù)據(jù)庫),就會得到查詢結(jié)束時數(shù)據(jù)庫中的結(jié)果。這兩種方法都會影響并發(fā)性。

oracle利用多版本來得到結(jié)果，也就是查詢開始時那個時間點的結(jié)果，然后完成查詢，而不做任何鎖定(轉(zhuǎn)賬事務(wù)更新第1行和第4行時，這些行會對其他寫入器鎖定，但不會對讀取器鎖定，如這里的 select sum...)。實際上，oracle根本沒有"共享鎖"。

oracle事務(wù)機制：

只要你修改數(shù)據(jù)，oracle就會創(chuàng)建撤銷(undo)條目，這些undo條目寫至undo段(撤銷段,undo segment)。

如果事務(wù)失敗，需要撤銷，oracle就會從這個回滾段讀取"之前"的映像，并恢復(fù)數(shù)據(jù)。

除了使用回滾段數(shù)據(jù) 撤銷事務(wù)外，oracle還會用它撤銷 讀取塊時對塊所做的修改，使之恢復(fù)到查詢開始前的時間點。

時間查詢轉(zhuǎn)賬事務(wù)T1讀取第1行；到目前為止 sum=500?T2?更新第1行；對第1行加一個排他鎖，阻止其他更新第1行?，F(xiàn)有100T3讀取第2行；到目前為止sum=750?T4讀取第3行；到目前為止sum=1150?T5?更新第4行；對第4行加一個排他鎖，阻止其他更新第4行(但不阻止讀操作)?，F(xiàn)有500T6讀取第4行，發(fā)現(xiàn)第4行已經(jīng)修改，這會將塊回滾到T1時刻的狀態(tài)。查詢從這個塊讀到值100?T7得到答案1250?

在T6時，oracle有效的"擺脫'了事務(wù)加在第4行上的鎖。非阻塞性讀是這樣實現(xiàn)的:oracle只看數(shù)據(jù)是否改變，它并不關(guān)心數(shù)據(jù)當(dāng)前是否鎖定(鎖定意味著數(shù)據(jù)已經(jīng)改變)。oracle只是從回滾段中取回原來的值，并繼續(xù)處理下一個數(shù)據(jù)塊。

數(shù)據(jù)的讀一致視圖總是在sql語句級執(zhí)行。sql語句的結(jié)果 對于查詢開始的時間點 來說是一致的。

正因為這一點，所以下面的語句可以插入可預(yù)知的數(shù)據(jù)集：

scott@ORCL>select?*?from?t;

USERNAME??????????????????????????USER_ID?CREATED
------------------------------?----------?--------------
yin???????????????????????????????1?????????18-3月?-18


scott@ORCL>begin
??2??for?x?in(?select?*?from?t)
??3??loop
??4??insert?into?t?values(x.username,x.user_id,x.created);
??5??end?loop;
??6??end;
??7??/

PL/SQL?過程已成功完成。

scott@ORCL>select?*?from?t;

USERNAME??????????????????????????USER_ID?CREATED
------------------------------?----------?--------------
yin?????????????????????????????????????1?18-3月?-18
yin?????????????????????????????????????1?18-3月?-18

select * from t的結(jié)果在查詢開始執(zhí)行時 就已經(jīng)確定了。這個select并不看insert生成的任何新數(shù)據(jù)。

oracle為所有語句提供了這種讀一致性，所以如下的insert也是可預(yù)知的：

scott@ORCL>insert?into?t?select?*?from?t;

已創(chuàng)建2行。

scott@ORCL>select?*?from?t;

USERNAME??????????????????????????USER_ID?CREATED
------------------------------?----------?--------------
yin?????????????????????????????????????1?18-3月?-18
yin?????????????????????????????????????1?18-3月?-18
yin?????????????????????????????????????1?18-3月?-18
yin?????????????????????????????????????1?18-3月?-18