Controlling Transactions and Locks Part 5: SQL 2005 Snapshots
December 2, 2005
This article will explore the new Row Versioning feature in SQL 2005, and the two new transaction isolation levels that take advantage of it: Read Committed Snapshot and Snapshot Isolation.
Below are the topics that were covered in previous parts of this series.
The examples used in this article run on the SQL 2005 Adventure Works DB. Adventure Works replaces the Pubs and Northwind databases from previous SQL versions. This database can be installed from the SQL media or downloaded from Microsoft at http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en . Row Versioning and Snapshots are not available in SQL versions prior to 2005.
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.
Enter the following uncommitted transaction as the first query.
USE AdventureWorks; GO BEGIN TRAN UPDATE Sales.SalesTerritory SET SalesYTD = 5000000 WHERE [Name] = 'Central';
In a second window, enter the following select:
SELECT SUM(SalesYTD) AS TotalSales FROM Sales.SalesTerritory
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.
Even with Read Committed Snapshot enabled, Updates are handled pessimistically, using exclusive locks like SQL 2000. Row Versioning is not used.
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.
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:
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
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.
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.