PRAGMA AUTONOMOUS_TRANSACTION in Oracle: Examples and Pitfalls

PRAGMA AUTONOMOUS_TRANSACTION tells Oracle to run a procedure, function, trigger or top-level anonymous block in its own separate transaction. That routine can COMMIT or ROLLBACK its own work without committing or rolling back the work of the code that called it. The classic use is an error log: the business change rolls back, but the log row stays.

This tutorial shows how the pragma works, with runnable examples for a logging procedure, an audit trigger and a function called from a query. It also covers the errors you will hit (ORA-06519, ORA-14551, ORA-00060) and the cases where you should not use an autonomous transaction at all.

How an autonomous transaction works

Normally, everything your session does between two commits is one transaction. If a procedure you call commits, it commits everything, including the half-finished work of the caller. That is usually not what you want.

An autonomous transaction is different. When execution enters the body of a routine marked with PRAGMA AUTONOMOUS_TRANSACTION, Oracle suspends the main transaction and starts a new, independent one. When the routine returns, the main transaction resumes exactly where it was. The two transactions share no locks and have no commit dependency on each other.

The Oracle documentation lists the differences from a normal (nested) call:

Where you can use the pragma

According to the PL/SQL Language Reference (19c), the pragma can mark these routines:

• A schema-level (not nested) anonymous PL/SQL block
• A standalone procedure or function, a package subprogram, or a nested subprogram
• A method of an object type (ADT)
• A simple (noncompound) trigger

You cannot mark a whole package as autonomous. You put the pragma in each subprogram that needs it. The pragma can appear anywhere in the declarative section, but Oracle recommends putting it first so readers see it immediately.

Sample setup

We will use a small orders table and an error_log table. Run this in a test schema to follow along.

CREATE TABLE orders (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER       NOT NULL,
  amount       NUMBER(10,2) NOT NULL,
  status       VARCHAR2(20) NOT NULL
);

CREATE TABLE error_log (
  log_id     NUMBER GENERATED ALWAYS AS IDENTITY,
  logged_at  TIMESTAMP DEFAULT SYSTIMESTAMP,
  source     VARCHAR2(60),
  message    VARCHAR2(4000)
);

INSERT INTO orders VALUES (101, 1, 2500.00, 'NEW');
INSERT INTO orders VALUES (102, 2,  800.00, 'NEW');
INSERT INTO orders VALUES (103, 1, 1200.00, 'SHIPPED');
COMMIT;

Identity columns need Oracle 12c or later. On 11g, use a sequence for log_id instead.

Example 1: an error log that survives a rollback

Step 1: see the problem with a normal procedure

Here is a logging procedure without the pragma, and a block that updates an order, hits an error, logs it and rolls back.

CREATE OR REPLACE PROCEDURE log_error_plain (
  p_source  IN VARCHAR2,
  p_message IN VARCHAR2
) AS
BEGIN
  INSERT INTO error_log (source, message)
  VALUES (p_source, p_message);
END;
/

BEGIN
  UPDATE orders SET status = 'PAID' WHERE order_id = 101;
  RAISE_APPLICATION_ERROR(-20001, 'Payment gateway timeout');
EXCEPTION
  WHEN OTHERS THEN
    log_error_plain('pay_order', SQLERRM);
    ROLLBACK;
END;
/

SELECT COUNT(*) FROM error_log;

  COUNT(*)
----------
         0

The log row is gone. The INSERT into error_log was part of the same transaction as the UPDATE, so the ROLLBACK removed both. Adding a COMMIT inside log_error_plain would be worse: it would also commit the half-done UPDATE on order 101.

Step 2: make the logger autonomous

CREATE OR REPLACE PROCEDURE log_error (
  p_source  IN VARCHAR2,
  p_message IN VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (source, message)
  VALUES (p_source, p_message);
  COMMIT;  -- commits only this INSERT
END;
/

Now run the same failing block, calling the new procedure:

BEGIN
  UPDATE orders SET status = 'PAID' WHERE order_id = 101;
  RAISE_APPLICATION_ERROR(-20001, 'Payment gateway timeout');
EXCEPTION
  WHEN OTHERS THEN
    log_error('pay_order', SQLERRM);
    ROLLBACK;
END;
/

SELECT source, message FROM error_log;
SELECT order_id, status FROM orders WHERE order_id = 101;

SOURCE      MESSAGE
----------- ------------------------------------
pay_order   ORA-20001: Payment gateway timeout

  ORDER_ID STATUS
---------- --------------------
       101 NEW

This is exactly the result you want. The error is recorded and committed, while the UPDATE on order 101 was rolled back and its status is still NEW. The COMMIT inside log_error touched only the log row because it ran in its own transaction.

Example 2: an audit trigger that logs even rolled-back changes

A normal trigger cannot run COMMIT or ROLLBACK. The Oracle docs state that a trigger must be autonomous to run transaction control (TCL) or DDL statements, and DDL must go through native dynamic SQL (EXECUTE IMMEDIATE).

A common use is to record every attempted change, including ones the application later rolls back. That is useful when you are investigating who tried to change sensitive data.

CREATE TABLE order_audit (
  order_id    NUMBER,
  old_amount  NUMBER(10,2),
  new_amount  NUMBER(10,2),
  changed_by  VARCHAR2(128),
  changed_at  DATE
);

CREATE OR REPLACE TRIGGER trg_orders_amount_audit
  BEFORE UPDATE OF amount ON orders
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO order_audit
  VALUES (:OLD.order_id, :OLD.amount, :NEW.amount, USER, SYSDATE);
  COMMIT;
END;
/

Now commit one change and roll back another:

UPDATE orders SET amount = 2600 WHERE order_id = 101;
COMMIT;

UPDATE orders SET amount = 50 WHERE order_id = 102;
ROLLBACK;

SELECT order_id, old_amount, new_amount FROM order_audit;
SELECT order_id, amount FROM orders WHERE order_id IN (101, 102);

  ORDER_ID OLD_AMOUNT NEW_AMOUNT
---------- ---------- ----------
       101       2500       2600
       102        800         50

  ORDER_ID     AMOUNT
---------- ----------
       101       2600
       102        800

Both attempts appear in order_audit, but order 102 still has its original amount of 800. The audit table tells you someone tried to change it to 50, even though that change never became permanent.

Example 3: calling a function that writes data from a SELECT

Oracle stops a function from changing data when you call it inside a query. Here is a function that logs and returns a value:

CREATE OR REPLACE FUNCTION tag_order_plain (p_order_id NUMBER)
  RETURN VARCHAR2
AS
BEGIN
  INSERT INTO error_log (source, message)
  VALUES ('report', 'Viewed order ' || p_order_id);
  RETURN 'ORDER-' || p_order_id;
END;
/

SELECT tag_order_plain(order_id) FROM orders;

ORA-14551: cannot perform a DML operation inside a query

The same function with the pragma and a COMMIT runs, because the insert happens in a separate transaction and does not affect the query's read consistency:

CREATE OR REPLACE FUNCTION tag_order (p_order_id NUMBER)
  RETURN VARCHAR2
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (source, message)
  VALUES ('report', 'Viewed order ' || p_order_id);
  COMMIT;
  RETURN 'ORDER-' || p_order_id;
END;
/

SELECT order_id, tag_order(order_id) AS tag
  FROM orders
 ORDER BY order_id;

  ORDER_ID TAG
---------- ----------------
       101 ORDER-101
       102 ORDER-102
       103 ORDER-103

Be careful with this pattern. The function runs once per row the query evaluates (and the optimizer decides how often that is), and every call is a separate commit. On a large result set that means many small transactions and a lot of redo. It is fine for debugging, not for production reports.

What the autonomous transaction can and cannot see

Because it is a separate transaction, an autonomous routine does not see the caller's uncommitted changes. This surprises many developers, so it is worth a test:

CREATE OR REPLACE PROCEDURE count_new_orders AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO l_cnt FROM orders WHERE status = 'NEW';
  DBMS_OUTPUT.PUT_LINE('Autonomous sees NEW: ' || l_cnt);
  COMMIT;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_cnt NUMBER;
BEGIN
  UPDATE orders SET status = 'NEW' WHERE order_id = 103;
  SELECT COUNT(*) INTO l_cnt FROM orders WHERE status = 'NEW';
  DBMS_OUTPUT.PUT_LINE('Main sees NEW: ' || l_cnt);
  count_new_orders;
  ROLLBACK;
END;
/

Main sees NEW: 3
Autonomous sees NEW: 2

The main transaction sees its own uncommitted update on order 103, so it counts 3. The autonomous procedure sees only committed data, so it counts 2. The other direction works too: when an autonomous routine commits, the main transaction sees those changes after it resumes, as long as it uses the default READ COMMITTED isolation level. Under SERIALIZABLE, it does not.

Common mistakes

Forgetting to commit or roll back: ORA-06519

An autonomous routine must end its transaction before it returns. If you leave out the commit, Oracle rolls back the autonomous work and raises:

ORA-06519: active autonomous transaction detected and rolled back

Fix it by ending every path through the routine with COMMIT or ROLLBACK, including the exception handler. A routine that only reads data and starts no transaction does not need one, but adding COMMIT after DML is the rule to remember.

Updating a row the caller has locked: deadlock

The autonomous transaction does not share the caller's locks. If the caller has updated a row and the autonomous routine tries to update the same row, the autonomous transaction waits for a lock held by a transaction that is suspended until it finishes. It can never get that lock.

CREATE OR REPLACE PROCEDURE mark_order_checked (p_id NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE orders SET status = 'CHECKED' WHERE order_id = p_id;
  COMMIT;
END;
/

BEGIN
  UPDATE orders SET status = 'PAID' WHERE order_id = 101;
  mark_order_checked(101);   -- same row, locked by the caller
END;
/

ORA-00060: deadlock detected while waiting for resource

Oracle detects the deadlock and raises the error inside the autonomous transaction. The fix is design, not tuning: never let an autonomous routine touch rows the calling transaction has changed. If the work belongs with the caller's change, it should be part of the caller's transaction.

Using it to hide the mutating table error

Developers sometimes add the pragma to a row-level trigger to get around ORA-04091: table is mutating. The error goes away, but the trigger now queries the table as a separate transaction. It does not see the rows changed by the statement that fired it, so any check or total it calculates can be wrong. Use a compound trigger or move the logic into the calling procedure instead.

Committing business data in an autonomous routine

If an autonomous procedure inserts an order line and commits, and the caller then fails and rolls back the order header, you are left with an orphan line. Autonomous transactions break the all-or-nothing rule on purpose. Use them only for data that must survive independently, such as logs, audit attempts and retry counters.

Trying to make a nested block or a whole package autonomous

The pragma is allowed only in a top-level anonymous block, not in a DECLARE ... BEGIN ... END block nested inside another block. It also cannot be placed in a package specification to cover all subprograms. Put it in the declaration section of each subprogram that needs it.

Best practices

• Keep autonomous routines small and focused: one insert and one COMMIT is the ideal logging procedure.
• Handle errors inside the routine. A logger that raises an exception can hide the original error, so consider a WHEN OTHERS handler that rolls back and stays quiet, but only in the logger itself.
• Never update rows the caller may have locked.
• Remember each autonomous call is a real commit, with its own redo and log file sync wait. Calling one inside a loop over many rows is slow. Collect messages and log once where you can.
• The TRANSACTIONS initialization parameter limits concurrent transactions, and each autonomous transaction counts in addition to the main one. This rarely matters, but keep it in mind for deep call chains.
• Name and document autonomous routines clearly so other developers know a commit happens inside.

Frequently asked questions

Can we use COMMIT inside a trigger in Oracle?

Only in an autonomous trigger. A normal trigger that runs COMMIT fails at run time. With PRAGMA AUTONOMOUS_TRANSACTION in the trigger's declaration section, the commit applies only to the trigger's own work, not to the statement that fired it.

Can I use PRAGMA AUTONOMOUS_TRANSACTION in a function?

Yes. Standalone functions, package functions and nested functions can all be autonomous. This is also the only way to call a function that performs DML from a SELECT statement without getting ORA-14551.

What is the difference between a nested transaction and an autonomous transaction?

A nested (normal) call is part of the caller's transaction: it shares locks, sees uncommitted changes and is rolled back with the caller. An autonomous transaction is independent: it has its own locks, sees only committed data, and its committed changes stay even if the caller rolls back.

Is COMMIT mandatory in an autonomous transaction?

If the routine runs any DML, yes. You must end it with COMMIT or ROLLBACK before returning, or Oracle raises ORA-06519 and rolls the work back.

Does PostgreSQL have PRAGMA AUTONOMOUS_TRANSACTION?

No. PostgreSQL has no built-in autonomous transactions. A common workaround is to run the logging statement over a separate connection, for example with the dblink extension. If you migrate Oracle code, plan for this early.

Summary

• PRAGMA AUTONOMOUS_TRANSACTION runs a routine in its own independent transaction.
• Its COMMIT and ROLLBACK affect only its own work, and its committed changes survive a caller rollback.
• Use it for error logs, audit of attempted changes and debug output from queries.
• Always end the routine with COMMIT or ROLLBACK to avoid ORA-06519.
• Never touch rows the caller has locked, or you get ORA-00060.
• It does not see the caller's uncommitted changes, so do not use it to dodge the mutating table error.
• Keep business data in the caller's transaction.