Autonomous transaction – is an independent transaction called from another transaction which is the main transaction.
Features of autonomous transaction
- autonomous transaction doesn’t see uncommitted changes made by main transaction
- committed changes in autonomous transaction are visible in main transaction
- autonomous transaction can call other autonomous transaction. There are no limit how many levels of autonomous transaction can be called
Special pragma AUTONOMOUS_TRANSACTION is introduced in PL/SQL. The pragma instructs the database that executed code is to be executed as new autonomous transaction independent from main transaction.
When you enter the executable section of an autonomous routine, the main routine suspends. When you exit the autonomous routine, the main routine resumes.
Below code declare anonymous block which is main transaction and subprogram auto_prc with defined pragma autonomous_transaction
create table log_tbl ( trans_lvl varchar2(20), trans_id number, trans_det varchar2(100) ); declare procedure auto_prc ( p_lvl varchar2, p_trans_id number, p_trans_det varchar2 ) is pragma autonomous_transaction; begin insert into log_tbl values(p_lvl, p_trans_id, p_trans_det); commit; end; begin execute immediate 'truncate table log_tbl'; insert into log_tbl values('MAIN', 1, 'main transaction 1'); auto_prc('AUTO', 1, 'autonomous transaction 1'); rollback; insert into log_tbl values('MAIN', 2, 'main transaction 2'); auto_prc('AUTO', 2, 'autonomous transaction 3'); commit; insert into log_tbl values('MAIN', 3, 'main transaction 3'); auto_prc('AUTO', 3, 'autonomous transaction 3'); rollback; end; /
from log we can see that rollback operations hasn’t got any impact on executed autonomous transactions
TRANS_LVL TRANS_ID TRANS_DET ----------- ---------- ------------------------- MAIN 2 main transaction 2 AUTO 1 autonomous transaction 1 AUTO 2 autonomous transaction 3 AUTO 3 autonomous transaction 3
The following types of PL/SQL blocks can be defined as autonomous transactions:
- Stored procedures and functions
create or replace procedure proc_name is PRAGMA AUTONOMOUS_TRANSACTION; ... or create or replace function func_name return number is PRAGMA AUTONOMOUS_TRANSACTION; ...
- Local procedures and functions defined in a PL/SQL declaration block.
declare v_id number; procedure auto_prc is PRAGMA AUTONOMOUS_TRANSACTION; begin ... end; begin ... or declare v_id number; function auto_fnc return number is PRAGMA AUTONOMOUS_TRANSACTION; begin ... end; begin ...
- Packaged procedures and functions
create or replace package body test_pkg is procedure test_prc is PRAGMA AUTONOMOUS_TRANSACTION; begin ... end; function test_prc return number is PRAGMA AUTONOMOUS_TRANSACTION; begin ... end; end; /
- Type methods
create type body test_typ as member function test_fnc return number PRAGMA AUTONOMOUS_TRANSACTION; is begin ... end; member procedure test_prc is PRAGMA AUTONOMOUS_TRANSACTION; begin ... end; end; /
- Top-level anonymous blocks
declare PRAGMA AUTONOMOUS_TRANSACTION; ... begin ...
Have a fun 🙂
Tomasz