Autonomous Transactions in Oracle – How to create and use if efficiently

An autonomous transaction is an independent transaction started by the main transaction that lets the user suspend the main transaction, perform SQL operations, commit or rollback these operations and then resume the main transaction. The autonomous transaction has no link to the main transaction. Transaction control statements such as COMMIT, ROLLBACK, SAVEPOINT etc used in an autonomous transaction apply only to the current session of autonomous transaction.

Autonomous transactions can be defined in stored procedures and functions, local procedures and functions defined in a PL/SQL declaration block, packaged procedures and functions, methods of a SQL object type and top level anonymous PL/SQL blocks and PL/SQL triggers. An autonomous transactions executes within an autonomous scope in the routine marked with a pragma (compiler directive) AUTONOMOUS_TRANSCATION. The syntax is
PRAGMA AUTONOMOUS TRANSACTION;

The pragma can be coded anywhere in the declarative section of the routine.

An important characteristic of the autonomous transaction is that the changes in an autonomous transaction are visible to other transactions as soon as commit is executed in the autonomous transaction. User’s can hence view the update without waiting for a commit from the main transaction. In case the isolation level of the main transaction is set to SERIALIZABLE changes made by the autonomous transactions are not visible to the main transaction. Another characteristic is that one autonomous transaction can start other autonomous transactions.

Example of an autonomous transaction in action is given below.

CREATE TABLE EMP (
EMP_ID NUMBER NOT NULL,
EMP_NAME VARCHAR2(100)
);

INSERT INTO EMP (1001, ‘AAAAA’);
INSERT INTO EMP (1002, ‘BBBBB’);

Now 2 rows are inserted by using an anonymous block as an autonomous transaction and committed as follows.
DECLARE
PRAGMA AUTONOMOUS TRANSACTION;
BEGIN
INSERT INTO EMP (1003, ‘CCCCC’);
INSERT INTO EMP (1004, ‘DDDDD’);
COMMIT;
END;

When this PL/SQL block is executed, 2 rows are inserted and committed.

If a rollback occurs now in the main session’s transaction then the rows inserted by the autonomous transactions alone will remain.

The rows with EMP_ID as 1001 and 1002 will have been rolled back.

Autonomous transactions are commonly used in error messages and transaction logging. There are used in instances where even if the main transaction fails, the related information concerning the transaction or the error message have to be preserved. COMMIT and ROLLBACK end the current active autonomous transaction but not the routine itself. A single autonomous routine can have more than one autonomous transaction with separate COMMIT statements. Transaction properties set in the main transaction apply only to it and are excluded in its autonomous transactions.

Autonomous transactions help in doing independent units of work and thus enhance modularity and reusability of software components, provided they are used appropriately. Care should be taken while using them as excessive or incorrect usage might create deadlocks. If an autonomous transaction tries to access a resource held by the main transaction which can resume only when the autonomous routine is exited, then a deadlock occurs.

Oracle raises an exception which if unhandled, rolls back the transaction. Oracle also raises exception if an autonomous transaction is exited without commit or rollback statements where again if exception is unhandled, transaction is rolled back. Care should be taken that the number of concurrent transactions running in the session does not exceed the Oracle initialization parameter TRANSACTIONS.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |