Concurrent Upserts in SQL Server Without the Existence Race

An upsert looks simple: update a row if its business key exists, otherwise insert it. The difficulty appears when two sessions target the same missing key. Both can observe absence, both decide to insert, and one receives a duplicate-key error. Without a unique constraint, both may succeed and corrupt the intended relationship.

The first design decision is the meaning of an existing value. Replacing a language preference is different from incrementing a balance or rejecting an edit based on stale data. An upsert is a write policy, not merely a convenient SQL syntax. Define that policy before choosing the locking pattern.

Protect the business key

This practice table enforces one preference per customer and preference name. Create it only in a disposable database.

-- Create only in a disposable practice database.
CREATE TABLE dbo.PreferenceDemo (
    CustomerId int NOT NULL,
    PreferenceKey nvarchar(50) NOT NULL,
    PreferenceValue nvarchar(200) NOT NULL,
    CONSTRAINT PK_PreferenceDemo PRIMARY KEY (CustomerId, PreferenceKey)
);

The unique key is the final integrity boundary even if every application is expected to follow the correct procedure. Include all dimensions of uniqueness, such as TenantId where identifiers are only unique within a tenant. Agree on case sensitivity and normalization for textual keys; the database collation affects which strings compare equal.

A separate IF NOT EXISTS followed by INSERT is vulnerable under ordinary read committed behavior. Wrapping those statements in a transaction alone does not necessarily protect the missing key. The important protection is the range in which the absent key would be inserted.

The following batch performs an update first and protects its lookup until the transaction ends.

DECLARE @CustomerId int = 42;
DECLARE @Key nvarchar(50) = N'language';
DECLARE @Value nvarchar(200) = N'en';
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'This batch owns its transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.PreferenceDemo WITH (UPDLOCK, HOLDLOCK)
    SET PreferenceValue = @Value
    WHERE CustomerId = @CustomerId AND PreferenceKey = @Key;

    IF @@ROWCOUNT = 0
        INSERT dbo.PreferenceDemo(CustomerId, PreferenceKey, PreferenceValue)
        VALUES (@CustomerId, @Key, @Value);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

HOLDLOCK applies serializable behavior to this table reference. UPDLOCK requests update-oriented locks, while the suitable unique index lets SQL Server protect the relevant key or key range. This is not a promise that only one physical row is ever locked; access paths and surrounding work affect lock scope.

The @@ROWCOUNT check must immediately follow UPDATE. Do not add a logging statement between them. An existing row matched by UPDATE counts even if its stored value already equals the supplied value, so the batch does not incorrectly insert another row in that case.

State which conflicts are acceptable

For two concurrent requests assigning different languages, this design permits one to follow the other. The last completed serialized assignment determines the value. It does not promise that the last request received by a web server wins, and it does not detect a user overwriting somebody else's edit.

If stale edits must be rejected, use optimistic concurrency with an expected rowversion in the update predicate and treat no match as a conflict requiring investigation. rowversion is a change token, not a date and not a globally meaningful timestamp. Creation and replacement may then deserve separate API operations.

Retrying an assignment with the same value is usually naturally idempotent at the data level. Retrying "add 10" is not. Audit records, triggers, or external messages can also make an apparently harmless repeat produce extra effects. Use a durable request identifier when the business operation requires one application per request.

MERGE does not remove the need to reason about uniqueness, isolation, and concurrency. Evaluate its behavior against the exact workload and supported engine build instead of assuming that one statement makes the overall business operation safe.

Test with competing sessions

Run the write batch from two connections against the same practice table, including a previously absent key. For a controlled test, temporarily pause one session after UPDATE while its transaction is open and observe the other waiting. Remove that pause from real application code.

Also test different keys, repeated values, constraint failures, and a connection loss around COMMIT. If the connection disappears, the client may not know whether the server committed. A blanket retry can repeat side effects; resolve through the request identifier or read the authoritative state.

Range protection can increase contention and does not eliminate deadlocks. Access multiple keys in a consistent order and keep transactions short. Retry a deadlock victim's whole transaction with bounded backoff, not just the final INSERT inside a damaged transaction. A correct upsert preserves both the unique-key invariant and the application's declared conflict policy.

Technical references: Microsoft Learn: Table hints · Microsoft Learn: Transaction locking guide.