Read Consistency behavior in SQL Server 2000 and 2005 with isolation level
Posted on 5/29/2007 5:21:59 AM
in #SQL SERVER
Hi
An interesting question came up, when I was discussing some point on database isolation level with my colleague. The point was that to get a read – consistent view to all the transactions in the database what isolation should be there.
The point got more interesting when we found that there is a difference in how this works in SQL Server 2000 and SQL Server 2005.
In SQL server 2000 we get the read consistent view to all transaction in the database only in the SERIALIZABLE isolation level. Any other isolation level we can perceive uncommitted dependency (also called dirty reads), non-repeatable reads or Phantom reads.
But in SQL Server 2005 we will get read consistent view to all transaction in the database in both SERIALIZABLE and SNAPSHOT isolation level.
Thanks
Vikram
|