Sql Merge statement causes duplication while multithreading

Question

I have a merge statment that performs an upsert based on a key. (for simplification, we will just call it RefId). This is to ensure that the table is unique by RefId. However, in production we have multiple servers that insert into this table using this stored proc, and if two servers insert using the same RefId at very close intervals, duplication occurs (i.e 2 inserts) instead of 1 insert and update. I believe it is because, SQL server locks the newly inserted row and the other parallel called stored proc is unable to detect it's existence. NoLock is not supported for the MERGE statement so there's no obvious workaround that I can see. I have simulated parallel inserts using multiple threads (instead of servers) and ocassionally duplication occurs in this case as well. Other than enforcing a unique constraint on the DB, (which I can't for reasons not in my control) is there any way I can get my upsert to work as expected in concurrent cases?

Here's the stored proc (it works correctly when the insert-updates are made with a reasonable timing difference, just fails in parallel cases)

WITH UniqueData AS 
    ( SELECT * FROM 
      ( SELECT *, rank() over ( PARTITION BY RefId ) AS UniqueRank 
          FROM @Data  
      ) AS Ranked WHERE UniqueRank=1 
    )
  MERGE MyTable AS destination
  USING UniqueData AS source
  ON ( destination.RefId = source.RefId)
  WHEN NOT MATCHED THEN
    INSERT (RefId, Miles,UpdateUTC)
    VALUES( source.RefId, source.Miles, getutcdate())
  WHEN MATCHED THEN
    UPDATE SET 
               destination.Miles = ISNULL(source.Miles , destination.Miles ),
               destination.UpdateUTC = getutcdate()   

Answer 1

You can use WITH (HOLDLOCK)

...
 MERGE MyTable WITH (HOLDLOCK) AS destination
...

And you can read the details here: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx