About SQL Server Row Versioning

The SQL Server Database Engine supports an optimistic concurrency control mechanism (versus pessimistic concurrency) based on row versioning. When data is modified using row versioning, logical copies of the data are maintained for all data modifications performed in the database. Every time a row is modified, the database system stores a before image of the previously committed row in the tempdb system database. Each version is marked with the transaction sequence number (XSN) of the transaction that made the change. (The XSN is used to identify all operations to be managed under the corresponding transaction.) The newest version of a row is always stored in the database and chained in the linked list to the corresponding version stored in tempdb. An old row version in the tempdb database might contain pointers to other, even older versions. Each row version is kept in the tempdb database as long as there are operations that might require it.

Row versioning isolates transactions from the effects of modifications made by other transactions without the need for requesting shared locks on rows that have been read. This significant reduction in the total number of locks acquired by this isolation level significantly increases availability of data. However, exclusive locks are still needed: transactions using the optimistic isolation level called SNAPSHOT request exclusive locks when they modify rows.

Row versioning is used, among other things, to

• Support the READ COMMITTED SNAPSHOT isolation level
• Support the SNAPSHOT isolation level
• Build the inserted and deleted tables in triggers

The following subsections describe the SNAPSHOT and READ COMMITTED SNAPSHOT isolation levels.

READ COMMITTED SNAPSHOT Isolation Level

READ COMMITTED SNAPSHOT is a slight variation of the READ COMMITTED isolation level discussed in the previous section. It is a statement-level isolation, which means that any other transaction will read the committed values as they exist at the beginning of the statement. In the case of updates, this isolation level reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. Actual data rows that have to be modified acquire exclusive locks.

The main advantage of READ COMMITTED SNAPSHOT is that read operations do not block updates, and updates do not block read operations. On the other hand, updates block other updates, because exclusive locks are set before an update operation is executed.

You use the SET clause of the ALTER DATABASE statement to enable the READ COMMITTED SNAPSHOT isolation level. After activation, no further changes are necessary. Any transaction specified with the READ COMMITTED isolation level will now run under READ COMMITTED SNAPSHOT.

SNAPSHOT isolation level

The SNAPSHOT isolation level is a transaction-level isolation, which means that any other transaction will read the committed values as they exist just before the snapshot transaction starts. Also, the snapshot transaction will return the initial value until it completes, even if another transaction changed it in the meantime. Therefore, only after the snapshot transaction ends will the other transaction read a modified value.

Transactions running under the SNAPSHOT isolation level acquire exclusive 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 transaction began. If the data row has been modified in a concurrent transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the database system, and no way exists to disable the update conflict detection.

Enabling the SNAPSHOT isolation level is a two-step process. First, on the database level, enable the ALLOW_SNAPSHOT_ISOLATION database option (using SQL Server Management Studio, for instance). Second, for each session that will use this isolation level, set the SET TRANSACTION ISOLATION LEVEL statement to SNAPSHOT. When these options are set, versions are built for all rows that are modified in the database.

READ COMMITTED SNAPSHOT vs. SNAPSHOT

The most important difference between the two optimistic isolation levels is that SNAPSHOT can result in update conflicts when a process sees the same data for the duration of its transaction and is not blocked. By contrast, the READ COMMITTED SNAPSHOT isolation level does not use its own XSN when choosing row versions. Each time a statement is started, such a transaction reads the latest XSN issued for that instance of the database system and selects the row based on that number.

Another difference is that the READ COMMITTED SNAPSHOT isolation level allows other transactions to modify the data before the row versioning transaction completes. This can lead to a conflict if another transaction modified the data between the time the row versioning transaction performs a read and subsequently tries to execute the corresponding write operation. (For an application based on the SNAPSHOT isolation level, the system detects the possible conflicts and sends the corresponding error message.)