The Database Engine supports an optimistic concurrency control mechanism 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 Engine stores the before image of the previously committed row in tempdb. 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 with old versions of the same row. 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 form significantly increases availability of data. However, exclusive locks are still needed.
The Database Engine supports two forms of row versioning:
- Read Committed Snapshot Isolation (RCSI)
- Snapshot Isolation (SI)
The following subsections describe Read Committed Snapshot Isolation and Snapshot Isolation.
1. Read Committed Snapshot Isolation
RCSI 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. The most important property of RCSI is that read operations do not block updates, and updates do not block read operations. RCSI will be applied only when activated and in the following way:
- Write operations will continue to use the pessimistic locking model, meaning that each data modification acquires exclusive lock(s). On the other hand, these locks will not affect reading operations. (The locks only affect other write operations.)
- Read operations are supplied with versioned copies of the data they need. This ensures that the data will not be changed while a process is performing read operations.
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 SNAPSHOT isolation level will now run under RCSI.
2. Snapshot Isolation
Snapshot Isolation 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 SI 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 Engine, and no way exists to disable the update conflict detection.
Enabling SI 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.
3. RCSI vs. SI
The most important difference between the two forms of row versioning is that SI can result in update conflicts when a process sees the same data for the duration of its transaction and is not blocked. By contrast, RCSI 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 RCSI allows other transactions to modify the data before the snapshot transaction completes. This can lead to a conflict if another transaction modifies the data between the time the former performs a read and subsequently tries to execute the corresponding write operation. (For an application based on SI, the system detects the possible conflicts and sends the corresponding error message.)
The third difference is in relation to code changes. If you choose SI, your applications must be written to trap concurrency problems. Otherwise, you will have to rewrite your application code. On the other hand, RCSI does not require any code changes in applications that use the pessimistic model of the Database Engine.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.