Database Engine isolation level

A transaction specifies an isolation level that defines the degree to which a transaction must be isolated from other transactions for resource or data changes. The isolation level is described from the perspective of allowed concurrent side effects (such as dirty read or phantom read. Transaction isolation level control: whether to occupy the lock and the requested lock type when reading data. The time when the lock is read. Introduction

A transaction specifies an isolation level that defines the degree to which a transaction must be isolated from other transactions for resource or data changes. The isolation level is described from the perspective of allowed concurrent side effects (such as dirty read or phantom read. Transaction isolation level control: whether to occupy the lock and the requested lock type when reading data. The time when the lock is read. Introduction

A transaction specifies an isolation level that defines the degree to which a transaction must be isolated from other transactions for resource or data changes. The isolation level is described from the perspective of allowed concurrent side effects (such as dirty read or phantom read.

Transaction isolation level control:

• Whether the lock is used and the requested lock type when reading data.

• The time when the lock is read.

• Whether to reference the read operations of rows modified by other transactions:

  • Other transactions are blocked before the exclusive lock on the row is released.

  • Retrieves the committed version of the row that exists during the start statement or transaction.

  • Read uncommitted data changes.

Selecting the transaction isolation level does not affect the lock obtained to protect data modification. A transaction always acquires an exclusive lock on any data it modifies and holds the lock before the transaction is completed, no matter what isolation level is set for the transaction. For read operations, the transaction isolation level mainly defines the protection level to prevent changes made by other firms.

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 read or loss of updates) that users may encounter. On the contrary, a higher isolation level reduces the types of concurrent side effects that users may encounter, but requires more system resources and increases the possibility of one transaction blocking other transactions. Balance the data integrity requirements of applications with the overhead of each isolation level, and select the corresponding isolation level. The highest isolation level (serializable) ensures that the transaction can accurately retrieve the same data for each repeated read operation. However, you need to execute a certain level of lock to complete this operation, locking may affect other users in multiple user systems. At the lowest isolation level (uncommitted read), you can retrieve data that has been modified but not committed by other transactions. In uncommitted reads, all concurrent side effects may occur, but because there is no read lock or version control, the overhead is minimal.