Database Reference
In-Depth Information
EODA@ORA12CR1> select * from t;
MSG
-------------------------
Anonymous Block
NonAutonomous Insert
As you can see, the work performed by the anonymous block, its INSERT , was committed by the
NONAUTONOMOUS_INSERT procedure. Both rows of data were committed, so the ROLLBACK command had nothing
to roll back. Compare this to the behavior of the autonomous transaction procedure:
EODA@ORA12CR1> delete from t;
2 rows deleted.
EODA@ORA12CR1> commit;
Commit complete.
EODA@ORA12CR1> begin
2 insert into t values ( 'Anonymous Block' );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from t;
MSG
-------------------------
Autonomous Insert
Here, only the work done by and committed in the autonomous transaction persists. The INSERT done in the
anonymous block was rolled back by the ROLLBACK statement on line 4. The autonomous transaction procedure's
COMMIT has no effect on the parent transaction started in the anonymous block. In a nutshell, this captures the essence
of autonomous transactions and what they do.
To summarize, if you COMMIT inside a “normal” procedure, it will make durable not only its own work but also
any outstanding work performed in that session. However, a COMMIT performed in a procedure with an autonomous
transaction will make durable only that procedure's work.
When to Use Autonomous Transactions
The Oracle database has supported autonomous transactions internally for quite a while. We see them all of the time
in the form of recursive SQL. For example, a recursive transaction may be performed when selecting from a sequence,
in order for you to increment the sequence immediately in the SYS.SEQ$ table. The update of the SYS.SEQ$ table in
support of your sequence is immediately committed and visible to other transactions, but your transaction is not yet
committed. Additionally, if you roll back your transaction, the increment to the sequence remains in place; it is not
rolled back with your transaction, as it has already been committed. Space management, auditing, and other internal
operations are performed in a similar recursive fashion.
 
Search WWH ::




Custom Search