title: The endless debate: atomic inserts, locking, & transactions in SQL Server. It ends here
body: <p><strong>Edit:</strong> My question is, "Why does my first code example work?" Please read on...</p>
  
  <p><strong>Edit1:</strong> There is no doubt that a unique constraint is the correct way to ensure duplicates don't happen. This is a given. However, sometime we need to know that we're attempting a duplicate entry. Further, this post goes beyond merely handling duplicates.</p>
  
  <p>This is potentially a repeat of probably over 100 questions on SO. I've read an endless array of confusions and contradictions about the simple questions of atomic updates, locks, and concurrency.</p>
  
  <p>I see that blogs and experts disagree widely on these points. Here I provide test code based on the various solutions people have advised, indicate the results, state my views, and invite your comment.</p>
  
  <p>Context: I'm running SQL Server 2008 Express SP2.</p>
  
  <p>I created the following test table:</p>
  
  <pre><code>create table dbo.Temp (Col int)
  </code></pre>
  
  <p>The table deliberately has no constraints on it as we want to test the SQL code ideas, not constraints.</p>
  
  <p>I ran the following concurrently in 2 and then 3 query windows:</p>
  
  <pre><code>declare @i int
  set @i = 0
  
  while @i &lt; 5000 begin
      set @i = @i + 1
      update dbo.temp set Col = (SELECT Col from dbo.Temp) + 1
  end
  </code></pre>
  
  <p>I did not use any explicit locking, as can be seen. All DB settings are default. I checked the value of Col and it was the desired number: 25,000. Nothing missed.</p>
  
  <p>Since SQL Server is ACID, the "A" tells us that a single statement is executed atomically. Therefore, based on the above, we could agree with those who say that locks are not needed for a simple update as above.</p>
  
  <p>Next, I ran the following concurrently in 3 query windows:</p>
  
  <pre><code>while @i &lt; 5000 begin
      set @i = @i + 1
      insert into dbo.temp select @i where not exists
        (select 1 from dbo.temp where Col = @i)
  end
  </code></pre>
  
  <p>The results were <strong>not</strong> correct, despite the fact this is one statement. There were missing values, duplicate values, and > 5k rows.</p>
  
  <p>Next, I ran the following popular solution concurrently in 3 query windows:</p>
  
  <pre><code>declare @i int
  set @i = 0
  
  while @i &lt; 5000 begin
      set @i = @i + 1
      insert into dbo.temp select @i where not exists 
        (select 1 from dbo.temp with (updlock) where Col = @i)
  end
  </code></pre>
  
  <p>The results were <strong>not correct</strong>. There were missing values, duplicate values, and > 5k rows.</p>
  
  <p>Next, for those who have doubts that SQL Server implicitly wraps single statements in a transaction (aka "A" in ACID):</p>
  
  <pre><code>declare @i int
  set @i = 0
  
  while @i &lt; 5000 begin
      set @i = @i + 1
      begin tran
      insert into dbo.temp select @i where not exists 
        (select 1 from dbo.temp with (updlock) where Col = @i)
      commit tran
  end
  </code></pre>
  
  <p>Same <strong>incorrect</strong> results.</p>
  
  <p>Next, I ran the following concurrently in 3 query windows:</p>
  
  <pre><code>declare @i int
  set @i = 0
  
  while @i &lt; 5000 begin
      set @i = @i + 1
      insert into dbo.temp select @i where not exists 
        (select 1 from dbo.temp with (XLOCK, ROWLOCK) where Col = @i)
  end
  </code></pre>
  
  <p>This <strong>worked</strong>. 5k unique values only.</p>
  
  <p>Next, the following in 3 windows:</p>
  
  <pre><code>declare @i int
  set @i = 0
  
  while @i &lt; 5000 begin
      set @i = @i + 1
      merge dbo.temp as t
      using (select @i) as test (Col)
      ON (t.Col = test.Col)
      when not matched then
          insert values (@i);
  end
  </code></pre>
  
  <p>This <strong>worked</strong>. 5k unique values only.</p>
  
  <p>My conclusions are:</p>
  
  <ol>
  <li>SQL Server is atomic for <em>single SQL operations</em>, not <em>groups of operations that appear as one statement</em>.</li>
  <li>UPDLOCK is <em>not</em> effective, as shown and as suggested by many, at providing the required locking needed to guarantee update integrity in a contentious environment.</li>
  <li>From a locking perspective, only XLOCK guarantees <em>concurrency integrity</em> and <em>atomicity in multi-statement operations as shown</em>.</li>
  <li>The MERGE command is a single command, and therefore is atomic.</li>
  </ol>
  
  <p>Please test this for yourself.</p>
  
  <p>Now, can someone explain to me why my first example worked? :-)</p>