Read Committed Isolation level in SQL server for a single statement

Question

Say, I have a person table and it has only 1 row -

id = 1, name = 'foo'

On one connection

select p1.id, p1.name, p2.name
from person p1 
join person p2 on p1.id = p2.id

On another connection at the same time:

update person set name = 'bar' where person.id = 1

Q1: Is it ever possible, for my select to return a result like this based on the timing of the update statement:

id = 1, p1.name = 'foo', p2.name = 'bar'

Neither connection uses an explicit transaction and both use default transaction isolation level READ COMMITTED.

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?

Q2: Would the answer to the question change if the set read_committed_snapshot on is set on the db?

Answer 1

Q1: Yes this is perfectly possible at least in theory. read committed just guarantees you do not read dirty data it makes no promises about consistency. At read committed level shared locks are released as soon as the data is read (not at the end of the transaction or even the end of the statement)

Q2: Yes the answer to this question would change if read_committed_snapshot is on. You would then be guaranteed statement level consistency. I'm finding it hard finding an online source that unambiguously states this but quoting from p.648 of "Microsoft SQL Server 2008 Internals"

A statement in RCSI sees everything committed before the start of the statement. Each new statement in the transaction picks up the most recent committed changes.

Also see this MSDN blog post

Setup Script

CREATE TABLE person 
(
id int primary key,
name varchar(50)
)

INSERT INTO person
values(1, 'foo');

Connection 1

while 1=1
update person SET name = CASE WHEN name='foo' then 'bar' ELSE 'foo' END

Connection 2

DECLARE @Results TABLE (
  id    int primary key,
  name1 varchar(50),
  name2 varchar(50))

while( NOT EXISTS(SELECT *
                  FROM   @Results) )
  BEGIN
      INSERT INTO @Results
      Select p1.id,
             p1.name,
             p2.name
      from   person p1
             INNER HASH join person p2
               on p1.id = p2.id
      WHERE  p1.name <> p2.name
  END

SELECT *
FROM   @Results  

Results

id          name1 name2
----------- ----- -----
1           bar   foo

Looking at the other join types in Profiler it appears that this issue could not arise under either the merge join or nested loops plan for this particular query (no locks get released until all are acquired) but the point remains that read committed just guarantees you do not read dirty data it makes no promises about consistency. In practice you may well not get this issue for the exact query you have posted as SQL Server would not choose this join type by default. However you are then just left relying on implementation details to produce the behaviour that you want .

NB: If you were wondering why some row level S locks appear to be missing this is an optimisation explained here.