Understanding AUTONOMOUS TRANSACTION with ISOLATION LEVEL

The default behavior of AUTONOMOUS TRANSACTIONS is that once a COMMIT/ROLLBACK occurs in the AUTONOMOUS TRANSACTION, those changes are visible immediately in the main transaction. But what if you want to hide those changes from the main transaction? You want them saved but the information should not be available to the main transaction. To achieve this, use SET TRANSACTION as follows :-

SET TRANSACTION ISOLATION LEVEL READ COMMITTED :–

If you specify READ COMMITTED, a DML statement that attempts to modify a table already modified in an uncommitted transaction will wait until those row locks are released. This is the default.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE :–

When you specify SERIALIZABLE, a DML statement that attempts to modify a table already modified in an uncommitted transaction will fail.

SQL> CREATE OR REPLACE PROCEDURE test_proc(p_empno NUMBER)
  2  AS
  3   PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5   UPDATE emp SET sal = sal + 1 WHERE empno = p_empno;
  6   COMMIT;
  7  END;
  8  /

Procedure created.

SQL> DECLARE
  2   nsal NUMBER;
  3   nempno NUMBER := 7839;
  4  BEGIN
  5   SELECT sal
  6   INTO nsal
  7   FROM emp
  8   WHERE empno = nempno;
  9
 10   DBMS_OUTPUT.PUT_LINE('Before Calling AUTONOMOUS_TRANSACTION Procedure : '||nsal);
 11
 12   test_proc(nempno);
 13
 14   SELECT sal
 15   INTO nsal
 16   FROM emp
 17   WHERE empno = nempno;
 18
 19   DBMS_OUTPUT.PUT_LINE('After Calling AUTONOMOUS_TRANSACTION Procedure : '||nsal);
 20  END;
 21  /

--OUTPUT :::
Before Calling AUTONOMOUS_TRANSACTION Procedure : 5000
After Calling AUTONOMOUS_TRANSACTION Procedure : 5001

/*
As you can see the changes that is made in the AUTONOMOUS TRANSACTION is visible in the MAIN TRANSACTION.
*/

SQL> CREATE OR REPLACE PROCEDURE test_proc(p_empno NUMBER)
  2  AS
  3   PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5   UPDATE emp SET sal = sal + 1 WHERE empno = p_empno;
  6   COMMIT;
  7  END;
  8  /

Procedure created.

SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transaction set.

SQL> DECLARE
  2   nsal NUMBER;
  3   nempno NUMBER := 7839;
  4  BEGIN
  5   SELECT sal
  6   INTO nsal
  7   FROM emp
  8   WHERE empno = nempno;
  9
 10   DBMS_OUTPUT.PUT_LINE('Before Calling AUTONOMOUS_TRANSACTION Procedure : '||nsal);
 11
 12   test_proc(nempno);
 13
 14   SELECT sal
 15   INTO nsal
 16   FROM emp
 17   WHERE empno = nempno;
 18
 19   DBMS_OUTPUT.PUT_LINE('After Calling AUTONOMOUS_TRANSACTION Procedure : '||nsal);
 20  END;
 21  /

--OUTPUT :::
Before Calling AUTONOMOUS_TRANSACTION Procedure : 5000
After Calling AUTONOMOUS_TRANSACTION Procedure : 5000

/*
As you can see the changes that is made in the AUTONOMOUS TRANSACTION is NOT visible in the MAIN TRANSACTION.
*/