SQL Server の MERGE で UPSERT する時に業務SEがハマる3つの罠（同時実行・HOLDLOCK・削除誤爆）

「INSERT して、もし重複したら UPDATE」。

これ、IF EXISTS で分岐して書いてた人、多いと思います。俺もずっとそうでした。

で、MERGE の1文にまとめられると知って乗り換える。ここまではいい。

問題は、MERGE は動くけど本番で事故る型だってこと。

どうも、ヒロポンです。

今回は SQL Server の MERGE で UPSERT する時にハマる3つの罠を、Docker の SQL Server 2019 で実際に再現しながら見ていきます。同時実行の一意キー違反、WHEN NOT MATCHED BY SOURCE の削除誤爆、source の重複キーで出る Msg 8672。どれも「テスト環境では動いたのに本番で」ってやつです。

先に1個だけ。MySQL の ON DUPLICATE KEY や PostgreSQL の ON CONFLICT は SQL Server にはありません。SQL Server の UPSERT は MERGE か IF EXISTS 分岐。他 DBMS から来た人は特にごっちゃにしないように。

はじめに: なぜこの3つを選んだか

MERGE は構文としては素直で、書けば動きます。だから「動いた＝OK」で本番に流してませんか？？

でも MERGE が事故るのは、たいてい書いた時じゃなくて後からです。

夜間バッチで同時に走った時、source が想定より少なかった時、ステージングに重複が紛れた時。つまり負荷とデータの状態で牙をむく。

今回の3つは、俺や周りが実際に踏んだ / 踏みかけたやつを選びました。順に、症状 → 再現 → 回避で見ていきます。

⏱ 対処目安サマリ

先に全体像。こんな感じで、どれも回避策自体は数分で入ります。知ってるかどうかだけの差です。

「知ってれば1分」のやつで本番が飛ぶ。1個ずつ潰しておきましょう。

1. 同時実行で一意キー違反 — HOLDLOCK なしの競合窓

MERGE は1文で完結するから「これアトミックやろ？？」と思いがち。

でも既定の分離レベルでは、MATCHED を判定してから INSERT するまでの間に競合窓が残る。

2つのセッションが同時に同じキーで MERGE を走らせると、両方が「その行は無い（NOT MATCHED）」と判断して、両方 INSERT する。結果、後から入れた方が一意キー違反（Msg 2627）で落ちる。運が悪いと重複が入る。

回避策は、MERGE の対象テーブルに WITH (HOLDLOCK) を付けるだけ。

MERGE dbo.Target WITH (HOLDLOCK) AS t
USING (SELECT @id AS id, @val AS val) AS s
    ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.val = s.val
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, val) VALUES (s.id, s.val);

HOLDLOCK は SERIALIZABLE と同じで、判定したキー範囲にロックを掛けて競合窓を閉じます。

実際、HOLDLOCK 付き MERGE をトランザクション内で走らせて sys.dm_tran_locks を覗くと、対象キー範囲に RangeS-U の範囲ロックが取られてるのが見える。これで、もう片方のセッションは先の MERGE が終わるまで待たされます。

UPSERT で MERGE を使うなら HOLDLOCK はほぼ必須、と覚えておいて損はないです。地味だけどこれが一番効きます！！

2. WHEN NOT MATCHED BY SOURCE の削除誤爆

これは事故った時のダメージが一番デカいやつ。

MERGE には「source に無い target の行」を処理する WHEN NOT MATCHED BY SOURCE が書けます。ここに THEN DELETE を付けると、source に含まれない既存行が全部消える。

全件同期（source が target のあるべき全量）なら正しい動き。問題は、source が部分集合の時です。

-- 危険: #TodaysBatch は「今日更新分」だけ（部分集合）なのに…
MERGE dbo.Target AS t
USING (SELECT id, val FROM #TodaysBatch) AS s
    ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.val = s.val
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, val) VALUES (s.id, s.val)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;   -- ← #TodaysBatch に無い既存行が全削除される

実機で再現。エラーは出ません。静かに消えます。

「今日の差分」を UPSERT するつもりが、BY SOURCE ... DELETE のせいで今日触ってない過去のデータが全部飛ぶ。夜間バッチで静かに走って、翌朝データが数万件消えてる、みたいな事故です。血の気が引くやつ。

回避は単純で、全同期じゃないなら WHEN NOT MATCHED BY SOURCE を付けない。どうしても消したいなら、AND で条件を絞る。

-- 消す対象を「今日のバッチ対象カテゴリ」に限定する等、範囲を明示
WHEN NOT MATCHED BY SOURCE AND t.category = @category THEN
    DELETE;

BY SOURCE は「便利そうだから」で付けない。付ける時は source が全量である保証があるか、削除範囲を条件で縛れているか、必ず確認する。

ん？この source って全部入ってるんやっけ？？と一瞬でも思ったら、付けないのが安全です。

3. source の重複キーで Msg 8672

3つ目は、ステージングテーブルから MERGE する時に踏むやつ。

MERGE は、source（USING 側）の1行が target の複数行に、あるいは source の複数行が target の1行に対応すると失敗します。特に source 側に結合キーの重複があると、こう出る。

Msg 8672, Level 16, State 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once.

エラー再現:

「同じ行を2回 UPDATE / DELETE しようとした」。CSV 取り込みのステージングに同じキーが2行紛れてた、みたいな時に一発で出ます。

回避は、MERGE の前に source を1キー1行に絞る。最新1件だけ残すなら ROW_NUMBER が定番。

MERGE dbo.Target WITH (HOLDLOCK) AS t
USING (
    SELECT id, val
    FROM (
        SELECT id, val,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
        FROM #Staging
    ) x
    WHERE rn = 1        -- 同じ id は最新の1件だけ
) AS s
    ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.val = s.val
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, val) VALUES (s.id, s.val);

実行結果:

PARTITION BY id で id ごとにグループ化して、updated_at DESC で新しい順に番号を振り、rn = 1 の最新だけを source にする。これで重複が潰れて Msg 8672 は出なくなる。いい感じに1文で収まります。

まとめ / チートシート

MERGE で UPSERT する時の安全チェックはこの3つ。

• 同時実行: MERGE dbo.Target WITH (HOLDLOCK) を付ける。判定〜INSERT の競合窓を閉じる
• 削除誤爆: WHEN NOT MATCHED BY SOURCE THEN DELETE は全同期以外で付けない。付けるなら AND で範囲を絞る
• 重複キー: source は ROW_NUMBER などで1キー1行に絞ってから MERGE。Msg 8672 を防ぐ

この3点を入れた「そのまま使える安全形」がこんな感じ。

MERGE dbo.Target WITH (HOLDLOCK) AS t
USING (
    SELECT id, val
    FROM (
        SELECT id, val,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
        FROM #Staging
    ) x WHERE rn = 1
) AS s
    ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.val = s.val
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, val) VALUES (s.id, s.val);
-- BY SOURCE DELETE は「全同期の時だけ」慎重に足す