Autonomous Transactions and Its Application

Q: What is Autonomous Transactions?

Autonomous transactions allow you to create a new transaction within a transaction that may commit or roll back changes, independently of its parent transaction. They allow you to suspend the currently executing transaction, start a new one, do some work and commit or roll back, all without affecting the currently executing transaction state. And may be used in:

·        Top-level anonymous blocks

·        Local, standalone, or packaged functions and procedures

·        Methods or object types

·        Database triggers

EX:

             Create or replace procedure AutonomousInsert

             As

                          Pragma autonomous transaction;

             Begin

                          Insert into t values (‘Autonomous Insert’);

                          Commit;

             End; 

             /

Begin

   Insert into t values (‘Anonymous Block’);

   AutonomousInsert;

   Rollback;

End;

/

Select * from t;

Output:

MSG

-------------------------

Autonomous Insert

Q: In which case we need Autonomous Transactions?

Logging Mechanisms:

             You need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries. What's a person to do? Go autonomous!

Commits and rollbacks in your database triggers:

             If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code. 

Q: Describe the rules and restriction for Autonomous Transactions?

·        We can only make a top-level anonymous block an autonomous transactions 

        This will work

             DECLARE

                          PRAGMA AUTONOMOUS_TRANSACTION;

                          myempno NUMBER;

             BEGIN

                          INSERT INTO EMP VALUES (myempno, ...);

                          COMMIT;

             END;

             /

Whereas this construction:

             DECLARE

                          myempno NUMBER;

             BEGIN

                          DECLARE

                                      PRAGMA AUTONOMOUS_TRANSACTION;

                          BEGIN  

                                      INSERT INTO EMP VALUES (myempno, ...);

                                      COMMIT;

                           END;

             END;

             /

         Results an error      

             PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here

·        If an autonomous transaction attempts to access a resource held by the main transaction (which has been suspended until the autonomous routine exits), you can cause a deadlock to occur in your program.

             CREATE OR REPLACE PROCEDURE update_salary (dept_in IN NUMBER)

             IS

             PRAGMA AUTONOMOUS_TRANSACTION;

             CURSOR myemps IS SELECT empno FROM EMP WHERE deptno=dept_in FOR UPDATE NOWAIT;

             BEGIN

             FOR rec IN myemps

             LOOP

                          UPDATE EMP SET Sal = Sal * 2      WHERE empno = rec.empno;

             END LOOP;

             COMMIT;

             END;

             /

             BEGIN

                UPDATE EMP SET Sal = Sal * 2;

                date_salary (10);

             END;

             /   

           The results are not pretty:

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

• You cannot mark all subprograms in a package (or all methods in an object type) as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicitly in each program. For example, the following package specification is invalid:

CREATE PACKAGE warcrimes_pkg 

AS

   PRAGMA AUTONOMOUS_TRANSACTION;

   PROCEDURE register (Culprit IN VARCHAR2, event IN VARCHAR2);

END warcrimes_pkg;

/

• To exit without errors from an autonomous transaction program, you must perform an explicit commit or rollback. If the program (or any program called by it) has transactions pending, the runtime engine will raise an exception -- and then it will roll back those uncommitted transactions.

CREATE OR REPLACE PROCEDURE fire_em_all

IS

   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

   DELETE FROM EMP;

END;

/

             SQL :\> exec fire_em_all

             *

             ERROR at line 1

ORA-06519: active autonomous transaction detected and rolled back

Q: Explain the concepts of Autonomous Transactions within SQL statement?

We have been able to call your own functions from within SQL -- provided that you follow a variety of rules. The main one is this: you are not allowed to update the database. But this can be done by using Autonomous Transactions

Suppose that we want to keep a trace of all the rows that have been touched by a query. We create this simple function to perform the audit:

CREATE OR REPLACE FUNCTION traceit (

   Tab IN VARCHAR2,

   Rowid_in IN ROWID)

   RETURN INTEGER

IS

BEGIN

   INSERT INTO query_trace VALUES (tab, rowid_in, USER, SYSDATE);

   RETURN 0;

END;

/

When I try to use this function inside a query, I get the expected error:

SQL> select ename, traceit ('emp', rowid) from EMP;

                   *

ERROR at line 1:

ORA-14551: cannot perform a DML operation inside a query

If we now transform traceit into an autonomous transaction by adding the pragma (and committing my results before the RETURN statement!), the results are very different. My query works, and the query_trace table is filled:

SQL> SELECT ename, traceit ('emp', ROWID)

  2    FROM EMP;

ENAME      TRACEIT ('EMP', ROWID)

----------       --------------------

KING                          0

...

MILLER                       0

SQL> SELECT table_name, rowid_info, queried_by, TO_CHAR (queried_at, 'HH: MI: SS’) queried_at FROM query_trace;

TABLE_NAME           ROWID_INFO                     QUERIED_BY QUERIED_AT

----------       ------------------                ----------       ----------

EMP              AAADEPAACAAAAg0AAA     SCOTT          05:32:54

...

EMP              AAADEPAACAAAAg0AAN     SCOTT          05:36:50