【PL/SQL】データ整合性を保証するトランザクション設計パターン

ビジネスルールを確実に守りつつ高いスループットを出すには、単にCOMMIT/ROLLBACKを呼ぶだけでは不十分で、要件に応じてトランザクションの粒度・ロック・再試行・冪等性などを設計する必要があります。ここではOracle/PLSQLで実務的に使われる設計パターンを、具体的なコード例とともに体系立てて解説します。

前提：Oracleの整合性モデルと基本
パターン1：制約ファースト（整合性の土台をDBに置く）
パターン2：ロックの明示化（SELECT FOR UPDATE / NOWAIT / SKIP LOCKED）
パターン3：楽観的同時実行（バージョン列でロストアップデート防止）
パターン4：部分ロールバック（SAVEPOINTで境界を刻む）
パターン5：バルクDML×ERRLOG×コミットポリシー（スループット最適化）
パターン6：トランザクション外ログ（AUTONOMOUS TRANSACTION）
パターン7：外部副作用の整合性（トランザクション・アウトボックス）
パターン8：順序整合の確保（親子一貫性とDEFERRED制約）
パターン9：シリアライズ必要箇所の限定（DBMS_LOCK/一意キー）
パターン10：再試行と冪等性（リトライ・バックオフ・重複防止キー）
パターン11：読み取り整合の固定（SET TRANSACTION READ ONLY / SERIALIZABLE）
アンチパターンと回避策
チェックリスト（投入前の最終確認）
まとめ

前提：Oracleの整合性モデルと基本

OracleはデフォルトでREAD COMMITTEDの一貫性読取を提供し、UNDOにより「読み取り一貫性」を担保します。書き込みの競合は行レベルロックで制御し、論理整合は制約（PK/UK/FK/CHK/NOT NULL）とトランザクション境界（COMMIT/ROLLBACK、SAVEPOINT）で保証します。設計の出発点は「整合性の大部分は制約と集合志向SQLで担保し、PL/SQLは補助的に扱う」ことです。

パターン1：制約ファースト（整合性の土台をDBに置く）

重複排除や必須値はアプリでチェックするのではなく、まずDB制約で落とすのが堅牢です。UPSERTもMERGE＋一意制約を前提に設計すると、二重挿入を自然に防げます。

-- 例：一意制約で二重登録を禁止
ALTER TABLE orders ADD CONSTRAINT uk_orders_ext UNIQUE (external_id);

-- 冪等なUPSERT。二重リクエストでも結果は一意に収束
MERGE INTO orders d
USING (SELECT :external_id ext_id, :cust_id cid, :amt amt FROM dual) s
   ON (d.external_id = s.ext_id)
 WHEN MATCHED THEN UPDATE SET d.amount = s.amt, d.updated_at = SYSTIMESTAMP
 WHEN NOT MATCHED THEN INSERT (external_id, cust_id, amount, created_at)
                     VALUES (s.ext_id, s.cid, s.amt, SYSTIMESTAMP);

パターン2：ロックの明示化（SELECT FOR UPDATE / NOWAIT / SKIP LOCKED）

同一資源を複数ワーカーで並列処理する場合、対象レコードを明示的にロックし、競合時の待機戦略を決めます。NOWAITは即失敗、WAIT nは期限付き待機、SKIP LOCKEDはロック中の行を飛ばして前進します。

-- 待たずに占有（行がロック中なら即例外：ORA-00054）
SELECT id FROM task_queue WHERE status='READY'
  FOR UPDATE NOWAIT;

-- 並列ワーカー向け：ロック済みを飛ばす（空振り時は後で再スキャン）
SELECT id, payload FROM task_queue
 WHERE status='READY'
  FOR UPDATE SKIP LOCKED;

ロック粒度は最小限にし、クリティカルセクションを短く保つのが性能面の要点です。

パターン3：楽観的同時実行（バージョン列でロストアップデート防止）

更新頻度が高くないが競合は起こり得る、という場面ではバージョン列（行バージョン/タイムスタンプ）で衝突検知を行い、失敗時は再読込→再試行の流れにします。

-- バージョン列（数値カウンタ）を追加
ALTER TABLE customers ADD (row_ver NUMBER DEFAULT 0 NOT NULL);

-- 更新時にバージョンを一致条件に含め、成功時にインクリメント
UPDATE customers
   SET email = :new_email,
       row_ver = row_ver + 1
 WHERE id = :id
   AND row_ver = :expected_ver;

IF SQL%ROWCOUNT = 0 THEN
  -- 他セッションに更新された（楽観ロック失敗）
  RAISE_APPLICATION_ERROR(-20001, 'conflict, retry');
END IF;

パターン4：部分ロールバック（SAVEPOINTで境界を刻む）

長い一括処理で一件の失敗が全体を巻き戻すのは非効率です。SAVEPOINTで小さな単位に区切り、回復可能な失敗は部分ロールバックでリカバリします。

FOR r IN (SELECT * FROM staging_data ORDER BY id) LOOP
  SAVEPOINT sp_row;
  BEGIN
    -- 行ごとの検証と適用
    INSERT INTO target(...) VALUES (...);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO sp_row; -- その行だけ巻き戻す
      INSERT INTO err_log(row_id, msg, at) VALUES (r.id, SQLERRM, SYSTIMESTAMP);
  END;
END LOOP;
COMMIT;

パターン5：バルクDML×ERRLOG×コミットポリシー（スループット最適化）

大量データは集合志向で一気に処理し、失敗行はDBMS_ERRLOGで退避、コミットはチャンク単位に行うと安全と速度のバランスが取れます。

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('TARGET'); -- 初回のみ
  INSERT /*+ APPEND */ INTO target(cols...)
  SELECT ... FROM staging
  LOG ERRORS INTO err$_target('LOAD_202510') REJECT LIMIT UNLIMITED;
  COMMIT;
END;
/

チャンク処理が必要なら、キー範囲やROWNUMで分割し、各チャンクでCOMMIT。障害時の再実行時間を短縮できます。

パターン6：トランザクション外ログ（AUTONOMOUS TRANSACTION）

監査ログは本流と同一トランザクションに載せると大量UNDOやロールバック時の消失が起こり得ます。自治トランザクションで確実に吐き出します（ただし乱用禁止）。

CREATE OR REPLACE PROCEDURE audit_log(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_table(msg, at) VALUES (p_msg, SYSTIMESTAMP);
  COMMIT; -- 本流と独立して確定
END;
/

本流の成否に依らず証跡を残したい時に限定して使います。

パターン7：外部副作用の整合性（トランザクション・アウトボックス）

メール送信や外部API呼び出しなどDB外の副作用は、DBのACID外にあります。DMLと同一トランザクションで「アウトボックス」表にイベントを記録し、ワーカーが別トランザクションで確実配送します。重複送信は外部側の冪等キーで抑制します。

-- 本流トランザクションで発生イベントを記録
INSERT INTO outbox(event_key, type, payload, created_at)
VALUES (:key, 'ORDER_CREATED', :json, SYSTIMESTAMP);

-- 別プロセス/ジョブが未送信を取り出して送信、成功したら確定的にフラグ更新
SELECT * FROM outbox WHERE sent_at IS NULL FOR UPDATE SKIP LOCKED;
-- 送信成功後に sent_at を更新してCOMMIT

パターン8：順序整合の確保（親子一貫性とDEFERRED制約）

ロード順に依存する参照整合は、原則「親→子」の順で書くべきですが、大量ロードではDEFERRABLE INITIALLY DEFERREDなFKにしてトランザクション終端で一括検証する選択もあります。

-- 参照整合をトランザクション終端で検証（大規模ロード向け）
ALTER TABLE order_items
  ADD CONSTRAINT fk_items_orders
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
  DEFERRABLE INITIALLY DEFERRED;

-- ロード中は一時的に検証を遅延、COMMIT時にまとめてチェック

パターン9：シリアライズ必要箇所の限定（DBMS_LOCK/一意キー）

「ジョブを同時に一つだけ」など行では表現しにくい排他は、DBMS_LOCKで短時間の専用ロックを取得するか、制御テーブル＋一意制約で表現します。

DECLARE
  h VARCHAR2(128); r PLS_INTEGER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('JOB:DAILY_AGG', h);
  r := DBMS_LOCK.REQUEST(h, DBMS_LOCK.X_MODE, 0, TRUE); -- 0=待たずに即判定
  IF r <> 0 THEN RAISE_APPLICATION_ERROR(-20000,'already running'); END IF;
  -- クリティカルセクション
  NULL;
END;
/

パターン10：再試行と冪等性（リトライ・バックオフ・重複防止キー）

一時的なロック競合やネットワーク障害はリトライで解消しますが、同じ処理を繰り返しても結果が変わらないよう冪等設計が前提です。外部からのリクエストには重複防止キーを要求し、DB側は一意キーまたは履歴で吸収します。

-- リトライ例（擬似コード）
FOR i IN 1..3 LOOP
  BEGIN
    -- 競合しやすい区間
    UPDATE ...; COMMIT; EXIT;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE IN (-54, -60) THEN -- ロック待ち/デッドロック等
        DBMS_LOCK.SLEEP(POWER(2, i)); -- 指数バックオフ
      ELSE
        RAISE;
      END IF;
  END;
END LOOP;

パターン11：読み取り整合の固定（SET TRANSACTION READ ONLY / SERIALIZABLE）

レポートなどで「一貫したスナップショット」を求める場合、READ ONLYもしくはSERIALIZABLEで開始し、セッション内で安定したビューを得ます（更新は行えません／制限あり）。

SET TRANSACTION READ ONLY;
-- 以後のSELECTは開始時点の一貫性で固定
SELECT ... FROM ...;
COMMIT;

アンチパターンと回避策

「アプリ側で排他制御（フラグ列の手動管理）」「行ループ＋逐次コミット」「動的SQLの文字列結合」「ログを本流にべったり」「例外の握りつぶし」は典型的に不安定化します。排他はDBの機構に寄せ、集合志向で一括、動的SQLはバインド、ログは必要に応じ自治化、例外は発生点・原因・入力キーを必ず記録します。

チェックリスト（投入前の最終確認）

制約で守れるものは全てDB側で守っているか。ロック戦略（FOR UPDATE/WAIT/NOWAIT/SKIP LOCKED）は定義したか。再試行は冪等性と対で設計されているか。バルク処理のコミット粒度とERRLOGの回収動線はあるか。外部副作用はアウトボックス化されているか。監査・障害ログは自治で確定できるか。これらを満たして初めて、整合性とスループットを両立できます。

まとめ

データ整合性は「制約＋集合志向SQL」を土台に、「ロックの明示化」「楽観制御」「部分ロールバック」「バルク×ERRLOG」「自治ログ」「アウトボックス」「DEFERRED検証」「限定的シリアライズ」「冪等リトライ」「スナップショット読取」を状況に応じて組み合わせることで堅牢に保てます。要件ごとに最小限の仕組みを選び、クリティカルセクションを短く、境界を明確に刻む――それが高信頼・高性能なPL/SQLトランザクション設計の要諦です。