MySQL Isolation level

1. READ UNCOMMITTED, not submitted. Transaction A can read data that has been modified but not committed by transaction B. Dirty Read

2. Read Committed, Reading has been submitted. Transaction A can read only the data that has been committed by transaction B, but the result of multiple select of the same transaction may be different because the transaction reads a snapshot of the data it was before each select. cannot be read repeatedly. Default isolation level for most relational databases (such as Oracle)

3. REPEATABLE read, repeatable. Transaction A can only read data that has been committed by transaction B, and the same transaction reads the same data every time. This is because the transaction reads a snapshot of the data at the beginning of the transaction, and only one copy of the same transaction remains unchanged until the end of the transaction. However, since the data snapshot is generated at the beginning of the transaction, if there is a conflict (primary key/unique key conflict, etc.) for different transactions that have been modified and uncommitted, then the modified transaction is suspended until the modified transaction commits, but the data that caused the conflict is not read, that is, the Phantom read

For example: Table T has a unique key UK, transaction a first to T insert uk=x but not commit, transaction B again to T insert Uk=x, at this time transaction B will be suspended, if transaction a commits, at this time transaction B will be due to the unique key conflict error; If transaction a deletes uk=x after commit, the insert operation of transaction B succeeds However, until transaction B is committed, it does not select a uk=x that is inserted by transaction A. This is the default isolation level for MySQL

4. Serializable, serialization. Transactions must be executed serially, fully isolated

Comprehensive:

First, the isolation level is for the select operation of the transaction.

Secondly, the isolation level is based on the non-isolated (dirty read) and non-repeatable READ = REPEATABLE Read but there is phantom Read and full serialization (disable concurrency, resolve Phantom Read), gradually enhance the degree of isolation of the

another:

There are many errors in the understanding of phantom reading on the Internet. such as:

Some articles cite" high performance MySQL ", the MySQL InnoDB engine through MVCC (multi-version concurrency control) at the repeatable read level has resolved the problem of phantom reading. The actual error is that MVCC uses a mechanism similar to optimistic locking to not solve the phantom reading in principle, and from the above example, it can be seen that the Phantom reading problem does not resolve in the repeatable read class

Some articles use a description similar to this: when a transaction (a) reads a range of data, another transaction (B) inserts some data into the range, and when the original transaction (a) updates the data within that range, it" surprises " To find these data, as in the "phantom" general. This kind of description is very vague, what is called "surprise", What is "phantom"? As you can see in the previous example, transaction A is "surprisingly" aware of the data because it does not select the data that is updated by transaction B, but the data affects the operation of a, like Phantom, but it already exists

Finally, a summary of ease of memory, for transactions:

" READ UNCOMMITTED "refers to data that can be read by other transactions regardless of whether they are committed

" Read Committed "refers to data that can be read only after other transactions have been committed

repeatable read" means that data submitted by other transactions can be read only after you have committed them

"Serialization" means that transactions are executed one after the other is suspended before the current transaction commits

MySQL Isolation level