Autonomous Transactions are available with Oracle8I, Oracle8I EE, and Oracle8I Workstation.
Autonomous Transactions provide a new method of controlling transactions in stored procedures. Autonomous Transactions allow you to create a new subtransaction that may commit or rollback changes independent of the parent transaction. We will cover
A quick example and an explanation of what occurs best explain this feature:
SQL> create table t ( x int );
Table created.
SQL>
SQL> create or replace procedure insert_into_t
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( 1 );
6 commit;
7 end;
8 /
Procedure created.
SQL> select * from t;
no rows selected
SQL> begin
2 insert into t values ( -1 );
3 insert_into_t;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from t;
X
----------
1 |
In the above example, we created a procedure INSERT_INTO_T. In this procedure we used the new pragma AUTONOMOUS_TRANSACTION. That directive tells the database that this procedure, when executed, is to be executed as a new subtransaction – independent from its parent transaction. This procedure simply inserts a record with the value of ‘1’ into the table T and commits. We then proceed to create an anonymous PL/SQL block in which we insert the value of ‘-1’ into T, invoke the stored procedure INSERT_INTO_T and rollback. Prior to autonomous transactions, the commit in the subroutine INSERT_INTO_T would have committed not only the work it performed (the insert of ‘1’) but any outstanding work the session had performed and not yet committed (the insert of ‘-1’ in the anonymous block). The rollback would have rolled back no work since the commit in the procedure would have committed both inserts. We see this is not the case with autonomous transactions. The work performed in the procedure marked with AUTONOMOUS_TRANSACTION was committed however the work performed outside the autonomous transaction was rolled back.
Previous releases of Oracle supported autonomous transactions internally. They were known as recursive SQL operations. For example, when selecting from a non-cached sequence a recursive transaction was performed for you to increment the sequence immediately. The update of the sequence was immediately committed and visible to other transactions but your transaction was not committed as yet. Additionally, if you rolled back your transaction, the increment to the sequence remained in place – it was not rolled back with your transaction as it had already been committed. Space management and other internal operations are performed in a similar recursive fashion.
Now that we see what an autonomous transaction is, we’ll take a look at why we might use them.
So, why would you want to use autonomous transactions? There are many reasons from performing auditing without worrying about losing the audit trail records if the client rolls back to performing DDL in triggers, to writing to the database state in a function called from SQL, to building more modular applications. We will take a look at a few examples to see how they are implemented.
A frequent question in the past posed by application developers has been "how can I audit an attempt to modify information securely". The approach in the past that many have tried (and failed) to use was triggers. The trigger would detect the update and upon discovering a user modifying data they should not – the trigger would create an audit record and fail the update. Unfortunately, when you failed the update you also rolled back the audit record – it was an all or nothing failure. Now with autonomous transactions we can securely capture the audit of an attempted operation as well as roll back that operation – giving us the ability to tell the end user – you cannot modify that data and we have a record of you attempting to modify it.
Here is a small example:
SQL> REM Create a copy of the EMP demo table for working with. SQL> REM we will allow anyone to modify this table for the demo. SQL> create table emp as select * from scott.emp; Table created. SQL> grant all on emp to public; Grant succeeded. SQL> REM this is our audit table. We will capture the username SQL> REM date of attempting operations and some message that SQL> REM describes the operation they attempted in this table. SQL> create table audit_tab (uname varchar2(30), dt date, msg varchar2(4000)); Table created. SQL> create or replace trigger emp_trigger 2 before update of SAL on emp 3 for each row 4 declare 5 -- this pragma will allow our trigger to commit and 6 -- capture an audit trail record. We can then fail 7 -- the triggering statement, preventing the update from 8 -- occurring 9 pragma autonomous_transaction; 10 l_cnt number; 11 begin 12 -- this query just makes sure the empno being updated 13 -- is a person who reports to the employee doing the update. 14 -- connect by is a nice way to build this hierarchy. 15 -- since the where clause is processed after the hierarchy 16 -- is built, we can easily use exists on this 17 select count(*) 18 into l_cnt 19 from dual 20 where exists ( select empno 21 from emp 22 where empno = :new.empno 23 start with mgr = 24 (select empno from emp where ename=USER) 25 connect by prior empno = mgr ); 26 27 -- If the exists returns no rows, we have attempted 28 -- to update someone who does not work for us. We 29 -- will audit the attempt as well as fail the attempt. 30 -- the employee salary will not be updated and we will 31 -- have a record of the attempt 32 if ( l_cnt = 0 ) 33 then 34 insert into audit_tab values ( user, sysdate, 35 'Attempt to update salary of ' || 36 :new.ename || '-' || :new.empno); 37 commit; 38 39 raise_application_error( -20001, 40 'You have tried to do something you should '|| 41 'not have and we know it'); 42 end if; 43 end; 44 / Trigger created. |
So, what we have so far is a table EMP of data we want to protect, a table AUDIT_TAB in which we will record failed attempts to update data (attempts we have prevented) and a trigger that employs the autonomous transaction to do the work. As different users, we will now attempt some DML operations to see what happens:
SQL> show user
USER is "DEMO_AUTONOMOUS"
SQL> REM First we will try to update a record as the
SQL> REM demo account. This will fail as the selects on emp
SQL> REM below will show however, the audit trail record of
SQL> REM our attempt will persist.
SQL> select empno, ename, mgr, sal
2 from emp where ename = 'ADAMS';
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7876 ADAMS 7788 1100
SQL> update emp set sal = sal*2 where ename = 'ADAMS';
update emp set sal = sal*2 where ename = 'ADAMS'
*
ERROR at line 1:
ORA-20001: You have tried to do something you should not have and we know it
ORA-06512: at "DEMO_AUTONOMOUS.EMP_TRIGGER", line 36
ORA-04088: error during execution of trigger 'DEMO_AUTONOMOUS.EMP_TRIGGER'
SQL> select empno, ename, mgr, sal
2 from emp where ename = 'ADAMS';
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7876 ADAMS 7788 1100
SQL> select * from audit_tab;
UNAME DT MSG
------------------------------ --------- ------------------------------
DEMO_AUTONOMOUS 10-JUN-99 Attempt to update salary of
ADAMS-7876 |
Since the user DEMO_AUTONOMOUS has no record in the EMP table, this update fails. The SELECT from EMP shows that the update did not take place and the SELECT from AUDIT_TAB shows we were able to detect it and audit it.
Now, lets show a user who is allowed to update some of the data. The user SCOTT has one employee --- ADAMS – that reports to him.
SQL> show user
USER is "SCOTT"
SQL> REM Now, we will try as a user who has employees reporting to them.
SQL> REM This will succeed as hown by the data.
SQL> select empno, ename, mgr, sal
2 from demo_autonomous.emp where ename = 'ADAMS';
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7876 ADAMS 7788 1100
SQL> update demo_autonomous.emp set sal = sal*2 where ename = 'ADAMS';
1 row updated.
SQL> select empno, ename, mgr, sal
2 from demo_autonomous.emp where ename = 'ADAMS';
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7876 ADAMS 7788 2200
SQL> REM Now, try to update a record we should not (our own salary)
SQL> REM and get caught.
SQL> select empno, ename, mgr, sal
2 from demo_autonomous.emp where ename = 'SCOTT';
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7788 SCOTT 7566 3000
SQL> update demo_autonomous.emp set sal = sal*2 where ename = 'SCOTT';
update demo_autonomous.emp set sal = sal*2 where ename = 'SCOTT'
*
ERROR at line 1:
ORA-20001: You have tried to do something you should not have and we know it
ORA-06512: at "DEMO_AUTONOMOUS.EMP_TRIGGER", line 36
ORA-04088: error during execution of trigger 'DEMO_AUTONOMOUS.EMP_TRIGGER'
SQL> select empno, ename, mgr, sal
2 from demo_autonomous.emp where ename = 'SCOTT';
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7788 SCOTT 7566 3000
SQL> connect demo_autonomous/demo_autonomous
Connected.
SQL> select * from audit_tab;
UNAME DT MSG
------------------------------ --------- ------------------------------
DEMO_AUTONOMOUS 01-JUN-99 Attempt to update salary of
ADAMS-7876
SCOTT 01-JUN-99 Attempt to update salary of
SCOTT-7788 |
So, that shows that SCOTT can update some of the data but again, when updating data he should not – SCOTT gets caught.
This is a frequently asked question – "How can I create a database object whenever I insert a row into such and such a table". The database object varies from question to question – sometimes people want to create a database USER when they insert into some table, sometimes they want to create a table or sequence. Regardless – autonomous transactions make this possible.
In the past, one might have used DBMS_JOB to schedule the DDL to execute after the transaction commits. This is still a viable option and in many cases is still the correct option. The nice thing about using DBMS_JOB to schedule the DDL is that is offers a way of making DDL transactional. If the trigger queues a job to be executed and that job creates a user account – upon rollback of the parent transaction, the job to create the user will be rolled back as well. No record in your ‘people’ table and no database account. Using autonomous transactions in the same scenario – you will have created the database account but have no record in the people table. Which method you use will be decided upon based on your requirements.
Here is a small example that shows the creation of a database account anytime a user record is placed into the "APPLICATION_USERS" table. Note that the definer of this trigger must have been granted the "CREATE USER" privilege directly (not via some role).
SQL> create user demo_ddl identified by demo_ddl; User created. SQL> REM In our trigger below, we want to grant CONNECT SQL> REM and RESOURCE to other users. Hence, we need to SQL> REM grant connect and resource to our schema SQL> REM WITH ADMIN OPTION so it can grant them to others. SQL> grant connect, resource to demo_ddl with admin option; Grant succeeded. SQL> REM Additionally, since we want to create and drop users SQL> REM in the trigger -- we must be granted the CREATE and DROP SQL> REM user privelege directly. Roles are never enabled during SQL> REM the execution of a trigger. roles may be enabled during SQL> REM the execution of a procedure or function but not a trigger SQL> grant create user to demo_ddl; Grant succeeded. SQL> grant drop user to demo_ddl; Grant succeeded. SQL> connect demo_ddl/demo_ddl Connected. SQL> REM create a table to hold our users. We will place a trigger SQL> REM after insert for each row on this table to create the accounts. SQL> REM We could (but won't) further extend the example to provide SQL> REM after update for each row triggers to allow the changing of SQL> REM passwords and roles REM as well. SQL> REM We will put a delete for each row trigger on this table SQL> REM as well. SQL> create table application_users ( uname varchar2(30), pw varchar2(30), 2 role_to_grant varchar2(4000) ); Table created. SQL> create or replace trigger application_users_aifer 2 after insert on application_users 3 for each row 4 declare 5 -- this pragma will allow our trigger to perform DDL 6 pragma autonomous_transaction; 7 begin 8 -- shows off the dynamic sql we will see in a later section 9 10 execute immediate 11 'grant ' || :new.role_to_grant || 12 ' to ' || :new.uname || 13 ' identified by ' || :new.pw; 14 end; 15 / Trigger created. |
The above row level trigger is coded as an autonomous transaction. This allows this trigger to perform DDL. We are using the new dynamic sql feature of PL/SQL as well in this example, something we will take a deeper look at in another section. When this trigger fires, it will execute a statement similar to "grant connect, resource to some_username identified by some_password". This statement performs both a CREATE USER and GRANT in one step. The advantage to this is that if the single statement above fails – we will fail the parent insert as well, the row will not be inserted into the APPLICATION_USERS table and we are left in a consistent statement. On the other hand, if we used two statements to create and grant to the user, the CREATE USER statement might succeed and the GRANT could fail. The failure of the grant would cause the insert to be rolled back leaving us in a state where the user account was created, no grants were performed and no row exists in the APPLICATION_USERS table. Bear in mind that a multi row insert into the APPLICATION_USERS table could get us into the same predicament and therein lies one of the issues with autonomous transactions. It is similar to the issue with sequences – rolling back does not undo the sequence increment. This is what makes sequences great for concurrency (many people can select from them simultaneously) but makes them not useable to generate a gap free sequence of numbers (a rollback after a select of a NEXTVAL on a sequence will always leave a gap). You, as the developer, need to be aware of this and develop your applications to take this into consideration.
Now, let’s finish the application:
SQL> create or replace trigger application_users_adfer 2 after delete on application_users 3 for each row 4 declare 5 -- this pragma will allow our trigger to perform DDL 6 pragma autonomous_transaction; 7 begin 8 execute immediate 'drop user ' || :old.uname; 9 end; 10 / Trigger created. SQL> REM Let’s test it by inserting a user to create SQL> insert into application_users values 2 ( 'NewUser', 'NewPW', 'connect, resource' ); 1 row created. SQL> REM To show it works – let us look at our new user account SQL> REM and then connect as that new user SQL> select * from all_users where username = 'NEWUSER'; USERNAME USER_ID CREATED ------------------------------ ---------- --------- NEWUSER 414 03-JUN-99 SQL> connect newuser/newpw Connected. SQL> select * from session_roles; ROLE ------------------------------ CONNECT RESOURCE SQL> REM The above shows the user is created, the password SQL> REM is in place and the appropriate roles have been granted SQL> REM Now, lets try ‘deleting’ the user SQL> connect demo_ddl/demo_ddl Connected. SQL> delete from application_users; 1 row deleted. SQL> commit; Commit complete. SQL> select * from all_users where username = 'NEWUSER'; no rows selected |
So, there it is – the triggers are capable of adding and dropping users upon inserts and deletes from a database table.
From time to time people have a need to perform a DML operation from an environment where they only have the ability to execute SQL select statements. This frequently occurs in a report writing tool. For example, a posting on the internet newsgroup comp.databases.oracle.misc asked:
>Hi, > >This is a bit convoluted but: > >- I am using an application that can only execute SQL statements >- The SQL statement can call user defined functions >- The function in turn can call procedures > >However, I noticed that the procedure that is called cannot do any >updates, inserts or deletes. Is there a trick to get past this ? > >for example: >select myfunc(parent) from dual; > >Function myfunc >..... >bom_exploder(parent) <---- this proc does a BOM explosion which inserts > >records into a temp table. >
Here, the person was asking to do a Bill Of Materials (BOM) explosion into another table. This BOM explosion would then be queried up by the reporting tool and displayed. The creation of the BOM explosion was a procedural operation and could not be done in a view or single query effectively. The reporting tool was not capable of performing anything other then a SELECT statement. Using autonomous transactions, this is something we can accomplish easily today. The following is an example of this. Instead of a BOM explosion, we will explode a hierarchy based on the EMP table. We will create a function that takes a department number and it will build the hierarchy of people that manage people working in that department in a temporary table. Additionally, you’ll be able to order each of the levels of the hierarchy by any column you wish (something you cannot do in a connect by query). This function will return a message indicating success or failure, if successful, another select may be issued to retrieve the result set. The bottom line is – using only the SELECT statement, we will be able to insert data:
SQL> REM create a table of demo data SQL> create table emp as select * from scott.emp; Table created. SQL> REM we will use a temporary table to hold our data. SQL> REM Since we are using AUTONOMOUS transactions to populate SQL> REM this table, we *must* use a session level temporary table SQL> REM not a transaction level temporary table since our autonomous SQL> REM transaction must commit. A transaction level table would SQL> REM always appear empty to the parent transaction SQL> create global temporary table hierarchy 2 on commit preserve rows 3 as select 0 seq, 0 lev, emp.* from emp where 1=0; Table created. |
The EMP table created above represents our application data. We will write our queries against this table – it contains our hierarchy. The temporary table HIERARCHY is a true temporary table. The way we have defined it "on commit preserve rows" allows our session – and all transactions in that session – see data our session puts in that table. This table will appear empty to all other sessions until they put data in it. When we build our hierarchy procedurally from the EMP table below, we will put the hierarchy we build into the temporary table.
Now for the code to build the hierarchy for a given department. We need to be able to call this function from SQL – but it does inserts into the temporary table. In the past this was impossible to accomplish. If a procedure wrote to the database state (did an insert, update, delete, etc) it could not be called from SQL. The pragma autonomous_transaction allows us to overcome this.
SQL> REM our function to fill the temporary table. SQL> REM this function takes a department number in, we will SQL> REM start with all the managers for that department (anyone SQL> REM who manages someone in that department). We can also SQL> REM supply an order by. The goal of this procedure is similar SQL> REM to a connect by query but it lets us order the data in each SQL> REM level and subtree -- something that is impossible to do in SQL> REM a connect by. SQL> REM this procedure is *similar* to the query: SQL> REM select * SQL> REM from emp SQL> REM start with empno = :x SQL> REM connect by prior mgr = empno SQL> REM (order by something) SQL> REM it is different from that in that the order by will SQL> REM be applied to each subtree in the hierarchy -- NOT SQL> REM to the entire hierarchy!!! SQL> create or replace 2 function create_hierarchy( p_deptno in number, 3 p_order_by in varchar2 default NULL ) 4 return varchar2 5 as 6 pragma autonomous_transaction; 7 8 -- we need to dynamically open our cursors since 9 -- we don't know the "order by" at compile time. Hence 10 -- the use of a ref cursor. 11 type refCur is ref cursor; 12 13 -- l_seq will be used to preserve the order of the rows 14 -- in the temp table when we select them out. 15 l_seq number default 0; 16 l_cur refCur; 17 18 19 -- this procedure inside the function does all of the work 20 -- It is a recursive procedure. It takes an OPEN cursor and 21 -- for each row in that cursor will add it to the result set 22 -- and then recursively process the people who work for that 23 -- employee 24 procedure explode( p_cur in out refcur, 25 p_level in number ) 26 is 27 l_rec emp%rowtype; 28 l_cur refCur; 29 begin 30 loop 31 fetch p_cur into l_rec; 32 exit when p_cur%notfound; 33 34 l_seq := l_seq+1; 35 insert into hierarchy 36 values ( l_seq, p_level, 37 l_rec.empno, l_rec.ename, l_rec.job, l_rec.mgr, 38 l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ); 39 40 open l_cur for 'select * 41 from emp 42 where mgr = :x ' || 43 p_order_by 44 USING l_rec.empno; 45 46 explode( l_cur, p_level+1 ); 47 end loop; 48 close p_cur; 49 end; 50 51 begin 52 -- begin by reseting our temporary table. Just in case 53 -- we've already run such a query in this session 54 55 delete from hierarchy; 56 57 -- our initial set of people is anyone who manages 58 -- someone in the department we are interested in 59 60 open l_cur for 'select * 61 from emp 62 where empno in ( select mgr 63 from emp 64 where deptno = :x ) ' || 65 p_order_by 66 USING p_deptno; 67 68 -- explode that set (and every subset) 69 explode( l_cur, 1 ); 70 71 -- commit, if we forget to commit, we get 72 -- ORA-06519: active autonomous transaction 73 -- detected and rolled back 74 -- which removes our rows! 75 76 commit; 77 78 -- On success, return a message to that effect... 79 -- the exception handler below returns the oracle error that 80 -- happend on failure. 81 return 'Ok, result set ready to go'; 82 exception 83 when others then 84 rollback; 85 return sqlerrm; 86 end; 87 / Function created. |
So that is our procedure. It works by starting with a query "select * from emp where empno in ( select ALL mgr’s of people in deptno X ) order by <something>". That query is opened in the main block and passed to ‘explode’. Explode takes that query and fetches from it. For every row in that result set, explode builds another query – this time the set of all people whose manager is the current record. This query is sent down to explode – which does the same thing again (and again) until finally, we get to the end of a tree. The recursion ‘unwinds’ and we finish. We are left with the result set in the hierarchy table. To query it is easy now. Here are some examples using this technique:
SQL> REM Now to test it. We start with department 20. We will order
SQL> REM by ename in each level of the hierarchy
SQL> select create_hierarchy( 20, 'order by ename' ) msg from dual;
MSG
------------------------------
Ok, result set ready to go
SQL> REM Now display it. Should be all managers of people in deptno = 20
SQL> REM ordered by ename -- under each of them, we see their directs (ordered
SQL> REM by ename) and so on and so on...
SQL> select lpad(' ',lev*2,' ')|| ename ename, hiredate, job, deptno
2 from hierarchy
3 order by seq;
ENAME HIREDATE JOB DEPTNO
-------------------- --------- --------- ----------
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
KING 17-NOV-81 PRESIDENT 10
BLAKE 01-MAY-81 MANAGER 30
ALLEN 20-FEB-81 SALESMAN 30
JAMES 03-DEC-81 CLERK 30
MARTIN 28-SEP-81 SALESMAN 30
TURNER 08-SEP-81 SALESMAN 30
WARD 22-FEB-81 SALESMAN 30
CLARK 09-JUN-81 MANAGER 10
MILLER 23-JAN-82 CLERK 10
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
23 rows selected.
SQL> REM do the same thing, order by hiredate this time
SQL> select create_hierarchy( 20, 'order by hiredate' ) msg from dual;
MSG
------------------------------
Ok, result set ready to go
SQL> select lpad(' ',lev*2,' ')|| ename ename,
2 lpad(' ',lev*2,' ')|| hiredate hiredate_str, job, deptno
3 from hierarchy
4 order by seq;
ENAME HIREDATE_STR JOB DEPTNO
-------------------- -------------------- --------- ----------
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
KING 17-NOV-81 PRESIDENT 10
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
BLAKE 01-MAY-81 MANAGER 30
ALLEN 20-FEB-81 SALESMAN 30
WARD 22-FEB-81 SALESMAN 30
TURNER 08-SEP-81 SALESMAN 30
MARTIN 28-SEP-81 SALESMAN 30
JAMES 03-DEC-81 CLERK 30
CLARK 09-JUN-81 MANAGER 10
MILLER 23-JAN-82 CLERK 10
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
23 rows selected.
SQL> REM lets see what happens when we have an error:
SQL> select create_hierarchy( 20, 'order by bogus' ) msg from dual;
MSG
------------------------------
ORA-00904: invalid column name |
Using autonomous transactions is very straightforward, no special init.ora parameters, no session events – just a pragma autonomous_transaction in the PL/SQL block. The programmer must take care to commit or rollback the autonomous transaction. If they do not, the error:
SQL> declare 2 pragma autonomous_transaction; 3 begin 4 insert into t values ( 1 ); 5 end; 6 / declare * ERROR at line 1: ORA-06519: active autonomous transaction detected and rolled back ORA-06512: at line 4 |
Will be raised. Additionally, the programmer must be aware of the fact they can deadlock themselves using this feature. Since the autonomous transaction is running in a separate transaction context, it cannot lock any rows it’s parent transaction has locked. For example:
SQL> REM Create a demo table with a PRIMARY KEY SQL> create table t ( x int primary key ); Table created. SQL> REM put some data into it… SQL> insert into t values ( 1 ); 1 row created. SQL> REM Now, in an autonomous transaction, lets try to SQL> REM insert the same record. Since the autonomous SQL> REM transaction cannot ‘see’ uncommitted work from its SQL> REM parent transaction, we will deadlock ourselves. SQL> declare 2 pragma autonomous_transaction; 3 begin 4 insert into t values ( 1 ); 5 commit; 6 end; 7 / declare * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4 |
There are not many drawbacks that come to mind with autonomous transactions. Once you understand how they work and what they are useful for, they work without any ‘gotchas’. They have very few limitations or restrictions and, as they have been implemented for a long time in Oracle as recursive SQL, they are very well tested.
|
Pros |
Cons |
|
Allows you to commit in a trigger |
Cannot perform parallel queries in autonomous transactions. These queries will execute serially. |
|
Allows you to perform DML from a SELECT |
Deadlocks may occur more frequently as a single user can now deadlock themselves |
|
Allows for more modular code with less side effects (avoids the "hey – you rolled back my work!") |
Must be used in a top level anonymous block, procedure, or function. Cannot be included in a nested PL/SQL block. |
|
Allows you to implement auditing that cannot be rolled back |
|
|
As it is an extension of recursive SQL, it is a feature that has been around internally for a long time (tested). |
|
Here you can find the demonstration scripts used in this article. Make sure you read through them. They drop and create users called "demo_autonomous", "demo_ddl", and "demo_hierarchy".