Detailed explanation of transaction isolation level in MySQL

1. Transaction concurrency issues

When multiple transactions operate the same data at the same time, the following concurrency problems may occur:

• Dirty Read: One transaction reads data that has not been modified by another transaction. If the latter rolls back, the data read by the former is invalid.
• Non-repeatable Read: In the same transaction, the same query is executed twice, and the query results are different due to the submission of another transaction.
• Phantom Read: One transaction is between two queries, and the other transaction inserts or deletes data, resulting in inconsistent records in the previous and subsequent queries.

To avoid these problems, the SQL standard defines four transaction isolation levels, which MySQL also supports.

2. MySQL transaction isolation level

MySQL viaSET TRANSACTION ISOLATION LEVELStatement to set transaction isolation level:

SET SESSION TRANSACTION ISOLATION LEVEL <level>;
SET GLOBAL TRANSACTION ISOLATION LEVEL <level>;

in<Level>Can beREAD UNCOMMITTED、READ COMMITTED、REPEATABLE READorSERIALIZABLE。

1. READ UNCOMMITTED (read not submitted)

Features：

• Transactions can read data from other uncommitted transactions.
• A "dirty reading" problem may occur.
• Performance is better because locks are not used to limit reading.

Example：

• • Transaction A modified a record but has not been committed yet.
  • Transaction B reads the modified data before Transaction A commits.
  • If transaction A rolls back, the data read by transaction B is "dirty data".

Applicable scenarios：

• • Allows reading of unsubmitted data, suitable for applications that are not very concerned about data consistency, such as logging, monitoring data, etc.

2. READ COMMITTED (read submitted)

Features：

• Only the submitted data can be read, avoiding "dirty reading".
• A "no-repeatable" problem may occur.
• MySQL InnoDB byMVCC(Multi-version concurrent control) to implement this isolation level.

Example：

• Transaction A reads a record.
• Transaction B modifys the record and submits it.
• Transaction A reads the record again and finds that the data has changed (it cannot be read repeatedly).

Applicable scenarios：

• • Applicable to most OLTP (online transaction processing) systems, such as bank transfers, order management systems, ensures that the read data is submitted but allows data to be updated.

3. REPEATABLE READ (repeatable) (MySQL default level)

Features：

• When the same data is read multiple times within a transaction, the data remains consistent (even if other transactions modify and commit data).
• passMVCCImplement repeatable reading.
• Avoid "dirty reading" and "no repetitive reading".
• But "illusion reading" problems may still occur.

Example：

• Transaction A reads a piece of data for the first time.
• Transaction B modifys the data and commits it.
• Transaction A reads the data again and finds that the data has not changed (because Transaction A reads a snapshot at the beginning of the transaction).

How to solve the problem of fantasy reading?

• MySQL InnoDB byNext-Key LockingThe mechanism is used to solve the problem of phantom reading, that is, locking the scope so that other transactions cannot insert new data, thereby preventing phantom reading.

Applicable scenarios：

• • Suitable for high concurrency scenarios, especially in financial industries, such as bank account inquiry and order management systems.

4. SERIALIZABLE (serializable)

Features：

• The highest level of isolation completely avoids dirty reading, non-repetitive reading and phantom reading.
• Transactions must be executed sequentially, and cannot be parallel, usually usedTable lockorLock。
• The concurrency performance is extremely poor and is suitable for scenarios with extremely high requirements for data consistency.

Example：

• Transaction A reads a certain piece of data, and transaction B must wait until Transaction A completes before reading or modifying the data.

Applicable scenarios：

• • It is suitable for scenarios that require strict data consistency, such as financial settlement, ticketing systems, etc.

3. MySQL default transaction isolation level

The default transaction isolation level of the MySQL InnoDB storage engine isREPEATABLE READ (repeatable), which is different from the default level of SQL standard (READ COMMITTED). MySQL viaMVCC(Multiple version concurrent control) andGap lockSolved the problem of fantasy reading, soREPEATABLE READ is stronger than SQL standard in MySQL。

If you want to modify the default transaction isolation level, you can(Modified in MySQL configuration file):

[mysqld]
transaction-isolation = REPEATABLE-READ

Or change at runtime:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

4. The impact of different isolation levels on concurrency problems

5. How to choose the appropriate isolation level?

• READ UNCOMMITTED: Suitable for scenarios with low requirements for data consistency, such as log analysis, cached data, etc.
• READ COMMITTED: Suitable for most business scenarios, such as e-commerce systems, user management systems, etc., to avoid dirty reading and improve concurrency performance.
• REPEATABLE READ (MySQL default): Applicable to financial system and inventory management, ensuring consistency of data within transactions and preventing non-repeatability.
• SERIALIZABLE: Suitable for scenarios with extremely high requirements for data consistency, such as bank settlement and core financial systems, but with large performance losses.

Summarize

• READ UNCOMMITTED: Dirty reading, non-repeatable reading, or phantom reading may occur, with the highest performance but the lowest security.
• READ COMMITTED: Prevent dirty reading, but non-repetitive reading and phantom reading may occur.
• REPEATABLE READ (MySQL default): Prevent dirty reading and non-repeatable reading. MySQL can also avoid phantom reading and is suitable for most highly concurrent businesses.
• SERIALIZABLE: All concurrency problems can be avoided, but the performance is the worst.

MySQL is used by defaultREPEATABLE READ, mainly because MySQL passesMVCCIt solves most concurrency problems, which can maintain a high transaction isolation level without affecting too much performance.

There are four types of transaction isolation levels in MySQL, namely:

Read Uncommitted：

This level allows transactions to read data that other transactions have not yet committed (dirty read). This means that one transaction can read data in the middle state of another transaction, which may cause data inconsistency.

Read Committed：

This level ensures that transactions can only read committed data and prevent dirty reading. Even so, "No Repeatable Reading" is still allowed to occur (the same data is read twice in the same transaction, the values ​​may be different because the other transaction has modified the data and committed).

Repeatable Read：

This level ensures that the results are always consistent when the same data is read multiple times in a transaction, avoiding "no repetitive reading". However, "phantom read" may still occur (i.e., the result set of transaction reads changes because another transaction inserts a new record).

Serializable：

This is the highest isolation level, which forces transactions to execute serially, that is, transactions are queued for execution, and one transaction cannot access the same data before it completes. This completely avoids dirty reading, non-repeatable reading, and phantom reading, but has lower performance.

This is the end of this article about the transaction isolation levels in MySQL. For more related content on mysql transaction isolation levels, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!