Always forget the scene: MySQL InnoDB four transaction levels with dirty read, no repetition, Phantom read

MySQL INNODB Transaction ISOLATION level dirty read, repeatable read, Phantom read

The level of isolation for MySQL InnoDB transactions is level four, which is "repeatable read" by default (Repeatable Read).

· READ UNCOMMITTED (readuncommitted). Another transaction modifies the data but has not yet committed, and select in this transaction reads the uncommitted data (dirty read).

· Submit Read (readcommitted). This transaction reads the most up-to-date data (after other transactions have been committed). The problem is that in the same transaction, two times the same select will read different results (do not repeat).

· Repeatable Read (RepeatableRead). In the same transaction, the result of select is the state of the point in time at which the transaction started, so the same select operation will read the same result. However, there is a phantom reading phenomenon (explained later).

· Serialization (SERIALIZABLE). A read operation implicitly acquires a shared lock, which guarantees mutual exclusion between different transactions.

Four levels are gradually enhanced, and each level solves a problem.

· Dirty read, the most easy to understand. Another transaction modifies the data but has not yet committed, and select in this transaction reads the uncommitted data.

· Do not repeat the repetition. After the dirty read is resolved, the same transaction is executed, another transaction commits the new data, so the result of the data read two times in this transaction will be inconsistent.

· Phantom read. solves the non-repetition, guarantees the same transaction, the result of the query is the state (consistency) at the beginning of the transaction. However, if another transaction commits new data at the same time, when the transaction is updated, the new data is "surprisingly" discovered, as if the previously read data were "ghost" hallucinations.

Always forget the scene: MySQL InnoDB four transaction levels with dirty read, no repetition, Phantom read