One, read uncommitted (not submitted)
At the READ uncommitted level, modifications in a transaction are visible to other transactions, even if they are not committed. Transactions can read uncommitted data, which is also known as dirty reads (Dirty read). This level can cause a lot of problems. In terms of performance, READ uncommitted is not much better than the other levels, but lacks many of the benefits of other levels, which are rarely used in real-world development unless there are really very necessary reasons.
Second, read COMMITTED (submit reading)
The default isolation level for most database systems is read COMMITTED, but MySQL is not. READ committed satisfies a simple definition of rationality: When a transaction starts, it can only "see" the changes that have been made to a transaction that has already been committed. In other words, any changes you make to a transaction before it starts to commit are not visible to other transactions. This level is sometimes called non-repeatable read (nonrepeatable), because two times the same query is executed, you may get different query results.
Third, Repeatable read (repeatable read, MySQL default isolation level)
This is the default isolation level for MySQL. Repeatable read resolves the problem of dirty reads. This level guarantees that the result of reading the same record multiple times in the same transaction is consistent. In theory, however, the repeatable read isolation level does not solve the problem of another phantom read (Phantom read). The so-called Phantom Reading, refers to when a transaction is reading a range of records, another thing in the scope of inserting new records. A phantom row (Phantom row) is generated when the previous transaction reads the record in that range again. The InnoDB and XTRADB storage engines solve the Phantom read problem by using the multi-version ice control (MVCC, Multiverson Concurrency controls).
Iv. SERIALIZABLE (Serializable)
The serializable is the highest isolation level. He avoids the above-mentioned phantom-reading problem by forcing the transaction to execute serially. Simply put, serializable locks on every row of data read, so it can cause a lot of timeouts and lock contention issues. This isolation level is rarely used in real-world applications, and is only considered when there is a need to ensure that the data is consistent and acceptable without concurrency.
The form is summarized as follows:
ANSI SQL Isolation Level
isolation level |
dirty read possibility |
non-repeatable reading possibilities |
Phantom reading possibilities |
lock |
read UN COMMITTED |
yes |
yes |
yes |
no |
R EAD committed |
no |
yes |
yes |
no |
repeatable READ |
no |
no |
yes |
no |
serializable |
no |
no |
no |
yes |
Four types of isolation levels for MySQL