Database isolation levels-database Isolation level

The transaction isolation level is used to controlWhether the lock needs to be acquired and what type of lock to get when reading the data. The holding time of the lock.

When reading data that is being modified by another transaction: whether blocking reads waits until an exclusive lock is released or Read committed (Read Committed) or reads the modified data that has not yet been committed (read UNCOMMITTED).Setting the Transaction isolation level does not affect the locks you get from modifying the data, because any data modification involved in the transaction is an exclusive lock until the end of the transaction is released, regardless of the transaction level you set. for database read operations, the transaction isolation level is primarily used to define the level of protection that the data is affected by other transactional modifications. lower isolation basically improves system concurrency, but at the same time increases the user's exposure to dirty read and update loss caused by system concurrency. Conversely, the high isolation level reduces the concurrency-related problems of dirty reads, but requires more system resources and greatly increases the likelihood of system transaction blocking. you should choose the appropriate transaction isolation level to balance the total cost of each isolation level based on the application's own requirements for data integrity. The highest level of isolation (Serializable) ensures that the same data is obtained for each read operation of the transaction, which, as mentioned earlier, results in a lock operation under multi-user concurrency. The lowest level of transaction isolation level (READ UNCOMMITTED) can cause data to be dirty read, but there is no read lock problem at this level, so the total overhead is also the lowest. If you set the Serializable isolation level, DDL operations and transactions on replicated tables may fail. Because the replication query hint is incompatible with this. SQL Server also supports two transaction isolation levels that use row versioning, one is a new implementation (Read committed) and the other is a (Snapshot) snapshot. SQL Server itself does not support versioning operations on metadata, so in explicit transactions at the snapshot (spapshot) isolation level, the DDL operations defined in the transaction are constrained accordingly. Under the snapshot isolation level, the BEGIN TRANSACTION statement does not allow the following DDL operations to be defined:Alter TABLE CREATE INDEX create XML index ALTER index ALTER PARTITION FUNCTION, ALTER PARTITION SCHEMEor CLR (Common Language Runtime) statement. However, in implicit transactions at the snapshot isolation level, these operations are allowed. What is an implicit transaction is that there is no begin Transaction, but rather a simple statement (SQL Server automatically wraps it into a transaction, also known as an autocommit transaction), making it possible to execute the appropriate semantics at the snapshot isolation level, even if it contains DDL statements. If this principle is not followed, it is possible to cause a 3961 error.

Original link