SQL Server › Merge Statement (Upsert)

What are some known issues or caveats with the MERGE statement that developers should be aware of before relying on it heavily?

Intermediate

Answer

The MERGE statement has historically had a few documented bugs related to trigger behavior and concurrency under certain isolation levels, and Microsoft has recommended being cautious in some high concurrency scenarios, so many teams thoroughly test MERGE statements under realistic concurrent load or consider separate INSERT and UPDATE statements wrapped in a transaction as an alternative for critical systems.

-- Some teams prefer explicit separate statements
-- for critical, high concurrency scenarios
BEGIN TRANSACTION;
UPDATE Products SET Price = @Price WHERE ProductId = @Id;
IF @@ROWCOUNT = 0
  INSERT INTO Products (ProductId, Price) VALUES (@Id, @Price);
COMMIT TRANSACTION;

Real-world example A team building a high concurrency payment system chooses to use separate, carefully tested INSERT and UPDATE statements instead of MERGE, after reading about known edge case issues with MERGE under certain concurrent conditions.

Common follow-ups: What specific concurrency issues have been documented with MERGE in the past?;How do you decide whether MERGE is safe enough for a specific use case?

Isolation & Locking;Transactions & ACID