plsql_autonomous

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:

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:

  • Logging
  • Usage Counters
  • Retry Counters
  • Provide Modular Independent Code
  • plsql_autonomous.txt
  • Last modified: 2020/05/31 14:03
  • by andonovj