Snapshot Isolation

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:

• By turning on the READ_COMMITTED_SNAPSHOT database option. Then the default READ COMMITTED isolation level makes use of row versioning instead of locks to protect transactions from dirty reads and to support repeatable reads. Once the READ_COMMITTED_SNAPSHOT database option is turned on, all the READ COMMITTED TRansactions in that database use row versioning to run in snapshot isolation mode.

• By turning on the ALLOW_SNAPSHOT_ISOLATION database option. Then you set the session's isolation level to snapshot isolation by running the SET TRANSACTION ISOLATION LEVEL SNAPSHOT T-SQL statement. With this, only the transactions in the current session are run in snapshot isolation mode.

Note

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