sqlplus之多版本

時(shí)間：2018-11-26 15:36:02

關(guān)鍵字： sqlplus 版本數(shù)據(jù)

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]oracle采用了一種多版本、讀一致(read-consistent)的并發(fā)模型。讀一致查詢：對(duì)于一個(gè)時(shí)間點(diǎn)(point in time)，查詢會(huì)產(chǎn)生一致的結(jié)果非阻塞查詢：查詢不會(huì)被寫(xiě)入器阻塞多版本(

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

讀一致查詢：對(duì)于一個(gè)時(shí)間點(diǎn)(point in time)，查詢會(huì)產(chǎn)生一致的結(jié)果

非阻塞查詢：查詢不會(huì)被寫(xiě)入器阻塞

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

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

創(chuàng)建一個(gè)測(cè)試表T，并把a(bǔ)ll_users表的一些數(shù)據(jù)加載到T表中

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

表已創(chuàng)建。

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

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

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

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

PL/SQL?過(guò)程已成功完成。

在同一會(huì)話中，再?gòu)谋碇袆h除所有數(shù)據(jù)，甚至commit提交了刪除所做的工作。

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

scott@ORCL>commit;
提交完成。

記錄行都沒(méi)有了，但是，還是可以通過(guò)游標(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é)果集那一刻(時(shí)間點(diǎn))就已經(jīng)確定了。

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

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

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

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

1.多版本和閃回

SCN(System Change Number or System Commit Number)

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

授權(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?過(guò)程已成功完成。

scott@ORCL>print?scn

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

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

首先看看emp表：

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

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

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

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的時(shí)間點(diǎn)上表中有什么：

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

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

不僅如此，這個(gè)功能還能跨事務(wù)邊界。我們甚至可以在統(tǒng)一查詢中得到同一個(gè)對(duì)象在"兩個(gè)時(shí)間點(diǎn)"上的結(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以及以上版本，有個(gè)“閃回”(flashback)命令，它使用了這種底層多版本技術(shù)，可以把對(duì)象返回到以前某個(gè)時(shí)間點(diǎn)的狀態(tài)：

scott@ORCL>flashback?table?emp?to?scn?:scn;
flashback?table?emp?to?scn?:scn
????????????????*
第?1?行出現(xiàn)錯(cuò)誤:
ORA-08189:?因?yàn)槲磫⒂眯幸苿?dòng)功能,?不能閃回表


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中，插入一行時(shí)就會(huì)為它分配一個(gè)rowid,而且這一行永遠(yuǎn)擁有這個(gè)rowid。

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

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

2.讀一致性和非阻塞讀

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

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

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

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

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

oracle事務(wù)機(jī)制：

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

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

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

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

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

數(shù)據(jù)的讀一致視圖總是在sql語(yǔ)句級(jí)執(zhí)行。sql語(yǔ)句的結(jié)果對(duì)于查詢開(kāi)始的時(shí)間點(diǎn) 來(lái)說(shuō)是一致的。

正因?yàn)檫@一點(diǎn)，所以下面的語(yǔ)句可以插入可預(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?過(guò)程已成功完成。

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

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

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

oracle為所有語(yǔ)句提供了這種讀一致性，所以如下的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