【DDL】DDL的隱式commit和自治事務

DDL的隱式commit和自治事務

 

DDL的隱式commit

ddl的執行過程

Begin

  Commit;

  Ddl;

  Commit;

Exception

  When others then

       Rollback;

       Raise;

End;

很清楚，在真正執行ddl操作前後共執行了兩次commit操作，這兩次commit操作是自動執行的。即使ddl語句因為某種原因執行失敗（ddl語句本身語法錯誤除外）,也會執行第一次commit，但不會執行第二次commit，而是rollback。

 

假設我們執行了DML操作，沒有commit，緊接著在同會話裡繼續執行了ddl操作。此時，之前的DML操作就會被自動commit。我們可以使用自治事務來避免這個問題。

 

自治事務

所謂自治事務（autonomous transaction）就是建立一個“事務中的事務”，它能獨立於其父事務提交或回滾。利用自治事務，可以掛起當前執行的事務，開始一個新的事務，完成一些工作，然後提交或回滾，所有這些操作都不影響父事務的狀態。上面的例子中，DML操作就相當於父事務，DDL操作就相當於自治事務，DDL中的兩次commit操作不會對DML操作中的狀態產生任何影響，也就是不會commit ddl操作。下面的例子中將利用過程和匿名塊對自治事務和ddl 的auto commit進行演示。

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

0.演示思路

有兩個表t0,t1。在會話1中：表t0上進行dml操作:Insert不commit，然後在同一個回話中truncate t2表，然後從會話2中查詢t0表，預想：完成了commit操作，看到insert插入的記錄，證明了ddl操作的隱式提交。接著清除t0表上的資料，執行dml操作insert，但不commit。然後在同一個會話中執行自治事務的DDL操作：truncate t2表。然後在會話2中查詢t0表，預想：dml操作還沒有commit，所以看不到insert插入的記錄，證明了自治事務避免了ddl操作的隱式提交。

 

1.版本

BALLONTT@TEST> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

 

2.建立環境

在會話1中：

BALLONTT@TEST> create table t0(name varchar2(10));

Table created.

BALLONTT@TEST> create table t1(name varchar2(10));

Table created.

 

建立truncate t1表的普通過程NonAuto：

BALLONTT@TEST> create or replace procedure NonAuto

  2  as

  3  begin

  4  DBMS_UTILITY.EXEC_DDL_STATEMENT(

  5  'truncate table t1');

  6  end;

  7  /

 

建立truncate t1表的自治事務的過程Auto：

BALLONTT@TEST> create or replace procedure Auto  

  2  as

  3   pragma autonomous_transaction;

  4  begin

  5  DBMS_UTILITY.EXEC_DDL_STATEMENT('

  6  truncate table t1');

  7  end;

  8  /

Procedure created.

注意這裡使用了pragama AUTONOMOUS_TRANSACTION。這個指令告訴資料庫，執行這個過程時要作為一個新的自治事務來執行，而且獨立於其父事務

 

3.正常情況下的DDL操作

在會話1中：

BALLONTT@TEST> insert into t0 values('b');

1 row created.

不提交

 

在會話2中:

BALLONTT@TEST> select * from t0;

no rows selected

 

在會話1中:（執行普通的DDL操作）

BALLONTT@TEST> begin

  2  NonAuto;

  3  end;

  4  /

PL/SQL procedure successfully completed.

 

在會話2中:

BALLONTT@TEST> select * from t0;

NAME

----------

B

會話1中的dml操作已經被隱式提交

 

4.自治事務下的DDL操作

清除資料

BALLONTT@TEST> truncate table t0;

Table truncated.

BALLONTT@TEST> select * from t0;   

no rows selected

BALLONTT@TEST> insert into t0 values('c');

1 row created.

BALLONTT@TEST> select * from t0;

NAME

----------

C

 

在會話2中:

BALLONTT@TEST> select * from t0;

no rows selected

 

在會話1中:（執行自治事務的DDL）

BALLONTT@TEST> begin

  2  Auto;

  3  end;

  4  /

PL/SQL procedure successfully completed.

 

在會話2中:

BALLONTT@TEST> select * from t0;

no rows selected

會話1中的DML操作沒有被DDL操作隱式commit;

補充：為什麼要在DDL上進行自動commit？TOM在asktom上給出瞭如下回答

Tom說：
a user that gets blocked on the "do the ddl" inside the savepoint is blocked on the transaction -- 
not the "subtransaction".  Hence it would block people on the data dictionary -- a place we cannot 
afford to get jammed up.
The data dictionary is "special" -- it drives the rest of the system.  If portions of it get locked 
up for extended periods of time -- it could be deadly.

DDL操作也就是資料定義語言，對錶的結構進行修改，這個修改其實就是對資料字典中表的定義進行了修改。DDL操作發生了阻塞，那麼”阻塞“這個動作其實是發生在了資料字典表上，在資料字典上發生嚴重的阻塞可不是一個小問題，資料字典和整個資料庫相關連著，在資料字典上的長期阻塞可能會造成嚴重的問題。因此，在每個DDL執行後自動commit，提高資料庫的整天效能。

ballontt

2013/4/26

---The End---

如需轉載，請標明出處和連結，大謝！

 

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