I mentioned in Conditional INSERT/UPDATE Race Condition that most “UPSERT” code is defective and can lead to constraint violations and data integrity issues in a multi-user environment .  In this post, I’ll show how to prevent duplicate key errors and data problems with the MERGE statement too.  You might want to peruse Conditional INSERT/UPDATE Race Condition before reading this for a background on these concurrency concerns.

Background on MERGE

Microsoft introduced the ANSI-standard MERGE statement in SQL Server 2008.  MERGE is very powerful in that it can perform multiple actions in a single statement that previously required separate INSERT/UPDATE/DELETE statements.  MERGE is also a good alternative to the proprietary UPDATE…FROM syntax allowed in the T-SQL dialect.

MERGE can (and in my opinion should) be used to address the requirement to either INSERT or UPDATE depending on whether the source data already exists.  One need only include the MERGE statement clauses WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT in order to take the proper action, all within a single statement.

 “UPSERT” MERGE Concurrency Test

Even though MERGE provides the means to perform multiple actions within a single statement, developers still need to consider concurrency with MERGE to prevent errors and data issues.  Let me illustrate using the table and stored procedure that I originally posted in Conditional INSERT/UPDATE Race Condition:

I ran the script below from 2 different SSMS windows after changing the time to the near future so that both executed at the same time.  My test box had a single quad-core processor with SQL Server 2008 Developer Edition installed, which I expected to have enough multi-processing power to create the error.

I got a primary key violation error, showing that MERGE is vulnerable to concurrency problems like a multi-statement conditional INSERT/UPDATE technique. However, I couldn’t reproduce the error with MERGE nearly as consistently as I could with the conditional INSERT/UPDATE in Conditional INSERT/UPDATE Race Condition.  This could be due to a number of reasons (e.g. faster processor, different SQL Server version, MERGE locking behavior) but I wanted to make sure I could reproduce the error reliably.  I created a more robust test to exercise MERGE on a loop:

I ran the script below from 4 different SSMS windows after changing the time to the near future so that all executed at the same time.   

I was able to reproduce the primary key violation every time with this test script.

Addressing the MERGE Race Condition

The underlying issue with any conditional insert technique is that data must be read before the determination can be made whether to INSERT or UPDATE.  To prevent concurrent sessions from inserting data with the same key, an incompatible lock must be acquired to ensure only one session can read the key and that lock must be held until the transaction completes.

I showed how one might address the problem using both UPDLOCK and HOLDLOCK locking hints in Conditional INSERT/UPDATE Race Condition.  MERGE is slightly different, though.  I repeated the test with only the HOLDLOCK hint added:

This test showed that simply adding the HOLDLOCK hint prevented the primary key violation error.  Unlike the conditional INSERT/UPDATE in Conditional INSERT/UPDATE Race Condition, MERGE acquired a key update lock by default so UPDLOCK was not needed.  Also, in contrast the multi-statement conditional INSERT/UPDATE technique, no explicit transaction is required because MERGE is an atomic DML statement.  The HOLDLOCK hint was still needed, though, because MERGE otherwise releases the update key lock before the insert.  I gleaned this by examining the locks from a Profiler trace of the MERGE without the HOLDLOCK:

If another concurrent MERGE of the same key occurs after the update lock is released and before the exclusive key lock is acquired, a duplicate key error will result.

The trace below of the MERGE with the HOLDLOCK hint shows that locks aren’t released until the insert (and statement) completes, this avoiding the concurrency problem with MERGE.