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
