Understanding oracle autonomous transactions

Understand oracle Autonomous transactions 1. What is Oracle Autonomous transactions? In the official documents, the definition of "Autonomous transactions are independent transactions that can be called from within another transaction. that is to say, it is a transaction called by a transaction, but it is independent of its parent transaction commit or rollback. The following example shows how to create a test table www.2cto.com [SQL] MIKE @ ORA11G> create Table test (m varchar2 (30); table created. create two procedure, one for autonomous transactions and one for non-autonomous transactions [SQL] create or replace procedure auto_proce as pragma autonomous_transaction; begin insert into test values ('autonomous! '); Commit; end;/[SQL] create or replace procedure nonauto_proce as www.2cto.com begin insert into test values ('nonautonomous! '); Commit; end;/call nonauto_proce first to see what will happen? [SQL] SQL> select * from test; unselected rows SQL> begin 2 insert into test values ('test'); 3 nonauto_proce; 4 rollback; 5 end; 6. the PL/SQL process is successfully completed. SQL> select * from test; www.2cto.com M -------------------------------- test nonautonomous! The rollback of row 4th is not rolled back. Since the nonauto_proce process is not an autonomous transaction, its commit commits the insert statement in the parent transaction together, resulting in no rollback, that is, nonauto_proce affects its parent transaction. Next, let's take a look at auto_proce. [SQL] SQL> truncate table test; the table is truncated. SQL> select * from test; unselected rows SQL> begin 2 insert into test values ('test1'); 3 auto_proce; 4 rollback; 5 end; 6. the PL/SQL process is successfully completed. SQL> select * from test; www.2cto.com M -------------------------------- autonomous! Because the auto_proce process is an autonomous transaction, its commit does not affect the rollback of its parent transaction. This has been proved in the results. Through this example, we can see that the autonomous transaction has no impact on its parent transaction. Autonomous transactions are generally used for: a anonymous Block B local, opposite or packaged functions or methods of c object type d triggers 2. Autonomous transactions and parent transactions (callers) to view some information about the system. [SQL] create or replace procedure auto_p1 as pragma AUTONOMOUS_TRANSACTION; begin insert into test values ('test2'); dbms_lock.sleep (10); commit; end; View session SID (session) www.2cto.com [SQL] SQL> conn sys/admin as sysdba is connected. SQL> select sid from v $ mystat where rownum = 1; SID ---------- 144 use sqlplus to open another connection (session B) and View session information [SQL] SQL> select sid, username, status from v $ session; sid username status ---------- -------------------------- -------- 138 ACTIVE 143 sys inactive 144 sys inactive 145 sys inactive 147 sys inactive 149 sys active 151 ACTIVE 157 ACTIVE 159 ACTIVE 160 ACTIVE www.2cto.com in session A execute auto_p1 [SQL] SQL> The begin 2 auto_p1; 3 end; 4/PL/SQL process has been completed successfully. During execution, in session B, view the session information [SQL] SQL> select sid, username, status from v $ session; sid username status ---------- ---------------------------- -------- 143 sys inactive 144 sys inactive 145 sys inactive 147 sys active 149 ACTIVE 151 ACTIVE 157 ACTIVE 160 ACTIVE 161 ACTIVE no new session is generated, that is, the autonomous transaction auto_p1 and its parent transaction (anonymous block) are in the same session. Www.2cto.com 3. application scenarios refer to this aspect in Oracle programming art, which is used by Tom to record error logs or messages. Think about the projects you have done. Indeed, autonomous transactions are mainly used in many aspects. Whether your request is successful or not, as long as you access the database, it must be recorded, using autonomous transactions is a good solution (of course there are other solutions, because only autonomous transactions are mentioned here, and other solutions are skipped ). Message Systems in basic databases such as queue messages can also use autonomous transactions to record message sending, which is actually a log record. The following is a simple example. Create two tables, one test table (with primary key) and the other is the error log information table of the test table. [SQL] SQL> create table test1 (id number (8), msg varchar2 (10), constraint test1_pk primary key (id); the table has been created. [SQL] SQL> create table test1_log 2 (dat timestamp, 3 err clob 4); the table has been created. Create an autonomous transaction to record the error message when operating the test table. [SQL] SQL> create or replace procedure log_err (errinfo varchar2) 2 as 3 pragma autonomous_transaction; 4 begin 5 insert into test1_log values (paiimestamp, errinfo); 6 commit; 7 end; 8/www.2cto.com has been created. Create a process to insert data to the test. [SQL] SQL> create or replace procedure insert_test (numid number, msg varchar2) 2 as 3 begin 4 insert into test1 values (numid, msg); 5 end; 6. The process has been created. Insert data to the test table (no error message is displayed ). [SQL] SQL> begin 2 insert_test (1, 'testtest'); 3 insert_test (2, 'test'); 4 exception 5 when others 6 then 7 log_err (dbms_utility.format_error_backtrace ); 8 raise; 9 end; 10/PL/SQL process completed successfully. [SQL] SQL> select * from testbench log; www.2cto.com the error log table for unselected Rows has no information, and the data in the test table is correct. Next, perform the insert operation (Violation of the primary key constraints of the test table, and an error occurs ). [SQL] SQL> begin 2 insert_test (1, 'ffffffff'); 3 exception 4 when others 5 then 6 log_err (dbms_utility.format_error_backtrace); 7 raise; 8 end; 9/begin * 1st row error: ORA-00001: violation of unique constraints (SYS. testbench PK) ORA-06512: in line 7 [SQL] SQL> set linesize 1000 SQL> select * from testbench log; www.2cto.com DAT ERR tables -------------------------------------------------------------------------------------------------- ------------------ 30-7 months-12 07.55.26.281000 afternoon ORA-06512: In "SYS. INSERT_TEST ", line 4 ORA-06512: The insert operation in line 2 failed, the test table did not have the newly inserted data, but the error log table records an error message for this insert operation. The above example is very simple. It needs to be transformed based on the system requirements when applied to the application system. Author IndexMan