SQL Server 2005 introduces a new isolation level called snapshot isolation, which, like REPEATABLE READ, avoids dirty reads and non-repeatable reads, but unlike with REPEATABLE READ, with snapshot isolation, readers don't block writers. Snapshot isolation is based on a technique called as row versioning, which makes use of the tempdb system database to keep a version of updated rows. With snapshot isolation, if a transaction updates or deletes a row that is already read by another transaction, SQL Server copies the original version of the row to the tempdb database. If the transaction tries to read the row again, instead of reading the row from the user database, SQL Server reads the row from the tempdb database, and hence avoids the non-repeatable read issue; at the same time, it avoids the blocking. This increases the concurrency and data availability and reduces the locking and deadlocking.
Snapshot isolation in SQL Server 2005 is exposed in two ways:
The ALLOW_SNAPSHOT_ISOLATION database option is by default turned off. Even if this option is off, SET TRANSACTION ISOLATION LEVEL SNAPSHOT will succeed, but as soon as you try to perform any DML or SELECT operation, SQL Server raises an error indicating that the database is not enabled for snapshot isolation.
Snapshot isolation is discussed in great detail, including its pros and cons and examples, in Chapter 9, "Performance Analysis and Tuning."