Oracle 嵌套事務與自治事務思考

源文出自：http://hwhuang.javaeye.com/blog/650903

關鍵字
嵌套事務和自治事務的概念
嵌套事務的使用
自治事務的使用
一． 概念
1. 嵌套事務(Nested Transaction)：
指在一個Parent事務中嵌套的一個或多個Sub Transaction.並且主事務與其相互影響，這種事務就稱為嵌套事務。以Commit作為事務的結束。
2. 自治事務(Autonomous Transaction):
指在function,procedure等subprograms中對事務進行自治管理,當在別的pl/sql block裡去調用這些subprograms的時候這些subprograms並不隨著父pl/sql block的失敗而復原,而是自己管自己commit。以Commit作為事務的結束。自治事務常用於寫入LOG或TRAC資訊便於尋找錯誤。
二． 嵌套事務的運用(Nested Transaction)
1．預備Create Table：

Sql代碼

1. create table TEST_POLICY  
2. (  
3.   POLICY_CODE VARCHAR2(20),  
4.   POLICY_TYPE CHAR(1)  
5. ) 

create table TEST_POLICY(  POLICY_CODE VARCHAR2(20),  POLICY_TYPE CHAR(1))

2.建立一個嵌套事務的procedure:

1)

Sql代碼

1. Procedure P_Insert_Policy(I_Policy_code varchar2(20),   
2.                             I_Policy_type char(1)) as
3.   cnt number :=0;  
4. begin
5. select count(1) into cnt from Test_Policy;  
6.       Dbms_Output.put_line('records of the test_policy is '|| cnt);  
8. Insert into Test_Policy values(I_Policy_code, I_Policy_type);  
9. commit;--commit in nested transaction 
10. end P_Insert_Policy;  
11. --call procedure used in nested transaction
12. PROCEDURE TEST_PL_SQL_ENTRY(  
13.                               I_POL_ID IN VARCHAR2,  
14.                               O_SUCC_FLG OUT VARCHAR2) AS
15.   strSql varchar2(500);  
16.   cnt number := 0;  
17. BEGIN
18. delete from test_policy;  
19. commit;  
20. insert into test_policy values('2010042101', '1');  
21. select count(1) into cnt from Test_Policy;  
22.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
23. --call nested transaction
24.      P_Insert_Policy('2010042102', '2');  
25. rollback;--rollback data for all transactions
26. commit;--master transaction commit
27. select count(1) into cnt from Test_Policy;  
28.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
29. rollback;  
31. select count(1) into cnt from Test_Policy;  
32.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
34. END TEST_PL_SQL_ENTRY;  
36. =>run Pl/sql:  
37. records of the test_policy is 1 –-主事務中的操作已經commit
38. records of the test_policy is 1 –-主事務的操作對Nested transaction有影響。  
39. records of the test_policy is 2 –-Nested transaction 已經Commit
40. records of the test_policy is 2 –-Nested transaction對主事務有影響。 

Procedure P_Insert_Policy(I_Policy_code varchar2(20),                             I_Policy_type char(1)) as   cnt number :=0;  begin      select count(1) into cnt from Test_Policy;      Dbms_Output.put_line('records of the test_policy is '|| cnt);            Insert into Test_Policy values(I_Policy_code, I_Policy_type);      commit;--commit in nested transaction   end P_Insert_Policy;--call procedure used in nested transaction  PROCEDURE TEST_PL_SQL_ENTRY(                              I_POL_ID IN VARCHAR2,                              O_SUCC_FLG OUT VARCHAR2) AS  strSql varchar2(500);  cnt number := 0;  BEGIN     delete from test_policy;     commit;     insert into test_policy values('2010042101', '1');     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     --call nested transaction     P_Insert_Policy('2010042102', '2');     rollback;--rollback data for all transactions     commit;--master transaction commit     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     rollback;          select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     END TEST_PL_SQL_ENTRY;=>run Pl/sql:records of the test_policy is 1 –-主事務中的操作已經commitrecords of the test_policy is 1 –-主事務的操作對Nested transaction有影響。records of the test_policy is 2 –-Nested transaction 已經Commitrecords of the test_policy is 2 –-Nested transaction對主事務有影響。

將上面的nested transaction的procedure修改一下，不需要commit:

Sql代碼

1. Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type,   
2.                             I_Policy_type t_contract_master.policy_type%type) as
3.   cnt number :=0;  
4. begin
5. select count(1) into cnt from Test_Policy;  
6.       Dbms_Output.put_line('records of the test_policy is '|| cnt);  
8. Insert into Test_Policy values(I_Policy_code, I_Policy_type);  
9. --commit;
10. end P_Insert_Policy;  
11. PROCEDURE TEST_PL_SQL_ENTRY(  
12.                               I_POL_ID IN VARCHAR2,  
13.                               O_SUCC_FLG OUT VARCHAR2) AS
14.   strSql varchar2(500);  
15.   cnt number := 0;  
16. BEGIN
17. delete from test_policy;  
18. commit;  
19. insert into test_policy values('2010042101', '1');  
20. select count(1) into cnt from Test_Policy;  
21.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
23.      P_Insert_Policy('2010042102', '2');  
24. rollback;  
25. commit;  
26. select count(1) into cnt from Test_Policy;  
27.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
28. rollback;  
30. select count(1) into cnt from Test_Policy;  
31.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
33. END TEST_PL_SQL_ENTRY;  
34. Run Pl/Sql=>  
35. 結果是：  
36. records of the test_policy is 1 –-主事務中的操作已經commit
37. records of the test_policy is 1 –-主事務的操作對Nested transaction有影響。  
38. records of the test_policy is 0 –-Nested transaction 的資料被主事務rollback.  
39. records of the test_policy is 0  

Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type,                             I_Policy_type t_contract_master.policy_type%type) as   cnt number :=0;  begin      select count(1) into cnt from Test_Policy;      Dbms_Output.put_line('records of the test_policy is '|| cnt);            Insert into Test_Policy values(I_Policy_code, I_Policy_type);      --commit;  end P_Insert_Policy;  PROCEDURE TEST_PL_SQL_ENTRY(                              I_POL_ID IN VARCHAR2,                              O_SUCC_FLG OUT VARCHAR2) AS  strSql varchar2(500);  cnt number := 0;  BEGIN     delete from test_policy;     commit;     insert into test_policy values('2010042101', '1');     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);          P_Insert_Policy('2010042102', '2');     rollback;     commit;     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     rollback;          select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     END TEST_PL_SQL_ENTRY;Run Pl/Sql=>結果是：records of the test_policy is 1 –-主事務中的操作已經commitrecords of the test_policy is 1 –-主事務的操作對Nested transaction有影響。records of the test_policy is 0 –-Nested transaction 的資料被主事務rollback.records of the test_policy is 0 

三.自治事務(Autonomous transaction)

1.下面是來自於Oracle上對自治事務的描述：

autonomous transactions  does not depend on the main transaction. For example, if the main transaction rolls back, nested transactions roll back, but autonomous transactions do not.

autonomous transactions‘  committed changes are visible to other transactions immediately. (A nested transaction's committed changes are not visible to other transactions until the main transaction commits.)

自治事務（以下簡稱AT）是由主事務(以下簡稱MT)調用但是獨立於MT的事務。在自治事務被調用執行時，MT被掛起，在自治事務內部，一系列的DML可以被執行並且commit或rollback. 自治事務防止嵌套提交，使事務在自己的事務區內提交或復原不會影響其他的事務。由於自治事務的獨立性，它的commit和rollback並不影響MT的執行效果。在自治事務執行結束後，主事務獲得控制權，又可以繼續執行了。

實現自治事務的定義,只需下列PL/SQL的聲明部分加上PRAGMA AUTONOMOUS_TRANSACTION 就可以了。

1）． 頂級的匿名PL/SQL塊

2）． Functions 或 Procedure.

2.  定義一個自治事務:

Sql代碼

1. Procedure p_insert_policy_new(i_policy_code Varchar2(20),  
2.                                 i_policy_type char(1)) as
3.   Pragma Autonomous_Transaction;--define auto trans
4.   cnt number := 0;  
5. begin
6. select count(1) into cnt from test_policy;  
7.       Dbms_Output.put_line('records of the test policy table is: '||cnt);      
9. Insert into Test_Policy values(I_Policy_code, I_Policy_type);                            
10. commit;  
11. select count(1) into cnt from test_policy;  
12.       Dbms_Output.put_line('records of the test policy table is: '||cnt);   
13. end p_insert_policy_new;  
14. --call auto trans procedure
15. PROCEDURE TEST_PL_SQL_ENTRY(  
16.                               I_POL_ID IN VARCHAR2,  
17.                               O_SUCC_FLG OUT VARCHAR2) AS
18.   strSql varchar2(500);  
19.   cnt number := 0;  
20.   v_policyCode t_contract_master.policy_code%type;  
21. BEGIN
22. delete from test_policy;  
23. commit;  
24. insert into test_policy values('2010042101', '1');  
25. select count(1) into cnt from Test_Policy;  
26.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
28.      p_insert_policy_new('2010042102', '2');  
29. select count(1) into cnt from Test_Policy;  
30.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
31. rollback;  
32. select policy_code into v_policyCode from test_policy;  
33.      Dbms_Output.put_line('policy_code: '|| v_policyCode);  
34. commit;  
35. select count(1) into cnt from Test_Policy;  
36.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
37. rollback;  
39. select count(1) into cnt from Test_Policy;  
40.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
42. END TEST_PL_SQL_ENTRY;  
43. Run pl/sql=>  
44. records of the test_policy is 1 –-Master trans has been committed.  
45. records of the test policy table is: 0 -–Auto trans isn’t affected by master trans.  
46. records of the test policy table is: 1—-Auto trans has been committed.  
47. records of the test_policy is 2  
48. policy_code: 2010042102—-rollback affected master trans  
49. records of the test_policy is 1   
50. records of the test_policy is 1 

Procedure p_insert_policy_new(i_policy_code Varchar2(20),                                i_policy_type char(1)) as  Pragma Autonomous_Transaction;--define auto trans  cnt number := 0;  begin      select count(1) into cnt from test_policy;      Dbms_Output.put_line('records of the test policy table is: '||cnt);                Insert into Test_Policy values(I_Policy_code, I_Policy_type);                                commit;      select count(1) into cnt from test_policy;      Dbms_Output.put_line('records of the test policy table is: '||cnt);   end p_insert_policy_new;--call auto trans procedurePROCEDURE TEST_PL_SQL_ENTRY(                              I_POL_ID IN VARCHAR2,                              O_SUCC_FLG OUT VARCHAR2) AS  strSql varchar2(500);  cnt number := 0;  v_policyCode t_contract_master.policy_code%type;  BEGIN     delete from test_policy;     commit;     insert into test_policy values('2010042101', '1');     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);          p_insert_policy_new('2010042102', '2');     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     rollback;     select policy_code into v_policyCode from test_policy;     Dbms_Output.put_line('policy_code: '|| v_policyCode);     commit;     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     rollback;          select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);       END TEST_PL_SQL_ENTRY;Run pl/sql=>records of the test_policy is 1 –-Master trans has been committed.records of the test policy table is: 0 -–Auto trans isn’t affected by master trans.records of the test policy table is: 1—-Auto trans has been committed.records of the test_policy is 2policy_code: 2010042102—-rollback affected master transrecords of the test_policy is 1 records of the test_policy is 1

3 總結Auto Transaction:

(1)其中 pragma 關鍵字的作用是通知 PL/SQL 編譯器，將聲明它的這個 PL/SQL 代碼塊分割為一個自治的或獨立的事務。定義自治事務時，要遵守以下幾條規則：

1) 如果 PL/SQL 塊是匿名的，那麼該匿名 PL/SQL 塊必須是一個頂層塊。

2) 如果 PL/SQL 塊不是匿名的，那麼它必須作為包或預存程序序單元一部分的一個過程或函數。當在包中定義自治事務時，只有包中具體的函數或過程才能被指定為自治的的。

3) PL/SQL 塊也可以是儲存物件類型的一個方法

4) PL/SQL 塊也可以是一個資料庫觸發器

(2)自治事務的一些關鍵問題

1) 帶 ALTER SESSION 的自治事務

自治事務應該與主事務共用一個會話，因此通過 ALTER SESSION 語句，對該會話的任何修改，對自治事務和主事務應該都是可見的。但自治事務執行與主事務不同的上下文中。任何從自治塊中引發的自治子程式調用都與自治事務共用相同的事務上下文。

2) 自治事務與死結

必須以下面的這種方式定義自治事務：

死結在自治事務試圖訪問一個被主事務佔用的資源時發生，這時主事務會掛起，直到自治事務結束。自治事務死等主事務釋放資源，結果可能導致死結。

3) 定義自治事務的條件

只有頂層匿名塊才能包括 PRAGMA AUTONOMOUS_TRANSTRACTION

4) COMMIT 或 ROLLBACK 行為與自治事務

自治事務是以 commit 或 rollback 語句結束的。因此，應該在自治事務程式的內部顯式地包含一個 commit 或 rollback 語句。如果沒有這樣做，任何一個未確定的事務都會復原。這是一個事務級的復原，而不是一個語句級的復原。

5) 異常與自治事務

當自治事務以一個異常的方式退出時，就發生一個事務級的復原，自治事務中所有未確定的改變都會復原 .

6) 多個自治事務

一個自治事務的上下文中，初始化多個自治事務。可以在一個自治塊內定義多個 commit 或 rollback 語句來完成這項任務，當包含一個 rollback 時，它就會屬於當前事務，而不屬於主事務。

7) 自治事務的並發問題

自治事務與主事務是並發啟動並執行，初始設定檔案 init.ora 中的 transactions 參數決定著每個會話中並發事務的數量。

8) 通過自治事務和從 SQL 中調用使用者自訂函數

通過自治事務，可以從 SQL 中調用使用者自訂函數來執行 DML 操作。只需把使用者自訂函數定義為自治事務，函數就變為可從 SQL 中調用的。

9) 自治事務和隔離等級

應該在自治事務中給出 commit 或 rollback 命令，一旦 commit 或 rollback 在自治事務內部執行，那些改變對主事務而言是可見。但是 oracle 也允許從主事務中通過設定主事務的隔離等級為 SERIALIZABLE ，而不是預設的 READ COMMITTED ，對主事務隱藏這些改變，這可以通過 Set TRANSCTION 陳述式完成，其文法如下：

Set transaction isolation level serializable;

關於隔離等級的問題，以下兩點值得注意：

a) 當自治事務以 commit 或 rollback 而結束時，由自治事務造成的改變對主事務以外的其它事務是可見的 .

b) 設定隔離等級為 serializable ，對主事務隱藏自治事務的變化。直到主事務提交，主事務一旦提交，自治事務中的改變對自治事務也就可見。

10)  隔離等級

隔離等級是一種處理修改資料庫事務的方法，它影響到一個事務中的改變對另一個事務的可見度。 SQL92 中定義了 4 種隔離等級即：

Sql代碼

1. READ UNCOMMITTED
2. REPEATABLE READ
3. READ COMMOTTED   
4. SERIALIZABLE

READ UNCOMMITTED  REPEATABLE READ  READ COMMOTTED  SERIALIZABLE 

oracle 支援 READ COMMOTTED 和 SERIALIZABLE 兩種隔離等級

Read committed ：這種設定是 oracle 預設的 , 它使 oracle 查詢可以看到查詢之前提將近的資料。換句話說，這種模式的事務是基於每句一致的事務集的。

Serializable ： 這種設定意味著一個事務中的所有語句，都在操作一個事務開始時的資料庫映像中，這意味著沒有 commit 之前，當前事務都不能看到其它事務所提交的資料。