What are the mysql transaction isolation levels?

There are four transaction isolation levels: 1. Read Uncommitted (read uncommitted), which allows reading of uncommitted data changes, which may cause dirty reads, non-repeatable reads, and phantom reads. 2. Read Committed (Read Committed) allows reading data that has been submitted by concurrent transactions, which can avoid dirty reads, but may cause non-repeatable and phantom reads. 3. Repeatable Read (repeatable read), the results of multiple reads of the same field are consistent. 4. Serializable (serializable).

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

1. What is a transaction?

A transaction is a logical set of operations, either all of them are executed or none of them are executed.

The most classic and often mentioned chestnut in affairs is bank transfer. For example, if Xiao Ming wants to transfer 1,000 yuan to Xiao Hong, this transfer will involve two key operations: reducing Xiao Ming's balance by 1,000 yuan and reducing Xiao Hong's balance by 1,000 yuan. If an error suddenly occurs between these two operations, causing Xiao Ming's balance to decrease but Xiao Hong's balance not to increase, this situation is definitely not allowed. The transaction is to ensure that these two key operations either succeed or neither succeeds.

2. Transaction characteristics (ACID)

• **Atomicity:* *The smallest execution unit of a transaction, no division is allowed. The atomicity of transactions ensures that actions are either all executed or none at all.
• **Consistency:**The data remains consistent before and after executing the transaction. For example, in a money transfer business, the total amount of the transferor and the payee should remain unchanged regardless of whether the transaction is successful or not.
• **Isolation:**When accessing the database concurrently, a user's transaction should not be affected by other transactions, and the database is independent between concurrent transactions.
• **Persistence:**After a transaction is committed, its changes to the data in the database are durable and should not be affected even if the database fails.

3. Problems caused by concurrent transactions

In a typical application, multiple transactions run concurrently, often Manipulate the same data to complete their respective tasks (multiple users operating on the same data). Although concurrency is necessary, it may cause the following problems:

• **Dirty read: **When a transaction is accessing data and has modified it, but it is still The transaction was not submitted. At this time, another transaction also accessed the data and then used the data. Because the modification of the data has not been submitted to the database, the data read by the other transaction is "Dirty Data ", this behavior is "Dirty Read", and operations based on "Dirty Data" may cause problems.
• Lost of modify: means that when a transaction reads a piece of data, another data also accesses the data, then after the first transaction modifies the data , the second transaction also modified this data. In this way, the modification results in the first transaction are lost. This situation is called Modification loss. For example: transaction 1 reads the data A=20 in the table, transaction 2 also reads A=20, transaction 1 modifies A=A-1, transaction 2 also modifies A=A-1, and the final result is 19, but the modification record of transaction 1 is lost.
• Unrepeatable read: refers to reading the same data multiple times within a transaction. Before the transaction ends, another transaction also accesses the data and evaluates the data. If the data is modified, the data read twice by the first transaction may be inconsistent. This situation is called non-repeatable read.
• Phantom read (Phantom read): Phantom read is similar to non-repeatable read. Phantom read refers to a transaction that reads several rows of data. The transaction has not ended yet, and then Another transaction inserts some data. In subsequent queries, the first transaction reads more data than originally read, as if an hallucination has occurred, so it is called phantom reading.

The difference between non-repeatable reading and phantom reading:

The focus of non-repeatable reading is modification, while the focus of phantom reading is addition or deletion.

Chestnut 1 (Same conditions, the data you have read will be different when you read it again): Mr. A in transaction 1 has read that his salary is 1,000. The operation has not ended yet, and the transaction Mr. B in 2 modified Mr. A's salary to 2000. When Mr. A read his salary again, it became 2000. This is a non-repeatable read.

Chestnut 2 (same conditions, the number of records read out for the first and second times is different): If there are 4 people in a salary table with a salary greater than 3,000, transaction 1 reads all For people with a salary greater than 3,000, a total of 4 records were queried. This is because transaction 2 queried another record with a salary greater than 3,000. Transaction 1 read again and found 5 records. This is a phantom read.

4. Transaction isolation levels

The SQL standard defines four isolation levels:

• **READ-UNCOMMITTED: **The lowest isolation level, allowing reading of uncommitted data changes, may cause dirty reads, non-repeatable reads, and phantom reads.
• **READ-COMMITTED: **Allows reading of data that has been submitted by concurrent transactions, can avoid dirty reads, but may cause non-repeatable and phantom reads.
• **Repeatable read (REPEATABLE-READ): **The results of multiple reads of the same field are consistent unless the transaction itself is modified, can avoid dirty reads and unresolved reads Repeated reading may cause phantom reading.
• **Serializable (SERIALIZABLE): **The highest isolation level, fully compliant with the ACID isolation level, all transactions are executed in sequence, can avoid dirty reads and non-repeatable reads , phantom reading.

##Read submitted×√√##Repeatable readSerializable

MySQL InnoDB The default transaction isolation level of the storage engine is Repeatable Read (REPEATABLE-READ) , which can be obtained through the command select @@tx_isolation; statement View, MySQL 8.0 This statement is changed to SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

MySQL InnoDBRepeatable read and merge of the storage engine Phantom reads cannot be avoided, and the application needs to use locked reads to ensure that the mechanism used for locked reads is Next-Key Locks.

Because the lower the isolation level, the fewer locks requested by the transaction, so the isolation level of most database systems is READ-COMMITTED , InnoDB The storage engine uses REPEATABLE-READ by default without any performance loss.

InnoDBThe storage engine generally uses the serializable isolation level in the case of distributed transactions.

? Expand (the following content is excerpted from Chapter 7.7 of "MySQL Technology Insider: InnoDB Storage Engine (2nd Edition)"):

The InnoDB storage engine provides support for XA transactions , and supports the implementation of distributed transactions through XA transactions. Distributed transactions refer to allowing multiple independent transaction resources to participate in a global transaction. Transactional resources are typically relational database systems, but can be other types of resources. Global transactions require that all participating transactions must either be committed or rolled back, which further improves the original ACID requirements for transactions. In addition, when using distributed transactions, the transaction isolation level of the InnoDB storage engine must be set to SERIALIZABLE.

4. Actual situation demonstration

MySQLIn the default configuration of the command line, transactions are automatically submitted , that is, executing the SQL statement will immediately execute the COMMIT operation. You can start a transaction with the command START TRANSACTION.

We can set the transaction isolation level through the following command.

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

Let’s take a look at some of the concurrency control statements we use in actual operations:

• START TRANSACTION | BEGIN: Open a transaction explicitly.
• COMMIT: Commit the transaction, making all modifications to the database permanent.
• ROLLBACK: Roll back to the end of the user's transaction and undo all uncommitted modifications in progress.

(dirty read) read uncommitted

(Avoid dirty reads) Read committed

##Non-repeatable read

It’s still the read-committed diagram above. Although it avoids reading uncommitted, it does occur. Before a transaction ends, a non-repeatable read problem occurs.

Repeatable reading

[External link image transfer failed, the source site may have anti-leeching mechanism, it is recommended to save the picture and upload it directly (img-ysjbfC4b-1651149978452)(https://qtspace.cn/contentimg/81.jpg)]

phantom reading

Demonstrate the occurrence of phantom reading

sql script 1 When querying the record with a salary of 500 for the first time, there is only one record, sql script 2 inserts A record with a salary of 500 was obtained. After submission, sql script 1 used the current read query again in the same transaction and found that two records with a salary of 500 appeared. This is a phantom read.

There are some similarities between phantom reading and non-repeatable reading, but the focus of non-repeatable reading is modification, while the focus of phantom reading is addition or deletion.

Methods to solve phantom reads

Adjust the transaction isolation level to
• SERIALIZABLE.
At the repeatable read transaction level, add a table lock to the table in the transaction operation.
• At the repeatable read transaction level, add
• Next-Key Locks to the table of the transaction operation.

Description:

Next-Key Locks Equivalent to row lock gap lock

[Related recommendations:

mysql video tutorial】

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	√	√	√
×	×	√
×	×	×

The above is the detailed content of What are the mysql transaction isolation levels?. For more information, please follow other related articles on the PHP Chinese website!