SQL Server isolation LEVEL-go

The SQL-92 standard defines four isolation levels, four of which are supported in previous versions of SQL Server:

READ UNCOMMITTED

READ Uncommitted is the least restrictive isolation level because the level ignores locks placed by other transactions. transactions performed using the Read UNCOMMITTED level can read modified data values that have not yet been committed by other transactions, which are called dirty reads. This is because at the read UNCOMMITTED level, the read data does not require an S lock, so that it does not conflict with the X lock on the data being modified . For example, transaction 1 modifies a row, and transaction 2 reads this line before transaction 1 commits. If transaction 1 is rolled back, transaction 2 is enrolled in a row of uncommitted data, which we think is nonexistent.

READ COMMITTED

READ COMMITTED (nonrepeatable reads) is the default isolation level for SQL Server. This level prevents dirty reads by specifying that the statement cannot read data values that have been modified but not yet committed by other transactions. Between the execution of individual statements in the current transaction, other transactions can still modify, insert, or delete data, resulting in a read operation that cannot be repeated, or "shadow" data. For example, transaction 1 reads a row, transaction 2 modifies or deletes the line and commits. If transaction 1 wants to read this line again, it will either get the modified data or discover that it has been deleted, so the second read result of the transaction is different from the first read result, and therefore also called non-repeatable read.

Experiment 1

Query1: Transaction 1

--STEP1: Creating experimental Data Select * into Employee from AdventureWorks.HumanResources.Employeealter table employee add constraint Pk_ Employee_employeeid primary KEY (EmployeeID)--STEP2: Sets the isolation level, which is the default isolation bounds for the database set TRANSACTION isolation Levels READ COMMITTED--STEP3: Start the first transaction begin TRAN Tran1    --STEP4: Perform a select operation, view vacationhours, add s lock to the found record, and automatically release the S lock    after the statement has finished executing SELECT EmployeeID, vacationhours from        Employee         WHERE EmployeeID = 4;    --STEP5: To view the current lock situation, there is no lock on the employee table because the current isolation is read COMMITTED-the    s lock is released immediately after the execution of Step2.    SELECT request_session_id, Resource_type, resource_associated_entity_id,        request_status, Request_mode, Resource _description from        sys.dm_tran_locks

Looking at the lock situation as shown, we found a lock at the database level only, not at the table level or at the lower level, because at the Read committed level, the S lock is released after the statement is executed .

Query2: Transaction 2

--STEP6: Start a second transaction begin TRAN tran2;    --STEP7: Modify vacationhours, need to get exclusive lock X, there is no S lock on vacationhours    UPDATE Employee         SET vacationhours = VacationHours-8          WHERE EmployeeID = 4;    --STEP8: View current lock conditions    SELECT request_session_id, Resource_type, resource_associated_entity_id,        Request_status, Request_mode, resource_description from        sys.dm_tran_locks

After we open another update transaction, we look at the current lock condition, as shown in, we found that we added an IX lock on the table (Object) level, and an IX lock on the Page where the table is located, because the table has a clustered index. So the x lock is added to the leaf node, and the type of the lock is KEY.

Then we go back to the transaction 1 to execute the query statement again, we will find that the query is blocked, we create a new query Query3 to see the lock condition at this time, its query results are as follows, we can find that the query operation needs to request S lock at the key level, in the page and table (Object) The is lock is applied above, but because the key above has an X lock, it conflicts with the S lock requested by the current read operation, so this step is in the WAIT state.

If the update operation for transaction 2 is committed at this time, then the select operation of transaction 1 is no longer blocked, and the query results are obtained, but we find that the result of the query is different from the result of the first query, which is why read committed is called non-repeatable read. The results of the same query operation may be different for two times within the same thing.

Repeatable READ

Repeatable Read is a more restrictive isolation level than Read Committed. This level includes read COMMITTED, and also specifies that no other transaction can modify or delete the data that the current transaction has read until the current transaction commits. Concurrency is lower than read COMMITTED because shared locks on read data are held during the entire transaction, rather than being released at the end of each statement. For example, transaction 1 reads a row, transaction 2 wants to modify or delete the row and commit, but because transaction 1 has not yet been committed, the data row has a transaction 1 lock, Transaction 2 is unable to update the operation, so transaction 2 is blocked. If the transaction 1 wants to read this line again, it reads the result the same as the first read, so it is called repeatable read.

Experiment 2

Query1: Transaction 1

--STEP1: Creating experimental Data Select * into Employee from AdventureWorks.HumanResources.Employeealter table employee add constraint Pk_ Employee_employeeid primary KEY (EmployeeID)--STEP2: Set isolation Levels set TRANSACTION isolation level repeatable READ--STEP3: Start the first transaction begin TRAN Tran1    --STEP4: Perform a select operation, view vacationhours    Select EmployeeID, vacationhours from        Employee         WHERE EmployeeID = 4;    --STEP5: Check the current lock situation and find that there is an S lock on the employee table, because the current isolation bounds are repeatable READ    --s locks are released only after the transaction has been executed.    SELECT request_session_id, Resource_type, resource_associated_entity_id,        request_status, Request_mode, Resource _description from        sys.dm_tran_locks

As shown in the query lock state, we find that the S lock is added on the key and the is lock is added on the page and object, because the s lock is not released until after the transaction is executed at the repeatable read level .

Query2: Transaction 2

--STEP6: Start a second transaction begin TRAN tran2;    --STEP7: Modify vacationhours, need to obtain an exclusive lock x, there is a s lock on vacationhours, there is a conflict, so the update operation is blocked    update Employee         SET vacationhours = VacationHours-8          WHERE EmployeeID = 4;

When performing the above update operation, it was found that the operation was blocked because the update operation was to lock X, and because the original query operation's S lock was not released, the two conflicts. We create a new query 3 to perform the query lock state operation, we find the results as shown, we can find that WAIT occurs on the key plus X lock operation above.

At this point, the select operation in Query 1 is performed again, we find that the query results are the same as the first, so this is called REPEATABLE read operation. But repeatable reads are not exactly the same as data that is read two times, and one of the problems with Repeatable Read is Phantom reading, which is that the second read data returns more entries than the number of entries returned for the first time.

For example, under the REPEATABLE read isolation level, transaction 1 executes the query for the first time, the select ID from the users where id>1 and ID <10, and the returned result is 2,4,6,8. This time the transaction 1 did not commit, then the 2,4,6,8 still has a s lock on it. At this point, transaction 2 performs an insert operation INSERT into user (ID) valuse (3), the insertion succeeds. At this point, the query in transaction 1 is executed again, then the returned result is 2,3,4,6,8. The 3 here is the result of phantom reading. Therefore, it can be concluded that therepeatable read isolation level guarantees two queries in the same transaction under the same query condition, and the content of the second read is bound to the first read.

SERIALIZABLE

SERIALIZABLE is the most restrictive isolation level because the level locks the entire range of keys and holds the lock until the transaction is complete. This level includes repeatable read, and adds the restriction that other transactions cannot insert new rows into a range that the transaction has read before the transaction completes. For example, transaction 1 reads a series of rows that satisfy the search criteria. Transaction 2 is rolled back when the execution of SQL statement produces a row or rows that satisfy a transaction 1 search condition . At this point , transaction 1 reads again a series of rows that satisfy the same search criteria, and the result of the second read is the same as the first read.

Repetitive reading and phantom reading

Repeated reading is to ensure that in one transaction, the data values read under the same query condition do not change, but there is no guarantee that the next time the same condition query, the result record number will not increase.

The illusion of reading is to solve this problem, he has locked the scope of the query, so you can no longer insert data into this range, this is what serializable isolation level does.

Relationship of isolation level to lock

1. At READ UNCOMMITTED level, reading operation does not add S lock;
2. At the Read committed level, the read operation requires an S lock, but the S lock is released after the statement is executed;
3. Under the REPEATABLE read level, the read operation requires an S lock, but does not release the S lock until the transaction commits, that is, it must wait for the transaction to complete before releasing the S lock.
4. At the serialize level, a range lock is added on the basis of the repeatable read level. Ensure that the results of two queries within a transaction are exactly the same, without the result of the first query being a subset of the results of the second query.

SQL Server Isolation level-go