title: SQL Server 2008 R2 - UPDATE with SELECT on same table as part of query
body: <p>I've been trying for weeks to figure out an issue that happens once every 100,000+ transactions. I've tried dozens of variations and have run out of ideas, so I'm hoping someone has seen this before.</p>
  
  <p>In summary, I have a table that acts like a queue. Records are inserted either singly or in transaction'ed batches, and sometimes one record is "dependent" on another (so that it is not eligible to be removed from the queue until the record it's dependent on is first removed). The basic structure of the tables includes these columns:</p>
  
  <ul>
  <li><code>item_id</code> - a GUID that uniquely identifies the record</li>
  <li><code>depend_id</code> - a GUID that identifies the record that this record is dependent on (or NULL if it's not dependent on anything)</li>
  <li><code>item_lock</code> - a GUID that starts out NULL, but is set to the "owner process" when the record is "locked down" to be worked on (when the work is done, the record is deleted from the table)</li>
  </ul>
  
  <p>A simplified version of the polling query that is called to "lock down" the next "ready" record is:</p>
  
  <pre><code>UPDATE TOP(1) Q1 SET
    lock_id = @lock,
    FROM item_queue Q1
  WHERE (lock_id IS NULL)
  AND (depend_id IS NULL OR depend_id NOT IN (SELECT item_id FROM item_queue))
  AND execute_at &lt; GETUTCDATE()
  </code></pre>
  
  <p>My objective here (and this works almost all of the time) is that the NOT IN SELECT will simply check to see if the item that is otherwise eligible to have its lock_id set, that it won't be chosen if it's depend_id matches another item that's still in the table. But 1 out of 100,000+ calls to the stored procedure, that constraint fails and the record with the depend_id that does match an item_id that's still in the table gets chosen.</p>
  
  <p>I have tried various alternatives to the NOT IN SELECT; all methods "work" but all fail in the same way. It is always the case that "dependent" records are inserted with their dependencies within a committed transaction.</p>
  
  <p>Any and all ideas welcomed...I'm stumped.</p>
  
  <p>PS - I should mention that there are many different threads on different client machines adding to and polling/locking/deleting records in this table. One of my working theories is that there is some sort of locking/contention that occasionally causes the record that is being depended on to not show up in the NOT IN SELECT subquery, causing the dependent record to become eligible (but I have not been able to come up with the specific scenario for that to happen).</p>
  
  <p><strong>EDIT:</strong> More on transaction isolation level: I'm running with the default READ COMMITTED isolation level. Is it possible that this is causing the "depended on" record to be omitted from the NOT IN SELECT subquery in the "race condition" case where another thread has just updated it? If so, I'm not entirely clear on what isolation level I need to ensure that any record that's still in the table (whether it's being updated or not) comes back in that query.</p>