Use row-based version control isolation level

Row version control isolation: overview

Row version control isolation is a new isolation framework of SQL Server 2005. Using row version control isolation can significantly reduce the number of concurrent operations, and it can significantly reduce the number of dirty reads, Phantom, read_committed_snapshot: when reading data from a transaction running under the Isolation Based on Row version control, the read operation will not obtain the shared lock (s lock) on the data being read, so it will not block the transaction that is modifying the data. In addition, the cost of locking resources decreases to a minimum as the number of locks obtained. Using row Version Control's committed read isolation and Snapshot isolation can provide statement-level or transaction-level read consistency for copy data. Row version control isolation: Advantages The row version control isolation level has the following advantages: Read operations to retrieve consistent database snapshots. · The SELECT statement does not lock data during read operations (the reader does not block the writer and the writer does not block the reader ). · The SELECT statement can access the last committed row value when other transactions update rows without blocking the application. Program . · The number of deadlocks is reduced. · The number of locks required by the firm is reduced, which reduces the system overhead required to manage locks. · The number of lock upgrades is reduced. Row version control isolation: Principle The row version control principle of SQL Server 2005 is very simple, that is, a timestamp column (row version column) is quietly added to each row in the database table ). When row version control is used for isolation, SQL Server 2005 database engine assigns a transaction serial number (xsn) to each transaction that uses row Version Control for data operations ). The transaction is started when the begin transaction statement is executed. However, the transaction serial number increases for the first read/write operation after the begin transaction statement is executed. The transaction serial number is increased by 1 at each allocation. When a transaction is executed, SQL Server provides the corresponding version of the row based on the row version column. Sqlserver maintains the logical copies (versions) of all data modifications executed in the database ). Each time a specific transaction modifies a row, the database engine instance stores the image version of the previously submitted tempdb row. Each version indicates the transaction serial number of the transaction that made the change. The version of the modified row is linked together with the link list. The latest row values are always stored in the current database and linked to the version stored in tempdb. (When modifying a large object (LOB), only modified fragments are copied to the version storage area in tempdb. for short-term transactions, the modified row version may be stored in the buffer pool, but will not be written to the disk file of the tempdb database. If you only need a temporary copy row, it will simply delete it from the buffer pool without causing I/O overhead .) On msdn, I stressed that we should pay attention to improving the position of tempdb. Otherwise, the performance of the entire database may be degraded due to the performance of tempdb, but it would be a disaster.
Row version control isolation: Type Row version control is divided into two types: read_committed_snapshot and allow_snapshot_isolation. Row version control is classified into row version control isolation, but they actually have different aspects.

The following is a comparison between them by msdn.

Attribute	Read Snapshot isolation level submitted	Snapshot isolation level
Must be setOnTo enable the required supported Database options.	Read_committed_snapshot	Allow_snapshot_isolation
How does a Session Request row version control of a specific type.	Use the default committed read isolation level or runSET transaction isolation levelStatement to specifyRead committedIsolation level. This can be completed after the transaction starts.	Need to executeSET transaction isolation levelTo specifySnapshotIsolation level.
version of the data read by the statement.	all data submitted before each statement is started.	all data committed before each transaction starts.
how to handle updates.	restores data from the row version to the actual data, to select the row to be updated and use the update lock on the selected data row. Obtain the exclusive lock on the actual data row to be modified. No update conflict detection.	use the row version to select the row to be updated. Try to obtain the exclusive lock on the actual data row to be modified. If the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.
Update conflict detection is available.	None.	Integration Support. Cannot be disabled.

Behavior when reading data Snapshot isolation is like a real snapshot, which ignores the changes that involve rows. Transactions running under Snapshot isolation will read data and then be modified by another transaction. The Snapshot transaction does not block the update operations performed by other transactions. It ignores data modifications and continues to read data from versionized rows. However, when the snapshot transaction attempts to modify the data that has been modified by another transaction, the snapshot transaction will generate an error and terminate. read_committed_snapshot is similar to the previous read_committed isolation level. That is to say, the same as the snapshot transaction, even if other transactions modify data, committed read transactions read versionized rows. However, unlike snapshot transactions, committed reads perform the following operations: · read the modified data after other transactions commit data changes. · Data modified by other transactions can be updated, but snapshot transactions cannot. The behavior of modifying data is performed in committed read transactions that use row version control and obstructive scanning (the update lock (U Lock) will be used on the Data row when reading data values) select the row to be updated. This is the same as a committed read transaction that does not use row version control. If the data row does not comply with the update standard, the update lock will be released on the row and the next row will be locked and scanned. Transactions running under Snapshot isolation adopt an optimistic method for data modification: The data lock is not obtained until the data is modified. You can select the row to be updated without obtaining the lock. When the data row meets the update standard, the snapshot transaction verifies the data row that has not been modified by the concurrent transaction (committed after the snapshot transaction starts. If the data row has been modified outside the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. Update conflict detection cannot be disabled because update conflicts are handled by the database engine. Let's review the architecture of read_committed_snapshot.
No lock is used when reading data, but data modified by other transactions can be updated in the transaction. When you modify data, use the update lock (U Lock) to finish selecting the row to be updated. This is consistent with the default read committed isolation layer policy of sqlserver 2k. Read_committed_snapshot's strength is that it does not lock when querying, affecting other transaction operations, and can ensure that the queried data meets the vast majority of precise requirements. Imagine that when a long-running query or aggregation occurs, countless transactions are waiting for a long time. It can be seen that the SQL Server 2k5 team uses version columns, tempdb, and other methods to minimize a series of concurrency bottlenecks caused by locks at an acceptable isolation layer. Just think about it. As long as you pay a small price (to ensure tempdb, the data file will become bigger and the query will be slower), the database will be greatly increased and released. ^ _ ^ How comfortable the world is... (In fact, this is also an important reason why many enterprise-level applications switch from SQL Server 2 K to 2k5. Set read_committed_snapshot: It is easy to set the read_committed_snapshot Isolation Mode. You only need to perform one operation. Alter database database_name Set read_committed_snapshot on; OK! The read_committed_snapshot Isolation Mode is activated immediately. P.s: read_committed_snapshot cannot be enabled in tempdb, MSDB, or master. Read_committed_snapshot: The following is an example of read_committed_snapshot: Use adventureworks; go -- Display declaration using read_committed_snapshot SET transaction isolation level read committed; go -- A simple example Begin transaction; select employeeid, vacationhours from HumanResources. Employee where employeeid = 4;