SQL Server transaction operation Isolation Mode introduction, SQL Server

SQL Server transaction operation Isolation Mode introduction, SQL Server

In general, it is recommended that SQL Server isolate transaction operations in the default form. After all, isolation is one of the basic principles of the ACID model. However, sometimes, due to business requirements, the database administrator has to violate the default behavior, instead of taking a more flexible approach to isolate transaction operations. In this case, the Database Administrator provides five different transaction operation isolation modes. Before detailed introduction to the isolation modes of these transaction operations, you must first address the database problems that the Administrator may encounter:

　　1. Dirty data read/writeThis occurs when a transaction reads and writes data modified by another transaction but not committed. If another transaction never commits its modification data, the first transaction will always get an invalid value, that is, dirty data.

　　2. Repeated read/write is not allowed.This occurs when a transaction attempts to read the same data repeatedly, and another transaction modifies the data before the first transaction reads the data repeatedly. This will enable the first transaction to obtain two different values when reading the same data, resulting in non-repeated reading of the original data.

　　3. Read and Write ImagesThis occurs when a transaction performs multiple data queries in a table, and another transaction inserts or deletes data rows that meet the query conditions. This will cause the previous transaction to get or lose an "image" value.

Every Isolation Mode of SQL Server tries to solve the above problems, so that the database administrator can maintain a balance between transaction operation isolation and business requirements. The following are the five isolation modes of SQL Server:

　　1. read/write submission Isolation ModeThis is the default Isolation Mode of SQL Server. The database does not allow transaction operations to read and write data written by uncommitted transaction operations. This mode prevents dirty data reading and writing, but does not prevent image reading and writing or repeated reading and writing.

　　2. read/write uncommitted Isolation ModeThis mode is basically not isolated between transaction operations. Any transaction can read and write data from another uncommitted transaction. In this mode, transaction operations are prone to dirty data read/write, image read/write, and non-repeated read/write.

　　3. Repeated read/write Isolation ModeIt is more advanced than the read/write commit isolation mode, which can prevent the transaction from modifying the data being read and written by another transaction until the read/write operation ends. This isolation mode can prevent dirty data read/write and non-repeated read/write.

　　4. serialize Isolation ModeThis mode uses the range lock to prevent a transaction from inserting or deleting data rows when reading data from another transaction. The serial isolation mode can prevent the above three situations.

　　5. Snapshot isolation ModeThis mode can also prevent the occurrence of three situations, but the methods are different. It provides a "snapshot" for each transaction to query data. transactions can query snapshots without returning them to the source data table, thus preventing dirty Data Reading.

To change the isolation mode used by SQL Server, enter the following command:

　　Copy codeThe Code is 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.

SQL server issues transaction isolation level,

You have two processes (for example, two in the same tool)

The setting is not automatically submitted.

One program operates on the same table, and the other program reads data

You can see the difference.

If you search for this, there should be a ready-made example.

The transaction mode and features of SQL SERVER

Three modes:
1. explicit transactions: both start and end transactions are controlled by explicit commands.
2. Automatic transaction commit: the default mode of the Data Engine. Each separate statement is committed after completion and rolled back after failure.
3. Implicit transactions: a statement is a new transaction until the transaction ends.