title: merge statement duplicate key bug
body: <p>I thought MERGE was an atomic insert/update operation, but running a test with multiple threads calling my sproc doing upsert I am running into duplicate key constraint violation, the sproc is not all that difficult</p>
  
  <pre><code>-- @someVal, @val1, @val2, @val3 are params passed to my sproc
  -- nothing fancy going on there (with the params)
  -- where MyVal is a unique non-clustered index
  MERGE dbo.MyTable T
  USING (@someVal [SomeVal]) S
      ON T.MyVal = S.SomeVal
  WHEN MATCHED THEN 
      UPDATE
      SET A = @val1
          ,B = @val2
          ,C = @val3
  WHEN NOT MATCHED THEN 
      INSERT (MyVal, A, B, C)
      VALUES (@someVal, @val1, @val2, @val3)
  </code></pre>
  
  <p>Yet I get the following exception, the only way to get around this has been to either remove the unique index, or add a retry into the sproc.</p>
  
  <blockquote>
    <p>System.Data.SqlClient.SqlException (0x80131904): Cannot insert
    duplicate key row in object 'dbo.MyTable' with unique index
    'UIX_MyUniqueConstraint'. The duplicate key value is
    (03414D0B-15D2-4AFA-BB7F-7359BB95668A).</p>
  </blockquote>
  
  <p>When testing without the unique index, I ran a dupe check which turned nothing, confirming the upsert did what it was supposed to and no dupes were actually inserted</p>
  
  <pre><code>SELECT MyVal, COUNT(1)
  FROM dbo.MyTable
  GROUP BY MyVal
  HAVING COUNT(1) &gt; 1
  </code></pre>
  
  <p>Is this a known bug in SQL Server 2008 R2 and prior versions, or am I doing something wrong?</p>
  
  <p>I found this <a href="http://connect.microsoft.com/SQLServer/feedback/details/434632/cannot-insert-duplicate-key-row-in-object-xxx-with-unique-index-yyy" rel="nofollow">connect issue</a> which is very similar to my issue and it appears they fixed it in SQL Server 2012 but not in prior version</p>