Handling concurrent data modifications in SQL Server is a common challenge. A classic issue arises when two or more sessions attempt to run an "UPSERT" (update existing rows, insert new rows) simultaneously. Even with a WHERE NOT EXISTS clause in place, developers frequently encounter the following error:

Violation of UNIQUE KEY constraint 'UX_TransactionCode'. Cannot insert duplicate key in object 'dbo.Transaction'

In this guide, we'll explore why this race condition occurs and how to fix it cleanly without locking the entire table with TABLOCKX.

Why Does the UNIQUE KEY Violation Occur?

Under default transaction isolation levels (such as READ COMMITTED), SQL Server releases shared locks as soon as a SELECT statement completes. This opens the door to two common failure modes:

  1. Inter-Session Race Conditions (Time-of-Check to Time-of-Use):
    • Session A checks NOT EXISTS for TransactionCode = 1234. It finds nothing.
    • Session B checks NOT EXISTS for TransactionCode = 1234. It also finds nothing.
    • Session A proceeds to insert 1234 and commits.
    • Session B attempts to insert 1234, immediately tripping the UNIQUE constraint.
  2. Intra-Batch Duplicates: If your temporary table (e.g., #Data) generates duplicate keys within the same session batch, an INSERT ... SELECT statement will attempt to insert both, violating the constraint within the same query.

The Solution: Proper Locking Hints and Source Deduplication

To eliminate race conditions without resorting to coarse-grained table locks (TABLOCKX), you need to combine two techniques:

  1. Deduplicate Source Data: Ensure your staging set contains distinct keys.
  2. Use WITH (UPDLOCK, HOLDLOCK): Apply these hints to the target table inside the NOT EXISTS subquery.

What Do UPDLOCK and HOLDLOCK Do?

  • UPDLOCK (Update Lock): Signals that the session intends to modify the data, preventing other concurrent sessions from acquiring update or exclusive locks on the same keys.
  • HOLDLOCK (Serializable): Holds the lock until the transaction finishes and applies Key-Range locks to protect the range, preventing other sessions from inserting rows into the gap.

Refactored Stored Procedure

Here is the corrected, thread-safe version of your procedure:

CREATE OR ALTER PROCEDURE [dbo].[p_PerformTransactions]
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Staging table with a primary key to guarantee uniqueness within the batch
    CREATE TABLE #Data (
        TransactionCode INT NOT NULL PRIMARY KEY
    );

    -- Populate staging data while avoiding duplicates in the current batch
    INSERT INTO #Data (TransactionCode)
    SELECT DISTINCT CHECKSUM(NEWID()) % 10000
    FROM GENERATE_SERIES(1, 100);

    BEGIN TRANSACTION;

    -- Step 1: Update existing records
    UPDATE tgt
    SET tgt.Occurred += 1
    FROM [dbo].[Transaction] tgt WITH (UPDLOCK, HOLDLOCK)
    INNER JOIN #Data src ON src.TransactionCode = tgt.TransactionCode;

    -- Step 2: Insert non-existing records
    INSERT INTO [dbo].[Transaction] (TransactionCode, Occurred)
    SELECT src.TransactionCode, 1
    FROM #Data src
    WHERE NOT EXISTS (
        SELECT 1
        FROM [dbo].[Transaction] tgt WITH (UPDLOCK, HOLDLOCK)
        WHERE tgt.TransactionCode = src.TransactionCode
    );

    COMMIT TRANSACTION;
END;
GO

Alternative: Handling High-Concurrency with Retries or IGNORE_DUP_KEY

While locking hints eliminate unique key violations, under extreme concurrency they may convert key violations into deadlocks (Error 1205) because multiple sessions compete for overlapping key ranges. Here are complementary patterns:

  • Implement Transient Error Retries: Always implement retry logic in your application layer (or with T-SQL TRY...CATCH) for transient deadlocks.
  • Index Property IGNORE_DUP_KEY = ON: If you only want to insert rows that do not exist and discard duplicates silently without rolling back the transaction, you can define your unique index with WITH (IGNORE_DUP_KEY = ON).

Summary

To safely prevent UNIQUE KEY constraint violations in high-concurrency environments:

  • Always deduplicate your incoming/staging data.
  • Wrap the Upsert inside a single transaction.
  • Use WITH (UPDLOCK, HOLDLOCK) on the target table during both the UPDATE and NOT EXISTS check.