PolarDB:PRAGMA AUTONOMOUS_TRANSACTION

Record audit logs with an autonomous anonymous block

This example shows how audit logs are preserved even when the parent transaction rolls back.

1. Create the emp table for employee records and the empauditlog table for audit entries.

   CREATE TABLE emp (
       emp_id   INT,
       emp_name VARCHAR(50),
       job      VARCHAR(50)
   );
   
   CREATE TABLE empauditlog (
       audit_date      DATE,
       audit_user      VARCHAR2(20),
       audit_desc      VARCHAR2(100)
   );

2. Start a transaction, insert an employee record, write an audit log in an autonomous block, then roll back the parent transaction.

   BEGIN;
   INSERT INTO emp (emp_id, emp_name, job) VALUES (101, 'John Doe', 'Engineer');
   DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
       INSERT INTO empauditlog VALUES (SYSDATE, USER, 'Added employee(s)');
       COMMIT;  -- Commits only the autonomous transaction
   END;
   ROLLBACK;  -- Rolls back only the parent transaction

3. Verify the results. The emp insert is rolled back, but the audit log entry is preserved.

   -- Returns no rows: the parent transaction was rolled back
   SELECT * FROM emp WHERE emp_id = 101;
   
   -- Returns one row: the autonomous transaction committed independently
   SELECT * FROM empauditlog;

Log failed order attempts with an autonomous stored procedure

This example creates an order if stock is sufficient. If stock runs out, the order is rolled back, but the failure is still logged.

1. Create the required tables and seed the inventory with 5 units.

   -- Order table
   CREATE TABLE orders (
       order_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       user_id      NUMBER,
       product_id   NUMBER,
       qty          NUMBER,
       order_time   TIMESTAMP DEFAULT SYSDATE
   );
   
   -- Inventory table
   CREATE TABLE inventory (
       product_id   NUMBER PRIMARY KEY,
       stock        NUMBER
   );
   
   -- Operation log (written by the autonomous transaction)
   CREATE TABLE operation_log (
       log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       user_id      NUMBER,
       product_id   NUMBER,
       action       VARCHAR2(50),  -- e.g., 'ORDER_ATTEMPT'
       status       VARCHAR2(20),  -- 'SUCCESS' or 'FAILED'
       reason       VARCHAR2(200),
       log_time     TIMESTAMP DEFAULT SYSDATE
   );
   
   -- Seed: product 1001 has 5 units in stock
   INSERT INTO inventory (product_id, stock) VALUES (1001, 5);

2. Create an autonomous stored procedure that writes to operation_log independently of the caller's transaction.

   CREATE OR REPLACE PROCEDURE log_order_attempt(
       p_user_id    IN NUMBER,
       p_product_id IN NUMBER,
       p_reason     IN VARCHAR2
   ) IS
       PRAGMA AUTONOMOUS_TRANSACTION;  -- Declared at the start of the declaration section
   BEGIN
       INSERT INTO operation_log (
           user_id, product_id, action, status, reason, log_time
       ) VALUES (
           p_user_id, p_product_id, 'ORDER_ATTEMPT', 'FAILED', p_reason, SYSDATE
       );
       COMMIT;  -- Commits independently; not affected by the caller's rollback
   END;

3. Attempt to order 10 units. Because stock is only 5, the order fails, but the autonomous transaction logs the attempt.

   DECLARE
       v_user_id     NUMBER := 123;
       v_product_id  NUMBER := 1001;
       v_order_qty   NUMBER := 10;
       v_stock       NUMBER;
   BEGIN
       SELECT stock INTO v_stock FROM inventory
       WHERE product_id = v_product_id FOR UPDATE;
   
       IF v_stock < v_order_qty THEN
           log_order_attempt(
               p_user_id    => v_user_id,
               p_product_id => v_product_id,
               p_reason     => 'Insufficient stock: need ' || v_order_qty || ', available ' || v_stock
           );
           RAISE_APPLICATION_ERROR(-20001, 'Insufficient stock, order creation failed');
       END IF;
   
       INSERT INTO orders (user_id, product_id, qty)
       VALUES (v_user_id, v_product_id, v_order_qty);
   
       UPDATE inventory SET stock = stock - v_order_qty
       WHERE product_id = v_product_id;
   
       COMMIT;
   
   EXCEPTION
       WHEN OTHERS THEN
           ROLLBACK;
           RAISE;
   END;

4. Verify the results. The order and inventory changes are rolled back, but the log entry is committed.

   SELECT * FROM orders;        -- Empty: parent transaction rolled back
   SELECT * FROM inventory;     -- Unchanged: stock still 5
   SELECT * FROM operation_log; -- One row: autonomous transaction committed

Avoid connection exhaustion

Each autonomous transaction consumes a separate database connection for its entire duration. In high-concurrency environments, this can exhaust the max_connections limit and prevent new connections from being established.

High-risk patterns:

• Calling autonomous transactions inside a loop

• Calling autonomous transactions from a frequently invoked function

Nesting risk: Autonomous transactions can be nested. Each level of nesting uses an additional connection. For example, three levels of nested autonomous transactions consume four connections in total—one for the main session and one per autonomous transaction.

Before deploying autonomous transactions in production:

• Estimate the additional connection demand.

• Adjust the max_connections parameter based on that estimate.

• Run stress tests to validate the configuration.

Keep autonomous transactions short

An autonomous transaction should complete quickly. Use it only for fast, simple operations such as inserting a single log entry. Avoid complex queries or long-running operations inside an autonomous block.

Use autonomous transactions only for auxiliary logic

Reserve autonomous transactions for tasks that must be independent of the main transaction outcome—auditing, failure logging, and statistics updates. Do not use them to implement core business logic.

Always commit or roll back explicitly

End every autonomous transaction block with COMMIT or ROLLBACK. Relying on implicit behavior leads to different outcomes on Oracle and PolarDB for PostgreSQL (Compatible with Oracle). See Compatibility notes.

How do I troubleshoot "sorry, too many clients already"?

This error means active connections have reached the max_connections limit. To check whether autonomous transactions are the cause:

1. Check the current connection count and the configured limit.

   -- Total active connections
   SELECT count(*) FROM pg_stat_activity;
   
   -- Configured limit
   SHOW max_connections;

2. Find long-running autonomous transactions (running longer than five minutes).

   SELECT pid, query_start, now() - query_start AS duration, query
   FROM pg_stat_activity
   WHERE state = 'active'
     AND query ILIKE '%PRAGMA AUTONOMOUS_TRANSACTION%'
     AND now() - query_start > interval '5 minutes';

3. Based on the results, take one or more of the following actions:

   • Optimize the code: Look for loops or frequently called functions that trigger autonomous transactions. Refactor to reduce call frequency.

   • Increase max_connections: If the current usage is legitimate but connections are still exhausted, raise max_connections after evaluating the system-wide memory impact.

   • Set a query timeout: Configure statement_timeout for the session to automatically terminate queries that run too long, preventing connections from being held open indefinitely.