行がない場合のみ挿入する

質問

私はいつも以下のようなものを使って実現していました。

INSERT INTO TheTable
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WHERE
        PrimaryKey = @primaryKey)

...しかし、一度負荷がかかると、主キー違反が発生しました。 これは、このテーブルにまったく挿入しない唯一のステートメントです。 ということは、上記のステートメントはアトミックではないということでしょうか？

問題は、これを自由に再現することはほとんど不可能だということです。

おそらく、次のようなものに変えることができると思います。

INSERT INTO TheTable
WITH
    (HOLDLOCK,
    UPDLOCK,
    ROWLOCK)
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WITH
        (HOLDLOCK,
        UPDLOCK,
        ROWLOCK)
    WHERE
        PrimaryKey = @primaryKey)

とはいえ、ロックの使い方が間違っているのか、ロックの使いすぎなのか、何なんでしょうね。

私は、stackoverflow.com で、回答が "IF (SELECT COUNT(*) ... INSERT" などを提案している他の質問を見ましたが、私はいつも、単一の SQL 文がアトミックであるという（おそらく間違った）前提で考えていました。

誰か考えをお持ちですか？

どのように解決するのですか？

についてはどうですか？ "JFDI"。 のパターンはどうでしょうか？

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

真面目な話、これはロック無しで最も速く、特に大容量では最も同時性が高いです。 UPDLOCKがエスカレートして、テーブル全体がロックされたらどうするんですか？

レッスン4を読む :

レッスン4 インデックスのチューニングに先立ち、upsert procを開発する際、まず信頼できるのは If Exists(Select…) 行がどのアイテムに対しても実行され、重複を禁止してくれると信じていました。しかし、そうではありませんでした。同じアイテムが同じミリ秒にアップサートにヒットし、両方のトランザクションが存在しないことを確認して挿入を実行したため、短期間で何千もの重複が発生しました。多くのテストの後、解決策は、ユニーク インデックスを使用し、エラーをキャッチし、トランザクションが行を見ることができるように再試行して、挿入の代わりに更新を実行することでした。