Autonomous Transactions in Oracle PL/SQL

In Oracle PL/SQL, autonomous transactions are a powerful feature that allows you to perform operations independently of the main transaction. This is especially useful for logging, auditing, or error-handling scenarios where you want to commit changes regardless of the outcome of the main transaction.

Let’s dive into what autonomous transactions are, why they matter, and how to use them effectively—with examples.

What Is an Autonomous Transaction?

An autonomous transaction is a separate, independent transaction initiated from within another transaction (the main transaction). It can perform SQL operations, commit, or roll back without affecting the main transaction.

Use Case Example: Logging Audit Data

Suppose you're inserting a record into a table and want to log this action. Even if the insert fails later, you still want the audit log to persist.

Why and When Should You Use Autonomous Transactions?

Autonomous transactions are ideal when:

• You need to log events or audit actions.
• You want to increment retry counters or track errors.
• You’re building modular, reusable components that should not affect the main transaction.

They are fully independent—no shared locks, resources, or commit dependencies.

How to Use Autonomous Transactions

To define an autonomous transaction, use the PRAGMA AUTONOMOUS_TRANSACTION directive in the declarative section of a PL/SQL block, procedure, or function.

Syntax

PRAGMA AUTONOMOUS_TRANSACTION;

Example: Logging Procedure

CREATE OR REPLACE PROCEDURE log_action(p_message VARCHAR2) IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO audit_log (log_time, message)
 VALUES (SYSDATE, p_message);
 COMMIT;
END;

This procedure logs a message and commits it, regardless of the calling transaction’s outcome.

Controlling Autonomous Transactions

You control autonomous transactions using standard transaction control statements:

• COMMIT
• ROLLBACK [TO savepoint_name]
• SAVEPOINT savepoint_name
• SET TRANSACTION

Example: Using Savepoints

CREATE OR REPLACE PROCEDURE log_with_savepoint(p_message VARCHAR2) IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 SAVEPOINT before_log;
 INSERT INTO audit_log (log_time, message)
 VALUES (SYSDATE, p_message);
 -- Simulate error
 -- ROLLBACK TO before_log;
 COMMIT;
END;

Entering and Exiting Autonomous Routines

When you enter an autonomous routine, the main transaction suspends. It resumes only after the autonomous routine exits.

Committing and Rolling Back

Each COMMIT or ROLLBACK ends the current autonomous transaction. A new transaction starts with the next SQL statement.

CREATE OR REPLACE PROCEDURE multi_log IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO audit_log VALUES (SYSDATE, 'Step 1');
 COMMIT;
 INSERT INTO audit_log VALUES (SYSDATE, 'Step 2');
 COMMIT;
END;

Using Savepoints

Savepoints in autonomous transactions are isolated from those in the main transaction. You can use the same names, but they are scoped to their respective transactions.

Avoiding Errors with Autonomous Transactions

Keep these in mind:

• Deadlocks can occur if the autonomous transaction accesses resources locked by the main transaction.
• The TRANSACTIONS parameter limits concurrent transactions—autonomous ones count too.
• Always COMMIT or ROLLBACK Before exiting to avoid exceptions.

Autonomous transactions are a vital tool in any Oracle developer’s toolkit. It allows for clean, modular, and resilient PL/SQL code, especially in systems where logging and auditing are critical.