Share via

  • Article

Understanding Row Versioning-Based Isolation Levels

Row versioning is a general framework in SQL Server that is used to do the following:

  • Build the inserted and deleted tables in triggers. Any rows modified by the trigger are versioned. This includes the rows modified by the statement that launched the trigger, as well as any data modifications made by the trigger.

  • Support Multiple Active Result Sets (MARS). If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) at a time there is an active result set, the rows affected by the modification statement are versioned.

  • Support index operations that specify the ONLINE option.

  • Support row versioning-based transaction isolation levels:

    • A new implementation of read committed isolation level that uses row versioning to provide statement-level read consistency.

    • A new isolation level, snapshot, to provide transaction-level read consistency.

The tempdb database must have enough space for the version store. When tempdb is full, update operations will stop generating versions and continue to succeed, but read operations might fail because a particular row version that is needed no longer exists. This affects operations like triggers, MARS, and online indexing. For more information, see Row Versioning Resource Usage.

Using row versioning for read-committed and snapshot transactions is a two-step process:

  1. Set either or both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options ON.

  2. Set the appropriate transaction isolation level in an application:

    • When the READ_COMMITTED_SNAPSHOT database option is ON, transactions setting the read committed isolation level use row versioning.

    • When the ALLOW_SNAPSHOT_ISOLATION database option is ON, transactions can set the snapshot isolation level.

When either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is set ON, the SQL Server Database Engine assigns a transaction sequence number (XSN) to each transaction that manipulates data using row versioning. Transactions start at the time a BEGIN TRANSACTION statement is executed. However, the transaction sequence number starts with the first read or write operation after the BEGIN TRANSACTION statement. The transaction sequence number is incremented by one each time it is assigned.

When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

Note

For modification of large objects (LOBs), only the changed fragment is copied to the version store in tempdb.

Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. The Database Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number.

When both database options are set to OFF, only rows modified by triggers or MARS sessions, or read by ONLINE index operations, are versioned. Those row versions are released when no longer needed. A background thread periodically executes to remove stale row versions.

Note

For short-running transactions, a version of a modified row may get cached in the buffer pool without getting written into the disk files of the tempdb database. If the need for the versioned row is short-lived, it will simply get dropped from the buffer pool and may not necessarily incur I/O overhead.

Behavior When Reading Data

When transactions running under row versioning-based isolation read data, the read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data. Also, the overhead of locking resources is minimized as the number of locks acquired is reduced. Read committed isolation using row versioning and snapshot isolation are designed to provide statement-level or transaction-level read consistencies of versioned data.

All queries, including transactions running under row versioning-based isolation levels, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Query transactions, including those running under a row versioning-based isolation level, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock. For more information about lock behavior, see Lock Compatibility (Database Engine).

When a transaction using the snapshot isolation level starts, the instance of the Database Engine records all of the currently active transactions. When the snapshot transaction reads a row that has a version chain, the Database Engine follows the chain and retrieves the row where the transaction sequence number is:

  • Closest to but lower than the sequence number of the snapshot transaction reading the row.

  • Not in the list of the transactions active when the snapshot transaction started.

Read operations performed by a snapshot transaction retrieve the last version of each row that had been committed at the time the snapshot transaction started. This provides a transactionally consistent snapshot of the data as it existed at the start of the transaction.

Read-committed transactions using row versioning operate in much the same way. The difference is that the read-committed transaction does not use its own transaction sequence number when choosing row versions. Each time a statement is started, the read-committed transaction reads the latest transaction sequence number issued for that instance of the Database Engine. This is the transaction sequence number used to select the correct row versions for that statement. This allows read-committed transactions to see a snapshot of the data as it exists at the start of each statement.

Note

Even though read-committed transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes.

Behavior When Modifying Data

In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read. This is the same as a read-committed transaction that does not use row versioning. If the data row does not meet the update criteria, the update lock is released on that row and the next row is locked and scanned.

Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the Database Engine and there is no way to disable the update conflict detection.

Note

Update operations running under snapshot isolation internally execute under read committed isolation when the snapshot transaction accesses any of the following:

A table with a FOREIGN KEY constraint.

A table that is referenced in the FOREIGN KEY constraint of another table.

An indexed view referencing more than one table.

However, even under these conditions the update operation will continue to verify that the data has not been modified by another transaction. If data has been modified by another transaction, the snapshot transaction encounters an update conflict and is terminated.

Behavior in Summary

The following table summarizes the differences between snapshot isolation and read committed isolation using row versioning.

Property

Read-committed isolation level using row versioning

Snapshot isolation level

The database option that must be set to ON to enable the required support.

READ_COMMITTED_SNAPSHOT

ALLOW_SNAPSHOT_ISOLATION

How a session requests the specific type of row versioning.

Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. This can be done after the transaction starts.

Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.

The version of data read by statements.

All data that was committed before the start of each statement.

All data that was committed before the start of each transaction.

How updates are handled.

Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. Acquires exclusive locks on actual data rows to be modified. No update conflict detection.

Uses row versions to select rows to update. Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.

Update conflict detection.

None.

Integrated support. Cannot be disabled.