Notes about Snapshot Isolation

MSDN: Using Snapshot Isolation

The SQL92 standard defines four isolation levels, two of them being READ_COMMITTED and REPEATABLE_READ.

READ_COMMITTED prevents the current transaction from seeing data changed by uncommitted transactions.

REPEATABLE_READ guarantees that the data read since the start of the current transaction will not be changed by other transactions. This way the state of the database remains the same from the point of view of the current transaction.

By default, SQL Server uses locks to implement READ_COMMITTED. That means any query attempting to read a row that is being changed by a transaction will block. Not great really. Oracle uses rollback segments to achieve the same thing and queries are never blocked by writes!

Snapshot Isolation (from SQL Server 2005 onwards) is another way to implement REPEATABLE_READ and READ_COMMITTED without using locks. Instead row versions are saved in tempdb every time an insert or update takes place.

To allow all future sessions to use snapshot isolation:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

To enable REPEATABLE_READ in snapshot mode for the current connection:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Articles

MSDN:
Isolation levels in SQL Server 2005
Understanding Row Versioning-Based Isolation Levels
Cost of Row Versioning
Stack Overflow:
Why is READ_COMMITTED_SNAPSHOT not on by default?