SYSK 46: New in SQL 2005 -- Snapshot Isolation

Did you know that SQL 2005 now has new snapshot isolation level?  Snapshot isolation is a row level versioning mechanism.

For example, in SQL 2000, if a transaction changes a row, another transaction cannot read the row until the write commits, thus, creating locking can cause blocks between applications that are reading and writing the same data simultaneously, and, in some cases, even deadlocks.  With snapshot isolation in SQL 2005, the reader can access the previous committed value of the row (which, by the way, is stored in tempdb).  You can think of this as an optimistic locking, but done totally by the SQL engine – no programming required.

To enable snapshot isolation, and allow each transaction can decide whether it wants to enable the Snapshot Isolation feature or not, execute the following statement:

        ALTER DATABASE YourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;

Transactions desiring this isolation level would have to execute SET TRANSACTION ISOLATION LEVEL SNAPSHOT method.

To enable snapshot isolation for all transactions automatically, execute:

        ALTER DATABASE YourDbNameHere SET READ_COMMITTED_SNAPSHOT ON

Reference: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/814e4c22-eaea-4871-8d24-8644f42996d0.htm