Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Thursday, 7 April 2016

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 TRIGGERautonomous_transaction_trigger
   AFTER INSERT
   ON student
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE student
      SET marks = 555;

   COMMIT;
ENDautonomous_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.
Share this article :

0 comments:

Post a Comment