Row Versioning-based Isolation Levels in the Database Engine
The Microsoft SQL Server Database Engine introduces a new implementation of an existing transaction isolation level, read committed, that provides a statement level snapshot using row versioning. SQL Server Database Engine also introduces a new transaction isolation level, snapshot, that provides a transaction level snapshot also using row versioning.
Read committed isolation using row versioning is enabled by setting the READ_COMMITTED_SNAPSHOT database option ON. Snapshot isolation is enabled by setting the ALLOW_SNAPSHOT_ISOLATION database option on. When either option is enabled for a database, the Database Engine maintains versions of each row that is modified. Whenever a transaction modifies a row, image of the row before modification is copied into a page in the version store. The version store is a collection of data pages in tempdb. If multiple transactions modify a row, multiple versions of the row are linked in a version chain. Read operations using row versioning retrieve the last version of each row that had been committed when the transaction or statement started.
Applications that were written for SQL Server 2000 or are new to SQL Server, implement read committed isolation using row versioning by specifying the read committed transaction isolation level when the READ_COMMITTED_SNAPSHOT database option is ON. All read operations view row versions that were committed at the time a statement started. This provides a statement level snapshot of the data.
Applications written for SQL Server implement snapshot isolation by specifying the snapshot transaction isolation level when the ALLOW_SNAPSHOT_ISOLATION database option is ON. All read operations in a snapshot transaction view row versions that were committed at the time the transaction started. This provides a transaction level snapshot of the data.
For transactions using an isolation level based on row versioning, read operations do not request shared locks on the data. This means readers using row versioning do not block other readers or writers accessing the same data. Similarly, the writers do not block readers. However, writers will block each other even while running under row versioning-based isolation levels. Two write operations cannot modify the same data at the same time.
The following table describes the topics that provide more information about row versioning-based isolation levels.
Topic |
Description |
|---|---|
Describes row versioning concepts. |
|
Describes the benefits and costs of row versioning, and gives recommendations for the scenarios best suited to row versioning. |
|
Describes the options that enable row versioning in a database. |
|
Describes the limitations of row versioning-based isolation levels. |
|
Describes the system impact of row versioning, and discusses the Dynamic Management Views (DMVs) and performance counters that return information about the state of row versioning in databases and transactions. |