Upsert Patterns in SQL Server Without Race Conditions

Two connections both decide that the row doesn't exist. Reliable upsert patterns prevent that decision from creating duplicates or avoidable insert errors. The missing row needs protection before either caller inserts it.

Begin Upsert Patterns with a Unique Business Key

I check the unique key before reading the upsert procedure. Application logic isn't the final protection against duplicate identifiers. A database constraint must enforce that rule.

The sample table uses ItemCode as its primary key. Every upsert addresses one existing or absent code. Create it in a disposable database for the two-session tests.

An upsert means update when present and insert when absent. The operation needs one concurrency contract around both possibilities. Two independent statements don't gain that contract merely by appearing next to each other.

A unique key rejects a duplicate that slips through application logic. It doesn't make every racing caller succeed. The transaction design decides how callers coordinate before reaching that guard.

Keep the business key narrow enough for efficient range locking. An appropriate index helps SQL Server protect the requested key range. A broad unindexed search can take much broader locks.

CREATE TABLE dbo.UpsertItemsDemo
(
    ItemCode varchar(20) NOT NULL PRIMARY KEY,
    Quantity int NOT NULL,
    ModifiedAt datetime2(3) NOT NULL
);

See Why IF EXISTS Races

Under ordinary READ COMMITTED behavior, a completed existence check doesn't reserve an absent key. Another connection can make the same decision. Both then attempt to insert.

Putting the two branches inside a transaction alone isn't enough. The isolation and lock duration still matter. READ COMMITTED can release the existence-check locks before the later statement.

The fragment below demonstrates the decision gap without inserting data. Run it in two windows with the same absent key. Both windows can report the same absence.

The next insert would then race without additional coordination. The primary key would reject a duplicate. That error is protection, but it isn't a successful concurrency design for both callers.

Don't remove the unique constraint to make the second insert succeed. That fixes the error message by damaging the data. The constraint is doing its assigned work.

SELECT CASE WHEN EXISTS
(SELECT 1 FROM dbo.UpsertItemsDemo WHERE ItemCode = 'A100')
THEN 'Present' ELSE 'Absent' END AS CurrentDecision;

Update under a Protected Key Range

The procedure first attempts an UPDATE with UPDLOCK and SERIALIZABLE. Within the transaction, those hints protect the relevant key or missing-key range. Another competing caller cannot independently reserve the same absence.

If no row was updated, the procedure inserts the new row. The insert includes a defensive NOT EXISTS check under the same protection. The transaction holds the decision until commit.

The procedure owns its transaction and rejects a caller-owned transaction. That makes rollback ownership explicit. A shared transaction design needs its own reviewed contract.

SET XACT_ABORT ON and CATCH clean up failures. THROW returns the original exception. A deadlock still requires the application's bounded retry policy.

The procedure definition is a separate batch. Use it through the same permissions intended for the application. Administrative tests don't establish the production permission boundary.

GO
CREATE PROCEDURE dbo.UpsertItemDemo @ItemCode varchar(20), @Quantity int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    IF @@TRANCOUNT <> 0 THROW 51000, 'No existing transaction is supported.', 1;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE dbo.UpsertItemsDemo WITH (UPDLOCK, SERIALIZABLE)
        SET Quantity = @Quantity, ModifiedAt = SYSUTCDATETIME()
        WHERE ItemCode = @ItemCode;
        IF @@ROWCOUNT = 0
            INSERT dbo.UpsertItemsDemo(ItemCode, Quantity, ModifiedAt)
            SELECT @ItemCode, @Quantity, SYSUTCDATETIME()
            WHERE NOT EXISTS
            (SELECT 1 FROM dbo.UpsertItemsDemo WITH (UPDLOCK, SERIALIZABLE) WHERE ItemCode = @ItemCode);
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

Hold the First Session for a Visible Test

The first test window deliberately keeps a protected transaction open briefly. It updates or inserts a fresh sample code. The pause creates time to run the second window.

Run the second block in another SSMS session while the first waits. It uses the production-shaped procedure rather than an unprotected insert. Observe blocking until the first session releases its range protection.

The sample values express two requested quantities. They aren't measured outcomes asserted here. Read the final stored value after both sessions complete.

The second call updates after obtaining the protection. This is last-writer-wins behavior for the supplied replacement quantity. Increment semantics or conflict detection require different business logic.

Upsert patterns must settle that business rule too. Concurrency safety doesn't decide whether replacing an earlier value is acceptable. A valid single row can still contain an undesired overwrite.

-- Session A, use a fresh test code.
BEGIN TRANSACTION;
UPDATE dbo.UpsertItemsDemo WITH (UPDLOCK, SERIALIZABLE)
SET Quantity = 10, ModifiedAt = SYSUTCDATETIME() WHERE ItemCode = 'RACE100';
IF @@ROWCOUNT = 0
    INSERT dbo.UpsertItemsDemo VALUES('RACE100', 10, SYSUTCDATETIME());
WAITFOR DELAY '00:00:15';
COMMIT TRANSACTION;

-- Session B, run while Session A is waiting.
EXEC dbo.UpsertItemDemo @ItemCode = 'RACE100', @Quantity = 20;
SELECT ItemCode, Quantity, ModifiedAt
FROM dbo.UpsertItemsDemo WHERE ItemCode = 'RACE100';

Compare MERGE with the Same Protection

MERGE combines matching and modification into one statement. That doesn't automatically settle missing-key races under every isolation choice. HOLDLOCK supplies serializable target protection for this example.

The source below contains one row. A larger source must not contain duplicate matches for one target key. Validate that source before assuming MERGE can update the same target repeatedly.

The terminator is required after MERGE. Keep the match condition limited to the intended key. Adding business filters to ON can misclassify an existing row as absent.

I prefer the explicit update-and-insert pattern for this small operation because its locking contract is easy to review. MERGE still deserves a comparison when the workload needs it. Test the exact target features and concurrency conditions.

A unique key remains necessary with either version. HOLDLOCK isn't a reason to remove durable uniqueness. Code paths outside this statement still need the guard.

MERGE dbo.UpsertItemsDemo WITH (HOLDLOCK) AS target
USING (VALUES('MERGE100', 30)) AS source(ItemCode, Quantity)
ON target.ItemCode = source.ItemCode
WHEN MATCHED THEN
    UPDATE SET Quantity = source.Quantity, ModifiedAt = SYSUTCDATETIME()
WHEN NOT MATCHED THEN
    INSERT(ItemCode, Quantity, ModifiedAt)
    VALUES(source.ItemCode, source.Quantity, SYSUTCDATETIME());

Review Deadlocks and Retries in Upsert Patterns

Protecting key ranges introduces coordination, not immunity from deadlocks. Multi-key operations need consistent access order. Keep transactions short and index the business key.

Retry the complete transaction after a retryable deadlock. Don't retry only its insert branch with stale assumptions. Use a bounded policy and retain the error evidence.

What should happen when the same request is submitted twice? Define that separately from two requests changing the same key. Idempotence and concurrency are related, but different, requirements.

I test absent keys and existing keys under two connections. I also test rejected duplicate source rows. Upsert patterns earn trust through those cases, not through one quiet successful call.

Keep the unique key, protected decision and business overwrite rule together. Then the second connection gets a coordinated answer. Two optimistic absence checks aren't a reservation system.

Keep a request identifier when the caller needs duplicate-request detection. The business key alone identifies the item, not the request. A retried increment needs protection against applying the same request twice.

Related reading on this blog: Resolving Deadlock by Accessing Objects in the Same Order and Reading Deadlock Graphs From the system_health Session.

An upsert is not two independent decisions, it is one protected operation around a unique key.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.