關於oracle autonomous transaction

autonomous transaction 翻譯成中文叫自治事務，多好的名字，一聽名字就明白

大半的意思了。autonmous transaction 是一個獨立的事務，是由於main transaction呼叫而產生的。

autonomous transaction的獨立性

autonomous transaction 是一個獨立的事務，這一點是理解autonomous transaction

的關鍵,雖然受main transaction 的呼叫。下面用一個例子來加深理解。

建立一個測試表，往其中插入兩條記錄，不提交，接著宣告一個自治事務，在其中繼續

往表中插入記錄，並且在自治事務對插入的記錄進行提交。我們從輸出中可以知道main

transaction 和 autonomous transaction 的控制是獨立開來的。autonomous transaction

的提交不會提交main transaction ，main transaction 的回滾也不會影響到 autonomous

transaction。

SQL> create table test_at(id number,name varchar2(20));

Table created.

SQL> insert into test_at values(1000,'oracle');

1 row created.

SQL> insert into test_at values(1001,'db2');

1 row created.

SQL> ed

Wrote file afiedt.buf

1 declare

2 pragma autonomous_transaction;

3 begin

4 for i in 1002 .. 1010 loop

5 insert into test_at values(i,'redis');

6 end loop;

7 commit;

8* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> select * from test_at;

ID NAME

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

1000 oracle

1001 db2

1002 redis

1003 redis

1004 redis

1005 redis

1006 redis

1007 redis

1008 redis

1009 redis

1010 redis

11 rows selected.

SQL> rollback;

Rollback complete.

SQL> select * from test_at;

ID NAME

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

1002 redis

1003 redis

1004 redis

1005 redis

1006 redis

1007 redis

1008 redis

1009 redis

1010 redis

9 rows selected.

autonomous transaction 與dead lock

autonomous transaction 嘗試lock main transaction lock 的資源的話，將會

發生死鎖。main transaction 呼叫autonomous transaction 後會被掛起，但是

他所持有的資源還是保持著，這時候如果autonomous transaction 試圖訪問

main transaction 持有的資源。autonomous transaction 不可能請求的到，

所以autonomous transaction 不能繼續下去，也就導致了main transaction

無法繼續走。死鎖就發生了。

SQL> ed

Wrote file afiedt.buf

1 create or replace procedure p_at as

2 pragma autonomous_transaction;

3 begin

4 update test_at

5 set name = 'mysql'

6 where name = 'redis';

7 commit;

8* end p_at;

SQL> /

Procedure created.

SQL> ed

Wrote file afiedt.buf

1 begin

2 update test_at

3 set name = 'mysql'

4 where name = 'redis';

5 p_at;

6 commit;

7* end;

SQL> /

begin

*

ERROR at line 1:

ORA-00060: 等待資源時檢測到死鎖

ORA-06512: 在 "SYS.P_AT", line 4

ORA-06512: 在 line 5

autonomous transaction 與 rollback

autonomous transaction 中必須包括rollback，commit 。autonomous transaction

出錯的時候將會被回滾掉，即使有exception handle.

SQL> ed

Wrote file afiedt.buf

1 declare

2 pragma autonomous_transaction;

3 begin

4 update test_at

5 set name = 'mysql'

6 where name = 'redis';

7* end;

SQL> /

declare

*

ERROR at line 1:

ORA-06519: 檢測到活動的獨立的事務處理, 已經回退

ORA-06512: 在 line 7

SQL> select * from test_at;

ID NAME

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

1002 redis

1003 redis

1004 redis

1005 redis

1006 redis

1007 redis

1008 redis

1009 redis

1010 redis

9 rows selected.

SQL> ed

Wrote file afiedt.buf

1 declare

2 pragma autonomous_transaction;

3 begin

4 update test_at

5 set name = 'mysql'

6 where name = 'redis';

7 exception

8 when others then

9 dbms_output.put_line('Error!,rollback or commit.');

10* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> select * from test_at;

ID NAME

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

1002 redis

1003 redis

1004 redis

1005 redis

1006 redis

1007 redis

1008 redis

1009 redis

1010 redis

9 rows selected.

autonomous transaction 的小運用

autonomous transaction 的獨立性是非常重要的，可以使呼叫他的main transaction

不需要額外的事務控制語句來控制呼叫的模組，結合exception handle 可以實現簡單的

日誌登記功能。

SQL> ed

Wrote file afiedt.buf

1 create table error_logs

2 ( id number(10),

3 user_name varchar2(30),

4 timestamp timestamp,

5* error_msg varchar2(1000))

SQL> /

Table created.

SQL> alter table error_logs add constraint error_logs_pk primary key (id);

Table altered.

SQL> ed

Wrote file afiedt.buf

1* create sequence error_logs_seq

SQL> /

Sequence created.

SQL> ed

Wrote file afiedt.buf

1 create or replace procedure log_errors(error_message in varchar2) as

2 pragma autonomous_transaction;

3 begin

4 insert into error_logs(id,user_name,timestamp,error_msg)

5 values(error_logs_seq.nextval,user,systimestamp,error_message);

6 commit;

7* end log_errors;

SQL> /

Procedure created.

SQL> alter table test_at add constraint test_at_pk primary key (id);

Table altered.

SQL> ed

Wrote file afiedt.buf

1 begin

2 insert into test_at

3 values(1010,'mysql');

4 insert into test_at

5 values(1011,'mssql');

6 exception

7 when others then

8 log_errors(sqlerrm);

9 rollback;

10* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> select * from test_at

2 where id >= 1010;

ID NAME

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

1010 redis

SQL> select * from error_logs;

ID USER_NAME TIMESTAMP ERROR_MSG

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

1 SYS 16-5月 -12 10.44.16.316000 下午 ORA-00001: 違反唯一約束條件 (SYS.TEST_AT_PK)

小結：autonomous transaction 在pl/sql 程式設計中是比較重要的部分，也是比較容易出現問題的部分。雖然上面討論了autonomous transaction 的幾個方面，其實很多地方還可以繼續深入，如果有興趣可以自己找資料研究。不過我個人認為除非有需要，否則有些知識夠用就好，人生有崖，學無涯啊。

關於oracle autonomous transaction

相關文章