PolarDB: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, 'John Doe', 'Engineer');
   DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
       INSERT INTO empauditlog VALUES (SYSDATE, USER, 'Added employee(s)');
       COMMIT;
   END;
   ROLLBACK;    

3. 親トランザクションがロールバックされたため、emp テーブルへのデータ挿入は元に戻され、データは永続化されません。ただし、自律型トランザクションは監査ログを empauditlog テーブルに個別に書き込んでコミットしたため、親トランザクションのロールバックの影響を受けません。

   -- emp テーブルをクエリします。メインのトランザクションがロールバックされたため、空のはずです。
   SELECT * FROM emp WHERE emp_id = 101;
   -- empauditlog テーブルをクエリします。自律型トランザクションがコミットされたことを確認します。これにより、1 つの監査レコードが返されるはずです。
   SELECT * FROM empauditlog;

自律型ストアドプロシージャを使用して失敗した試行をログに記録する

このシナリオでは、在庫が十分な場合にシステムが注文を作成します。在庫が不十分な場合、システムは注文の作成をロールバックしますが、ユーザーの試行のログは記録します。

1. 3 つのテーブルを作成します: 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. 自律型トランザクションを使用してストアドプロシージャを作成し、親トランザクションがロールバックされた場合でも operation_log が永続化されるようにします。

   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, '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. 結果を確認します: 親トランザクションがロールバックされたため、orders テーブルは空で、inventory は変更されていません。ただし、自律型トランザクションがコミットされたため、operation_log テーブルにはレコードが含まれています。

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

接続枯渇のリスク

各々の自律型トランザクションは、個別のデータベース接続を消費します。高同時実行シナリオでは、自律型トランザクションを過度に使用すると、データベースの max_connections 制限をすぐに使い果たし、新しい接続リクエストが失敗してサービスの中断につながる可能性があります。

• リスクシナリオ: ループ内または頻繁に呼び出される関数内で自律型トランザクションを使用する。

• ネストのリスク: 自律型トランザクションはネストできます。ネストの各レベルで追加の接続が消費されます。たとえば、3 つのレベルのネストされた自律型トランザクションを持つセッションは、一時的に 4 つの接続を使用します: 1 つはメインセッション用、3 つは自律型トランザクション用です。

• キャパシティプランニング: 自律型トランザクションを使用する前に、それらがデータベース接続に与える追加の需要を評価してください。必要に応じて max_connections パラメーターを調整します。ストレステストを実行してください。

推奨プラクティス

• 明示的なコミットまたはロールバック: 自律型トランザクションブロックの最後に COMMIT または ROLLBACK を使用して、その結果を明示的に定義します。

• 短く保つ: 自律型トランザクションは短く、単一のログエントリの挿入など、高速に実行される操作のみを含む必要があります。複雑で時間のかかるクエリの実行は避けてください。

• 特定のシナリオでの使用: 監査、ロギング、統計の更新など、メインのトランザクションロジックから独立した補助的なタスクにのみ使用します。

PolarDB for PostgreSQL (Oracle 互換) における自律型トランザクションの実装は、Oracle Database と比較して重要な動作の違いがあります。この違いは、データベースの移行中に潜在的なリスクをもたらす可能性があります。

"sorry, too many clients already" エラーのトラブルシューティング方法

このエラーは、アクティブなデータベース接続の数が max_connections 制限に達したことを示します。自律型トランザクションが原因であると疑われる場合は、次の手順に従ってください。

1. 現在の接続数と設定されている max_connections 制限を確認します。

   -- 接続の総数を表示
   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 を設定して、長時間実行されるクエリを自動的に終了させ、接続が無期限に開かれたままになるのを防ぎます。