title: Sql Server Ignore rowlock hint
body: <p>This is a general question about how to lock range of values (and nothing else!) when they are not exists in table yet. The trigger for the question was that I want to do "insert if not exists", I don't want to use <code>MERGE</code> because I need to support SQL Server 2005.</p>
  
  <p>In the first connection I:</p>
  
  <ol>
  <li><code>begin transaction</code></li>
  <li>select data from a table using <code>(SERIALIZABLE, ROWLOCK)</code> + where clause to respecify range</li>
  <li>wait...</li>
  </ol>
  
  <p>In the second connection, I insert data to the table with values that <strong>do not</strong> match the where clause in the first connection</p>
  
  <p>I would expect that the second connection won't be affected by the first one, but it finishes only after I commit (or rollback) the first connection's transaction.</p>
  
  <p>What am I missing?</p>
  
  <p>Here is my test code:</p>
  
  <p>First create this table:</p>
  
  <pre><code>CREATE TABLE test
  (
      VALUE nvarchar(100)
  )
  </code></pre>
  
  <p>Second, open new query window sql server managements studio and execute the following:</p>
  
  <pre><code>BEGIN TRANSACTION;
  SELECT *
  FROM  test WITH (SERIALIZABLE,ROWLOCK)
  WHERE value = N'a';
  </code></pre>
  
  <p>Third, open another new query window and execute the following:</p>
  
  <pre><code>INSERT INTO test VALUES (N'b');
  </code></pre>
  
  <p>Notice that the second query doesn't ends until the transaction in the first window ends</p>