Four isolation levels of weak Database Consistency

ArticleDirectory

• Lab 1
• Lab 2

SQL-92The standard defines four isolation levels, which are in the previous versionSQL ServerIs supported:

Read uncommitted

Read uncommittedIs the weakest isolation level, because this level ignores the locks placed by other transactions. UseRead uncommittedTransactions that are executed at the level can read the modified data values that have not been committed by other transactions. These behaviors are called "dirty" reads. This is becauseAt the read uncommitted level, the S lock is not required to read data, so that it will not conflict with the X lock on the modified data.. For example, a transaction1Modify a row and transaction2In the transaction1This row is read before submission. If the transaction1Rollback, transaction2We read a row of uncommitted data, which we think does not exist.

Read committed

Read committed (nonrepeatable reads)YesSQL ServerDefault isolation level. At this level, the specified statement cannot read data values modified but not committed by other transactions, and dirty reads are prohibited. During the execution of each statement in the current transaction, other transactions can still modify, insert, or delete data to generate read operations that cannot be repeated, or "shadow" data. For example, a transaction1Read a row, transaction2Modify or delete this row and submit it. If the transaction1If you want to read this row again, it will obtain the modified data or find that this row has been deleted. Therefore, the second read Result of the transaction is different from the first read result, therefore, it is also called non-repeated read.

Lab 1

Query1: Transaction 1

 --  Step 1: Create lab data  Select   *   Into Employee From  Adventureworks. HumanResources. Employee  Alter  Table Employee Add   Constraint Pk_employee_employeeid Primary   Key  (Employeeid)  --  Step 2: Set the isolation level, which is the default isolation sector of the database  Set   Transaction   Isolation   Level   Read   Committed  -- Step 3: start the first transaction  Begin   Tran  Tran1  --  Step 4: Execute the Select Operation to view vacationhours and apply the S lock to the queried records. The S lock is automatically released after the statement is executed.      Select  Employeeid, vacationhours  From  Employee  Where Employeeid =   4  ;  -- Step 5: Check the current locking status. No locks are found on the employee table because the current isolation sector is read committed.      --  The S lock is immediately released after Step 2 is 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 following figure, we found that only the S lock at the database level is not at the table level or lower level. This is becauseAt the Read committed level, the S lock is released after the statement is executed..

Query2: Transaction 2

 --  Step 6: start the second transaction  Begin   Tran Tran2;  --  Step 7: Modify vacationhours and obtain the exclusive lock X. There is no s lock on vacationhours.      Update  Employee  Set Vacationhours = Vacationhours -   8            Where Employeeid =   4  ;  -- Step 8: view the current locking status      Select  Request_session_id, resource_type, resource_associated_entity_id, request_status, request_mode, resource_description  From SYS. dm_tran_locks

After another update transaction is started, we can check the current lock status, as shown in (ObjectThe IX lock is applied to the table.PageThe IX lock is also added, because the table has a clustered index, so the X lock is applied to the leaf node. The lock type isKey.

Then we return to transaction 1 and execute the query statement again. We will find that the query is blocked. We create a query query3 to view the lock status at this time. The query result is as follows, we can find that the query operation needs to apply for the s lock at the key level, apply for the is lock on the page and table (object), but because the key originally had the X lock, this step is in conflict with the S lock applied for by the current read operation.WaitStatus.

If the update operation of transaction 2 is committed at this time, the Select Operation of transaction 1 is no longer blocked and the query result is obtained. However, we find that the query result is different from the first query result, this is why read committed is called non-repeatable, becauseThe results of two identical query operations in the same thing may be different..

Repeatable read

Repeatable readYesratioRead committedMore Restrictive isolation level. This level includesRead committedAnd specify that no other transaction can modify or delete the data read by the current transaction before the current transaction is committed. Concurrency lowerRead committedBecause the shared lock of read data is held throughout the transaction, rather than released at the end of each statement. For example, a transaction1Read a row, transaction2You want to modify or delete this row and submit it, but because of the transaction1Not committed yet, there are transactions in the Data row1Locks, transactions2Unable to update, so the transaction2Blocking. If the transaction at this time1To read this row again, the read result is the same as that of the first read, so it is called repeatable.

Lab 2

Query1: Transaction 1

 --  Step 1: Create lab data Select   *   Into Employee From  Adventureworks. HumanResources. Employee  Alter   Table Employee Add   Constraint Pk_employee_employeeid Primary   Key  (Employeeid)  --  Step 2: Set the isolation level  Set  Transaction   Isolation   Level   Repeatable   Read  --  Step 3: start the first transaction  Begin   Tran  Tran1  --  Step 4: Execute the Select Operation to view vacationhours      Select  Employeeid, vacationhours  From  Employee Where Employeeid =   4  ;  --  Step 5: Check the current locking status and find that there is an S lock on the employee table, because the current isolation sector is Repeatable read      --  The S lock is released only after the transaction is executed.      Select  Request_session_id, resource_type, resource_associated_entity_id, request_status, request_mode, resource_description  From SYS. dm_tran_locks

The results of querying the lock status are shown in. We found that the S lock is applied to the key and the is lock is applied to the page and object. This is becauseAt the Repeatable read level, the S lock will be released only after the transaction is executed..

Query2: Transaction 2

  --   Step 6: start the second transaction   begin   Tran   tran2;   --   Step 7: Modify vacationhours and obtain exclusive lock X. There is a s lock on vacationhours, causing a conflict, therefore, the update operation is blocked.   Update   employee   set  vacationhours = vacationhours -  8   where  employeeid =  4 ; 

When performing the above update operation, we found that the operation was blocked because the update operation had to apply the exclusive lock X, and because the S lock of the original query operation was not released, the two were in conflict. Create a new query 3 and execute the query lock status operation. The result is shown in. We can find that:WaitThe operation occurs when the key is locked by X.

Execute the Select Operation in query 1 again. We find that the query results are the same as those for the first time. Therefore, this operation is called a Repeatable read operation. However, the Repeatable read operation does not specifically mean that the data read twice is exactly the same. One problem with Repeatable read is Phantom read, that is, the number of entries returned for the second read is more than that returned for the first read.

For example, at the Repeatable read isolation level, transaction 1 first executes the query select ID from users where ID> 1 and ID <10, and the returned results are 2, 4, 6, and 8. At this time, transaction 1 is not committed, so there is still s lock on the 2, 4, 6, and 8. At this time, transaction 2 executes the insert operation insert into user (ID) valuse (3), and the insert operation is successful. When the query in transaction 1 is executed again, the returned result is 2,3, 4, 6, 8. Here 3 is because of phantom reading. Therefore, we can draw a conclusion:The Repeatable read isolation level ensures two queries in the same transaction under the same query conditions. The content read for the second time must be replaced with the content read for the first time.

Serializable

SerializableIs the most restrictive isolation level becauseLock the entire range of keysAnd keep holding the lock until the transaction is completed. This level includesRepeatable readBefore the transaction is completed, other transactions cannot be read from the transaction.Insert new row. For example, a transaction1Read a series of rows that meet the search criteria. Transactions2In executionSQL statementGenerate one or more rows to meet the transaction requirements1If the row of the Search Condition conflicts, the transaction2Rollback. At this time, the transaction1A series of rows that meet the same search criteria are read again. The results of the second read are the same as those of the first read.

Repeated read and phantom read

Repeated read ensures that the data values read under the same query conditions in a transaction do not change. However, the number of results records does not increase in the next query with the same conditions.

Phantom read exists to solve this problem. He locks the query range and cannot insert data into this range. This is what serializable isolation levels do.

Relationship between isolation level and lock

1. At the read uncommitted level, the S lock is not applied to read operations;
2. At the Read committed level, the S lock is required for read operations, but the S lock is released after the statement is executed;
3. At the Repeatable read level, the S lock is required for read operations, but the S lock is not released before the transaction is committed, that is, the S lock must be released after the transaction is executed.
4. At the serialize level, a Range lock is added based on the Repeatable read level. Make sure that the results of the two queries in a transaction are identical without the first query result being the subset of the second query result.