Database Reference
In-Depth Information
Now let's observe the behavior of the
nonautonomous
transaction in an anonymous block of PL/SQL code:
EODA@ORA12CR1> begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
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.