OracleのMERGE文は、更新と挿入を1文へまとめられます。しかし、ON条件へ楽観ロック用のバージョンまで含めたり、重複したソースをそのまま渡したりすると、競合を検知するどころかORA-00001やORA-30926を起こします。
安全なMERGEでは、行を識別する条件と更新を許可する条件を分けます。ON句は主キーなどの不変キーだけにし、バージョン一致や差分判定はWHEN MATCHED THEN UPDATE ... WHEREへ置きます。この記事では、既存行の更新競合、同時INSERT、削除同期、再実行をそれぞれ分離して設計します。
- ON句には行を一意に識別するキーだけを置き、バージョン番号を含めません。
- 楽観ロックはUPDATE側のWHEREで判定し、更新件数が不足したら競合として扱います。
- 差分判定は
!=だけでなく、NULL同士を同値として扱う比較を使います。 - USING側はMERGE前に1キー1行へ確定し、ORA-30926を防ぎます。
- ソースから消えた行の論理削除はMERGEのNOT MATCHEDではなく、別のUPDATEで行います。
- 同時INSERTは一意制約を最終防衛線にし、ORA-00001を再読込または再試行へ回します。
MERGEの基本構文、件数取得、一般的なUPSERTはMERGE文の完全ガイドで解説しています。本記事は、同時実行と差分同期に絞った実務設計です。
検証用の表とSQLコレクション型を用意する
コレクションをSQLのTABLE演算子で参照するには、PL/SQLローカル型ではなくスキーマレベルのSQL型を使います。後続例で使うバージョン番号とソース順序も型へ含めます。
CREATE TABLE app.orders (
order_id NUMBER,
customer_id NUMBER NOT NULL,
segment VARCHAR2(20) NOT NULL,
amount NUMBER(12, 2),
version_no NUMBER DEFAULT 1 NOT NULL,
source_updated_at TIMESTAMP WITH TIME ZONE,
is_deleted CHAR(1) DEFAULT 'N' NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY (order_id),
CONSTRAINT ck_orders_deleted CHECK (is_deleted IN ('Y', 'N'))
);
CREATE OR REPLACE TYPE app.order_merge_row AS OBJECT (
order_id NUMBER,
customer_id NUMBER,
segment VARCHAR2(20),
amount NUMBER,
expected_version NUMBER,
source_updated_at TIMESTAMP WITH TIME ZONE,
source_seq NUMBER
);
/
CREATE OR REPLACE TYPE app.order_merge_tab
AS TABLE OF app.order_merge_row;
/
PL/SQLコレクションはTABLE(v_src)でMERGEへ渡す
静的SQLの中で宣言済みのコレクション変数を使う場合、:1のような動的SQL用プレースホルダは書きません。SQL型の変数をそのままTABLE(v_src)へ渡します。
DECLARE
v_src app.order_merge_tab := app.order_merge_tab(
app.order_merge_row(
1001, 10, 'RETAIL', 1200, 1,
TO_TIMESTAMP_TZ(
'2026-06-07 10:00:00 +09:00',
'YYYY-MM-DD HH24:MI:SS TZH:TZM'
), 1
),
app.order_merge_row(
1002, 11, 'RETAIL', 1500, NULL,
TO_TIMESTAMP_TZ(
'2026-06-07 10:01:00 +09:00',
'YYYY-MM-DD HH24:MI:SS TZH:TZM'
), 2
)
);
BEGIN
MERGE INTO app.orders d
USING (
SELECT
s.order_id,
s.customer_id,
s.segment,
s.amount,
s.source_updated_at
FROM TABLE(v_src) s
) s
ON (d.order_id = s.order_id)
WHEN MATCHED THEN
UPDATE SET
d.customer_id = s.customer_id,
d.segment = s.segment,
d.amount = s.amount,
d.source_updated_at = s.source_updated_at,
d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
INSERT (
order_id, customer_id, segment, amount,
version_no, source_updated_at, created_at, updated_at
)
VALUES (
s.order_id, s.customer_id, s.segment, s.amount,
1, s.source_updated_at, SYSTIMESTAMP, SYSTIMESTAMP
);
COMMIT;
END;
/
NULL安全な差分判定で不要な更新を避ける
Oracleでは、片方がNULLのd.amount != s.amountはTRUEではなくUNKNOWNになります。そのため、NULLから値、値からNULLへの変更を検出できません。DECODEはNULL同士を同値として比較できるため、差分列ごとに結果を判定できます。
MERGE INTO app.orders d
USING app.stage_orders s
ON (d.order_id = s.order_id)
WHEN MATCHED THEN
UPDATE SET
d.customer_id = s.customer_id,
d.segment = s.segment,
d.amount = s.amount,
d.source_updated_at = s.source_updated_at,
d.updated_at = SYSTIMESTAMP
WHERE DECODE(d.customer_id, s.customer_id, 0, 1) = 1
OR DECODE(d.segment, s.segment, 0, 1) = 1
OR DECODE(d.amount, s.amount, 0, 1) = 1
OR DECODE(d.source_updated_at, s.source_updated_at, 0, 1) = 1
WHEN NOT MATCHED THEN
INSERT (
order_id, customer_id, segment, amount,
version_no, source_updated_at, created_at, updated_at
)
VALUES (
s.order_id, s.customer_id, s.segment, s.amount,
1, s.source_updated_at, SYSTIMESTAMP, SYSTIMESTAMP
);
無変更行をUPDATEしないことで、行ロック、UNDO、更新トリガー、REDOの負荷を抑えられます。ただし、更新日時を必ず進めることが業務要件なら差分判定を入れない方が適切です。
ORA-30926を防ぐためUSING側を1キー1行にする
MERGEは同じ対象行を1文の中で複数回更新できません。ソースに同じキーが複数ある場合は、ROW_NUMBERで採用行を1行へ確定します。複数列を個別にMAXすると、異なる入力行の値を組み合わせる危険があります。
MERGE INTO app.orders d
USING (
SELECT
order_id,
customer_id,
segment,
amount,
expected_version,
source_updated_at
FROM (
SELECT
s.*,
ROW_NUMBER() OVER (
PARTITION BY s.order_id
ORDER BY s.source_updated_at DESC, s.source_seq DESC
) AS rn
FROM app.stage_orders s
WHERE s.batch_id = :batch_id
)
WHERE rn = 1
) s
ON (d.order_id = s.order_id)
WHEN MATCHED THEN
UPDATE SET
d.customer_id = s.customer_id,
d.segment = s.segment,
d.amount = s.amount,
d.source_updated_at = s.source_updated_at,
d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
INSERT (
order_id, customer_id, segment, amount,
version_no, source_updated_at, created_at, updated_at
)
VALUES (
s.order_id, s.customer_id, s.segment, s.amount,
1, s.source_updated_at, SYSTIMESTAMP, SYSTIMESTAMP
);
最新行を採用する前に、重複件数を監査表へ記録してください。重複が入力不正を示す業務では、MERGEを続行せずバッチ全体を停止する方が安全です。
楽観ロック条件をON句へ入れない
バージョン番号をON句へ含めると、主キーは存在するのにバージョンが違う行がNOT MATCHEDになります。その結果、INSERT分岐が同じ主キーを挿入しようとしてORA-00001になります。
ON句は主キーだけにし、バージョン一致はUPDATE側のWHEREへ置きます。これならバージョン不一致の既存行はMATCHEDのまま更新だけがスキップされ、INSERTへ落ちません。
DECLARE
v_source_count PLS_INTEGER;
v_merged_count PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_source_count
FROM app.stage_orders
WHERE batch_id = :batch_id
AND operation_type = 'UPDATE'
AND source_rn = 1;
MERGE INTO app.orders d
USING (
SELECT
order_id,
customer_id,
segment,
amount,
expected_version,
source_updated_at
FROM app.stage_orders
WHERE batch_id = :batch_id
AND operation_type = 'UPDATE'
AND source_rn = 1
) s
ON (d.order_id = s.order_id)
WHEN MATCHED THEN
UPDATE SET
d.customer_id = s.customer_id,
d.segment = s.segment,
d.amount = s.amount,
d.version_no = d.version_no + 1,
d.source_updated_at = s.source_updated_at,
d.updated_at = SYSTIMESTAMP
WHERE d.version_no = s.expected_version;
v_merged_count := SQL%ROWCOUNT;
IF v_merged_count != v_source_count THEN
RAISE_APPLICATION_ERROR(
-20031,
'optimistic lock conflict: expected=' || v_source_count ||
', updated=' || v_merged_count
);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
この例は更新対象だけを扱うため、入力1行につき正常なら必ず1行更新されます。対象行の不存在とバージョン不一致を個別に返したい場合は、MERGE前または失敗後にステージング表と対象表を結合し、NOT_FOUNDとVERSION_CONFLICTへ分類します。
競合検証では、別セッションから更新を挟むテストが必要です。トランザクションの見え方は分離レベル別の一貫性テストも参考になります。
新規行の同時INSERTは一意制約で検知する
2セッションが同時に同じ新規キーをMERGEすると、両方がNOT MATCHEDと判断した後、一方が主キーまたは一意制約でORA-00001になる場合があります。MERGEだけでこの競合を消すことはできません。
業務キーへ一意制約を設定し、ORA-00001を捕捉したら対象行を再読込します。到着順に関係なく同じ最終状態へ収束できる場合だけ、最新ソース時刻を条件に再試行します。
DECLARE
e_dup_key EXCEPTION;
PRAGMA EXCEPTION_INIT(e_dup_key, -1);
v_pk_exists PLS_INTEGER;
BEGIN
BEGIN
MERGE INTO app.orders d
USING (
SELECT
:order_id AS order_id,
:customer_id AS customer_id,
:segment AS segment,
:amount AS amount,
:source_updated_at AS source_updated_at
FROM dual
) s
ON (d.order_id = s.order_id)
WHEN MATCHED THEN
UPDATE SET
d.customer_id = s.customer_id,
d.segment = s.segment,
d.amount = s.amount,
d.source_updated_at = s.source_updated_at,
d.updated_at = SYSTIMESTAMP
WHERE d.source_updated_at IS NULL
OR d.source_updated_at < s.source_updated_at
WHEN NOT MATCHED THEN
INSERT (
order_id, customer_id, segment, amount,
version_no, source_updated_at, created_at, updated_at
)
VALUES (
s.order_id, s.customer_id, s.segment, s.amount,
1, s.source_updated_at, SYSTIMESTAMP, SYSTIMESTAMP
);
EXCEPTION
WHEN e_dup_key THEN
-- PK_ORDERS以外の一意制約違反は、この処理では回復できない
IF INSTR(UPPER(SQLERRM), '(APP.PK_ORDERS)') = 0 THEN
RAISE;
END IF;
-- 主キー行が存在する場合だけ、同時INSERT競合として再適用する
SELECT COUNT(*)
INTO v_pk_exists
FROM app.orders
WHERE order_id = :order_id;
IF v_pk_exists = 0 THEN
-- 別の一意制約違反は、この処理では回復できない
RAISE;
END IF;
UPDATE app.orders
SET customer_id = :customer_id,
segment = :segment,
amount = :amount,
source_updated_at = :source_updated_at,
updated_at = SYSTIMESTAMP
WHERE order_id = :order_id
AND (
source_updated_at IS NULL
OR source_updated_at < :source_updated_at
);
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
ソース時刻が一意で信頼できることが前提です。同じ時刻のイベントがあり得る場合は、単調増加するイベント番号や更新トークンも比較条件へ含めます。
ソースから消えた行の論理削除は別UPDATEで行う
Oracle MERGEのWHEN NOT MATCHEDで実行できるのはINSERTです。ソースに存在しない対象行をUPDATEする構文はありません。また、MERGEのDELETE WHEREは、MATCHEDで更新された行だけを削除対象にします。
完全同期で消失行を論理削除する場合は、同期範囲を明示した別UPDATEを使います。
BEGIN
UPDATE app.orders d
SET d.is_deleted = 'Y',
d.updated_at = SYSTIMESTAMP
WHERE d.segment = :segment
AND d.is_deleted = 'N'
AND NOT EXISTS (
SELECT 1
FROM app.stage_orders s
WHERE s.batch_id = :batch_id
AND s.segment = d.segment
AND s.order_id = d.order_id
);
DBMS_OUTPUT.PUT_LINE('logically deleted=' || SQL%ROWCOUNT);
COMMIT;
END;
/
空のステージングを「全件削除」と解釈しないよう、取込件数、対象セグメント、スナップショット完了フラグを検証してから実行します。増分取込では、ソースにないことは削除を意味しないため、この処理を実行してはいけません。
DMLエラーロギングで行エラーを隔離する
LOG ERRORSを使うと、型変換や制約違反などの行エラーをエラー表へ記録し、処理を継続できます。ただし、競合やソース重複をすべて解決する機能ではありません。USING側の一意性は事前に保証します。
BEGIN
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG(
dml_table_name => 'APP.ORDERS',
err_log_table_name => 'ERR$_ORDERS',
err_log_table_owner => 'APP',
skip_unsupported => TRUE
);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
END;
/
MERGE INTO app.orders d
USING (
SELECT *
FROM (
SELECT
s.*,
ROW_NUMBER() OVER (
PARTITION BY s.order_id
ORDER BY s.source_updated_at DESC, s.source_seq DESC
) AS rn
FROM app.stage_orders s
WHERE s.batch_id = :batch_id
)
WHERE rn = 1
) s
ON (d.order_id = s.order_id)
WHEN MATCHED THEN
UPDATE SET
d.customer_id = s.customer_id,
d.segment = s.segment,
d.amount = s.amount,
d.source_updated_at = s.source_updated_at,
d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
INSERT (
order_id, customer_id, segment, amount,
version_no, source_updated_at, created_at, updated_at
)
VALUES (
s.order_id, s.customer_id, s.segment, s.amount,
1, s.source_updated_at, SYSTIMESTAMP, SYSTIMESTAMP
)
LOG ERRORS INTO app.err$_orders ('BATCH=' || :batch_id)
REJECT LIMIT UNLIMITED;
エラー表の作成には対象表への権限、CREATE TABLE権限、表領域クォータが必要です。エラー行を残す運用はDBMS_ERRLOGでDMLエラーを記録する方法で詳しく解説しています。
ステージング表で入力・結果・再実行を管理する
大量データや競合結果の個別管理には、コレクションよりステージング表が適しています。少なくともbatch_id、業務キー、ソース順序、期待バージョン、処理状態、エラーコードを保存します。
BEGIN
-- 対象表の存在有無にかかわらず、重複ソースを先に隔離する
UPDATE app.stage_orders s
SET s.process_status = 'DUPLICATE_SOURCE'
WHERE s.batch_id = :batch_id
AND s.source_rn > 1;
-- 重複していない入力を、更新可能または競合へ分類する
MERGE INTO app.stage_orders s
USING app.orders d
ON (
s.batch_id = :batch_id
AND s.source_rn = 1
AND s.order_id = d.order_id
)
WHEN MATCHED THEN
UPDATE SET s.process_status =
CASE
WHEN s.operation_type = 'UPDATE'
AND d.version_no != s.expected_version
THEN 'VERSION_CONFLICT'
ELSE 'READY'
END;
-- 更新指定なのに対象行が存在しない入力を分類する
UPDATE app.stage_orders s
SET s.process_status = 'NOT_FOUND'
WHERE s.batch_id = :batch_id
AND s.operation_type = 'UPDATE'
AND s.source_rn = 1
AND NOT EXISTS (
SELECT 1
FROM app.orders d
WHERE d.order_id = s.order_id
);
COMMIT;
END;
/
MERGEを冪等にする条件を明示する
MERGEという構文自体が自動的に冪等になるわけではありません。同じ入力を再実行して同じ最終状態になるには、次の条件が必要です。
- USING側が業務キーごとに1行へ確定している
- ソース順序またはイベントIDが一意で、古い入力を拒否できる
- 無変更時にバージョン、更新日時、監査行を不要に増やさない
- INSERT対象へ主キーまたは一意制約がある
- 論理削除の対象範囲とスナップショット完了条件が固定されている
- 適用済みbatch_idまたはevent_idを重複登録できない
本番投入前の競合テスト
- 同じ主キーを2セッションから同時に新規MERGEする
- 読込後に別セッションでversion_noを更新し、楽観ロック競合を起こす
- USING側へ同じキーを2行入れ、MERGE前に重複として検出できるか確認する
- NULLから値、値からNULL、NULL同士の差分判定を確認する
- 空のステージングで論理削除が走らない安全弁を確認する
- LOG ERRORS対象行とトランザクション全体を止めるエラーを区別する
- 同じbatch_idを再実行しても状態や監査件数が増えないことを確認する
バインド変数とカーソル共有を含む動的なMERGE設計は、動的SQLのバインド変数設計も合わせて確認してください。
まとめ
MERGEの競合対策では、ON句を複雑にするほど安全になるわけではありません。ON句は不変の行識別キーへ限定し、バージョン一致と差分判定はUPDATE側のWHEREへ分離します。これにより、バージョン不一致の既存行がINSERT分岐へ落ちる事故を防げます。
USING側はROW_NUMBERで1キー1行へ確定し、NULL安全な比較で不要な更新を避けます。同時INSERTは一意制約と再試行、消失行の論理削除は範囲を限定した別UPDATE、行エラーはLOG ERRORSで扱います。競合検出と再実行条件を明示して初めて、MERGEを安全な差分同期処理として運用できます。
