ads

Thursday, July 21, 2016

What is Autonomous transaction in Oracle




An autonomous transaction is available from Oracle 8i. It is a very cool, useful, unique and powerful feature in oracle.
An autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement.
When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended.
The autonomous transaction must commit or roll back before it returns control to the calling transaction.
Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database.
Autonomous transactions can be nested. 
For use Autonomous Transactions feature in program we have to use 
PRAGMA AUTONOMOUS_TRANSACTION Keyword in Program
When to use Autonomous Transactions?
• Logging mechanism
you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries.
• Commits and rollbacks in your database triggers
If you define a trigger as an autonomous transaction, then you can commit and/or rollback in that code.
• Software usage meter
You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application.

Mutation Error

Using autonomous transaction we can avoid mutation error.

No comments:

Post a Comment