Snapshot Isolation in SQL Server 2005, Part II

art I of this article (see "Snapshot Isolation in SQL Server 2005," July/August, Volume 5, Issue 4) described the transaction-based SNAPSHOT isolation level. At the time Part I was written, just prior to the release of Beta 2, only the SNAPSHOT isolation level was fully documented. With the release of SQL Server 2005 Beta 2, a fuller explanation of a second type of SNAPSHOT isolation behavior has come to light. The READ_COMMITTED_SNAPSHOT option is a supplement to, or an alternative way of using, the default isolation level of READ COMMITTED. When a database has the READ_COMMITTED_SNAPSHOT option set to ON, and a SELECT statement is issued, if any of the SELECT's underlying data is changed during its execution, the SELECT statement reads unchanged versions of changed rows from versioned data stored in the tempdb database. The result is that the SELECT statement returns a consistent snapshot of the data as of the starting time of the query. Because the SELECT statements do not issue any shared locks on tables, they do not block other statements, nor are they blocked.

The alternative approach to row-versioned, or snapshot, data is called READ_COMMITTED_SNAPSHOT. It is a database-wide option, not an isolation level. Once the option is set, it provides a way for SELECT queries to read data that is consistent as of the time of the start of a single-statement query, no matter how long the query takes, and without causing any blocking.

Author's Note: The information in this article is based on the Community Technology Preview of SQL Server 2005 Beta 2, which is build 9.00.951. Expect that some of the details about the operation and behavior of snapshot isolation will change in the final release of SQL Server 2005.)

Two Forms of SNAPSHOT Isolation Behavior in SQL Server 2005
It's important to distinguish the READ_COMMITTED_SNAPSHOT option from the SNAPSHOT isolation level. As discussed in Part I of this article, the SNAPSHOT isolation level is a new and distinct isolation level, on a par with the legacy SQL Server isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE). All the isolation levels other than the default READ COMMITTED level are usually set at the session level in transactions, although locking hints can sometimes produce the same effect within a single query. SQL Server 2005 gives us a new isolation level, SNAPSHOT, resulting in a total of five isolation levels available for transactions.

READ_COMMITTED_SNAPSHOT, on the other hand, is not a new transaction isolation level. Rather, READ_COMMITTED_SNAPSHOT is a database option that changes the behavior of the default READ COMMITTED isolation level. It is not set for a given session or transaction, but applies database-wide as soon as it is set.

Next Page