【PL/SQL】AUTONOMOUS_TRANSACTIONで独立処理を実装する完全ガイド｜5ユースケース・デッドロック対策・ORA-06519・SAVEPOINT比較

-- 親トランザクションが更新中のデータは子から見えない
CREATE OR REPLACE PROCEDURE child_proc IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_balance NUMBER;
BEGIN
  -- 親がUPDATEしたが未COMMITの値は見えない
  SELECT balance INTO v_balance FROM accounts WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('child sees: ' || v_balance);  -- 親のUPDATE前の値
  COMMIT;
END;
/

DECLARE
  v_b NUMBER;
BEGIN
  -- 親が更新する（未COMMIT）
  UPDATE accounts SET balance = 9999 WHERE id = 1;
  SELECT balance INTO v_b FROM accounts WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('parent sees: ' || v_b);  -- 9999（自分のセッション内）

  -- 子を呼ぶ
  child_proc;   -- ← childは元の値を見る（READ COMMITTED）

  ROLLBACK;     -- 親はROLLBACK
END;
/

-- ログ表
CREATE TABLE audit_log(
  log_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  log_ts    TIMESTAMP DEFAULT SYSTIMESTAMP,
  who       VARCHAR2(64) DEFAULT USER,
  module    VARCHAR2(64),
  level_cd  VARCHAR2(10),  -- INFO/WARN/ERROR
  message   VARCHAR2(4000),
  context   CLOB
);

-- 監査ログAPIパッケージ
CREATE OR REPLACE PACKAGE pkg_audit AS
  PROCEDURE info (p_module VARCHAR2, p_msg VARCHAR2, p_ctx CLOB DEFAULT NULL);
  PROCEDURE warn (p_module VARCHAR2, p_msg VARCHAR2, p_ctx CLOB DEFAULT NULL);
  PROCEDURE error(p_module VARCHAR2, p_msg VARCHAR2, p_ctx CLOB DEFAULT NULL);
END pkg_audit;
/

CREATE OR REPLACE PACKAGE BODY pkg_audit AS

  -- 内部の汎用書き込み（独立トランザクション）
  PROCEDURE write_log(
    p_module VARCHAR2, p_level VARCHAR2,
    p_msg VARCHAR2, p_ctx CLOB
  ) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO audit_log(module, level_cd, message, context)
    VALUES(p_module, p_level, SUBSTR(p_msg, 1, 4000), p_ctx);
    COMMIT;   -- 親に影響せず確定
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;   -- ログ書き込み失敗は親に伝えず黙殺
  END;

  PROCEDURE info(p_module VARCHAR2, p_msg VARCHAR2, p_ctx CLOB) IS
  BEGIN write_log(p_module, 'INFO',  p_msg, p_ctx); END;

  PROCEDURE warn(p_module VARCHAR2, p_msg VARCHAR2, p_ctx CLOB) IS
  BEGIN write_log(p_module, 'WARN',  p_msg, p_ctx); END;

  PROCEDURE error(p_module VARCHAR2, p_msg VARCHAR2, p_ctx CLOB) IS
  BEGIN write_log(p_module, 'ERROR', p_msg, p_ctx); END;
END pkg_audit;
/

-- 利用例
BEGIN
  -- 業務処理（途中で失敗）
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  pkg_audit.info('transfer', 'started');
  -- 失敗発生
  RAISE_APPLICATION_ERROR(-20100, 'simulated');
EXCEPTION
  WHEN OTHERS THEN
    pkg_audit.error('transfer', SQLERRM,
      DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    ROLLBACK;
    -- → 業務UPDATEは取消し、ログだけ残る
END;
/

CREATE TABLE emp_audit(
  audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  emp_id   NUMBER, old_sal NUMBER, new_sal NUMBER,
  who      VARCHAR2(64), at_ts TIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_emp_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO emp_audit(emp_id, old_sal, new_sal, who, at_ts)
  VALUES(:OLD.id, :OLD.salary, :NEW.salary, USER, SYSTIMESTAMP);
  COMMIT;
END;
/

-- 注意: 親のUPDATE EMPLOYEESがROLLBACKされても
-- emp_auditには監査記録が残る（用途として正しいか業務要件を確認）

CREATE TABLE retry_counter(
  job_id    VARCHAR2(64) PRIMARY KEY,
  retry_cnt NUMBER DEFAULT 0 NOT NULL,
  last_at   TIMESTAMP
);

CREATE OR REPLACE PROCEDURE pkg_retry.bump(p_job_id VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- UPSERTで増分
  MERGE INTO retry_counter t
  USING (SELECT p_job_id AS job_id FROM dual) s
     ON (t.job_id = s.job_id)
  WHEN MATCHED THEN UPDATE SET
    t.retry_cnt = t.retry_cnt + 1,
    t.last_at   = SYSTIMESTAMP
  WHEN NOT MATCHED THEN INSERT(job_id, retry_cnt, last_at)
    VALUES(p_job_id, 1, SYSTIMESTAMP);
  COMMIT;
END;
/

-- 利用例：処理が失敗してもカウンタは確実に増える
BEGIN
  pkg_retry.bump('JOB_ABC');
  -- 業務処理
  pkg_xxx.do();
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;   -- 業務はROLLBACK
    RAISE;      -- だがカウンタはCOMMIT済み
END;
/

CREATE TABLE api_rate_limit(
  api_key   VARCHAR2(64),
  bucket_ts TIMESTAMP,
  call_cnt  NUMBER DEFAULT 0,
  PRIMARY KEY(api_key, bucket_ts)
);

CREATE OR REPLACE FUNCTION pkg_rate.check_and_record(
  p_api_key VARCHAR2,
  p_limit   NUMBER DEFAULT 100   -- 1分間に100回まで
) RETURN BOOLEAN AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_bucket TIMESTAMP := TRUNC(SYSTIMESTAMP, 'MI');
  v_cnt    NUMBER;
BEGIN
  -- 現在のバケットの件数を取得して+1
  MERGE INTO api_rate_limit t
  USING (SELECT p_api_key k, v_bucket b FROM dual) s
     ON (t.api_key = s.k AND t.bucket_ts = s.b)
  WHEN MATCHED THEN UPDATE SET t.call_cnt = t.call_cnt + 1
  WHEN NOT MATCHED THEN INSERT(api_key, bucket_ts, call_cnt)
    VALUES(s.k, s.b, 1);

  SELECT call_cnt INTO v_cnt
  FROM api_rate_limit
  WHERE api_key = p_api_key AND bucket_ts = v_bucket;

  COMMIT;
  RETURN v_cnt <= p_limit;
END;
/

CREATE TABLE event_queue(
  event_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occured_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  event_type VARCHAR2(64),
  payload    CLOB,
  status     VARCHAR2(10) DEFAULT 'PENDING'
);

CREATE OR REPLACE PROCEDURE pkg_event.publish(
  p_type IN VARCHAR2, p_payload IN CLOB
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO event_queue(event_type, payload)
  VALUES(p_type, p_payload);
  COMMIT;
END;
/

-- 利用例：処理結果に関わらず必ずイベントを記録
BEGIN
  pkg_event.publish('order.attempt',
    '{"order_id": 100, "user_id": 50}');
  -- 業務処理
  pkg_order.create_order(100, 50);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    pkg_event.publish('order.failed',
      '{"order_id": 100, "error": "' || SQLERRM || '"}');
    ROLLBACK;
    RAISE;
END;
/

-- ❌ デッドロック発生例
CREATE OR REPLACE PROCEDURE bad_logger(p_id NUMBER) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- 親が更新中の同じ表を子も更新→ロック待ち
  UPDATE orders SET status = 'LOG_RECORDED'
   WHERE order_id = p_id;
  COMMIT;
END;
/

DECLARE
BEGIN
  UPDATE orders SET status = 'PROCESSING' WHERE order_id = 100;
  bad_logger(100);   -- ← ここで永久ロック待ち（タイムアウトでORA-00060）
  COMMIT;
END;
/

-- ✅ 回避策1: 別の表に書く（推奨）
CREATE OR REPLACE PROCEDURE good_logger(p_id NUMBER, p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- 監査用の別テーブルに書く（親はordersをロック中だが
  -- audit_logは独立しているので衝突なし）
  INSERT INTO audit_log(module, message, context)
  VALUES('orders', p_msg, TO_CHAR(p_id));
  COMMIT;
END;
/

-- ✅ 回避策2: 同じ表を読むだけにする（書かない）
CREATE OR REPLACE PROCEDURE read_only_logger(p_id NUMBER) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_status VARCHAR2(20);
BEGIN
  -- 読み取り専用ならロック衝突なし（READ COMMITTED）
  -- ただし親の未COMMITの更新は見えない点に注意
  SELECT status INTO v_status FROM orders WHERE order_id = p_id;
  INSERT INTO audit_log(module, message)
  VALUES('orders', 'pre-status: ' || v_status);
  COMMIT;
END;
/

-- ❌ ORA-06519が発生する例
CREATE OR REPLACE PROCEDURE leaky_log(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log(message) VALUES(p_msg);
  -- COMMIT忘れ！
END;
/

BEGIN leaky_log('test'); END;
-- ORA-06519: アクティブな自律トランザクションが検出されました

-- ✅ 防止策1: 必ず終端で明示的COMMIT
CREATE OR REPLACE PROCEDURE safe_log(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log(message) VALUES(p_msg);
  COMMIT;
END;
/

-- ✅ 防止策2: 例外時もROLLBACKを必ず書く
CREATE OR REPLACE PROCEDURE robust_log(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log(message) VALUES(p_msg);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;     -- 例外時もきちんと閉じる
    -- ログAPI内の失敗は親に伝えない（黙殺）
END;
/

-- ✅ 防止策3: 標準テンプレートに沿って書く
CREATE OR REPLACE PROCEDURE template_log(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- 業務処理
  INSERT INTO audit_log(message) VALUES(p_msg);

  COMMIT;            -- 必須：成功パス
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;        -- 必須：失敗パス
    NULL;            -- ログ失敗は親に伝えない／RAISEするなら明示
END;
/

-- パターン1: テスト前後で対象表をクリーンアップ
CREATE OR REPLACE PROCEDURE test_audit_log AS
  v_cnt NUMBER;
BEGIN
  -- 準備：監査表をクリア
  DELETE FROM audit_log WHERE module = 'TEST_MODULE';
  COMMIT;

  -- テスト実行
  pkg_audit.info('TEST_MODULE', 'test message');

  -- 検証
  SELECT COUNT(*) INTO v_cnt
  FROM audit_log
  WHERE module = 'TEST_MODULE' AND level_cd = 'INFO';

  IF v_cnt != 1 THEN
    RAISE_APPLICATION_ERROR(-20999, 'audit log not recorded');
  END IF;

  -- 後始末：テストデータ削除
  DELETE FROM audit_log WHERE module = 'TEST_MODULE';
  COMMIT;
END;
/

-- パターン2: 専用テストスキーマ／テスト用接尾辞で隔離
-- AUTONOMOUS関数を本物のログ表ではなく
-- _TEST 接尾辞のテーブルに向ける設定で実行
-- → 設定値はSYS_CONTEXTやパッケージ定数で切替

-- パターン3: utPLSQLのbefore/afterでクリーンアップ
-- @BeforeEach: テスト前にDELETE+COMMITで初期状態へ
-- @AfterEach:  テスト後にDELETE+COMMITで後始末