Introduction to SQL Server transaction operation isolation mode _mssql

In general, it is best to have SQL Server operate in isolation transactions in the default form; Isolation is, after all, one of the basic principles of the acid model. However, sometimes the database administrator has to violate the default behavior because of business requirements, but instead take a more flexible approach to transaction isolation. SQL serve provides five different transaction operation isolation modes for this type of situation encountered by database administrators. Before detailing these transaction operation isolation modes, you must first have a database problem that the administrator encounters:

　　1. Dirty data reads and writes , which occurs when a transaction reads and writes data that has been modified by another transaction and not submitted. If the other thing never submits its modified data, then the first transaction will always get an invalid value, that is, dirty data.

　　2. Non-duplication of reading and writing occurs when a transaction modifies this data before it attempts to repeat the same data, while another transaction is modified before the first transaction is read again. This causes the first transaction to get two different values when reading the same data, resulting in the original data reading becoming repeatable.

　　3. Image reading and writing , which occurs when a transaction makes multiple data queries in a table, and another transaction inserts or deletes rows of data that satisfy the query criteria. This causes the previous transaction to gain or lose a "shadow" value.

Each isolation mode of SQL Server attempts to resolve the parts of the above problem, enabling the database administrator to maintain a balance between transaction operation isolation and business requirements. The following are five isolation modes for SQL Server:

　　1. Read-Write commit isolation mode , which is the default isolation mode for SQL Server, and the database does not allow transaction operations to read and write data written by uncommitted transaction operations. This pattern prevents the generation of dirty data from reading and writing, but does not prevent the image from being read or written or not.

　　2. Read-Write uncommitted quarantine mode , which is basically not quarantined between transaction operations. Any transaction can read and write data written by another uncommitted transaction. In this mode, transaction operations are prone to the occurrence of dirty data reading and writing, image reading and writing, and non duplication.

　　3. The repeatable read-write isolation mode is further than the read-write submission isolation mode, which prevents transactions from modifying data that is being read and written by another transaction until the read-write operation is completed. This isolation mode prevents dirty data from being read and written and cannot be duplicated.

　　4. Serialization isolation mode , which uses a range lock to prevent a transaction from inserting or deleting data rows while another transaction is reading the data. Serialization isolation mode can prevent the above three kinds of cases from happening.

　　5. Snapshot isolation mode , this mode can also prevent three kinds of situations, but the method is different. It provides a "snapshot" of its query data for each transaction, and the transaction can query for snapshots without having to return to the source data table to prevent dirty data from being read.

If you want to change the isolation mode used by SQL Server, you only need to enter the following command:

　　

Copy Code code as follows:

SET TRANSACTION Isolation Level

You can use the following keywords instead:

· READ committed

· READ UNCOMMITTED

· Repeatable READ

· SERIALIZABLE

· SNAPSHOT

These are basic SQL Server transaction isolation modes.