Read Committed Isolation level in SQL server for a single statement?

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).

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.

Exceptional. Thank you. – Raghu Dodda Jan 23 at 20:56 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 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 at 22:19.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.