Posted by Erland Sommarskog on 05/23/07 22:06

D. (d@hotmail.com) writes:
> I have a question about the "readCommitted" transaction isolation level.
>
> I have a client that is updating a record on a table.
> I suspend the execution after the UPDATE but before the commit statement.
>
> Than another client is trying to read the same record.
>
> As transaction isolation is set to "readCommited" I expected that the
> second client will read the old version of the record (before the
> update). Instead, the second client hangs and wait until the first
> client do the commit.
> I expect this behavior if transaction isolation is set to "serializable"
>
> Is this behavior correct?

Yes, it is correct. Permit me to expand a bit on Dan's answer. In SQL 2000,
there are four isolation levels:

READ UNCOMMITTED - locks are not honored, and you would be able to read
the update uncommitted value.

READ COMMITTED - all values you read are committed, but once a row has
been read, the lock is released. An attempt to read a row that is updated,
but not committed will block.

REPEATABLE READ - you can only read committed values. Furthermore you
are guaranteed that if you read the same row twice in the same transaction
you will get the same result.

SERIALIZABLE - In addition to REPEATABLE READ, you are also guaranteed
that if you read a range twice, you will get the same result. This includs
reading open-ended ranges for things like MAX and MIN.

To this comes a special twist of READ COMMITTED in the READPAST hint,
where you simply skip locked rows. In SQL 2000 you can never get the
previous value, because there is no previous value to read.

SQL 2005 adds one more isolation level, SNAPSHOT and a twist to READ
COMMITTED. In SNAPSHOT isolation you get the data the way it looked
when you transaction started. With READ COMMITTED SNAPSHOT you get
the behaviour you ask for: if a row is locked, you will get the old
value. To use SNAPSHOT isolation, the database must be configured
for this. Furthermore, it's a database setting whether READ COMMITTED
is with SNAPSHOT or not.

Finally, I like to point out that snapshot isolation is not always the
right thing. The system I work with not go in to would not work correctly
with READ COMMITTED SNAPSHOT:



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]