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:

• Commit
• Rollback (which also ends with commit but that is a different story :) )

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:

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:

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:

• Logging
• Usage Counters
• Retry Counters
• Provide Modular Independent Code