大規模なPL/SQLシステムでは、例外設計が品質と運用負荷を大きく左右します。単にWHEN OTHERSで握ってログを残すだけでは、原因の切り分けも再試行戦略も立てられません。望ましいのは、例外の性質を分類し、再試行の可否とロールバック範囲、呼び出し側への通知形態、監査ログの粒度をあらかじめ設計しておくことです。ここでは例外の分類基準、コードへの落とし込み、再試行テンプレート、冪等性や部分ロールバックとの組み合わせ、スケジューラや外部副作用を含む現実運用のパターンをまとめます。
例外を「再試行すべき一時障害」と「再試行すべきでない恒久障害」に分ける
最初に決めるべきは分類軸です。ネットワーク断、ロック競合、デッドロック、シリアライザブル競合、リソース一時不足のように再実行で解消する見込みがあるものは一時障害として扱い、入力不備、制約違反、存在しないオブジェクト、権限不足のように原因がデータや定義にあるものは恒久障害として即座に失敗させます。PL/SQLではPRAGMA EXCEPTION_INITで代表的なORAコードを名前付き例外にマップし、分岐を明確にします。
CREATE OR REPLACE PACKAGE err_codes IS
deadlock_ex EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_ex, -60); -- ORA-00060
lock_nowait_ex EXCEPTION; PRAGMA EXCEPTION_INIT(lock_nowait_ex, -54); -- ORA-00054
seri_conflict EXCEPTION; PRAGMA EXCEPTION_INIT(seri_conflict, -8177); -- ORA-08177
snapshot_old EXCEPTION; PRAGMA EXCEPTION_INIT(snapshot_old, -1555); -- ORA-01555
unique_violate EXCEPTION; PRAGMA EXCEPTION_INIT(unique_violate, -1); -- ORA-00001
END;
/
再試行は冪等性とセットで設計する
同じ要求を繰り返しても状態が悪化しないこと、すなわち冪等性が再試行の前提です。INSERTは自然キーや外部リクエストIDにユニーク制約を置いてMERGEに寄せ、更新や副作用は同じキーで二度実行しても同じ最終結果に収束する形にします。外部システムへの通知はトランザクション・アウトボックスに記録して別のワーカーが配送する構成にしておくと、DBの再試行と外部副作用の重複を分離できます。
MERGE INTO orders d
USING (SELECT :req_id req_id, :cust_id cust_id, :amt amt FROM dual) s
ON (d.req_id = s.req_id) -- 冪等キー
WHEN MATCHED THEN UPDATE SET d.amount = s.amt, d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN INSERT (req_id, cust_id, amount, created_at)
VALUES (s.req_id, s.cust_id, s.amt, SYSTIMESTAMP);
指数バックオフ付き再試行テンプレート（リトライ上限と対象例外を明示）
再試行は対象を限定し、回数と待機時間を制御します。下は典型テンプレートです。ロック競合、デッドロック、シリアライザブル競合のみを対象にし、その他は即時に失敗させます。
CREATE OR REPLACE PROCEDURE apply_delta(p_id NUMBER, p_amt NUMBER) IS
v_attempt PLS_INTEGER := 0;
v_max PLS_INTEGER := 5; -- 上限
BEGIN
LOOP
v_attempt := v_attempt + 1;
BEGIN
MERGE INTO balances d
USING (SELECT p_id id, p_amt amt FROM dual) s ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.amt = d.amt + s.amt, d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN INSERT (id, amt, updated_at) VALUES (s.id, s.amt, SYSTIMESTAMP);
COMMIT;
EXIT; -- 成功
EXCEPTION
WHEN err_codes.lock_nowait_ex OR err_codes.deadlock_ex OR err_codes.seri_conflict THEN
ROLLBACK;
IF v_attempt < v_max THEN
DBMS_LOCK.SLEEP(POWER(2, v_attempt) * 0.2); -- 0.2,0.4,0.8,1.6...
ELSE
RAISE; -- 上限到達
END IF;
WHEN err_codes.unique_violate THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, 'duplicate request id'); -- 恒久障害は即失敗
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
END LOOP;
END;
/
部分ロールバックと再試行の併用で長尺処理を安定させる
長い一括処理はSAVEPOINTで細かく境界を刻み、個別行の恒久障害は部分ロールバックで飛ばし、一時障害だけ再試行します。これにより全体を止めずに処理継続できます。
FOR r IN (SELECT * FROM staging ORDER BY id) LOOP
SAVEPOINT sp_row;
BEGIN
apply_delta(r.id, r.amt); -- 内部で必要に応じてリトライ
EXCEPTION
WHEN err_codes.unique_violate THEN
ROLLBACK TO sp_row; -- データ起因はスキップして継続
INSERT INTO err_rows(id, reason, at) VALUES (r.id, 'duplicate', SYSTIMESTAMP);
WHEN OTHERS THEN
ROLLBACK TO sp_row;
INSERT INTO err_rows(id, reason, at) VALUES (r.id, SQLERRM, SYSTIMESTAMP);
END;
END LOOP;
COMMIT;
DBMS_ERRLOGとの役割分担を明確にする
LOG ERRORSは行単位での変換や制約違反を自動的に退避し、成功分を先に確定できます。一方でロック競合などの一時障害には効きません。制約違反はERRLOGへ、一時障害はリトライへと責務を分けるとシンプルです。
INSERT /*+ APPEND */ INTO target(cols...)
SELECT ... FROM staging
LOG ERRORS INTO err$_target('LOAD_202510') REJECT LIMIT UNLIMITED;
COMMIT;
例外情報の正規化と呼び出し境界でのマッピング
パッケージ内では内部例外をそのまま投げ、ファサード層でアプリ向けのドメインエラーに変換します。外部公開APIではエラーコード、メッセージ、リトライ可否、相関IDなどを統一した形式で返すと、上位層の実装が簡潔になります。
CREATE OR REPLACE PACKAGE api AS
FUNCTION upsert_order(p_req_id VARCHAR2, p_amt NUMBER) RETURN VARCHAR2; -- JSONを返す例
END;
/
CREATE OR REPLACE PACKAGE BODY api AS
FUNCTION upsert_order(p_req_id VARCHAR2, p_amt NUMBER) RETURN VARCHAR2 IS
v_corr_id VARCHAR2(64) := RAWTOHEX(SYS_GUID());
BEGIN
-- 内部処理呼び出し
apply_delta(TO_NUMBER(p_req_id), p_amt);
RETURN JSON_OBJECT('ok' VALUE TRUE, 'correlationId' VALUE v_corr_id);
EXCEPTION
WHEN err_codes.lock_nowait_ex OR err_codes.deadlock_ex OR err_codes.seri_conflict THEN
RETURN JSON_OBJECT('ok' VALUE FALSE, 'retryable' VALUE TRUE,
'code' VALUE 'RETRYABLE_CONFLICT',
'correlationId' VALUE v_corr_id);
WHEN err_codes.unique_violate THEN
RETURN JSON_OBJECT('ok' VALUE FALSE, 'retryable' VALUE FALSE,
'code' VALUE 'DUPLICATE', 'correlationId' VALUE v_corr_id);
WHEN OTHERS THEN
RETURN JSON_OBJECT('ok' VALUE FALSE, 'retryable' VALUE FALSE,
'code' VALUE 'UNEXPECTED', 'message' VALUE SQLERRM,
'correlationId' VALUE v_corr_id);
END;
END;
/
ログとトレースは自治トランザクションで確定させる
本流の成否に依らず診断情報を残すにはAUTONOMOUS_TRANSACTIONの小さなロガーを用意します。再試行回数、待機時間、最後に捕捉した例外、相関IDなどを記録しておくと、運用時の調査が一気に早くなります。
CREATE OR REPLACE PROCEDURE audit_retry(p_action VARCHAR2, p_attempt NUMBER, p_ok NUMBER, p_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO retry_audit(action, attempt, ok, msg, at)
VALUES (p_action, p_attempt, p_ok, SUBSTR(p_msg,1,4000), SYSTIMESTAMP);
COMMIT;
END;
/
スケジューラと再試行ポリシーの統合
DBMS_SCHEDULERのジョブで実行するバッチは、ジョブ側のmax_failuresやrepeat_intervalで粗い再試行を、PL/SQL側で細かいリトライを担う二層構えにします。ジョブがリトライしても冪等性が保たれるよう、処理対象のチャンクや外部リクエストIDをキーに設計しておきます。
スナップショットが古い（ORA-01555）を安易に再試行しない
ORA-01555は長時間の一貫性読取とUNDO保持の設計問題が主因です。単純リトライは根治にならないため、読取時間を短縮する集合志向クエリへの見直し、チャンク分割、UNDO_RETENTIONや表領域の見直し、競合を避ける時間帯の分離など、計画的な対策を優先します。
例外設計を支えるチェックリスト
一時障害と恒久障害の分類は明確か。再試行対象の例外と上限・待機戦略はコード化されているか。冪等キーやMERGEで重複を無害化しているか。部分ロールバックとERRLOGの併用で長尺処理を止めないか。外部副作用はアウトボックスで分離されているか。ファサードでエラーを統一フォーマットにマッピングしているか。ログは自治で確定し相関IDで追跡できるか。これらが満たされていれば、大規模環境でも安定して再試行と失敗処理を運用できます。
まとめ
大規模システムのPL/SQLでは、例外を正しく分類し、冪等性に裏打ちされた再試行を限定的に適用し、部分ロールバックやERRLOGと組み合わせて成功を先に確定させる設計が肝要です。ファサードでのエラーマッピング、自治ログと相関ID、スケジューラとの二層リトライを揃えれば、障害時の挙動が予測可能になり、止まらないバッチと追跡容易なAPIを同時に実現できます。
