There are four types of transaction isolation levels in MySQL: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. It is arranged in increments according to the degree of isolation, and Repeatable Read is used by default. 1. Read Uncommitted may cause dirty reading, non-repeatable reading, and phantom reading; 2. Read Committed prevents dirty reading, but may not repeat and phantom reading; 3. Repeatable Read prevents dirty reading and non-repeatable reading, and phantom reading is also avoided through the Next-Key lock mechanism in InnoDB; 4. Serializable prevents all concurrency problems, but has the worst performance. When choosing, you need to weigh consistency and performance. For example, Serializable is used in financial systems, Read Committed is commonly used in OLTP scenarios or MySQL default Repeatable Read. Set the method to SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL level_name. Note that MVCC, engine differences and deadlock problems do not affect the isolation level settings.

MySQL's transaction isolation level, to put it bluntly, controls what data can and cannot see between multiple transactions when they are executed concurrently. If you don't understand it, you will easily encounter problems such as dirty reading, non-repeatable reading, and fantasy reading. This article will not talk about too much theory, let’s just talk about the key points and practical applications.

What are the transaction isolation levels?

MySQL supports four standard transaction isolation levels:

• Read Uncommitted (read not submitted)
• Read Committed (read submitted)
• Repeatable Read (repeatable)
• Serializable (serialization)

From top to bottom, the isolation level is getting higher and higher, but the performance impact is greater. You can understand it as "the stronger the protection, the slower the speed".

By default, MySQL uses the Repeatable Read isolation level. You can check the isolation level of the current session or globally by command SELECT @@tx_isolation; ;.

What problems are prevented from each isolation level?

Different isolation levels can prevent different problems. Three common questions are:

• Dirty Read : One transaction reads data that another transaction has not yet committed.
• Non-Repeatable Read : The same query is executed multiple times in the same transaction, and the results are different.
• Phantom Read : One transaction executes a scope query, and then another transaction inserts a new record. When querying again, it finds that one more one is found.

Note: MySQL's InnoDB engine has avoided the phantom reading problem through the Next-Key lock mechanism at the Repeatable Read level.

How to choose in actual use?

Which isolation level to choose depends on your business's needs for consistency and performance.

• Read Uncommitted : Almost no use, the risk is too high.
• Read Committed : Oracle uses this by default, suitable for most OLTP scenarios.
• Repeatable Read : The default level of MySQL is suitable for scenarios that require strong consistency but do not want to sacrifice too much performance.
• Serializable : The safest, but poor performance, suitable for financial high consistency requirements systems.

The method to set the isolation level is also very simple:

 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

You can also change to GLOBAL to affect all new connections:

 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

However, be careful when changing the overall situation, as it may affect the behavior of the entire database.

Common misunderstandings and precautions

Some students may think that everything will be fine if they set the isolation level. In fact, there are a few more things that are easy to get into:

• The impact of MVCC and lock mechanisms : The different isolation levels are achieved by MVCC (multi-version concurrent control) and locks. For example, under RR, MVCC can ensure a consistent view read, while under RC, a new view is generated every time you read it.
• Different engines behave differently : like MyISAM does not support transactions, so the isolation level does not make sense to it. InnoDB is the mainstream engine and the focus of our discussion.
• The deadlock problem has nothing to do with the isolation level : many people mistakenly think that the isolation level can solve the deadlock, but it is not. Deadlock is more caused by improper locking order and has little to do with the level.

Basically that's it. Understanding the isolation level is not complicated, but it is particularly easy to ignore details. Especially when you do concurrent writes or cross-transaction queries, if you are not careful, problems will arise.

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