PLSQL-AUTONOMOUS TRANSACTION

AUTONOMOUS TRANSACTION

Prior to Oracle8i, there was no way in which some SQL operations within a transaction could be committed independent of the rest of the operations. Oracle allows this, however, through autonomous transactions. An  autonomous transaction is a transaction that is started within the context of another transaction, known as parent transaction, but is independent of it. The autonomous transaction can be committed or rolled back regardless ot the state of the parent transaction.

Ex:

CREATE OR REPLACE TRIGGER AUTONOMOUS_TRANSACTION_TRIGGER

after insert on student

DECLARE

pragma autonomous_transaction;

BEGIN

update student set marks = 555;

commit;

END AUTONOMOUS_TRANSACTION_TRIGGER;

Output:

SQL> select * from student;

        NO  NA      MARKS

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

         1    a         111

         2    b         222

         3    c         300

SQL>  insert into student values(4,'d',444);

SQL> select * from student;

        NO  NA      MARKS

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

         1    a         555

         2    b         555

         3    c         555

         4    d         444

RESTRICTIONS ON AUTONOMOUS TRANSACTION

Ø  If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur in you program.

Ø  You cannot mark all programs in a package as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicity in each program.

Ø  To exit without errors from an autonomous transaction program that has executed at least one INSERT or UPDATE or DELETE, you must perform an explicit commit or rollback.

Ø  The COMMIT and ROLLBACK statements end the active autonomous transaction, but they do not force the termination of the autonomous routine. You can have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.

Ø  You can not rollback to a savepoint set in the main transaction.

Ø  The TRANSACTIONS parameter in the oracle initialization file specifies the maximum number of transactions allowed concurrently in a session. The default value is 75 for this, but you can increase the limit.