title: SQL Server locks - avoid insertion of duplicate entries
body: <p>After reading a lot of articles and many answers related to the above subject, I am still wondering how the SQL Server database engine works in the following example:</p>
  
  <p>Let's assume that we have a table named t3:</p>
  
  <pre><code>create table t3 (a int , b int);
  create index test on t3 (a);
  </code></pre>
  
  <p>and a query as follow:</p>
  
  <pre><code>INSERT INTO T3
  SELECT -86,-86
  WHERE NOT EXISTS (SELECT 1 FROM t3 where t3.a=-86);
  </code></pre>
  
  <p>The query inserts a line in the table t3 after verifying that the row does not already exist based on the column "a".</p>
  
  <p>Many articles and answers indicate that using the above query there is no way that a row will be inserted twice. </p>
  
  <p>For the execution of the above query, I assume that the database engine works as follow:</p>
  
  <ol>
  <li>The subquery is executed first.</li>
  <li>The database engine sets a shared(s) lock on a range.</li>
  <li>The data is read.</li>
  <li>The shared lock is released. According to MSDN a shared
  lock is released as soon as the data
  has been read.</li>
  <li>If a row does not exist it inserts a new line in the table.</li>
  <li>The new line is locked with an exclusive lock (x)</li>
  </ol>
  
  <p>Now consider the following scenario:</p>
  
  <ol>
  <li>The above query is executed by processor A (SPID 1).</li>
  <li>The same query is executed by a
  processor B (SPID 2).</li>
  <li>[SPID 1] The database engine sets a shared(s) lock</li>
  <li>[SPID 1] The subquery reads the
  data. Now rows are returned.</li>
  <li>[SPID 1] The shared lock is
  released.</li>
  <li>[SPID 2] The database engine sets a
  shared(s) lock</li>
  <li>[SPID 2] The subquery reads the
  data. No rows are return.</li>
  <li>[SPID 2] The shared lock is
  released.</li>
  <li>Both processes proceed with a row insertion (and we get a duplicate entry).</li>
  </ol>
  
  <p>Am I missing something? Is the above way a correct way for avoiding duplicate entries?</p>
  
  <p>A safe way to avoid duplicate entries is using the code below, but I am just wondering whether the above method is correct.</p>
  
  <pre><code>begin tran
      if (SELECT 1 FROM t3 with (updlock) where t3.a=-86)
      begin
          INSERT INTO T3
          SELECT -86,-86
      end
  commit
  </code></pre>