Read Committed Snapshot

By default, all versions of SQL Server use pesismistic Read Committed locking. The first main term in this statement, "pessimistic," refers to the database expecting there to be simultaneous access conflicts, therefore, SQL is expecting conflict and will lock any row prior to access, read access or write. The second term, "Read Committed," means only values that are fully committed will be returned. If we request a row that is being modified by some other transaction, our transaction will wait until the other is finished and fully committed. This blocking action ensures only valid data is returned. However, what if we didn't want to slow down our statement by waiting on this blocking? We could use the NOLOCK hint, but then we may not be receiving valid committed data back. This situation can be addressed with a new type of Read Committed that relies on Row Versioning, called Read Committed Snapshot available in SQL 2005. When enabled, our statement would read the last committed value out of the version history table rather than waiting on a block. In addition, our read statement will not block any other statement that is trying to write. For a description of Row Versioning, see the forth article in this series. The difference between the default locking read committed behavior and the new Read Committed Snapshot can be easily demonstrated. Open two query windows in SQL Management Studio, both using Adventure Works. This example will use the Sales.SalesTerritory table; it is a small tables containing rolled up year to date territory amounts.

As expected, the select statement will not respond, it is being blocked by the uncommitted update. This is the default level of SQL Read Committed using pessimistic locking. Now issue a ROLLBACK on the first statement so the second will complete. This next time, we will enable the new Read Committed Snapshot using Row Versioning and the select will execute unblocked. Execute the following statement to enable Row Versioning with the Read Committed level:

ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON;
GO

This may take several minutes. Now rerun the two queries above in the same order, the sales total 44294026.1334 should be returned. ROLLBACK the first statement. The select was not blocked because it did not wait for the first statement to commit or rollback, instead the version history table was read for the last committed value.

Updates

Even with Read Committed Snapshot enabled, Updates are handled pessimistically, using exclusive locks like SQL 2000. Row Versioning is not used.

Multiple Statements

Using Read Committed Snapshot, each statement in a transaction checks for committed data at the start of that statement, rather than just once at the beginning of the entire transaction. For example, if our above select example was run several times in a row inside a transaction, and in between statement executions some other transaction changed the total sales value, this new value would be reflected in our next return.

BEGIN Tran1

SELECT TotSales

Receives: 44294026

BEGIN Tran 2

UPDATE

SET TotSales = 5000000

COMMIT

SELECT TotSales

Receives: 5000000

COMMIT

Therefore, the data received is committed data at the time each statement executes, not at the time the transaction begins. If consistent data for the entire life of the transaction is required, there is an option that can be used in conjunction with Read Committed Snapshot, or used standalone, called "Allow Snapshot Isolation."

Allow Snapshot Isolation

The second method for taking advantage of Row Versioning is called "Allow Snapshot Isolation." When enabled, data received inside a transaction will be consistent during the entire life of the transaction, rather than at statement level. So using Allow Snapshot Isolation would change the above example results to the following:

BEGIN Tran1

SELECT TotSales

Receives: 44294026

BEGIN Tran 2

UPDATE

SET TotSales = 5000000

COMMIT

SELECT TotSales

Receives: 44294026

COMMIT

Unlike Read Committed Snapshot, which is enabled once at the database level and no further instruction is needed, Allow Snapshot Isolation must be enabled at the database level and then again inside each transaction wishing to make use of it. The following code enables it at the database level:

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON

As implied by the above command, Snapshot Isolation is now allowed, but not invoked. To take advantage of it, each transaction requiring the isolation must execute a SET statement. Here is the above select example rewritten to make use of isolation:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN Tran 1
SELECT TotSales

Updates

If Isolation is used on an update, the statement will execute optimistically, meaning an exclusive lock is not immediately placed. Instead, at the time the data is to be committed, the transaction will check the version store for any changes that may have taken place by another transaction since the time our transaction began; if there are any, our update will rollback. This is a very different behavior than the usual SQL pessimistic default of waiting for a block to be released, then committing. If Isolation is to be used, this error will have to be handled.

Conclusion

The new Read Committed and Snapshot Isolation levels give us greater control of SQL locking. This new control may lead to a performance increase for some read intensive databases. Read Committed Snapshot is easy to enable and test. Allowing Snapshot Isolation will give data transaction consistency, but error handling should be coded for possible update conflicts.