SQL Server locks - avoid insertion of duplicate entries -

After reading so many articles and many answers from the above topic, I am still wondering how the SQL Server database engine works In the following example:

Suppose we have a table named T3:

  make table T3 (an integer, BIT); Create an index test on T3 (A);  

and follow as a query:

  Select T3 is not present in -86, -86, where it is not present ( Selection 1 to T3 where t3.a = - 86);  

After verifying that the row does not already exist based on the query "t", the table contains a row in t3.

Many articles and replies indicate that there is no way to use the above query that a line will be inserted twice

The above query For the execution of, I think the database engine follows:

1. The subkey is executed first.
2. The database engine sets a shared lock on one share
3. Data is read.
4. Shared lock has been issued. According to MSDN, a shared lock is released such as data is read.
5. If a row is not present, then it contains a new row in the table.
6. The new line is

Consider the following scenario:

1. The above queries are executed by Processor A (SPID1) is done.
2. The same query is executed by Processor B (SPID 2). [SPID 1] Database Engine sets a shared lock
3. [SPID 1] reads the subquery data [SPID 1] The locked lock is released. [SPID 2] Database Engine sets a shared lock
4. [SPID 2] reads sub-section data
5. By releasing shared lock are given.
6. Both processes proceed with a line entry (and we get a duplicate entry).

Am I missing something? What is the right way to avoid duplicate entries?

A safe way to avoid duplicate entries is using the code below, but I am thinking that the above method is correct or not.

  Start the tran if you select T3 to T3 (UpdateLock) with (T3.a = -86) T3 SELECT-86, -86 end committed INSERT   
 
 
 To keep the lock between multiple statements, they have to be wrapped in transactions. In your example: 
 
  Select T3 from (T3.a = -86) -86, -86  
 
 Insert updates before execution Lock can be released. It will work reliably: 
 
  Start the transaction if T3 with T3 (T3.a = -86) (select T3 with T3) T3 SELECT-86, -86 Committed transactions INSERT  
 
 Single statements are always wrapped in a transaction, so it will also work: 
 
  Select IN3ER T3 -86, -86 where is not present (from where to select T3 (UpdateLock) where t3.a = -86)  
 
 (It is assumed that you have "underlying transaction" closed Is, as the default SQL Server setting.) < / P>