The level of isolation of transactions in SQL Server and their relationship to dirty reads, non-repeatable reads, phantom reads, etc. (code arguments and time sequences)
Understanding these problems that may arise in the case of concurrent access to the database, we can continue to understand the concept of database isolation level, in layman's terms: how do you want to isolate concurrent transactions, and to what extent? For example, if dirty reads can be tolerated, or if you don't want concurrent transactions to have dirty reads, then these can be set to the isolation level to make the isolation between concurrent transactions loose or severe.
The higher the level of isolation, the less chance of reading dirty data or incomplete data, but the more severe the performance degradation is in high-concurrency systems. The lower the isolation level, the greater the performance improvement in the concurrent system, but the data itself may be incomplete.
In SQL Server 2012, you can set the isolation level of a transaction (from low to high) using this syntax:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
Create a new window A, open a transaction, perform the update operation, and wait 10 seconds before committing, the code is as follows:
Create a new window B, set the transaction READ UNCOMMITTED (uncommitted read, the lowest level, the easy problem is dirty reading, because it can read the data modified by other transactions but not committed.) It does the same thing as setting (NOLOCK) on the SELECT statement object table in a transaction. Query the data, the code is as follows:
Create a new window C, directly query the data, as follows:
In turn,Execute windows A, B, and C, and find that when updating data, window B can immediately return data (It is possible that the reading is dirty data), C window needs to wait for A window to finish executingwill return the data, as shown in the figure below:
(Window B)
(Window C)
(End)
|
Posted on 2/5/2021 11:53:34 AM
|
|
|
|
