title: Read Committed Isolation level in SQL server for a single statement
body: <p>Say, I have a person table and it has only 1 row - </p>
  
  <pre><code>id = 1, name = 'foo'
  </code></pre>
  
  <p>On one connection </p>
  
  <pre><code>select p1.id, p1.name, p2.name
  from person p1 
  join person p2 on p1.id = p2.id
  </code></pre>
  
  <p>On another connection at the same time:</p>
  
  <pre><code>update person set name = 'bar' where person.id = 1
  </code></pre>
  
  <p><strong>Q1:</strong> Is it ever possible, for my select to return a result like this based on the timing of the update statement:</p>
  
  <pre><code>id = 1, p1.name = 'foo', p2.name = 'bar'
  </code></pre>
  
  <p>Neither connection uses an explicit transaction and both use default transaction isolation level READ COMMITTED.</p>
  
  <p>The question is really is to help me understand, whether the locks acquired at the beginning of a sql statement continue to exist until the statement completes, or if it is possible for a statement to  release the lock and re-acquire the lock for the same row if it is used twice in the same statement? </p>
  
  <p><strong>Q2:</strong> Would the answer to the question change if the <code>set read_committed_snapshot on</code> is set on the db?</p>