In addition to the transaction-based SNAPSHOT isolation level, SQL Server 2005 Beta 2 also introduces a statement-level variation of the READ COMMITTED isolation level called READ_COMMITTED_SNAPSHOT.

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

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.

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

A SNAPSHOT Isolation Backgrounder

As explained in Part I, the SNAPSHOT isolation level is one of two new ways that SQL Server 2005 Beta 2 allows you to govern database concurrency. In prior versions of SQL Server, the only way you could manage concurrency was by manipulating the standard four isolation levels, each of which had some disadvantages.

The lowest isolation level, READ UNCOMMITTED, maximizes concurrency because SELECT statements do not issue any locks on the data, shared or otherwise. It minimizes blocking due to shared locks, but it also allows your application to read uncommitted and therefore potentially inconsistent "dirty" data, data changes that may be rolled back.

The highest two isolation levels, REPEATABLE READ and SERIALIZABLE, have the least concurrency because shared locks are not released until the end of a transaction, and for SERIALIZABLE, shared locks are also issued on key ranges. These higher isolation levels result in the greatest chance of blocking, but also return the most consistent views of the data for transactions. (For more information about dirty reads, non-repeatable reads, and phantoms, see the section titled "Getting Started: SQL Server Isolation Levels in a Nutshell" in Part I.)

SQL Server's default isolation level, READ COMMITTED, takes a sort of middle way: concurrency is limited because SELECT statements do issue shared locks, and therefore may block or be blocked by transactions requiring exclusive locks. But those shared locks are released as soon as the query in a transaction is finished, and are not held to the end of a transaction. Although no uncommitted data is read in the READ COMMITTED isolation level, it is still possible for transactions to experience non-repeatable reads and phantoms. Blocking may occur, but much less often than with the higher REPEATABLE READ and SERIALIZABLE isolation levels.

The SNAPSHOT Isolation Level

Enter the SNAPSHOT isolation level. As a true isolation level, SNAPSHOT is used within transactions and set at the session level with the SET TRANSACTION ISOLATION statement. During the course of a transaction, any changes to data that were read during the transaction are versioned: the original unchanged copy is stored in the versioning system in tempdb. During the remainder of the transaction, any SELECT statements read changed data from the version store and unchanged data directly from the database tables.

The great benefit of the new SNAPSHOT isolation level is that transactions no longer block each other because of SELECT statements. Transactions may still undergo blocking because of data change commands such as INSERT, UPDATE, and DELETE, because those commands still obtain locks. With SNAPSHOT isolation, you get the consistency of READ COMMITTED, and the concurrency of READ UNCOMMITTED. However, in order to use SNAPSHOT isolation, you must begin explicit transactions with the SET TRANSACTION ISOLATION LEVEL command. The SNAPSHOT isolation level has no application to SELECT statements outside explicit transactions.

Statement-Level Snapshot Isolation

The READ_COMMITTED_SNAPSHOT option extends the benefits of SNAPSHOT isolation level to all SELECT statements in a database by changing the way the READ COMMITTED isolation level behaves. It is a database option, not a session level setting like the SNAPSHOT isolation level. However, you do have to enable it with ALTER DATABASE, as in the following command:

ALTER DATABASE AdventureWorks
   SET READ_COMMITTED_SNAPSHOT ON

And that's it: no other commands are required. (But note: there can be no other users in the database when you execute the ALTER DATABASE command with the READ_COMMITTED_SNAPSHOT option.) Automatically, the READ COMMITTED isolation level starts using row versioning, and SELECT statements no longer take shared locks.

Working Through an Example: The Default Behavior

To see the READ_COMMITTED_SNAPSHOT database option in action, let's contrast it with the default READ COMMITTED behavior. Assuming you have installed the AdventureWorks sample database with SQL Server 2005 Beta 2, open a new query window in the master database, and call it Query Window 1. You can detect whether the database has already enabled the READ_COMMITTED_SNAPSHOT option by executing the following query in Query Window 1:

SELECT is_read_committed_snapshot_on FROM
sys.databases WHERE name= 'AdventureWorks'

This statement queries the catalog view sys.databases in the master database. This catalog view replaces direct queries of the sys.databases table, and provides much more information. If the above query returns a 1, the database's READ COMMITTED isolation level uses row versioning; otherwise it will not.

Let's assume your query returns a 0. (If it doesn't, issue: 

ALTER DATABASE AdventureWorks
   SET READ_COMMITTED_SNAPSHOT OFF

in Query Window 1 to turn READ_COMMITTED_SNAPSHOT off.)

Now let's show how an update can block a read. Open a new query window in Management Studio, and call it Query Window 2, choose the AdventureWorks database, and execute the following:

BEGIN TRANSACTION
UPDATE AdventureWorks.Person.StateProvince
  SET IsOnlyStateProvinceFlag = 1
  WHERE IsOnlyStateProvinceFlag = 0

For the time being, do not issue a COMMIT or ROLLBACK.

In a new third query window, which you call Query Window 3, select the AdventureWorks database and execute the following:

SELECT *
  FROM Person.StateProvince

Notice that your SELECT query is blocked because it is attempting to issue shared locks on the table and cannot. You can see this in a couple of ways. First, find out what the SPID number is that is blocked. You can find this on the bottom of the Query Window 3 in parentheses next to your SQL user name. Then you can issue the following in Query Window 1 to see the locks against that SPID:

EXEC sp_lock 57

Your output should look something like that in Figure 1. Notice that the status of the attempted KEY lock is WAIT, with a mode of S (for Shared), and the IndId (index ID) is 1.

Figure 1: You can use the legacy sp_lock stored procedure to see that SPID 57 is in a WAIT state, requesting a shared lock on the key value of a clustered index (a row of a clustered table).

The output tells you that SPID 57 is in a WAIT state while attempting to gain a shared lock on a key value, in this case the key of a clustered index.

There is a subtle and arguably more important benefit for the statement-level READ_COMMITTED_SNAPSHOT option: because the data returned from a SELECT statement is a consistent snapshot of the data as of the beginning of the statement, you are assured that the SELECT statement will return consistent aggregate values.

However, using sp_lock is a legacy method of examining lock data, and in SQL Server 2005 Beta 2, you should really use the dynamic management view called sys.dm_tran_locks. This system view replaces sp_lock and returns much more data.

To inspect SPID 57, execute the following query in Query Window 1:

SELECT
    resource_type
  , request_spid
  , request_mode
  , request_status
FROM sys.dm_tran_locks
WHERE request_spid = 57

The full results from sys.dm_tran_locks have quite a number of columns, but the columns returned by the above query return sufficient information to see that SPID 57 is in a WAIT state, attempting to get a shared lock, as shown in Figure 2. (Unfortunately, at this time sys.dm_tran_locks does not show the index ID of the lock.)

Figure 2: In SQL Server 2005, the dynamic management view sys.dm_tran_locks replaces sp_lock and provides similar information.

The Example with Statement-Level Snapshot Enabled

Now let's try the same thing in the READ_COMMITTED_SNAPSHOT mode. In Query Window 2, roll back the transaction:

ROLLBACK

Now change the database contexts of Query Windows 2 and 3 to be the master database, so that the ALTER DATABASE in Query Window 1 runs OK. Execute the following to set the option on:

ALTER DATABASE AdventureWorks
   SET READ_COMMITTED_SNAPSHOT ON

Now return to Query Window 2, select the AdventureWorks database, and re-execute the transaction with the UPDATE statement. Then in Query Window 3, execute the SELECT statement. Note that this time it is not blocked; the SPID has no locks and no WAIT state. Further, note that the IsOnlyStateProvinceFlag column shows values of both 0 and 1, which is correct, because the transaction has not been committed, as you can see in Figure 3.

Figure 3: With READ_COMMITTED_SNAPSHOT enabled, even though the underlying data has been changed in an uncommitted transaction, a SELECT statement is not blocked.

The SELECT statement has returned data from the versioning system for those rows that have changed values, rather than placing any shared locks on the table. The SELECT statement has not been blocked by the locks on the table due to the UPDATE statement. To verify that the data has really changed, in Query Window 2, execute 

SELECT *
  FROM Person.StateProvince

You'll now see the values of all ones in the IsOnlyStateProvince column, as shown in Figure 4.

Figure 4: The uncommitted transaction shows the changes to the data.

The READ_COMMITTED_SNAPSHOT setting has increased the concurrency of the system because the SELECT statement does not need shared locks to read consistent and committed data. Put another way, you've gotten the concurrency increase equivalent to issuing a NOLOCK hint (or the READ UNCOMMITTED isolation level) without reading any uncommitted data and without leaving the READ COMMITTED isolation level.

Monitoring the Row Versions

You can inspect the row versions using other dynamic management views. In the example using READ_COMMITTED_SNAPSHOT, you can use the sys.dm_tran_version_store() dynamic management function to observe the rows that have been versioned due to the transaction in Query Window 2, as shown in Figure 5.

Figure 5: You can use the sys.dm_tran_version_store() system function to obtain a list of the rows currently being versioned.

Costs and Benefits of Statement-Level Snapshots

Like the SNAPSHOT isolation level, one of the major benefits of the database option READ_COMMITTED_SNAPSHOT is the elimination of blocking combined with reading only committed data.

A Key Benefit: Consistency

There is another subtle but arguably the most important benefit for the statement-level READ_COMMITTED_SNAPSHOT option: because the data returned from a SELECT statement is a consistent snapshot of the data as of the beginning of the statement, you are assured that the SELECT statement will return consistent aggregate values.

For example, suppose your SELECT statement sums up certain values from one or more tables. You are assured by READ_COMMITTED_SNAPSHOT that the summary values will be based on a snapshot of the data consistent at the starting time of the SELECT statement. Even though the underlying data may change during the execution of the SELECT statement, those changes are not read by the SELECT statement because only the version of the data current at the start time of the query is read.

Costs of Statement-Level Snapshots

Like the SNAPSHOT isolation level, the versioned rows for READ_COMMITTED_SNAPSHOT are kept in tempdb. Enabling this option database-wide implies that a considerable amount of activity in tempdb will be required to store and maintain those versions. This activity could potentially exceed that of the SNAPSHOT isolation level because the versioning is now applied database-wide. As a result, you must plan for increased space usage and increased I/O demands on the tempdb database.

Conclusion

SQL Server 2005 Beta 2 gives you two new uses for row versioning that can significantly increase database concurrency: the SNAPSHOT isolation level and the READ_COMMITTED_SNAPSHOT database option. You can use these two strategies independently. The SNAPSHOT isolation level applies only to transactions, and has no effect on the default READ COMMITTED isolation level.

By way of contrast, the statement-level READ_COMMITTED_SNAPSHOT strategy is a database option that changes the behavior of the READ COMMITTED isolation level. It eliminates the blocking caused by SELECT statements, and does not allow reading of uncommitted data.

Both options are intriguing, but it may be that the statement-level READ_COMMITTED_SNAPSHOT approach contains subtle advantages. Your SELECT statements are guaranteed a consistent view of committed data without any changes to Transact-SQL coding.