Question about Transaction isolation level="readCommitted"

[D.]

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?

Thanks,
D.

[Dan Guzman]

Is this behavior correct?

In order for the SELECT user to get the old data version in the
READ_COMMITTED isolation level, the READ_COMMITTED_SNAPSHOT database option
needs to be turned on. This was introduced in SQL 2005.
--
Hope this helps.

Dan Guzman
SQL Server MVP

"D." <[e-mail] in message
news:f3**********@newsreader.mailgate.org...

>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?

Thanks,
D.

[Erland Sommarskog]

D. ([e-mail]) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Robert Klemme]

On 23.05.2007 12:50, D. wrote:

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. Adding to the other replies: you should not expect SQL Server to
behave similarly to Oracle. Although TX isolation names are identical
there are still different ways to implement them. On Oracle the update
would not block the read but instead the read will see the old version
of the record.

Kind regards

robert