Microsoft® SQL Server 2008 R2 Unleashed

Optimistic Locking with Snapshot Isolation

SQL Server 2008’s Snapshot Isolation mode provides another mechanism for implementing optimistic locking through its automatic row versioning. If a process reads data within a transaction when Snapshot Isolation mode is enabled, no locks are acquired or held on the current version of the data row. The process reads the version of the data at the time of the query. Because no locks are held, it doesn’t lead to blocking, and another process can modify the data after it has been read. If another process does modify a data row read by the first process, a new version of the row is generated. If the original process then attempts to update that data row, SQL Server automatically prevents the lost update ...