Autonomous Transactions: When and How to Use Them Safely

What Is an Autonomous Transaction

An autonomous transaction is an independent transaction started within the context of another transaction. It can commit or roll back without affecting the calling transaction. This is declared with PRAGMA AUTONOMOUS_TRANSACTION and is primarily used for error logging, auditing, and any operation that must persist regardless of whether the main transaction succeeds or fails.

The Classic Use Case: Error Logging

CREATE OR REPLACE PROCEDURE log_error(
    p_error_code    IN NUMBER,
    p_error_message IN VARCHAR2,
    p_context       IN VARCHAR2 DEFAULT NULL
)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO error_log (error_code, error_message, context,
                           logged_by, logged_at)
    VALUES (p_error_code, p_error_message, p_context,
            SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSTIMESTAMP);
    COMMIT;
END;
/

-- Usage in an exception handler
BEGIN
    process_complex_order(p_order_id);
EXCEPTION
    WHEN OTHERS THEN
        log_error(SQLCODE, SQLERRM, 'process_complex_order: ' || p_order_id);
        RAISE;  -- Re-raise after logging
END;

Without PRAGMA AUTONOMOUS_TRANSACTION, the log_error insert would be rolled back along with the failed transaction, and you would lose the error record.

Audit Trail Logging

CREATE OR REPLACE PROCEDURE log_audit(
    p_action    IN VARCHAR2,
    p_table     IN VARCHAR2,
    p_record_id IN NUMBER,
    p_details   IN VARCHAR2 DEFAULT NULL
)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO audit_log (action, table_name, record_id, details,
                           performed_by, performed_at)
    VALUES (p_action, p_table, p_record_id, p_details,
            COALESCE(SYS_CONTEXT('APEX$SESSION','APP_USER'),
                     SYS_CONTEXT('USERENV','SESSION_USER')),
            SYSTIMESTAMP);
    COMMIT;
END;

Dangers and Anti-Patterns

Autonomous transactions are frequently misused. The most common anti-pattern is using them to work around mutating table errors in triggers. This hides the architectural problem rather than solving it and can lead to data inconsistencies because the autonomous transaction commits data that the main transaction might roll back.

Never use autonomous transactions for DML that is logically part of the main transaction. If process A inserts an order and process B (autonomous) inserts order items, a rollback of A will leave orphaned items from B. The ONLY safe use cases are operations that must persist regardless of the main transaction’s outcome: logging, auditing, and sequence-like counters.

Rules for Safe Use

Always COMMIT or ROLLBACK inside the autonomous transaction. Keep autonomous transactions as short as possible. Never read data written by the calling transaction (it is not yet committed and will not be visible). Never modify data that the calling transaction also modifies (this can cause deadlocks). Document why the autonomous transaction is necessary.