【原創】Oracle 事務探索與例項(二)

更多精彩內容盡在 www.leonarding.com

《Oracle 事務探索與例項(二)》

一 資料庫版本

SYS@LEO1>select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

二 事務那些事兒(二)

1.事務：事務可以理解為一系列動作的邏輯單位，我們把這一系列動作叫做一個事務。這一系列動作要持續完成的，中間不可以斷。

2.事務與鎖的關係：事務的併發是由鎖來控制的（同一時間只允許一個會話來操作），鎖是資料庫併發操作中最常見的保護機制。不同的資料庫鎖機制也有很大差異。

Oracle與SQL server的區別

Oracle                                              SQL server

可以實現讀/寫互不阻塞，利用undo實現              讀會阻塞寫，寫也會阻塞讀，用查詢鎖定實現

鎖作為資料塊一種屬性存在，沒有鎖管理器            使用鎖管理器控制事務併發，代價昂貴

主張按照業務需求確定事務邊界                      提倡儘快提交事務

3.事務控制：commit&rollback都是一個事務的結束

           我們可以在一個事務生命週期的一半設定一個save point，遇到問題我們可以回退到這個儲存點而不需要把事務全部回退，從而更加精細化的控制事務。

4.分散式事務：

三 舉一個例子，說明save point的用處，給出SQL演示。

儲存點save point：我們可以理解為放置在事務中的一個“標籤點”，防止一部分操作錯誤導致整個事務重新執行。我們只需要回退到這個標籤點的位置繼續重做後面的操作即可。

例項

鈴鈴鈴。。。下課鈴響了，剛剛上完tiger的“大資料探索之旅”課程，tiger老師在下課後給大家留了一道小作業，現在想把作業成績給大家登記下來。

LEO1@LEO1>drop table leo1 purge;                   清空環境

Table dropped.

LEO1@LEO1>create table leo1 (x int);                  建立成績表

Table created.

LEO1@LEO1>insert into leo1 values(10);                學號1的同學成績為10

1 row created.

LEO1@LEO1>select * from leo1;

    X

----------

    10

LEO1@LEO1>savepoint leo_1;                        為了防止誤操作，我們隨機做幾個儲存點

Savepoint created.

LEO1@LEO1>insert into leo1 values(20);                學號2的同學成績為20

1 row created.

LEO1@LEO1>insert into leo1 values(30);                學號3的同學成績為30

1 row created.

LEO1@LEO1>select * from leo1;                       成績表中有3條記錄

     X

----------

    10

    20

    30

LEO1@LEO1>savepoint leo_2;                         儲存點2

Savepoint created.

突然發現把學號2和學號3的成績登記錯了，現在只想回退到學號1登記之後的位置，如果全回退所有的工作都白做了，聰明的tiger老師不會這麼幹的，此時我們標記的儲存點派上用場了，我們只需回退到leo_1即可。

LEO1@LEO1>rollback to leo_1;           

Rollback complete.

這裡說明一點，雖說回退到了leo_1儲存點，但這個事務還沒有順利結束，其他會話不可見，只有commit之後才表示事務完整結束。

LEO1@LEO1>select * from leo1;                       ok此時繼續重做後面的操作就完事大吉

    X

----------

    10

LEO1@LEO1>commit;

Commit complete.

四 寫一個用於審計的觸發器，利用自治事務技術。

自治事務：一個事務中巢狀另一個事務，彼此是相互獨立的，互不干涉。

應用場合：需要獨立處理一件事情，例如獨立審計過程。

例項

我們經常會在淘寶上買東西，漸漸我們自己也想開個小鋪，我們有一個產品明細表product，還有一張審計表product_audit。審計表裡記錄著我們產品的變化情況（我們在審計的過程中使用自治事務技術），我們只需要知道產品還剩餘多少即可。

Product表

LEO1@LEO1>create table product (id int,name varchar2(20),num int,cost int);

Table created.

LEO1@LEO1>insert into product values(1,'apple',10,10);            插入5條記錄

1 row created.

LEO1@LEO1>insert into product values(2,'orange',20,20);

1 row created.

LEO1@LEO1>insert into product values(3,'banana',30,30);

1 row created.

LEO1@LEO1>insert into product values(4,'mango',40,40);

1 row created.

LEO1@LEO1>insert into product values(5,'KFC',50,50);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from product;

        ID NAME                        NUM       COST

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

         1 apple                        10         10

         2 orange                       20         20

         3 banana                       30         30

         4 mango                        40         40

         5 KFC                          50         50

Product_audit表

LEO1@LEO1>create table product_audit (id int,name varchar2(20),remain_num int);

Table created.

建立審計觸發器tib_product，當product表有update動作之後被觸發，會把剩餘數記錄到product_audit表裡

LEO1@LEO1>create or replace trigger tib_product after update on product for each row

declare

pragma autonomous_transaction;        這句話宣告下面的事務是一個自治事務，獨立存在的

tib_id         number;

tib_name       varchar2(20);

tib_remain_num number;

begin

tib_id :=:old.id;

tib_name :=:old.name;

tib_remain_num :=:new.num;

insert into product_audit values (tib_id,tib_name,tib_remain_num);

commit;

end;

/

  2    3    4    5    6    7    8    9   10   11   12   13   14  

Trigger created.

每個產品我們都賣了一半，更新5條記錄

LEO1@LEO1>update product set num=5  where name='apple';

1 row updated.

LEO1@LEO1>update product set num=10 where name='orange';

1 row updated.

LEO1@LEO1>update product set num=15 where name='banana';

1 row updated.

LEO1@LEO1>update product set num=20 where name='mango';

1 row updated.

LEO1@LEO1>update product set num=25 where name='KFC';

1 row updated.

LEO1@LEO1>select * from product;

        ID NAME                        NUM       COST

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

         1 apple                         5         10

         2 orange                       10         20

         3 banana                       15         30

         4 mango                        20         40

         5 KFC                          25         50

此時我們把改變的資料進行回滾，看看審計表有沒有影響

LEO1@LEO1>select * from product;                           產品明細表成功回滾，事務結束

        ID NAME                        NUM       COST

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

         1 apple                        10         10

         2 orange                       20         20

         3 banana                       30         30

         4 mango                        40         40

         5 KFC                          50         50

這時我們再看看審計表有沒有記錄著產品的剩餘情況

LEO1@LEO1>select * from product_audit;               good很好，完全記錄下來了

        ID NAME                 REMAIN_NUM

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

         1 apple                         5

         2 orange                       10

         3 banana                       15

         4 mango                        20

         5 KFC                          25

我們登陸一個新會話，如果新會話也可以正常訪問到product_audit表說明我們設計的自治事務完整結束

LEO1@LEO1>select distinct sid from v$mystat;

       SID

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

       142

LEO1@LEO1>select * from product_audit;                good可以正常看到測試非常成功

        ID NAME                 REMAIN_NUM

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

         1 apple                         5

         2 orange                       10

         3 banana                       15

         4 mango                        20

         5 KFC                          25

小結：通過上面的實驗我們可以感受到自治事務在某些場景下可以給我們帶來很大方便，通過自治事務我們可以很好的實現事務隔離。

五 請畫出一個分散式事務操作的原理圖。

分散式事務：所謂分散式事務就是發生在多臺資料庫之間的事務操作，它們通過dblink實現事務處理。分散式事務要比單機事務複雜的多，其中涉及到網路問題 作業系統問題 資料庫問題等等。具體操作原理請參考下圖所示

下方是三個資料庫A B C，它們之間是通過dblink連線進行分散式事務操作。

 

分散式事務之術語

Client：訪問其他資料庫的節點

Database：接收來自其他資料庫訪問的節點，例如 A庫->訪問->B庫，那麼A就是client端，B就是database端

Global coordinator：發起分散式事務的節點

Local coordinator：處理本地事務的節點

Commit point：被global coordinator指定為首先提交or回滾的事務節點，就是第一個提交的節點叫提交點，其他節點按順序完成提交。

資料庫叢集中每個庫都有一個commit_point_strength提交點權重引數，Oracle選取權重最大的資料庫為叢集提交點，如果權重都一樣，Oracle隨機選擇提交點。

SYS@base> show parameter commit_point

NAME                                 TYPE        VALUE

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

commit_point_strength                integer     1

2PC（two phase commit）2階段提交：第一階段叫prepare phase準備階段，第二階段叫commit phase提交階段。如果是本地事務就一階段直接提交，如果是分散式事務就採取二階段提交。下面解釋一下這兩個階段的具體內容

Prepare phase 準備階段

(1)每個節點檢查自己是否被其他節點所引用，如果有就通知這些節點準備提交。

(2)每個節點檢查自己執行的事務，看看有沒有修改資料的操作，如果沒有則跳過後面步驟直接返回一個只讀訊號給gc全域性協調程式。

(3)如果事務需要修改資料，就分配相應的資源給事務並記錄redo資訊，這些redo資訊保證事務失敗後的回滾。

(4)當上面的工作都成功後，給gc全域性協調程式返回準備就緒的訊號，反之則返回失敗的訊號。

Commit phase 提交階段

(1)首先gc全域性協調器通知提交點進行提交，完成後告知gc。

(2)gc全域性協調器再通知其他資料庫節點進行提交併記錄redo資訊，完成後告知gc

(3)當gc得知所有分散式事務提交完成後，告知提交點資料庫釋放事務相關資源，完成後告知gc

(4)gc全域性協調器釋放自己的資源

(5)分散式事務結束

六 請分析分散式事務為什麼無法保證事務最終的一致？請分析可能出現的問題以及解決方法。

這個論點已經由CAP理論證實了

CAP 理論斷言任何基於網路的資料共享系統，最多隻能滿足資料一致性、高可用性、分割槽容錯性三點中的兩點。

CAP理論圖示

 

AP架構例項：一個購物網站，如果允許系統實現高可用和分割槽容錯性，當叢集中某一節點出現故障的時候，就要暫時失去資料一致性，等待故障節點恢復正常，然後滿足資料的最終一致性，允許經過一個時間視窗後在一致。

解決方法：對於事務性很強的關係型資料庫而言，當發生一致性失敗後，整個分散式事務都進行回滾，回退到初始狀態，當故障解決後在執行事務操作。

我們在現實生活中常常會用“QQ傳送檔案”，當你傳送一個檔案傳到一半的時候網路斷了，這時就出現了資料不一致性現象，傳送事務沒有做完，通常QQ會選擇放棄資料的一致性保證QQ程式可用性，等待網路恢復後繼續完成事務。這裡面QQ應用了上面提到的“儲存點save point技術”實現斷點續傳功能，很好的解決了這個問題。

CA架構例項：中國人民銀行的網上徵信系統，必須滿足一致性和高可用性，犧牲分割槽容錯性，如果某個節點出現故障，斷開網路連線，事務全部回退，暫停業務進行維修。

CP架構例項：某大學的圖書館系統，借還資料要保持一致，當有節點故障的時候，整個叢集就會停止，維修後繼續使用。

儲存點   自治事務   分散式事務  CAP   資料一致性

Leonarding
2013.5.31
北京&spring
分享技術~成就夢想
Blog：www.leonarding.com