Autonomous transactions are used when you want a certain transaction to happen without another transaction and you don't want it to affect the first transactions. Let that sit for a second……ok, so what I mean here.
Let's imagine you start a transaction A. While you process that one, you start other transaction B which will finish prior the transaction A. Now, if you COMPLETE transaction B, that WILL complete also transaction A. Why?….Well because a transaction completes either with:
So, let's say we commit transaction B, well that will commit also transaction A, EXCEPT if transaction B isn't AUTONOMOUS transaction :) If Transaction B is defined as autonomous, then its completion WON'T affect transaction A.
So, when we know that, how we define an autonomous transaction:
Defining an autonomous transaction is done with the key word: “PRAGMA” as follows:
Defining Pragma transaction
CREATE OR REPLACE PROCEDURE log_msg (p_act_id accounts.act_id%TYPE, p_msg VARCHAR2) IS PRAGMA AUTONMOUS_TRANSACTION; <- This line indicates autonomous transactions BEGIN insert into log_table(log_id,...) ..... ..... COMMIT; END log_msg;
Autonomous transactions can be used withinb other functions, triggers or even anonymous blocks as follows:
Usage within a procedure
Create or replace procedure process_order(p_act_id accounts_act_id%TYPE, p_item_id items.item_id%TYPE, p_item_value items.item_value%TYPE) IS BEGIN --Debit Account Update accounts.... --Log message log_msg(1,'Act 1 debited'); <- AUTONOMOUS TRANSACTION .... COMMIT; .... END process_order;
Autonomous transactions are generally used in the following scenarios: