實驗演示Oracle“多版本一致讀”和“Cross DDL”

realkid4發表於2013-03-17

 

在各種事務級別中,Oracle實現的是“Read Committed”,也就是讀取的資料都是已經提交過的資料內容。在Oracle中,select不會阻塞任何操作,同樣也不會被任何其他操作阻塞。

 

Oracleselect動作是不會加鎖的,也不會受到資料表已經有鎖的影響。其他操作,如insertupdatedelete,通常會有兩個鎖定動作,一個是對資料表的共享鎖,保護資料表結構不被DDL操作修改。另一個鎖定動作是獨佔鎖,獨佔修改刪除的資料記錄和對應的Undo段地址。

 

如果Oracle需要保證在其他會話在修改資料塊,尚未提交的時候,一個會話select資料,還滿足“Read Committed”,就必須要在一個地方儲存住資料塊的“前映象”,也就是rollback segment/undo segment的作用。

 

當一個資料塊進行修改的時候,Oracle會自動將前映象資訊儲存在一個rollback/undo空間裡面,資料塊中包含一個定址到rollback/undo位置,這個位置就是ITL(活動事務槽)。當事務沒有結束,select操作到這個資料塊的時候,會透過ITL找到前映象資訊,並且拼湊出資料塊的前映象資訊。

 

另一個方面問題,如果一個select開始後,由於資料量的原因,持續很長時間。在這個時間段內發生了DML操作,比如刪除了幾條資料並且提交,那麼select操作檢索的範圍中,會不會包括這幾條資料呢?

 

下面我們透過實驗來證明。

 

1、環境準備

 

我們選擇11gR2環境進行實驗。

 

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE        11.2.0.1.0         Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

--建立實驗資料表

 

13:35:25 SQL> create table t as select * from dba_objects where rownum<100;

Table created

 

Executed in 0.124 seconds

 

 

實驗有一個重點,就是如何讓一個資料查詢持續很長時間,由於環境的限制,筆者不能拿到一個長時間操作的資料範圍。所以,採用了一些變通方法。

 

 

set time on;

set serveroutput on;

declare

  coun number;

  cursor tes is select * from t;

  t_info t%rowtype;

begin

  coun := -1;

 

  open tes;

  loop

    fetch tes into t_info;

    coun := coun + 1;   

    dbms_lock.sleep(0.1);

   

    exit when tes%notfound;

  end loop; 

  dbms_output.put_line('Total Count: '||to_char(coun)); 

end;

/

 

 

程式碼中的部分就是使用遊標逐條的fetch資料表t記錄。關鍵部分在於dbms_lock方法sleep,它可以實現讓程式碼終止一段時間,模擬長時間操作處理。這樣,我們就可以在另一個會話中更從容的進行操作了。

 

2、一致讀DML操作實驗

 

下面我們進行DML操作實驗,如果一個select很長時間,並且中間有已經提交的DML修改操作,如插入、刪除,select的結果會有什麼影響?

 

首先,我們在會話1裡面執行select過程指令碼。

 

 

會話1

 

14:16:22 SQL> set time on;

14:16:22 SQL> set serveroutput on;

14:16:22 SQL> declare

           2    coun number;

           3    cursor tes is select * from t;

           4    t_info t%rowtype;

           5  begin

           6    coun := -1;

           7 

           8    open tes;

           9    loop

          10      fetch tes into t_info;

          11      coun := coun + 1;

          12      dbms_lock.sleep(1);

          13 

          14      exit when tes%notfound;

          15    end loop;

          16    dbms_output.put_line('Total Count: '||to_char(coun));

          17  end;

          18  /

 

Total Count: 99

 

PL/SQL procedure successfully completed

 

Executed in 100.044 seconds

 

 

同時,第二個會話再執行指令碼,注意相應的時間。

 

 

會話2

 

14:15:54 SQL> set time on;

14:16:25 SQL> delete t;

99 rows deleted

 

Executed in 0.016 seconds

 

14:16:29 SQL> commit;

Commit complete

 

Executed in 0 seconds

 

14:16:31 SQL>

 

 

在第二個會話中,我們實現了14:16:29的時候提交了事務。此時任何select動作,都應該是不能訪問到資料。

 

但是,第一個會話的啟動時間是14:16:22,此時資料是存在的。執行了超過100秒之後,才執行結束。返回結果是99行,明顯是有結果的。

 

之後,第一個會話再執行的時候,我們就再也看不到資料了。

 

 

14:18:03 SQL> select count(*) from t;

 

  COUNT(*)

----------

         0

Executed in 0.031 seconds

 

 

這就是典型的Oracle一致讀過程。無論select持續多長時間,Oracle都會保證返回的資料SCN版本都是在發出select語句時的SCN。比如,當發出select命令的時候,SCN編號是1000,那麼在檢索資料表段的時候,只會去檢索那些SCN小於等於1000的資料行記錄。如果當前資料行正有事務資訊,就會根據ITL查詢Undo/Rollback中的前映象。如果資料行的SCN大於1000,那麼會去Undo/Rollback找那些Expired的記錄。

 

所以,在過去手工管理Rollback的時候,如果一個select時間很長,同時資料修改頻度很高,會報錯1555錯誤,也就是Snapshot Too Old。在現在自動Undo管理的時候,這樣的場景已經很少了。

 

3Cross DDL

 

那麼,我們想到一個場景,如果我們在長時間select的時候,發出DDL語句,如TruncateDrop資料表。因為select操作不會加鎖,所以不能組織DDL操作(獨佔六級鎖)。

 

我們還是透過實驗來證明。首先,我們恢復一下現場。

 

 

 

14:21:35 SQL> insert into t select * from dba_objects where rownum<100;

99 rows inserted

 

Executed in 0.032 seconds

 

14:23:05 SQL> commit;

Commit complete

 

Executed in 0.016 seconds

 

 

第一個會話,啟動匿名塊指令碼。

 

 

14:23:27 SQL> set time on;

14:23:27 SQL> set serveroutput on;

14:23:27 SQL> declare

           2    coun number;

           3    cursor tes is select * from t;

           4    t_info t%rowtype;

           5  begin

           6    coun := -1;

           7 

           8    open tes;

           9    loop

          10      fetch tes into t_info;

          11      coun := coun + 1;

          12      dbms_lock.sleep(1);

          13 

          14      exit when tes%notfound;

          15    end loop;

          16    dbms_output.put_line('Total Count: '||to_char(coun));

          17  end;

          18  /

Total Count: 99

 

PL/SQL procedure successfully completed

Executed in 99.997 seconds

 

 

第二個會話,進行drop資料表過程。

 

 

14:16:31 SQL> set time on;

14:23:30 SQL> drop table t purge;

 

Table dropped

 

Executed in 0.047 seconds

 

 

執行drop資料表動作。開始時間為14:23:30,持續不到1s。但是在第一個會話中,開始14:23:27,持續了100s。說明,在第一個會話結束之前,資料表就已經不存在了。

 

但是,從第一個會話結束的情況看,資料表還是存在的。第二次執行一次,第一個會話報錯。

 

 

14:25:07 SQL>

14:25:19 SQL> set time on;

14:25:19 SQL> set serveroutput on;

14:25:19 SQL> declare

           2    coun number;

           3    cursor tes is select * from t;

           4    t_info t%rowtype;

           5  begin

           6    coun := -1;

           7 

           8    open tes;

           9    loop

          10      fetch tes into t_info;

          11      coun := coun + 1;

          12      dbms_lock.sleep(1);

          13 

          14      exit when tes%notfound;

          15    end loop;

          16    dbms_output.put_line('Total Count: '||to_char(coun));

          17  end;

          18  /

 

declare

  coun number;

  cursor tes is select * from t;

  t_info t%rowtype;

begin

  coun := -1;

 

  open tes;

  loop

    fetch tes into t_info;

    coun := coun + 1;

    dbms_lock.sleep(1);

 

    exit when tes%notfound;

  end loop;

  dbms_output.put_line('Total Count: '||to_char(coun));

end;

 

ORA-06550: 3 , 31 :

PL/SQL: ORA-01775: 同義詞的迴圈鏈

ORA-06550: 3 , 17 :

PL/SQL: SQL Statement ignored

ORA-06550: 4 , 10 :

PLS-00201: 必須宣告識別符號 'T'

ORA-06550: 4 , 10 :

PL/SQL: Item ignored

ORA-06550: 10 , 20 :

PLS-00320: 此表示式的型別宣告不完整或格式不正確

ORA-06550: 10 , 5 :

PL/SQL: SQL Statement ignored

 

 

報錯。

 

說明,對於DDL操作(Truncate相同),一致讀的現象依然存在。Oracle不會因為同時的DDL操作,影響到原來已經發出的select動作。從原理上,筆者認為還是和Undo/Rollback有關。

 

對於Truncate資料表,Oracle沒有修改真正的資料,而是修改了段頭資訊,直接修改段頭的分割槽extent資訊。這部分動作其實也是會計入到Undo/Rollback空間,並且段資訊data_object_id被修改。

 

當原有data_object_id訪問需求出現的時候,Oracle會找Undo/Rollback上的段頭資訊,找到原有的Extent分割槽列表,進而可以範圍資料。

 

4、結論

 

本篇演示了一致讀現象,不僅對於DML操作有效,對DDL同樣有效。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17203031/viewspace-756336/,如需轉載,請註明出處,否則將追究法律責任。

相關文章