Sql server® 2012 t-sql fundamentals

Isolation Levels Based on row Versioning

With SQL Server, you can store previous versions of committed rows in tempdb. SQL Server sup-

ports two isolation levels called SNAPSHOT and READ COMMITTED SNAPSHOT based on this 

row-versioning technology. The SNAPSHOT isolation level is logically similar to the SERIALIZABLE 

isolation level in terms of the types of consistency problems that can or cannot happen; the READ 

COMMITTED SNAPSHOT isolation level is similar to the READ COMMITTED isolation level. How-

ever, readers using isolation levels based on row versioning do not issue shared locks, so they don’t 

wait when the requested data is exclusively locked. Readers still get levels of consistency similar to 

SERIALIZABLE and READ COMMITTED. SQL Server provides readers with an older version of the 

row if the current version is not the one they are supposed to see. 

Note that if you enable any of the snapshot-based isolation levels (which are enabled in SQL 

Database by default), the DELETE and UPDATE statements need to copy the version of the row before 

the change to tempdb; INSERT statements don’t need to be versioned in tempdb because no earlier 

version of the row exists. But it is important to be aware that enabling any of the isolation levels that 

are based on row versioning may have a negative impact on the performance of data updates and 

deletes. The performance of readers usually improves because they do not acquire shared locks and 

don’t need to wait when data is exclusively locked or its version is not the expected one. The next sec-

tions cover snapshot-based isolation levels and demonstrate their behavior.

www.it-ebooks.info

---