Snapshot Isolation Adds Flexibility in SQL Server 2005

'Snapshot' is a new isolation level that reduces blocking reads and allows transactions to read previously committed versions of data, instead of uncommitted data. Picture your data access in a whole new light.

by Ron Talmage

Aug 2, 2004

Page 1 of 3

QL Server uses isolation levels to govern the tradeoff between consistency of transactions and concurrency of multiple users. As you raise a transaction's isolation level, you also increase the chances that a transaction's reads may block and deadlock with other transactions. Even with the relatively low default READ COMMITTED isolation level, transactions may experience some blocking and deadlocking due to reading data. SQL Server 2005 introduces a new type of isolation level called SNAPSHOT that is designed to allow transactions to read older but consistent versions of data without blocking other transactions. In this way, SNAPSHOT isolation can preserve the consistency of the READ COMMITTED isolation while reducing the frequency of blocking and deadlocking.
SQL Server's default isolation level of READ COMMITTED restricts transactions to reading only data that has been committed. This isolation level has the effect of blocking a transaction's read operation when the data is exclusively locked by another transaction. A lower isolation level called READ UNCOMMITTED allows your transactions to read another transaction's uncommitted data while it is locked, but the values are not stable because the data is uncommitted. SQL Server 2005 introduces a new isolation level that provides a middle way between READ COMMITTED and READ UNCOMMITTED. The SNAPSHOT isolation level allows your transaction to read the previously committed version of the data that is currently being changed, so that your transaction's view of the data is consistent with the state of the data when your transaction began without being blocked by the other transaction. Snapshot isolation has the benefit of reducing blocking and deadlocking in your transactions that is due to blocking reads, without having to let your transactions read uncommitted data. (Note: this article is based on a pre-Beta 2 version of SQL Server called IDW3, build 9.00.790. Some details about the operation and behavior of snapshot isolation may change before the final version of SQL Server 2005 ships.)

Getting Started: SQL Server Isolation Levels in a Nutshell
Before SQL Server 2005, all transactions operated in the context of four isolation levels. These isolation levels affect the way that transactions take and hold shared locks on data resources. Shared locks are the locks taken when reading data; exclusive locks are the locks taken when changing data through an INSERT, UPDATE, or DELETE statement.
In SQL Server, exclusive locks are never compatible with other exclusive locks. If your transaction updates a data row, no other transaction can update that row as long as your transaction is still running and has not been committed or rolled back. But shared locks are another story.

You can use the SQL Server isolation levels to prevent certain types of anomalies from occurring during your transaction due to reads or shared locks. These anomalies are called dirty read, non-repeatable read, and phantom read, respectively.