Programming Microsoft® SQL Server™ 2008

SNAPSHOT Isolation

For working with T-SQL transactions, SQL Server 2005 introduced a new isolation level called SNAPSHOT that allows you to work in a mode in which writers don’t block readers. Readers thus read from a previously committed version of the data they request, rather than being blocked during write transactions. SNAPSHOT isolation works by SQL Server maintaining a linked list in tempdb that tracks changes to rows and constructs an older, committed version of data for readers to access. This isolation level is useful for optimistic locking, in which UPDATE conflicts are uncommon. For example, if process 1 retrieves data and later attempts to modify it, and if process 2 has modified the same data between the retrieval and modification ...