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?

Martin Smith · Accepted Answer · 2011-01-23T22:50:09.130

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.

Could you also elaborate why the answer would change for Q2? The way I see it happening is: at time T1, 'from' attempts to read row from table, but it is locked by 'update', and so it reads an older version ('foo'); at time T2, update completes and releases lock; at time T3, join attempts to read row, and the row is not locked, but has the new value ('bar'), and so reads it to show the inconsistent result. Is this possible, or is it guaranteed that once an older version of a row is read, the new version will never be read in read_committed_snapshot mode? Appreciate much. — Raghu Dodda, Jan 23 '11 at 21:18
@Raghu - In RCSI mode it would be possible to read two different values within the same transaction (if the transaction had multiple statements) but not within the same statement. 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. — Martin Smith, Jan 23 '11 at 22:19

Read Committed Isolation level in SQL server for a single statement

1 Answers1

Setup Script

Connection 1

Connection 2

Results