Four isolation levels in the SQL standard

Source: Internet
Author: User

Read uncommited (UNCOMMITTED)

At the Rerad uncommited level, modifications in a transaction are visible to other transactions, even if they are not committed. The transaction can read uncommitted data, which also becomes dirty read (Dirty read). This level can cause a lot of problems, in terms of performance, read uncommited will not be much better than the other levels, but there is a lack of other levels of benefits, unless there is a very necessary reason, in practical applications generally rarely use read uncommited.

Read commited (submit reading)

The default isolation level for most database systems is read commited (but MySQL is not). READ commited satisfies the simple definition of the previously mentioned isolation: When a transaction starts, it can only see the changes made by the transactions that have been committed. In other words, any changes you make to a transaction from the beginning to the commit are not visible to other transactions. This level is sometimes called non-repeatable (nonerepeatable read), because two times the same query is executed, you may get a different result.

REPEATABLE READ (Repeatable Read)

REPEATABLE READ (Repeatable Read) solves the problem of dirty reads. This level guarantees that the result of reading the same record multiple times in the same transaction is consistent. However, in theory, it is not possible to read the isolation level repeatedly to resolve another phantom read (Phantomread) problem. The so-called Phantom reads, when a transaction reads a range of records, another transaction inserts a new record within that range, and when the previous transaction reads the record for that range again, a magic line (Phantom row) is generated. The InnoDB and XTRADB storage engines solve the Phantom read problem through multiple version concurrency control (MVCC, Multivesion Concurrency control).

Repeatable Read is the default transaction isolation level for MySQL.

SERIALIZABLE (Serializable)

The serializable is the highest isolation level. It avoids the above-mentioned phantom-reading problem by forcing the transaction serial. Simply put, serializable will add a lock on every line of data read, so it can cause a lot of timeouts and lock expropriation problems. This isolation level is rarely used in real-world applications, and can only be considered if there is a need to ensure that data is consistent and acceptable without concurrency.

Isolation level dirty read possibility non-repeatable read possibility Magic read possibility lock read

READ uncommited Yes Yes Yes NO

READ commited No Yes yes No

Repeatable READ No no YES no



Four isolation levels in the SQL standard

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.