PL/SQLの業務処理から外部API、メール送信、他システム通知を直接呼び出すと、成功したのか失敗したのか判断しにくい状態が生まれます。たとえば注文登録の途中で外部APIを呼び、その直後にセッションが切れると、DB側はロールバックされたのに外部側には通知済み、またはDB側はコミット済みなのに通知だけ失敗、というズレが起きます。
この問題を小さくする代表的な設計がアウトボックスパターンです。業務トランザクションの中では「送るべきメッセージ」をDBのoutboxテーブルへ記録し、実際の外部送信は別ワーカーが非同期に処理します。これにより、再送、監視、二重送信防止、失敗時の調査をDB上で扱いやすくなります。
この記事では、PL/SQLでアウトボックステーブルを作り、FOR UPDATE SKIP LOCKEDで並列ワーカーを動かし、外部API連携を安全にリトライする実装を整理します。外部APIの呼び出し自体は UTL_HTTPでREST API連携する方法、再試行の考え方は PL/SQLの例外設計と再試行パターン とあわせて読むとつながりが見えやすくなります。
- 外部連携を業務トランザクションから分離する理由
- outboxテーブルの状態、再送回数、エラー保存の設計
FOR UPDATE SKIP LOCKEDで安全にキューを取り出す方法- 二重送信を防ぐキー設計と送信先側の冪等性
- DBMS_SCHEDULERで再送ワーカーを動かす方法
- 滞留、失敗、処理中固定を見つける監視SQL
なぜ外部連携を直接呼ばないほうがよいか
PL/SQLのプロシージャ内で業務更新と外部送信を同時に行うと、DBトランザクションと外部システムの状態を完全には同期できません。OracleのCOMMITやROLLBACKはDB内の変更には効きますが、すでに呼び出した外部API、SMTP送信、ファイル連携までは巻き戻せないためです。
アウトボックスパターンでは、業務処理はまずDB内に「送信予定」を保存します。保存と業務更新は同じトランザクションに含められるため、業務データだけ登録されて送信予定がない、という状態を防ぎやすくなります。実際の外部送信は後段のワーカーに任せ、失敗したらoutbox上で再送します。
アウトボックステーブルの基本設計
outboxテーブルには、何を、どこへ、どの状態で、何回送ったかを残します。本文では外部API向けのJSONペイロードを想定しますが、メール、ファイル連携、メッセージキュー連携にも同じ考え方を使えます。
CREATE TABLE app_outbox (
outbox_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
message_key VARCHAR2(200) NOT NULL,
event_type VARCHAR2(100) NOT NULL,
destination VARCHAR2(200) NOT NULL,
payload_json CLOB NOT NULL,
status VARCHAR2(20) DEFAULT 'PENDING' NOT NULL,
retry_count NUMBER DEFAULT 0 NOT NULL,
max_retry_count NUMBER DEFAULT 5 NOT NULL,
next_run_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
locked_by VARCHAR2(128),
locked_at TIMESTAMP,
sent_at TIMESTAMP,
last_error_code VARCHAR2(100),
last_error_msg VARCHAR2(1000),
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT uq_app_outbox_message_key UNIQUE (message_key),
CONSTRAINT ck_app_outbox_status CHECK (
status IN ('PENDING','PROCESSING','SENT','FAILED','CANCELLED')
)
);
CREATE INDEX ix_app_outbox_pickup
ON app_outbox(status, next_run_at, outbox_id);
CREATE INDEX ix_app_outbox_event
ON app_outbox(event_type, created_at);
message_keyは二重登録を防ぐための業務キーです。注文通知ならORDER_NOTICE:注文ID、請求連携ならINVOICE_EXPORT:請求IDのように、同じイベントを一意に表す値にします。これにユニーク制約を置くことで、同じ業務イベントが誤って複数回outboxへ積まれることを防ぎます。
業務処理では送信予定だけを登録する
業務プロシージャでは、外部APIを直接呼ばず、業務データ更新とoutbox登録だけを行います。この2つを同じトランザクションでコミットすることが重要です。トランザクション境界の考え方は PL/SQLのトランザクション設計パターン も参考になります。
CREATE OR REPLACE PACKAGE outbox_enqueue AS
PROCEDURE order_created(
p_order_id NUMBER,
p_user_id VARCHAR2,
p_amount NUMBER
);
END;
/
CREATE OR REPLACE PACKAGE BODY outbox_enqueue AS
PROCEDURE order_created(
p_order_id NUMBER,
p_user_id VARCHAR2,
p_amount NUMBER
) IS
v_message_key app_outbox.message_key%TYPE;
v_payload CLOB;
BEGIN
v_message_key := 'ORDER_CREATED:' || p_order_id;
v_payload := JSON_OBJECT(
'orderId' VALUE p_order_id,
'userId' VALUE p_user_id,
'amount' VALUE p_amount,
'eventAt' VALUE TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM')
RETURNING CLOB
);
INSERT INTO app_outbox (
message_key, event_type, destination, payload_json
) VALUES (
v_message_key, 'ORDER_CREATED', 'ORDER_API', v_payload
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
END;
END;
/
DUP_VAL_ON_INDEXを握りつぶしているのは、同じイベントがすでに積まれているなら追加登録しない、という設計にしているためです。ただし、握りつぶしてよいのはmessage_keyの意味が明確で、同じキーなら同じ業務イベントだと言い切れる場合に限ります。キーが雑だと、別イベントまで消してしまうので注意が必要です。
状態遷移を決めておく
再送キューは状態遷移が曖昧だと運用で詰まります。最低限、処理待ち、処理中、送信済み、失敗、取消の状態を分けておくと、監視や手動リカバリがしやすくなります。
状態は後からSQLで追えるように、文字列でも意味が分かる値にしておくのがおすすめです。数値コードだけにすると、障害対応中に確認コストが増えます。
FOR UPDATE SKIP LOCKEDで安全に取り出す
複数ワーカーを動かす場合、同じoutbox行を同時に処理しないようにする必要があります。FOR UPDATE SKIP LOCKEDを使うと、他セッションがロック中の行を飛ばして次の候補を取得できます。同時実行制御の詳しい考え方は マルチセッション環境での排他・同時実行制御 と相性がよいです。
CREATE OR REPLACE PACKAGE outbox_worker AS
PROCEDURE run_once(p_limit NUMBER DEFAULT 50);
END;
/
CREATE OR REPLACE PACKAGE BODY outbox_worker AS
FUNCTION claim_one(
p_worker_id VARCHAR2
) RETURN app_outbox.outbox_id%TYPE IS
CURSOR c_target IS
SELECT outbox_id
FROM app_outbox
WHERE status = 'PENDING'
AND next_run_at <= SYSTIMESTAMP
ORDER BY outbox_id
FOR UPDATE SKIP LOCKED;
v_outbox_id app_outbox.outbox_id%TYPE;
BEGIN
OPEN c_target;
FETCH c_target INTO v_outbox_id;
CLOSE c_target;
IF v_outbox_id IS NULL THEN
ROLLBACK;
RETURN NULL;
END IF;
UPDATE app_outbox
SET status = 'PROCESSING',
locked_by = p_worker_id,
locked_at = SYSTIMESTAMP,
updated_at = SYSTIMESTAMP
WHERE outbox_id = v_outbox_id;
COMMIT;
RETURN v_outbox_id;
EXCEPTION
WHEN OTHERS THEN
IF c_target%ISOPEN THEN
CLOSE c_target;
END IF;
ROLLBACK;
RAISE;
END;
PROCEDURE run_once(p_limit NUMBER DEFAULT 50) IS
v_worker_id VARCHAR2(128) := SYS_CONTEXT('USERENV','SID');
v_outbox_id app_outbox.outbox_id%TYPE;
BEGIN
FOR i IN 1 .. p_limit LOOP
v_outbox_id := claim_one(v_worker_id);
EXIT WHEN v_outbox_id IS NULL;
outbox_sender.send_one(v_outbox_id);
END LOOP;
END;
END;
/
ここでは1件ずつPROCESSINGへ更新してコミットし、その後に送信処理へ渡しています。FOR UPDATEで取得したカーソルを開いたままCOMMITすると、次のフェッチでORA-01002になることがあるため、1件を確保する処理をclaim_oneに分けています。大量件数を1トランザクションで抱えると、ロック時間が長くなり、失敗時の巻き戻しも大きくなります。1件単位または小さなチャンク単位で区切るほうが、外部連携では扱いやすいことが多いです。
送信処理では成功と失敗を必ず記録する
送信ワーカーは、外部API呼び出しの結果をoutboxへ戻します。次のapi_client.post_jsonは、自社で用意したUTL_HTTPラッパーやAPIクライアントに置き換える想定の呼び出しです。成功ならSENT、一時障害ならPENDINGへ戻して次回実行時刻を先延ばし、最大回数を超えたらFAILEDにします。処理名や識別子を残すなら DBMS_APPLICATION_INFOを使ったインストゥルメンテーション設計 も有効です。
CREATE OR REPLACE PACKAGE outbox_sender AS
PROCEDURE send_one(p_outbox_id NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY outbox_sender AS
FUNCTION next_delay_minutes(p_retry_count NUMBER) RETURN NUMBER IS
BEGIN
RETURN LEAST(POWER(2, p_retry_count), 60);
END;
PROCEDURE mark_sent(p_outbox_id NUMBER) IS
BEGIN
UPDATE app_outbox
SET status = 'SENT',
sent_at = SYSTIMESTAMP,
locked_by = NULL,
locked_at = NULL,
updated_at = SYSTIMESTAMP
WHERE outbox_id = p_outbox_id;
END;
PROCEDURE mark_retry(
p_outbox_id NUMBER,
p_error_code VARCHAR2,
p_error_msg VARCHAR2
) IS
BEGIN
UPDATE app_outbox
SET status = CASE
WHEN retry_count + 1 >= max_retry_count THEN 'FAILED'
ELSE 'PENDING'
END,
retry_count = retry_count + 1,
next_run_at = SYSTIMESTAMP
+ NUMTODSINTERVAL(next_delay_minutes(retry_count + 1), 'MINUTE'),
locked_by = NULL,
locked_at = NULL,
last_error_code = SUBSTR(p_error_code, 1, 100),
last_error_msg = SUBSTR(p_error_msg, 1, 1000),
updated_at = SYSTIMESTAMP
WHERE outbox_id = p_outbox_id;
END;
PROCEDURE send_one(p_outbox_id NUMBER) IS
v_message_key app_outbox.message_key%TYPE;
v_event_type app_outbox.event_type%TYPE;
v_destination app_outbox.destination%TYPE;
v_payload app_outbox.payload_json%TYPE;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE('OUTBOX_WORKER', 'send_one');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('outbox_id=' || p_outbox_id);
SELECT message_key, event_type, destination, payload_json
INTO v_message_key, v_event_type, v_destination, v_payload
FROM app_outbox
WHERE outbox_id = p_outbox_id
AND status = 'PROCESSING'
FOR UPDATE;
api_client.post_json(
p_destination => v_destination,
p_body => v_payload,
p_idempotency_key => v_message_key
);
mark_sent(p_outbox_id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
mark_retry(p_outbox_id, TO_CHAR(SQLCODE), SQLERRM);
COMMIT;
END;
END;
/
例外を捕まえたあとに必ず状態を戻すのがポイントです。失敗時にPROCESSINGのまま残ると、再送対象から外れてしまいます。ただし、セッション異常終了などでは例外処理まで到達しないこともあるため、後述の固定検知も用意します。
二重送信を防ぐ設計
アウトボックスパターンは二重送信を減らしますが、それだけで完全に防げるわけではありません。タイムアウト、ネットワーク切断、送信先の応答欠落がある以上、「送ったかもしれないが結果が分からない」状態は残ります。そのため、送信先にも冪等性キーを渡し、同じキーのリクエストは同じ結果として扱ってもらう設計が安全です。
message_keyにユニーク制約を置き、同じ業務イベントを複数積まないようにします。Idempotency-Key相当の値を渡し、送信先でも重複処理を避けます。自分たちで送信先APIも管理している場合は、REST API側で同じキーを処理済みとして保存する設計にします。PL/SQLからJSON形式でエラーや応答を返すAPI設計は 例外をJSON形式で返すREST対応API設計 と組み合わせると実装しやすくなります。
DBMS_SCHEDULERでワーカーを定期実行する
outboxワーカーはアプリケーションサーバーから起動してもよいですが、DB内で完結させるならDBMS_SCHEDULERで短い間隔のジョブにできます。ジョブ管理の細かい設定は DBMS_SCHEDULERでジョブ管理を極める が参考になります。
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_OUTBOX_WORKER',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN outbox_worker.run_once(50); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
enabled => TRUE,
comments => 'Send pending outbox messages'
);
END;
/
実務では、ジョブの同時起動数、処理件数、送信先APIのレート制限、夜間停止時間帯を考えて間隔を決めます。前回記事の 設定値管理テーブルとFeature Flag設計 を使い、送信停止フラグや1回あたりの処理件数を設定値に逃がすのも相性がよいです。
PROCESSINGに残った行を救済する
ワーカーが送信中に異常終了すると、outbox行がPROCESSINGのまま残ることがあります。この状態は通常の取得条件から外れるため、一定時間を超えたらPENDINGへ戻す救済処理を用意します。
CREATE OR REPLACE PROCEDURE recover_stuck_outbox(
p_timeout_minutes NUMBER DEFAULT 30
) AS
BEGIN
UPDATE app_outbox
SET status = 'PENDING',
locked_by = NULL,
locked_at = NULL,
last_error_code = 'STUCK_PROCESSING',
last_error_msg = 'Recovered from PROCESSING timeout',
next_run_at = SYSTIMESTAMP,
updated_at = SYSTIMESTAMP
WHERE status = 'PROCESSING'
AND locked_at < SYSTIMESTAMP
- NUMTODSINTERVAL(p_timeout_minutes, 'MINUTE');
COMMIT;
END;
/
救済時間は短すぎると正常処理中の行を奪ってしまい、長すぎると障害復旧が遅れます。送信先APIの最大タイムアウト、通常の処理時間、ネットワーク遅延を見て決めます。
監視SQLを用意する
outboxは運用監視まで含めて設計しておく必要があります。特に、未送信の滞留、失敗件数、処理中固定、リトライ増加をすぐ確認できるSQLを用意しておくと、障害時の初動が速くなります。
-- 状態別件数
SELECT status, COUNT(*) AS cnt
FROM app_outbox
GROUP BY status
ORDER BY status;
-- 送信待ちの最古レコード
SELECT MIN(created_at) AS oldest_pending_at,
COUNT(*) AS pending_count
FROM app_outbox
WHERE status = 'PENDING';
-- 最大リトライに近いレコード
SELECT outbox_id, message_key, event_type, retry_count, max_retry_count,
last_error_code, SUBSTR(last_error_msg, 1, 200) AS last_error_msg
FROM app_outbox
WHERE status IN ('PENDING','FAILED')
AND retry_count >= GREATEST(max_retry_count - 1, 1)
ORDER BY updated_at DESC;
-- PROCESSINGのまま長時間残っているレコード
SELECT outbox_id, message_key, locked_by, locked_at
FROM app_outbox
WHERE status = 'PROCESSING'
AND locked_at < SYSTIMESTAMP - INTERVAL '30' MINUTE
ORDER BY locked_at;
監視は件数だけでなく、最古の滞留時刻を見るのが重要です。件数が少なくても、古いPENDINGが残り続けているなら、特定の送信先や特定イベントだけが詰まっている可能性があります。
手動再送と取消の入口を作る
障害対応では、失敗したメッセージを手動で再送したい場面があります。このとき、運用者が直接UPDATEするより、専用プロシージャを用意して監査ログや入力チェックを通すほうが安全です。
CREATE TABLE app_outbox_audit (
audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
outbox_id NUMBER NOT NULL,
action_name VARCHAR2(30) NOT NULL,
reason VARCHAR2(1000),
changed_by VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER') NOT NULL,
changed_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE OR REPLACE PACKAGE outbox_admin AS
PROCEDURE retry_failed(p_outbox_id NUMBER, p_reason VARCHAR2);
PROCEDURE cancel_message(p_outbox_id NUMBER, p_reason VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY outbox_admin AS
PROCEDURE write_audit(
p_outbox_id NUMBER,
p_action VARCHAR2,
p_reason VARCHAR2
) IS
BEGIN
INSERT INTO app_outbox_audit(outbox_id, action_name, reason)
VALUES (p_outbox_id, p_action, p_reason);
END;
PROCEDURE retry_failed(p_outbox_id NUMBER, p_reason VARCHAR2) IS
BEGIN
UPDATE app_outbox
SET status = 'PENDING',
next_run_at = SYSTIMESTAMP,
locked_by = NULL,
locked_at = NULL,
updated_at = SYSTIMESTAMP
WHERE outbox_id = p_outbox_id
AND status = 'FAILED';
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20040, 'retry target is not FAILED');
END IF;
write_audit(p_outbox_id, 'RETRY_FAILED', p_reason);
COMMIT;
END;
PROCEDURE cancel_message(p_outbox_id NUMBER, p_reason VARCHAR2) IS
BEGIN
UPDATE app_outbox
SET status = 'CANCELLED',
locked_by = NULL,
locked_at = NULL,
updated_at = SYSTIMESTAMP
WHERE outbox_id = p_outbox_id
AND status IN ('PENDING','FAILED');
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20041, 'message cannot be cancelled');
END IF;
write_audit(p_outbox_id, 'CANCEL_MESSAGE', p_reason);
COMMIT;
END;
END;
/
再送時に新しいメッセージをINSERTし直すと、二重送信や追跡不能の原因になります。原則として、同じoutbox行、同じmessage_key、同じ送信先冪等性キーで再送します。本文や送信先を変える必要がある場合は、監査ログに理由を残し、別イベントとして扱うかどうかを業務ルールで決めます。
設計時に避けたい失敗パターン
アウトボックスは便利ですが、雑に作ると単なる失敗レコード置き場になってしまいます。最初から次の点を避けておくと、長く運用しやすくなります。
DBMS_AQを使うか、outboxテーブルを使うか
OracleにはAdvanced Queuing、つまりDBMS_AQもあります。キューイング機能としては強力ですが、すべての現場で採用できるとは限りません。既存アプリの運用者がSQLで状態を追いたい、テーブルとして監視したい、まずは小さく導入したい場合は、outboxテーブルのほうが始めやすいことがあります。
この記事の構成は、DBだけで完結する小さな外部連携から始めたい場合に向いています。規模が大きくなったら、outboxを外部メッセージ基盤へ橋渡しする中継テーブルとして使う設計にも拡張できます。
まとめ
PL/SQLで外部API連携を安全に扱うなら、業務処理の中で直接送信するより、outboxテーブルに送信予定を積み、別ワーカーで再送可能にする設計が安定します。業務更新とoutbox登録を同じトランザクションにし、送信処理はFOR UPDATE SKIP LOCKEDで安全に取り出し、成功、失敗、再送、取消を状態として残します。
特に重要なのは、message_keyによる二重登録防止、送信先へ渡す冪等性キー、PROCESSING固定の救済、失敗理由の保存、監視SQLです。ここまで用意しておくと、外部連携が失敗しても「どれが未送信か」「どれを再送すべきか」「なぜ失敗したか」をDB上で追えます。結果として、障害対応の属人性を減らし、再送してよい処理と止めるべき処理を落ち着いて判断できるようになります。
