There are four isolation levels defined in the SQL standard, each of which defines the changes made in a transaction, which are visible within and between transactions, and which are not. Lower-level isolation can typically perform higher concurrency and lower system overhead.
- UNCOMMITTED read (READ UNCOMMITTED): In the uncommitted read level, changes in the transaction are visible even if no commits are committed. Transactions can read uncommitted data, which is also known as Dirty reads (Dirty read). This level can cause a lot of problems, and in performance, uncommitted reads are not much better than the others, but there is a lack of many benefits from other levels that are rarely used in practical applications.
- Read Committed: The default isolation level for most database systems is read-committed (but not MySQL). The commit read satisfies the simple definition of the previously mentioned isolation: When a transaction starts, it can only "see" the changes that have been made to the firm that has been submitted. In other words, any changes you make to a transaction from the beginning until it is committed are not visible to other transactions. This level is sometimes called non-repeatable read (nonrepeatable read), because two times the same query is executed, you may get a different result.
- 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. In theory, however, it is not possible to read the isolation level repeatedly to resolve another Phantom read (Phantom Read) problem. The so-called Phantom reading , refers to when a transaction is reading a range of records, another transaction in the scope of the new record inserted, when the previous transaction read the record of the range, will produce a magic line (Phantom row). Repeatable reads are the default transaction isolation level for MySQL .
- Serializable (Serializable): Serializable is the highest isolation level. It avoids the above-mentioned phantom-reading problem by forcing the transaction to execute serially. In simple terms, serializable can add locks to every row of data that is 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 data is consistent and acceptable without concurrency.
Database High score Note 02: Isolation LEVEL
