PolarDB:PRAGMA AUTONOMOUS_TRANSACTION

Use an autonomous Anonymous Block to record audit logs

This scenario ensures that audit logs are saved, regardless of whether the main transaction is committed or rolled back.

1. Create the emp table for employee data and the empauditlog table for audit logs.

   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 data, and then roll back the 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;
   END;
   ROLLBACK;    

3. Because the parent transaction was rolled back, the data insertion into the emp table is undone, and the data is not persisted. However, the autonomous transaction wrote and committed the audit log to the empauditlog table independently and is not affected by the parent transaction's rollback.

   -- Query the emp table. It should be empty because the main transaction was rolled back.
   SELECT * FROM emp WHERE emp_id = 101;
   -- Query the empauditlog table. Verify that the autonomous transaction was committed. This should return one audit record.
   SELECT * FROM empauditlog;

Use an autonomous Stored Procedure to log failed attempts

In this scenario, the system creates an order if stock is sufficient. If stock is insufficient, the system rolls back the order creation but still records a log of the user's attempt.

1. Create three tables: orders (for order data), inventory, and operation_log (including failure logs). Initialize the stock to 5. Placing an order for 10 items triggers an insufficient stock exception and logs the failure.

   -- 1. Order table (main business data)
   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
   );
   
   -- 2. Inventory table
   CREATE TABLE inventory (
       product_id   NUMBER PRIMARY KEY,
       stock        NUMBER
   );
   
   -- 3. Operation log table (for autonomous transaction writes)
   CREATE TABLE operation_log (
       log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       user_id      NUMBER,
       product_id   NUMBER,
       action       VARCHAR2(50),  -- such as 'ORDER_ATTEMPT'
       status       VARCHAR2(20),  -- 'SUCCESS' / 'FAILED'
       reason       VARCHAR2(200), -- Failure reason
       log_time     TIMESTAMP DEFAULT SYSDATE
   );
   
   -- 4. Initialize data
   INSERT INTO inventory (product_id, stock) VALUES (1001, 5); -- Product 1001 has only 5 items in stock

2. Create a a Stored Procedure using an autonomous transaction to ensure the operation_log is persisted even when the parent transaction is rolled back.

   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;  -- Declaration section within the procedure
   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; -- Independent commit, not affected by the main transaction
   END;

3. A user attempts to place an order. It fails due to insufficient stock, but the autonomous transaction ensures the log is committed independently.

   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 orders table is empty and the inventory is unchanged because the parent transaction was rolled back. However, the operation_log table contains a record, because the autonomous transaction was committed.

   SELECT * FROM orders;
   SELECT * FROM inventory;
   SELECT * FROM operation_log;

Risk of connection exhaustion

Each autonomous transaction consumes a separate database connection. In high-concurrency scenarios, overusing autonomous transactions can quickly exhaust the database's max_connections limit, causing new connection requests to fail and leading to service disruptions.

• Risk scenario: Using autonomous transactions inside a loop or in a frequently called function.

• Nesting risk: Autonomous transactions can be nested. Each level of nesting consumes an additional connection. For example, a session with three levels of nested autonomous transactions temporarily uses four connections: one for the main session and three for the autonomous transactions.

• Capacity planning: Before you use autonomous transactions, evaluate the extra demand they will place on database connections. Adjust the max_connections parameter as needed. Perform stress tests.

Recommended practices

• Explicitly commit or roll back: Use COMMIT or ROLLBACK at the end of an autonomous transaction block to explicitly define its outcome.

• Keep them short: An autonomous transaction should be short and contain only fast-executing operations, such as inserting a single log entry. Avoid running complex, time-consuming queries.

• Use for specific scenarios: Use them only for auxiliary tasks that are independent of the main transaction logic, such as auditing, logging, or updating statistics.

The implementation of autonomous transactions in PolarDB for PostgreSQL (Compatible with Oracle) has a key behavioral difference compared to Oracle Database. This difference can introduce potential risks during database migration.

How to troubleshoot the "sorry, too many clients already" error?

This error indicates that the number of active database connections has reached the max_connections limit. If you suspect that autonomous transactions are the cause, follow these steps:

1. Check the current number of connections and the configured max_connections limit.

   -- View the total number of connections
   SELECT count(*) FROM pg_stat_activity;
   
   -- View the current maximum number of connections
   SHOW max_connections;

2. Identify long-running autonomous transactions. Use the following query to find queries related to autonomous transactions running for more 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. Solutions:

   • Optimize the code: Check for and optimize any loops or long-running operations that call autonomous transactions.

   • Increase the number of connections: If usage is reasonable but connections are still insufficient, consider increasing max_connections after evaluating the system-wide impact.

   • Set a timeout: Set statement_timeout for the session to automatically terminate long-running queries and prevent connections from being held open indefinitely.