Four isolation levels of SQL Server

SQL ServerFour isolation-level knowledge points are organized, and a flowchart is specially created for later viewing!

SET transaction isolation level
{

Read uncommitted

| Read committed

| Repeatable read

| Serializable

}

1. uncommitted read uncommitted (dirty read)
Meaning: Read that contains uncommitted data. For example, in a multi-user environment, user B changes a row. User A reads the changed rows before user B submits the changes. If user B rolls back the changes, user a reads the rows that have never existed logically. (DEMO)

Demo:
1) User B:
Begin tran
Update test set age = 25 where name = 'A'
2) User:
SET transaction isolation level read uncommitted (the default mode is read committed if this sentence is not written)
Select * from test (the age value of AA is 25)
3) User B:
Rollback (revoking the update operation in step 1, the error data read by user a is called dirty read)

Ii. Read committed)
Meaning: control the shared lock when reading data to avoid dirty reading. The main function of this isolation level is to avoid dirty reading.
Demo:
1) User B:
Begin tran
Update test set age = 25 where name = 'A'
2) User:
SET transaction isolation level read committed
Select * from test, this statement will take effect)

3. Inconsistent analysis Repeatable read)
Meaning: In a multi-user environment, user a opens a transaction and first queries a record in the test table (select * from test where name = 'aa '), then, user B updates and submits the test table (update test set age = 25 where name = 'A'). Then, user a queries this record in the test table, the age value for the first read is 12, and the second read is 25. The data read twice is different. (DEMO)

 

Solution:
Before user a runs a transaction, set the SQL isolation level to Repeatable read.
The SQL statement is set transaction isolation level Repeatable read.
In this way, after user a completes the query, user B will not be able to update any data in the dataset queried by user a (but it can update, insert, and delete data other than the dataset queried by user ), updates can only be performed until the end of user a's transaction. This effectively prevents users from reading inconsistent data in the same transaction.

Iv. serializable)
Meaning: In a multi-user environment, user a starts a transaction and queries all records in the test table. Then, user B inserts (or deletes) the transaction) in the test table, a record is submitted and the transaction is committed. At this time, user a executes the previous operation to query the entire table record, and the result will be an extra (missing) record. This operation is called an illusion. (DEMO)

 

Solution:
Before user a runs a transaction, set the SQL isolation level to serializable.
The statement is set transaction isolation level serializable.
In this way, other users cannot update, insert, or delete any data during the execution of user a's transaction until user a's transaction is rolled back or committed. This is the most restrictive level among the four isolation levels. Because the concurrency level is low, this option should be used only when necessary.