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.
  

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.
  

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.
  

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.

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.
  

Concepts

Other Resources

Help and Information