title: Strange Locking Behaviour in SQL Server 2005
body: <p>Can anyone please tell me why does the following statement inside a given stored procedure returns repeated results even with locks on the rows used by the first <code>SELECT</code> statement?</p>
  
  <pre><code>BEGIN TRANSACTION
  
  DECLARE @Temp TABLE ( ID INT )
  
  INSERT INTO @Temp SELECT ID FROM SomeTable WITH (ROWLOCK, UPDLOCK, READPAST) WHERE SomeValue &lt;= 10
  INSERT INTO @Temp SELECT ID FROM SomeTable WITH (ROWLOCK, UPDLOCK, READPAST) WHERE SomeValue &gt;= 5
  
  SELECT * FROM @Temp
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p>Any values in <code>SomeTable</code> for which <code>SomeValue</code> is between 5 and 10 will be returned twice, even though they were locked in the first <code>SELECT</code>. I thought that locks were in place for the whole transaction, and so I wasn't expecting the query to return repeated results. Why is this happening?</p>