Autonomous Transaction

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

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *