MYSQL-INNODB Transaction ISOLATION LEVEL

For an introduction to these four isolation levels:

• Unread (Read UNCOMMITTED). Another transaction modifies the data but has not yet committed, and select in this transaction reads the uncommitted data (dirty read).

• Submit Read (COMMITTED). 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 (Repeatable read). 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.

These 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.

Or the test results to illustrate these problems, the following results based on the MySQL version is 5.1.53.

READ UNCOMMITTED

Sessiona

Sessionb

PS: From the results, when Sessiona has not yet commit, SESSIONB can already read the update data, although there is no commit. This results in "dirty reads".

read-committed

Ps:sessiona only after the submission, SESSIONB can read the update data, which avoids the ' dirty read ', but there is still a problem, that is not repeatable read. SESSIONB in the same transaction, the data read two times is inconsistent.

Repeatable READ

PS: By setting the REPEATABLE Read isolation level, resolves the problem of multiple read data inconsistencies in the same transaction.

But there is still a problem, that is ' phantom reading ', for example, transaction a finds no data in the table before insert, and then prepares the insert data, while transaction B inserts the data into the table and commits.

At this point, transaction a continues with the insert, and if a field has a unique constraint, the insert conflicts.

SERIALIZABLE

PS: This solves the problem of phantom reading, but affects the concurrent read and write performance, Sessiona acquired a shared lock, SESSOINB is a suspended state, only Sessiona commit, SESSIONB can update (not time-out premise).

MYSQL-INNODB Transaction ISOLATION LEVEL