Author: Jiang Xia

| zhihu: www.zhihu.com/people/1024…

| GitHub:github.com/JiangXia-10…

| CSDN:blog.csdn.net/qq_4115394…

| the nuggets: juejin. Cn/user / 651387…

| public no. : 1024 notes

This article contains 1,989 words and takes 8 minutes to read

A few concepts need to be explained before describing the isolation level of a transaction.

1. Transaction

A transaction is the smallest unit of work of a database operation, a series of operations performed as a single logical unit of work, and a set of operations that cannot be separated. These operations are submitted to the system as a whole and either all or none of them are executed.

2. Four Properties of Transactions (ACID)

For any database that supports transactions, they must have four major features Atomicity, Consistency, Isolation, and persistence (transaction ACID) to ensure that data in a transaction is correct.

Atomicity:

A transaction is the logical unit of work of a database in which all or none of the operations involved are performed. If any operation fails, the entire transaction will fail, and all other operations that have been performed will be undone and rolled back. The entire transaction is considered to have completed successfully only if all operations succeed.

Consistency:

The result of the transaction execution must change the database from one consistent state to another. So a database is said to be in a consistent state when it contains only the results of successful transaction commits. If a failure occurs during the operation of the database and some transactions are interrupted before completion, some of the changes made by these unfinished transactions have been written to the physical database, then the database is in an incorrect state, that is, an inconsistent state. Consistency is closely related to atomicity.

Isolation,

The execution of a transaction cannot be interfered by other transactions. The operations and data used within the transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

persistence

Once a successfully executed transaction is committed, its changes to the data in the database should be permanent. It will not be affected by any other operation or failure.

3. Transaction isolation level

When multiple threads in the system start transaction operation of data in the database, the database system should be able to isolate operations to ensure the accuracy of data acquisition by each thread. If transactions are not isolated, dirty reads, unrepeatable reads, and virtual reads (phantom reads) generally occur

Dirty read:

A dirty read is one that reads data from another uncommitted transaction during a transaction.

For example, there is 100 yuan in the bank card, at this time, we put 100 yuan in the bank card to be operated by transaction A, but transaction A has not submitted the transaction, at this time, transaction B wants to query the bank card money, the query result is 200 yuan, this is dirty read, transaction A has not submitted the transaction, transaction B read.

Unrepeatable read:

Non-repeatable reads are those in which different values are returned by multiple queries within the scope of a transaction for some data in the database, because the data has been modified and successfully committed by another transaction within the interval of multiple queries. For example, if the bank card has 100 yuan, transaction A queries the money in the bank card for the first time and the result is 100 yuan, which is correct. Then it queries again, and in the interval between the first time and the second time, transaction B updates the money in the bank card to 1000 yuan, at this time the two query results are inconsistent.

Phantom read

Phantom reading is a phenomenon that occurs when a transaction does not execute independently.

For example, transaction A changes the value of the bank card table from 100 to 200, and then transaction B inserts A row into the table with the value of 100 and submits it to the database. If the user of transaction A looks at the data that was just modified, they will find that the value of the data that was inserted in the row is not changed, and the user of transaction A does not know that the data was inserted in transaction B, as if it were an illusion, that’s what happened.

Illusory reads and unrepeatability are those in which a transaction reads data that has been successfully committed by another transaction, while dirty reads are those in which a transaction has been read that has not yet committed. To prevent dirty reads, unrepeatable reads, and phantom reads, you need to set the isolation level of the database based on actual requirements.

A database can be divided into four isolation levels: Read Uncommitted, Read Committed, Repeated Read, and Serializable.

Read unsubmitted:

Read uncommitted indicates that one transaction can read the content that another transaction has not committed. In this case, dirty reads are generated. Therefore, you need to set the isolation level of the transaction to read committed to effectively solve the dirty read problem. Read uncommitted is the smallest of all isolation levels and does not solve any of the problems mentioned earlier.

Read submitted:

Read committed isolation levels solve the problem of dirty reads. Under this isolation level, parallel execution between two uncommitted transactions is not allowed, but it allows one transaction to be executed and committed while another transaction is executing. This can lead to the previously mentioned non-repeatable and phantom read situations.

Repeatable reading:

Repeatable read, can solve the problem of non-repeatable read. Under this isolation level, while a transaction is consuming data on a row, other transactions are not allowed to operate on that row’s data. However, at this isolation level, other transactions are still allowed to insert and delete data from the table, so the phantom read situation mentioned earlier cannot be solved.

Serialization:

Serialization isolation level will make the transaction execute completely serialized, which can effectively solve the problem of phantom read, but at the cost of losing concurrent execution efficiency, so it is rarely used. In MySQL database, the default isolation level is repeatable read.

The serialization isolation level is the highest, while the read uncommitted isolation level is the lowest. The higher the isolation level is, the lower the execution efficiency is, but the more problems are solved. The lower the isolation level is, the higher the execution efficiency is, but the more problems are caused.

In the MySQL database, the above four isolation levels are supported, and the default is repeatable read. In Oracle databases, only the serialization isolation level and read committed isolation level are supported, with the read committed isolation level being the default.

4. Set the isolation level

Viewing the Isolation Level

mysql> show global variables like %isolation%; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | Transaction_isolation | the REPEATABLE - READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + row in the set (0.00 seCopy the code

You can also use the variable name if you know it

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
row in set (0.00 sec)
Copy the code

Setting the Isolation Level

mysql> set global transaction_isolation =read-committed; Query OK, 0 rows affected (0.00 SEC) mysql> show global variables like % Isolation %; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | Transaction_isolation | READ - COMMITTED | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + row in the set (0.00 SEC)Copy the code

At the end

This article provides a basic introduction and summary of transaction isolation levels and related concepts. This question should be asked in a written interview.

Finally, welcome to pay attention to the public number: 1024 notes, free access to massive learning resources (including video, source code, documents)!

Related recommendations:

  • Spring annotation (3) : @scope sets the scope of the component
  • Spring is worth your collection!!
  • Spring annotation (7) : Assign attributes to beans using @value
  • Spring Note (5) : Four ways a container can register a component
  • Spring Note (6) : Four ways to customize initialization and destruction methods during the Bean’s life cycle