"Go fix" Isolation level for SQL Server row versioning

Under the SQL Server standard read COMMITTED isolation level, a read operation is blocking each other from a write operation. Uncommitted read (READ UNCOMMITTED) Although there is no such blocking, read operations may read dirty data, which is unacceptable to most users. Some relational databases, such as Oracle, use a different approach. before any modification, make a copy of the pre-modified version [WX1], and all subsequent reads will read the copied version, and the modification will create a new version. in this processing mode, read and write operations do not block each other. The advantage of using this line versioning mechanism is that the concurrency of the program is high, but the disadvantage is that the user is not reading a dirty data, but it is probably a data value [WX2] that is going to expire immediately. If data modifications are made based on this expired value, a logic error is generated.

[WX1] The copied content is saved in tempdb.

[WX2] If read and write at the same time, read the value is not now being modified, if it is read the value is being modified that is dirty read. Read the value before the change. However, this value will expire at any time. Wait until the changes are finished.

Some users may not care about this disadvantage for higher concurrency, so they prefer Oracle's approach. To meet the needs of this subset of users, SQL Server 2005 also introduced this mechanism to implement similar functionality. So choosing the row versioning isolation level can also be a means of eliminating blocking and deadlocks.

SQL Server has two row versioning, with row versioning for read-committed isolation (read_committed_snapshot) and direct use of the SNAPSHOT transaction isolation level.

• When the Read_committed_snapshot database option is on, the read_committed transaction provides statement-level read consistency by using row versioning.
• When the allow_snapshot_isolation database option is on, the SNAPSHOT transaction provides transaction-level read consistency by using row versioning.

The following examples illustrate the differences in the behavior of committed read transactions that use normal committed read transactions, row versioning, snapshot isolation transactions, and row version control.

Database structure

Manually create the database name HumanResources, execute the following script

CREATE TABLE [dbo]. [Employee] (    [EmployeeID] [int] null,    [vacationhours] [int] null,    [ Sickleavehours] [int] NULL) on [Primary]goinsert [dbo]. [Employee] ([EmployeeID], [vacationhours], [sickleavehours]) VALUES (4)

Experiment 1:read Committed Isolation LEVEL

Query1: Transaction 1

--Step1: Start the first transaction begin TRAN    tran1--Step2: Perform a select operation, view vacationhours, add s lock to the found record    select EmployeeID, vacationhours from        Employee         4;     --STEP6: Rerun the query statement in the first transaction and find that the query    is blocked - This is because there is an exclusive lock on the vacationhours, and now the VacationHours field must be queried for the S lock. But the x lock conflicts    with the S lock -- so the query cannot be executed and is blocked.    SELECT EmployeeID, vacationhours from        Employee        4;     --STEP8: Because session 2 has committed a transaction and no longer blocks the current query, it returns the new data that session 2has modified:--STEP9: Rollback or COMMIT transaction rollback TRANSACTION;COMMIT Tran Tran1go

Query2: Transaction 2

--Step3: Start the second transaction begin TRAN tran2    ; --Step4: Modify vacationhours, need to get update lock U, there is a s lock on vacationhours, US does not conflict, so    can be modified. -- after modifying vacationhours, the update lock U becomes an exclusive lock x    update         Employee8          4;     -- Step5: Querying vacationhours in the current transaction, found only 40 hours    SELECT vacationhours from        Employee        4 ; --STEP7: ROLLBACK TRANSACTION rollback TRAN Tran2--commit Tran Tran2

Summarize:

1. Read operation in transaction 1 does not block write operations in transaction 2
2. The update operation in transaction 2 blocks the subsequent read operation in transaction 1, as shown in:
3. The data obtained from the transaction 12 queries are 48 and 40, and two times the data content is different. therefore also into Read committed for non-repeatable reading. In the Read Committed isolation level, locks are only added at the statement level, and locks are released automatically when the statement finishes executing. For example, the first query in transaction 1, although the query is in the transaction, and the transaction is not committed, but at this time the query statement after the execution of the table will not find the lock.

Experimental 2:snapshotIsolation

In this example, a transaction that runs under snapshot isolation reads the data and then modifies it by another transaction. The snapshot transaction is not blocked by an update operation performed by another transaction, it ignores the modification of the data and continues to read data from the versioned row. That is, the version that was read before the data was modified. However, when a snapshot transaction attempts to modify data that has been modified by another transaction, it generates an error and terminates.

Query1: Transaction 1, snapshot transaction

--Step1: Enable snapshot isolation alter DATABASE humanresources SET allow_snapshot_isolation on; GO--Step2: Set Use snapshot isolation level, not previously set because the database default isolation interface is read Committedset TRANSACTION isolation level SNAPSHOT; GO--Step3: Start the first transaction begin TRAN Tran1--step4: Perform a select operation, view vacationhours, add s lock to the found record, SELECT EmployeeID, vacationhours from Employee WHERE Empl Oyeeid=4; --STEP8: After you modify the data in Transaction 2, run the query statement again in transaction 1--The query statement is not blocked at this time, and the value returned is 48, which is the data before the transaction 2 modification--this is because transaction 1 is reading data from versioned rows SELECT EmployeeID, vacationhours from Employee WHERE EmployeeID=4; --STEP10: After transaction 2 commits, transaction 1 performs a query operation again--The query result is found to be 48 because the transaction still reads data from the versioned row SELECT EmployeeID, vacationhours from Employee WHERE EmployeeID=4; --STEP11: After transaction 2 commits the modification, transaction 1 wants to make any changes again, here we modify the value of the Sickleavehours field--A 3960 error is encountered, transaction 1 is automatically rolled back, and modifications in transaction 2 are not rolled back. UPDATE Employee SET sickleavehours= Sickleavehours-8WHERE EmployeeID=4; --attached: If a modification operation is performed in transaction 2, but no commit, then the modification operation in transaction 1 is blocked--at this point, if you commit the modification manipulation in transaction 2, transaction 1 encounters a 3960 error, as above. UPDATE Employee SET sickleavehours= Sickleavehours-8WHERE EmployeeID=4; rollback Tran Tran1commit Tran Tran1/*MSG 3960, Level A, state 2, line 1Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot Isolation to access table ' Employee ' directly or indirectly in database ' AdventureWorks ' to update , delete, or insert the row, the with been modified or deleted by another transaction. Retry the transaction or change the isolation level for the Update/delete statement.*/--End of Experiment 2------------------------------------

Query2: Transaction 2

--Step5: Start the second transaction begin TRAN tran2    ; --Step6: Modify vacationhours, need to get update lock U, there is a s lock on vacationhours, US does not conflict, so    can be modified. -- after modifying vacationhours, the update lock U becomes an exclusive lock x    update         Employee8          4;     -- STEP7: Querying vacationhours in the current transaction, found only 40 hours    SELECT vacationhours from        Employee        4 ; --STEP9: Commit Transaction 2commit Tran Tran2--end of Experiment 2------------------------------------

Summarize:

1. The read operation of snapshot transaction 1 does not block the read operation of normal transaction 2, but it blocks the delete operation of transaction 2, and if the delete operation is performed in transaction 2, the error is: Employees cannot be deleted. They can only is marked as not current. MSG 3609, Level A, State 1, line 1The transaction ended in the trigger. The batch has been aborted.
2. The update operation for normal transaction 2 does not block the read operation of transaction 1, but we find that the data in transaction 1 is read before the transaction 2 update. Because the row data in the versioned is read.

In the above experiment, we found that the following two statements are used together, that is, first allow the database to open snapshot isolation, and then set isolation level to snapshot.

--STEP1: Enable snapshot isolation alter DATABASE AdventureWorks    SET allow_snapshot_isolation on; GO--STEP2: Set Use snapshot isolation level, not previously set because the database default isolation interface is read Committedset TRANSACTION isolation level SNAPSHOT; GO

Before we finish Step1, we can view the Advantureworks snapshot_isolation_state and is_read_committed_snapshot_on properties in sys.databases:

SELECT name,snapshot_isolation_state, Snapshot_isolation_state_desc, is_read_committed_snapshot_on from sys.databases where name= ' AdventureWorks ';

The query results are as follows:

If we do not execute Step1, only execute step2 and then open the transaction for query, the following error will be reported:

MSG 3952, Level A, State 1, line 3
Snapshot isolation transaction failed accessing database ' AdventureWorks ' because Snapshot isolation is not Allowe D in this database. Use the ALTER DATABASE to allow snapshot isolation.

After executing STEP1, we look again at the contents of sys.databases and find that snapshot_isolation_state is changed from 0 to 1, as shown in:

Lab 3: Read-committed Isolation using row versioning (Read_committed_snapshot)

In this example, committed read transactions that use row versioning are run concurrently with other transactions. The behavior of a committed read transaction differs from the behavior of a snapshot transaction. As with snapshot transactions, read-committed transactions will read versioned rows even if other transactions have modified the data. Unlike snapshot transactions, read-committed will do the following:

• Reads the modified data after the other transaction commits the data change.
• Ability to update data modified by other transactions, while snapshot transactions cannot.
  

Query1: Transaction 1

--Experimental 3:read_committed_snapshot---------------------Stpe1: Enable row versioning for read-committed--Note that when you run this sentence, you cannot have other connections using the Adventureworksuse masteralter DATABASE humanresources SET read_committed_snapshot on WIT H ROLLBACK Immediatego--Step2: Set using Read Committed isolation level use humanresources; Goset TRANSACTION Isolation level READ COMMITTED; GO--Step3: Start the first transaction begin TRAN Tran1--step4: Perform a select operation, view vacationhours, add s lock to the found record, SELECT EmployeeID, vacationhours from Employee WHERE Empl Oyeeid=4; --STEP8: After you modify the data in Transaction 2, run the query statement again in transaction 1--The query statement is not blocked at this time, and the value returned is 48, which is the data before the transaction 2 modification--this is because transaction 1 is reading data from versioned rows SELECT EmployeeID, vacationhours from Employee WHERE EmployeeID=4; --STEP10: After transaction 2 commits, transaction 1 performs a query operation again--here, unlike experiment 2, transaction 1 always returns a committed value, so this returns 40 because session 2 has committed the transaction SELECT EmployeeID, vacationhours from Employee WHERE Emp Loyeeid=4; --STEP11: The change here will be successful without error. UPDATE Employee SET sickleavehours= Sickleavehours-8WHERE EmployeeID=4; rollback Tran Tran1--End of experiment 3------------------------------------

Query2: Transaction 2

--Step5: Start the second transaction begin TRAN tran2    ; --Step6: Modify vacationhours, need to get update lock U, there is a s lock on vacationhours, US does not conflict, so    can be modified. -- after modifying vacationhours, the update lock U becomes an exclusive lock x    update         Employee8          4;     -- STEP7: Querying vacationhours in the current transaction, found only 40 hours    SELECT vacationhours from        Employee        4 ; --STEP9: Commit Transaction 2commit Tran Tran2--end of experiment 3------------------------------------

Summarize:

1. After transaction 2 modifies the data, the transaction 1 reads the snapshot data before committing, that is, the value before transaction 2 is modified.
2. After transaction 2 commits the modification, transaction 1 reads the modified data. and transaction 1 can modify the data modified by other data.

Conclusion

1. Use the sys.databases catalog view to determine the status of two row versioning database options.
2. Row versions are generated for user tables and any updates that are stored in some system tables in master and msdb.
3. In the master and msdb databases, the Allow_snapshot_isolation option is automatically set to on and cannot be disabled.
4. Users cannot set the Read_committed_snapshot option to on in the master database, the tempdb database, or the msdb database.
5. As you can see from the previous test, two sessions that were previously blocked will not experience blocking after they use the row version-controlled isolation level. However, the results of both row versioning are different. Can be summed up in table 1 .

Table 1 differences after using row versioning to control isolation levels

Session 1	Session 2	Results
		A. Ordinary committed transactions	B. Using snapshot isolation		C. Read-Committed with row versioning
BEGIN TRAN Enquiry 1		48
	BEGIN TRAN Modified 1	Success
	Enquiry 1	40
Enquiry 2		Be blocked		48
	COMMIT TRAN	Query 2 returns 40
Enquiry 3		40		48		40
Modified 2 ROLLBACK TRAN		Success		Failed		Success

"Go fix" Isolation level for SQL Server row versioning