1	Autonomous Transactions: Extending the Possibilities Michael Rosenblum Dulcian, Inc. June 16, 2008
2	Autonomous Transactions Definition: Autonomous transactions are independent transactions that can be called from within another transaction. Syntax: declare Pragma autonomous_transaction; Begin …… commit; End
3	Specifications An autonomous transaction allows you to: Leave the context of the calling transaction (parent) Perform SQL operations Commit or rollback those operations Return to the calling transaction's context Continue with the parent transaction
4	Autonomous Transaction Syntax - OK Can be used in: Top-level anonymous blocks Local, standalone or packaged functions and procedures Methods of object types Database triggers Starting with Oracle version 9.2 – distributed transactions are allowed
5	Autonomous Transaction Syntax – NOT OK Cannot be used: Outside of a declaration section Within the declaration section of a nested block (a block within a block) In a package specification In a package body outside of a procedure or function definition In a type body outside of a method definition
6	Simple example of autonomous transaction trigger Bu_emp before update of sal on Emp for each row begin p_log_audit (user, 'update', 'update of emp.salary', sysdate); end;
7	Part 1: Autonomous vs. Nested Transactions
8	Familiar Things Nested transaction: "A tree of transactions, the sub-trees of which are either nested or flat transactions" © J. Moss In Oracle terms: Each time a function, procedure, method, or anonymous block is called within another block or trigger, it spawns a sub-transaction of the main transaction. Question: What is the difference between nested and autonomous transactions?
9	Scope of Transaction
10	Autonomous vs. Nested (1)
11	Autonomous vs. Nested (1) Example Locks declare v varchar2(2000); begin select ename into v from emp where ename = SCOTT' for update; lock_test; commit; End;
12	Autonomous vs. Nested (2)
13	Autonomous vs. Nested (2) Example - Variables Begin dbms_output.put_line (‘Start value: ’\|\| var_test.global_nr ); var_test.global_nr := 10 p_var_test (20); dbms_output.put_line (‘After auto value: ’\|\| var_test.global_nr ); End;
14	Autonomous vs. Nested (3)
15	Autonomous vs. Nested (3) Parent Data Changes Declare v_nr number; Begin Select count(1) into v_nr from audit_emp; insert into audit_emp values (user, sysdate, ‘Test’); dbms_output.put_line (‘Count#1=‘\|\|v_nr); data_change_test1; data_change_test2; End;
16	Autonomous vs. Nested (4)
17	Autonomous vs. Nested (4) Child Data Changes declare v_nr number; Begin set transaction isolation level read committed; insert into audit_emp values (user,sysdate,'Test',1 ); commit_test; select max(log_id) into v_nr from audit_emp; dbms_output.put_line (‘Maximum='\|\|v_nr); end;
18	Autonomous vs. Nested (5)
19	Autonomous vs. Nested (5): Exceptions Declare v_nr number; Begin rollback_test; Exception when others then select count(1) into v_nr from audit_emp; dbms_output.put_line (‘Count=‘\|\|v_nr); end;
20	Autonomous vs. Nested Transactions – Summary Autonomous transactions do not share transactional resources (such as locks) with the main transaction. Autonomous transactions do not depend on the main transaction. Non-committed changes of parent transactions are not immediately visible to autonomous transactions, but visible for nested ones. Changes made by autonomous transactions may or may not be visible to the parent one depending upon the isolation level. Changes made by nested transactions are always visible to the parent one. Exceptions raised in an autonomous transaction cause a transaction-level rollback, not a statement-level rollback.
21	Mission Critical Factors Transactional resources Session-level resources Data changes of parent transaction Data changes of autonomous transaction Exceptions
22	Part 2: How to use Autonomous Transactions
23	Usage #1: Audit querying
24	Usage #1: Example Create or replace view v_emp As Select empno, ename, audit$pkg.f_record(empno, ‘sal’, sal) sal From emp
25	Usage #1a: Extended audit of querying
26	Usage #1a: Example Create or replace view v_emp As Select empno, ename, audit$pkg.f_clean (empno, sal) sal From temp_emp
27	Usage #2: Audit activity
28	Usage #2: Example Trigger emp_audit Before update on emp for each row Declare pragma autonomous_transaction; Begin if (check_privileges (:new.mgr,:new.job)) then p_log_audit (user, ‘Update: rule succeeded‘, ‘Update of emp.salary’, sysdate); commit; else p_log_audit (user, ‘Update: rule FAILED‘, ‘Update of emp.salary’, sysdate); commit; raise_application_error (-2001, ‘Access denied!’); end if; End;
29	Usage #3: Consistency of environment
30	Usage #3: Example Create table A (a number primary key); Create table B (a number, b number); Alter table B add constraint a_fk foreign key (a) references A(a) deferrable initially deferred;
31	Usage #4: Structural Optimization
32	Usage #4: Example Create table A (a number primary key); Create table B (a number, b number); Alter table B add constraint a_fk foreign key (a) references A(a) deferrable initially deferred;
33	Usage #5: DDL in triggers
34	Usage #5: Example Create or replace trigger u_uml_attrib Instead of Insert on uml_attrib For each row Declare pragma autonomous_transaction; Begin if check(:new.attrib_cd)=‘Y’ then execute immediate ‘ alter table ’\|\|:new.class_cd \|\|‘ add column ’\|\|:new.attrib_cd \|\|‘ ’\|\|:new.datatype; commit; End;
35	Usage #6: SELECT-only environment
36	Usage #6: Example
37	Summary
38	Contact Information Michael Rosenblum – mrosenblum@dulcian.com Dulcian website - www.dulcian.com