Choosing Row Versioning-based Isolation Levels
Row versioning-based isolation levels improve read concurrency by eliminating locks for read operations. Microsoft SQL Server 2005 introduces two transaction isolation levels that use row versioning:
- A new implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON.
- A new isolation level, snapshot, that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON.
For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the following reasons:
It consumes less tempdb space than snapshot isolation.
It works with distributed transactions, whereas snapshot isolation does not.
It works with most existing applications without requiring any change. Applications written using the default isolation level, read committed, can be dynamically tuned. The behavior of read committed, whether to use row versioning or not, is determined by the database option setting, and this can be changed without affecting the application.
Note
For applications that are designed to depend upon the blocking behavior of read committed isolation, developers may want to alter the application to work with both modes of read committed isolation. Otherwise, it is important to note that the READ_COMMITTED_SNAPSHOT database option remains OFF.
Snapshot isolation is vulnerable to update conflicts that are not applicable to read committed isolation using row versioning. When a transaction running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with read committed isolation using row versioning.
When to Use Read Committed Isolation Using Row Versioning
Read committed isolation using row versioning provides statement-level read consistency. As each statement within the transaction executes, a new data snapshot is taken and remains consistent for each statement until the statement finishes execution. Enable read committed isolation using row versioning when:
- Reader/writer blocking occurs to the point that concurrency benefits outweigh increased overhead of creating and managing row versions.
- An application requires absolute accuracy for long-running aggregations or queries where data values must be consistent to the point in time that a query starts.
When to Use Snapshot Isolation
Snapshot isolation provides transaction-level read consistency. A data snapshot is taken when the snapshot transaction starts, and remains consistent for the duration of the transaction. Use snapshot isolation when:
- Optimistic concurrency control is desired.
- Probability is low that a transaction would have to be rolled back because of an update conflict.
- An application needs to generate reports based on long-running, multi-statement queries that must have point-in-time consistency. Snapshot isolation provides the benefit of repeatable reads (see Concurrency Effects) without using shared locks. Database snapshot can provide similar functionality but must be implemented manually. Snapshot isolation automatically provides the latest information in the database for each snapshot isolation transaction.
Benefits of Row Versioning-based Isolation Levels
Isolation levels that use row versioning provide these benefits:
- Read operations retrieve a consistent snapshot of the database.
- SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa).
- SELECT statements can access the last committed value of the row, while other transactions are updating the row without getting blocked.
- The number of deadlocks is reduced.
- The number of locks required by a transaction is reduced, which reduces the system overhead required to manage locks.
- Fewer lock escalations take place.
Costs of Row Versioning-based Isolation Levels
Deciding to use row versioning-based isolation requires weighing the concurrency benefit of minimizing locking against increased resource usage needed to maintain and read row versions. Consider the following costs associated with enabling row versioning for the snapshot and read-committed isolation levels:
Read performance can be affected when versions needed by queries become old and long version chains must be scanned.
Row versioning increases resource usage during data modification as row versions are maintained in tempdb.
When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level. Constructing a consistent snapshot of data using row versions involves system resources (CPU and memory), and potentially generates I/O activity. Because the record versions are stored in tempdb, performance is better and the number of issued I/Os is lower when more tempdb pages can be stored in memory for row versioning.
Note
Inserting a row generally does not generate a row version. Under certain conditions, however, the INSERT command does generate a row version. For example, if you insert a row into a table with a unique index when a previously deleted row version (ghost record) has not been truncated, the INSERT command generates a row version.
tempdb must have enough disk space for the version store. If there are very long-running transactions, all the versions generated by update transactions during the time must be kept in tempdb. If tempdb runs out of space, update operations do not fail, but read operations using row versioning might fail.
Row versioning information requires 14 bytes added to the database row.
Update performance can be slower due to the work involved in maintaining row versions. In typical OLTP workloads, each update changes just a few rows in a database. In these systems, the performance for updates in a database where the options are ON may be only a few percentage points slower compared to databases with both options OFF. The performance cost of versioned updates could be higher when larger amounts of data change during update operations.
Data readers face the extra cost of traversing the version link list. The older the snapshot, the slower the process of accessing it in a snapshot isolation transaction.
Some update transactions using snapshot isolation might have to be rolled back because of mandatory conflict detection for update operations. Transactions running under read-committed isolation using row versioning do not generate update conflicts.
Transactions using row versioning have other limitations. For more information, see Using Row Versioning-based Isolation Levels.
Systems That Benefit from Row Versioning-based Isolation Levels
The scenarios that benefit from row versioning-based isolation levels include:
- Systems in which read-only reports and ad hoc queries run in parallel with an application that is updating the data.
- Application migration to the Microsoft SQL Server Database Engine from other relational database systems supporting similar isolation levels.
- Systems in which getting consistent aggregates, such as AVG, COUNT, and SUM, or performing index intersections and index joins, would require a strict isolation level (such as repeatable read or serializable).
- Systems that have a high number of deadlocks because of read/write contention.
See Also
Concepts
Understanding Row Versioning-Based Isolation Levels
Using Row Versioning-based Isolation Levels
Row Versioning Resource Usage
Enabling Row Versioning-Based Isolation Levels
Isolation Levels in the Database Engine