Upsert race conditions

In one line: “update the row if it exists, insert it if it doesn’t” is two steps, and under concurrency another session can act between them. It passes every test with one user and produces primary key violations, duplicate rows or lost increments under load.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/upsert-race-conditions on GitHub.

What you’ll see

  • Primary key violations (Msg 2627) on an insert the code just checked was safe. The demo got 598 of them in 800 upserts.
  • Duplicate rows where a unique key was missing: 777 rows for 200 keys, with no error at all.
  • Counters that come out short: 52 of 800 increments vanished.
  • A MERGE that fails a few times a day and can’t be reproduced: 3 violations in 80,000.

Why it happens

IF EXISTS (SELECT 1 FROM Demo.Counters WHERE KeyID = @KeyID)
    UPDATE Demo.Counters SET Hits = Hits + 1 WHERE KeyID = @KeyID;
ELSE
    INSERT Demo.Counters (KeyID, Hits) VALUES (@KeyID, 1);

Four sessions all ask “is key 7 there?”, all hear “no”, and all insert it. The same thing happens with read-then-write: two sessions read 5 and both write 6. MERGE is one statement, but it still checks and then inserts, and under the default isolation level nothing stops another session in between. The window is just smaller.

What to do

BEGIN TRANSACTION;
    UPDATE Demo.Counters WITH (UPDLOCK, SERIALIZABLE) SET Hits = Hits + 1 WHERE KeyID = @KeyID;
    IF @@ROWCOUNT = 0
        INSERT Demo.Counters (KeyID, Hits) VALUES (@KeyID, 1);
COMMIT;
  • SERIALIZABLE locks the range where the missing key would go, so no one else can insert it.
  • UPDLOCK makes that lock an update lock, so two sessions can’t both hold it and then deadlock converting it.
  • Or MERGE ... WITH (HOLDLOCK), which is exact for the same reason.
  • When you must read first, read WITH (UPDLOCK) inside the transaction.
  • For a counter, SET Hits = Hits + 1 in one statement doesn’t lose updates.
  • Keep a unique constraint on the key regardless. It turns a silent duplicate into a loud error.

All of these were exact in the demo, even with a deliberate 10 ms pause that broke the unlocked versions.

What doesn’t help

  • “The application checks first.” Without a unique constraint the race is silent, and the duplicates inflate every later update. The total came out higher than the work done.
  • Trusting MERGE to be atomic. It’s rare, not safe.
  • The ORM pattern of load, change and save without a lock or a row version check. It’s the read-then-write lost update.

Check your server

-- Modules in this database that use MERGE or IF [NOT] EXISTS, with no HOLDLOCK/SERIALIZABLE.
-- A text search: it finds candidates to review, not proof.
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS module_name,
       UsesMerge    = CASE WHEN m.definition LIKE '%MERGE %' THEN 1 ELSE 0 END,
       UsesIfExists = CASE WHEN m.definition LIKE '%IF EXISTS%' OR m.definition LIKE '%IF NOT EXISTS%' THEN 1 ELSE 0 END
FROM sys.sql_modules AS m
WHERE (m.definition LIKE '%MERGE %' OR m.definition LIKE '%IF EXISTS%' OR m.definition LIKE '%IF NOT EXISTS%')
  AND m.definition NOT LIKE '%HOLDLOCK%'
  AND m.definition NOT LIKE '%SERIALIZABLE%';

See it happen

The upsert-race-conditions demo runs seven upsert patterns on four sessions at once over the same keys. It counts the rows, totals and errors each left behind, and prices the locking.

Related: deadlock patterns · blocking chains · RCSI and snapshot isolation

Measured on SQL Server 2022 CU25 (16.0.4255.1). The races are timing-dependent, so your counts will differ; which ones are zero shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights