What Is Snapshot Isolation?

Isolation level: The degree to which a transaction must be isolated from resource or data changes made by other transactions. Isolation levels are described in terms of allowed concurrent side effects, such as dirty reads or virtual reads.

Isolation level

Isolation level: The degree to which a transaction must be isolated from resource or data changes made by other transactions. Isolation levels are described in terms of allowed concurrent side effects, such as dirty reads or virtual reads.

Chinese name

Isolation level

the way

With resources conducted by other transactions

Form

Dirty read or dummy read

Designation

An isolation level

A transaction specifies an isolation level that defines the degree to which a transaction must be isolated from resource or data changes made by other transactions. Isolation level from allowed concurrent side effects (for example,

Whether the lock is occupied when reading data and the type of lock requested.

Takes up the time of the read lock.

Are read operations referencing rows modified by other transactions:

Blocks other transactions until the exclusive lock on that row is released.

Retrieves the committed version of a row that existed when the statement or transaction was started.

Read uncommitted data modifications.

Selecting a transaction isolation level does not affect locks acquired to protect data modifications. A transaction always acquires an exclusive lock on any data it modifies and holds it until the transaction is complete, regardless of the isolation level set for the transaction. For read operations, the transaction isolation level primarily defines the level of protection from being affected by changes made by other transactions.

A lower isolation level can enhance the ability of many users to access data at the same time, but it also increases the number of concurrent side effects (such as dirty reads or lost updates) that users may experience. In contrast, a higher isolation level reduces the types of concurrent side effects that users may encounter, but requires more system resources and increases the possibility that one transaction blocks other transactions. You should balance the data integrity requirements of your application with the cost of each isolation level, and choose the appropriate isolation level based on this. The highest isolation level (serializable) guarantees that the transaction can accurately retrieve the same data every time a read operation is repeated, but this needs to be done by performing a certain level of locking, which may affect multi-user systems Other users. The lowest isolation level (uncommitted read) can retrieve data that has been modified but not committed by other transactions. In uncommitted reads, all concurrent side effects can occur, but because there is no read locking or versioning, the overhead is minimal.

A transaction attribute that controls the extent to which data is isolated for use by one process and prevented from interfering with other processes. Setting the isolation level defines the default locking behavior for all SELECT statements in a SQL Server session.

When multiple transactions are performed simultaneously , set the isolation level to handle dirty read, non-repeatable read, phantom read events

read uncommitted | 0 uncommitted read

Specify the isolation level of the query as 0.

Can read dirty data

Read dirty data: One transaction added, deleted, or modified the data, but it was not committed, which may be rolled back, while another transaction read the uncommitted data

read committed | 1 committed read

Specify the isolation level of the query as 1.

Avoid dirty reads, but non-repeatable and phantom reads can occur

Non-repeatable read: one transaction has updated or deleted data, and the other query has inconsistent data

Phantom read: One transaction has added data to the data, and the other query has inconsistent data

repeatable read | 2 repeatable read

Specify the transaction isolation level of the query as 2.

Avoid dirty reads, non-repeatable reads, and allow phantom reads

serializable | 3 Serializable

Specify the isolation level of the query as 3.

Serialized read, transactions can only be executed one by one, avoiding dirty reads, non-repeatable reads, and phantom reads

Slow execution efficiency (I have encountered a situation where the use time is 30 times the isolation level 1), use carefully

SNAPSHOT

When reading data, it is guaranteed that the row read by the read operation is the last committed version available at the beginning of the transaction.

This means that this level of isolation guarantees that data that has already been committed is read, and that it can be read repeatedly,

It also ensures that there is no phantom reading. However, this isolation level does not use shared locks, but row versioning.

Supported after SQL Server 2005.

The following table shows the concurrent side effects allowed by different isolation levels.

Isolation level	Dirty read	Non-repeatable	Virtual read
Uncommitted read	Yes	Yes	Yes
Submitted for reading	no	Yes	Yes
Repeatable read	no	no	Yes
Snapshot	no	no	no
Serializable	no	no	no