Pragma 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 of 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 explicitly 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.