PRAGMA AUTONOMOUS_TRANSACTION

自治事务匿名块记录审计日志

此场景确保无论主业务操作最终是提交还是回滚，相关的审计日志都能被可靠地记录下来。

1. 创建员工表emp和审计日志表empauditlog。

   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. 开启一个事务，插入数据，然后回滚该事务。

   BEGIN;
   INSERT INTO emp (emp_id, emp_name, job) VALUES (101, '张三', '工程师');
   DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
       INSERT INTO empauditlog VALUES (SYSDATE, USER, 'Added employee(s)');
       COMMIT;
   END;
   ROLLBACK;    

3. 由于主事务执行了ROLLBACK，emp表中的数据插入操作已被撤销，数据未持久化。而empauditlog 表通过自治事务独立提交，审计日志成功写入，不受主事务回滚影响。

   -- 查询 emp 表，应为空，因为主事务已回滚
   SELECT * FROM emp WHERE emp_id = 101;
   -- 查询 empauditlog 表，应包含一条审计记录，因为是自治事务并已提交
   SELECT * FROM empauditlog;

自治事务存储过程记录失败日志

本场景用于实现：当库存充足时，正常创建订单；当库存不足时，回滚订单操作，但仍记录用户的“下单尝试”日志。

1. 建立 orders（订单）、inventory（库存）和 operation_log（操作日志）三张表。初始化库存为 5，当下单数量为 10 时，触发库存不足异常，并记录失败日志。

   -- 1. 订单表（主业务数据）
   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. 库存表
   CREATE TABLE inventory (
       product_id   NUMBER PRIMARY KEY,
       stock        NUMBER
   );
   
   -- 3. 操作日志表（用于自治事务写入）
   CREATE TABLE operation_log (
       log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       user_id      NUMBER,
       product_id   NUMBER,
       action       VARCHAR2(50),  -- 如 'ORDER_ATTEMPT'
       status       VARCHAR2(20),  -- 'SUCCESS' / 'FAILED'
       reason       VARCHAR2(200), -- 失败原因
       log_time     TIMESTAMP DEFAULT SYSDATE
   );
   
   -- 4. 初始化数据
   INSERT INTO inventory (product_id, stock) VALUES (1001, 5); -- 商品1001 只有5件

2. 创建支持自治事务的存储过程，确保在主事务回滚时仍能持久化操作日志。

   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;  -- 过程内部声明区
   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; -- 独立提交，不受主事务影响
   END;

3. 用户尝试下单，因库存不足失败，自治事务确保日志独立提交。

   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, '库存不足，订单创建失败');
       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. 验证结果：订单表为空，库存未变，因主事务已回滚；而操作日志中已有记录，得益于自治事务的成功提交。

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

连接消耗风险

每个自治事务在执行期间会消耗一个独立的数据库连接。 在高并发场景下，滥用自治事务可能迅速耗尽数据库的 max_connections，导致新的连接请求失败，从而引发服务中断。

• 风险场景：在循环中或高频调用的函数里使用自治事务。

• 嵌套风险：自治事务可以嵌套，每嵌套一层就会多占用一个连接。例如，一个嵌套了3层的自治事务会话会临时占用4个连接（1个主会话 + 3个自治事务）。

• 容量规划：使用前，请评估自治事务对数据库连接数的额外需求，并相应调整 max_connections 参数。建议进行压力测试。

推荐做法

• 显式提交或回滚：在自治事务代码块的末尾使用 COMMIT 或 ROLLBACK，以明确事务的结束方式。

• 保持简短：自治事务应只包含必要的、快速完成的操作（如 INSERT 一条日志），避免执行耗时长的复杂查询。

• 用于特定场景：仅用于审计、日志记录、更新统计信息等与主事务逻辑解耦的辅助性任务。

PolarDB PostgreSQL版（兼容Oracle） 的自治事务实现与 Oracle 数据库存在一个关键行为差异，这可能在数据库迁移时引入潜在风险。

如何排查 “sorry, too many clients already” 错误？

此错误表明数据库的活动连接数已达到 max_connections 上限。如果怀疑是自治事务引起的，可按以下步骤排查：

1. 检查当前连接数和最大连接数配置。

   -- 查看总连接数
   SELECT count(*) FROM pg_stat_activity;
   
   -- 查看当前配置的最大连接数
   SHOW max_connections;

2. 识别长时间运行的自治事务。使用以下查询找出执行时间超过5分钟的自治事务相关查询。

   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. 解决方案：

   • 优化代码：检查是否存在循环调用或长时间运行的自治事务，并进行优化。

   • 增加连接数：如果使用合理但连接仍然不足，评估后可适当增加 max_connections 的值。

   • 设置超时：为会话设置 statement_timeout，以自动终止长时间运行的查询，防止连接被永久占用。