title: ORDER BY and WITH(ROWLOCK, UPDLOCK, READPAST)
body: <p>I need to set up a queue system using some SQL tables, like the one described <a href="https://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue">here</a>. That being, and since I need to filter queued items by different critera, inside a stored procedure I am using</p>
  
  <pre><code>BEGIN TRANSACTION
  
  CREATE TABLE #Temp (ID INT, SOMEFIELD INT)
  
  INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition1
  
  INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition2
  
  (...)
  
  UPDATE TableB SET SomeField = 1 FROM TableB WITH (ROWLOCK, READPAST) WHERE ID IN (SELECT ID FROM #Temp)
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p>I am using <code>ROWLOCK</code> in the first table and <code>UPDLOCK</code> in the second because, after this select, I am going to update <code>TableB</code> only, though I need to make sure that these lines don't get updated in<code>TableA</code> by any other concurrent query. Everything goes well until the point where I need to insert an <code>ORDER BY</code> clause in any of the <code>SELECT</code>s above, so that only very specific  IDs get selected (I must really do this). What happens is:</p>
  
  <p>1) Without <code>ORDER BY</code>, two concurrent executions execute as desired, returning different and non-overlapping results; however, they don't return the results I want because those precise results were outside the scope of every <code>SELECT</code> statement.</p>
  
  <p>2) Using <code>ORDER BY</code> and two concurrent executions, only the first one returns results. The second one does not return anything.</p>
  
  <p>I recall seeing on a blog that for these kind of queries with <code>WITH (ROWLOCK, READPAST)</code> and <code>ORDER BY</code> to work one needs to create indexes on the fields one is using in the ordering. I tried it, but I got the same results. How can I get past this problem?</p>
  
  <p><strong>Edit:</strong> For example, if I have a table <code>TestTable</code> with fields (TestID INT, Value INT) and values "(1,1), (2,2), ..." and execute "simultaneously"</p>
  
  <pre><code>BEGIN TRANSACTION
  
  SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST)
  
  WAITFOR DELAY '00:00:05'
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p>the first execution returns lines (1,2) and the second one returns (3,4) as espected. However, if I execute</p>
  
  <pre><code>BEGIN TRANSACTION
  
  SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST) ORDER BY VALUE ASC
  
  WAITFOR DELAY '00:00:05'
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p>the first one returns (1, 2) and the second returns nothing. Why is this?!</p>