title: SELECT returning really old data
body: <p>We have an SQL server instance that gets used by 3 application servers. The other day, one of those 3 servers seemingly got stale data from the database, whereas the other 2 servers were fine.</p>
  
  <p>The query is literally this:</p>
  
  <pre><code>SELECT a, b, c, State FROM t WITH(NOLOCK);
  </code></pre>
  
  <p>The <code>State</code> of the rows get updated once a day, but more than one hour after the change, above query returned old values for <code>State</code>, but only for that one server.</p>
  
  <p>I know <code>WITH(NOLOCK)</code> means the DB can return uncommitted data, it can omit and even duplicate rows. But surely not 1 hour after a transaction was committed and read successfully by the other clients!?</p>
  
  <p>I'm at a loss here - is there any explanation for this? Any idea how to debug this further?</p>
  
  <p>The only idea I have come up with so far is an <code>SqlConnection</code> coming from the pool with some dirty state on it ("ideally", in a "Read committed" transaction that had read the table in question before its state changed). So far, it's a wild theory, and I wouldn't expect a transaction to stay alive for more than an hour, but according to the logs, earlier that day 4 queries timed out after (the custom timeout interval of) 120 seconds, so that leaves hope for some connections to have been put back into the pool in an unclean state...</p>
  
  <p><strong>Update</strong> All my attempts to create a dirty connection in the pool that could get reused and show the expected behavior have failed so far. Even when an <code>SqlConnection</code> with a transaction with isolation level repeatable-read is used, the <code>WITH(NOLOCK)</code> will override the isolation level and read uncommitted values, so my idea does not seem to get me any closer to reproducing the strange behavior.</p>