Transaction

Isolation Levels

Glossary

Term	Definition
Dirty Read	A dirty read refers to reading data from another transaction that has not yet been committed. Uncommitted means that this data may be rolled back, which means it may not ultimately be stored in the database, i.e., it does not exist. Reading data that may not ultimately exist is a dirty read.
Repeatable Read	Repeatable read means that within a transaction, the data read at the beginning and at any time before the end of the transaction are consistent. This mainly ensures the accuracy of data updates.
Non-repeatable Read	In contrast to repeatable read, non-repeatable read means that within the same transaction, the same batch of data read at different times may be different and may be affected by other transactions, such as other transactions modifying this batch of data and committing it. Non-repeatable reads often occur during data updates.
Phantom Read	Suppose transaction A changes the content of some rows, but has not yet committed. At this time, transaction B inserts a record row that is the same as the record before transaction A's change, and commits before transaction A. At this time, when querying in transaction A, it seems that the recent change has not taken effect for some data, but in fact, it was just inserted by transaction B, which makes the user feel very magical and feels like a hallucination. This is called a phantom read. Phantom reads often occur during data insertion.

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read	Description
READ-UNCOMMITTED	√	√	√	Allows reading of uncommitted data changes, meaning that changes to intermediate data will also be read. Uncommitted data may also be rolled back, leading to reading non-existent data.
READ-COMMITTED	×	√	√	Allows reading of data that concurrent transactions have committed
REPEATABLE-READ	×	×	√	The results of multiple reads of the same field are consistent, unless the data is modified by the transaction itself
SERIALIZABLE	×	×	×	Serialization, the highest isolation level, all transactions are executed in sequence without interference

Warning

The default isolation level of the database is REPEATABLE-READ, which cannot prevent phantom reads

In MySQL, MVCC (Multi-Version Concurrency Control) is mainly used to implement isolation levels. MVCC is implemented by saving a snapshot of the data at a certain point in time, so that the data seen by the transaction during execution is consistent. You can also use locks (Next-key lock) to implement isolation levels, but locks can cause performance problems, so MySQL chose MVCC.