title: SQL Server Read Uncommitted is blocking transactions
body: <p>I have a work queue in SQL, managed by some services that read out entries to process with <code>READPAST</code> queries.</p>
  
  <p>I recently added a UI to check on queue status that uses <code>READ UNCOMMITTED</code> NHibernate queries in C#. It's not at all critical that they give me the right numbers back, just an indication of how far along the processing is.</p>
  
  <pre class="lang-cs prettyprint-override"><code>/* called from a foreach (I know... I could get them all at once with SQL) */
  IQuery query = Persistence.CreateQuery(
      "select count(qi) from QueueItem qi where qi.Job = :job");
  query.SetEntity("job", thisJob);
  using(Persistence.ReadUncommitted())
  {
      return (long)query.UniqueResult();
  }
  </code></pre>
  
  <p>The problem is that these status queries have started causing timeouts. Sometimes they fail themselves; sometimes they cause queue operations to fail.</p>
  
  <p>The <a href="http://msdn.microsoft.com/en-us/library/ms187373.aspx" rel="nofollow">main MSDN documentation</a> says there can still be locking if I change the schema, which I am not.</p>
  
  <p>On the other hand, <a href="http://social.msdn.microsoft.com/profile/marcel%20van%20der%20holst%20-%20msft/?ws=usercard-mini" rel="nofollow">Marcel van der Holst</a> who apparently works for Microsoft's SQL Server team, <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-us/068d3fc7-648a-4eaa-b2c2-be0e8e26ad82/why-does-read-uncommitted-still-seem-to-block-other-transactions" rel="nofollow">says this</a> on the subject (the Michael asking the question is not me):</p>
  
  <blockquote>
    <p><code>READ UNCOMMITTED</code> transactions will not take any database locks, but will still have to read databases pages to read the actual data. If other transactions are writing these pages at the same time, their might be some blocking between the two. 
    Internally in the engine, we do not allow any transactions to read a page while a write is being in progress (we use latches to guarantee this). If a lot of transactions are writing while your big queries are going on, the big read might still become blocked.</p>
  </blockquote>
  
  <p>Am I doing something wildly wrong? What should I change to stop blocking?</p>
  
  <h1>Schema</h1>
  
  <pre><code>create table QueueItem(
      ID int identity(1,1) not null,
      JobID int not null,
      PersonID int not null,
      DateProcessed datetime null,
      Error varchar(max) null,
      constraint [PK_QueueItem] primary key nonclustered (ID)
  )
  
  alter table QueueItem
  add constraint [FK_QueueItemsToJobs] foreign key (JobID)
  references Job (ID)
  </code></pre>
  
  <p>Nonclustered indices:</p>
  
  <pre><code>JobID, DateProcessed, PersonID, ID (Non-Unique, Non-Clustered)
  DateProcessed, JobID, PersonID (Non-Unique, Non-Clustered)
  JobID, ID (Unique, Non-Clustered)
  JobID, PersonID, ID (Unique, Non-Clustered)
  PersonID, JobID, ID, DateProcessed (Unique, Non-Clustered)
  ID (Unique, Non-Clustered)
  </code></pre>
  
  <h1>In Practice</h1>
  
  <p>I have reduced the number of reads because it's not going to make things slower, but I'm still curious why there might be blocking with <code>READ UNCOMMITTED</code>.</p>