Deep Dive — PRAGMA AUTONOMOUS_TRANSACTION

A second transaction inside the same session: it commits on its own, it cannot see the first one's uncommitted work, and it will happily deadlock against it. Perfect for a log. Wrong for almost everything else.

Used in
Chapter 7 — Exceptions
Appendix entry
PRAGMA AUTONOMOUS_TRANSACTION
Related
COMMIT EXCEPTION FORMAT_ERROR_BACKTRACE SAVEPOINT

A subprogram declared with PRAGMA AUTONOMOUS_TRANSACTION runs in its own transaction, independent of the caller's: its COMMIT/ROLLBACK affect only its work; the caller's transaction is suspended while it runs and resumes unchanged afterwards. From the database's point of view it is a separate transaction that happens to share a session.

Legitimate uses:

  • Error / audit logging that must survive the caller's rollback.
  • Progress / heartbeat records written mid-job.
  • Sequence-like counters where the increment must not wait for the outer commit (rare; sequences are better).

Not for: business logic that "needs to commit part-way" (that is a design problem), working around mutating-table errors in triggers (that is a design problem too), or reading the caller's uncommitted changes (it cannot).

SQL 
CREATE [OR REPLACE] PROCEDURE <name> (...) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ...
  COMMIT;         -- or ROLLBACK: every path out must end the transaction, else ORA-06519
END;

Allowed in: top-level anonymous blocks, standalone and packaged subprograms (including nested subprograms), object-type methods, triggers. Not in nested blocks (BEGIN … END inside a subprogram).

Rules:

  • Must COMMIT or ROLLBACK before returning, on every path including exceptions — ORA-06519: active autonomous transaction detected and rolled back otherwise.
  • Sees only committed data — including the caller's committed work, but never its pending changes.
  • Has its own savepoints; the caller's savepoints are invisible to it.
  • Shares the session: package variables, DBMS_OUTPUT, NLS settings, SYS_CONTEXT.
  • Counts as an open transaction toward the TRANSACTIONS parameter while active; nesting depth is limited by it.

SQL 
CREATE OR REPLACE PROCEDURE log_error (
  p_ctx VARCHAR2, p_code NUMBER, p_msg VARCHAR2, p_backtrace VARCHAR2 DEFAULT NULL) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (logged_at, ctx, err_code, err_msg, backtrace)
  VALUES (SYSTIMESTAMP, p_ctx, p_code, p_msg, p_backtrace);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;       -- never let logging kill the job; and never leave the txn open
END;
/
SQL 
-- caller
BEGIN
  UPDATE orders SET status = 'PROCESSED' WHERE order_id = 1001;
  process_order(1001);                        -- raises
EXCEPTION
  WHEN OTHERS THEN
    log_error('process_order 1001', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    ROLLBACK;                                 -- the UPDATE is gone; the log row stays
    RAISE;
END;

SQL 
CREATE OR REPLACE PROCEDURE heartbeat (p_job VARCHAR2, p_done NUMBER, p_total NUMBER) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  MERGE INTO job_progress j
  USING (SELECT p_job AS job_name FROM dual) s ON (j.job_name = s.job_name)
  WHEN MATCHED THEN UPDATE SET rows_done = p_done, rows_total = p_total, updated_at = SYSTIMESTAMP
  WHEN NOT MATCHED THEN INSERT (job_name, rows_done, rows_total, updated_at)
                        VALUES (p_job, p_done, p_total, SYSTIMESTAMP);
  COMMIT WRITE NOWAIT BATCH;      -- a lost heartbeat is fine; a 1 ms wait per batch is not
END;
/
-- in the batch loop, once per batch (not per row):
heartbeat('reprice', l_done, l_total);

Another session can SELECT * FROM job_progress while the job's own transaction is still open. See also DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS (DBMS_APPLICATION_INFO) for the no-table version.

SQL 
CREATE OR REPLACE FUNCTION count_new_autonomous RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  n NUMBER;
BEGIN
  SELECT COUNT(*) INTO n FROM orders WHERE status = 'NEW';
  COMMIT;             -- required even for a read-only autonomous transaction
  RETURN n;
END;
/
DECLARE
  before_n NUMBER; after_n NUMBER;
BEGIN
  before_n := count_new_autonomous;
  UPDATE orders SET status = 'NEW' WHERE status = 'HOLD';      -- say 5,000 rows, uncommitted
  after_n := count_new_autonomous;
  DBMS_OUTPUT.PUT_LINE(before_n || ' -> ' || after_n);         -- same number: the 5,000 are invisible
  ROLLBACK;
END;

Anyone who uses an autonomous transaction to "check" something the caller just did gets the pre-change answer.

SQL 
BEGIN
  UPDATE orders SET status = 'X' WHERE order_id = 1001;        -- main txn locks row 1001
  audit_status_change(1001);                                    -- autonomous: tries to UPDATE orders 1001
END;
-- ORA-00060: deadlock detected while waiting for resource  (raised inside the autonomous txn)

The autonomous transaction waits for a lock held by the main transaction, which is suspended waiting for the autonomous one to return. Oracle detects it and raises ORA-00060 in the autonomous transaction. Rule: an autonomous transaction must not touch rows the caller may have locked — log tables only, and only inserts.

SQL 
CREATE OR REPLACE TRIGGER orders_status_audit
  AFTER UPDATE OF status ON orders
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO status_audit (order_id, old_status, new_status, changed_at, changed_by)
  VALUES (:OLD.order_id, :OLD.status, :NEW.status, SYSTIMESTAMP, SYS_CONTEXT('USERENV', 'SESSION_USER'));
  COMMIT;
END;
/

Deliberate: the audit row says "someone tried", whether or not the change survived. For an audit that must reflect only committed changes, use a normal trigger (no pragma) — its insert rolls back with the statement. Note the cost: on a bulk update of 40M rows this is 40M autonomous commits. Audit at batch level, or use a non-autonomous trigger, for bulk jobs.

SQL 
-- anti-pattern
PROCEDURE settle_order (p_id NUMBER) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE orders SET status = 'SETTLED' WHERE order_id = p_id;
  INSERT INTO ledger (...) VALUES (...);
  COMMIT;              -- "so the settlement is safe even if the caller fails later"
END;

Now the settlement can exist without the caller's shipment record, the caller's rollback cannot undo it, and it deadlocks if the caller touched the order first. Transaction boundaries belong to the job, not to the procedure — COMMIT §4.

  • A commit per call. Every autonomous procedure ends with a COMMIT — a log file sync wait each time (COMMIT). 3M errors logged one by one = 3M commits. When errors are the majority, collect them in a collection and write them in one autonomous FORALL per batch. COMMIT WRITE NOWAIT BATCH is acceptable for logs that may be lost in a crash.
  • Row-level autonomous triggers on bulk DML (3e): the per-row commit turns a 2-minute FORALL into an hour.
  • Undo and locks of the autonomous transaction are separate — and if it waits on a lock, the whole job waits with it (3d).
  • Parallel DML and autonomous transactions do not mix well: a parallel query slave invoking an autonomous transaction can fail with ORA-12839/ORA-14551-family errors. Keep them out of functions called from parallel SQL.

  1. ORA-06519 — a path without COMMIT/ROLLBACK, usually the exception path. Put ROLLBACK in the handler.
  2. Reading the caller's pending changes — impossible (3c).
  3. Updating rows the caller locked — ORA-00060 (3d).
  4. Using it for business logic to "make the commit safe" — it makes the data inconsistent (3f).
  5. The pragma in a nested BEGIN … END block — not allowed; only in subprograms, triggers, top-level anonymous blocks.
  6. A function with the pragma called from SQL — the function may not perform DML on tables read by the query (ORA-04091-style consistency problems); allowed but rarely wise.
  7. Forgetting that package state is shared — an autonomous procedure that modifies a package variable affects the caller immediately, transaction or not.