MySQL isolation level, mysql isolation

MySQL isolation level, mysql isolation

1. read uncommitted, read uncommitted. Transaction A can read the data modified but not committed by transaction B. Dirty read

2. read committed, read committed. Transaction A can only read the data committed by transaction B, but since the transaction reads the data snapshot before each SELECT, the results of multiple SELECT operations for the same transaction may be different. It cannot be read repeatedly. Default isolation levels for most relational databases (such as Oracle)

3. repeatable read, which can be repeatable. Transaction A can only read the data committed by transaction B, and the data read by the same transaction each time is the same. This is because the transaction reads the data snapshot at the beginning of the transaction, and only one copy of the transaction remains unchanged until the transaction ends. However, when a data snapshot is generated at the beginning of a transaction, if the data that has been modified in different transactions but has not been committed conflicts (primary key/unique key conflicts, etc ), then, the transaction that executes the modification will be suspended until the transaction that executes the modification is committed, but the conflicting data cannot be read, that is, phantom read.

For example, if table T has A unique key UK, transaction A first inserts UK = x into T, but does not commit, and transaction B inserts UK = x into transaction T. In this case, transaction B is suspended; if transaction A commits, transaction B will report an error due to A unique key conflict. If transaction A deletes UK = x and commits the error, transaction B's insert operation will succeed; however, until transaction B commits, it does not SELECT the UK = x inserted by transaction. This is the default isolation level of MySQL.

4. serializable, serialization. Transactions must be executed serially and completely isolated.

To sum up:

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

Secondly, the isolation level is based on non-isolation (dirty read) => non-repeated READ => Repeatable read but Phantom READ => full serialization (disable concurrency to solve phantom read ), gradually strengthen the degree of isolation

In addition:

There are many errors in understanding phantom reading on the Internet. For example:

Some articles reference high-performance MySQL. MySQL's InnoDB Engine solves phantom read problems at the repeatable read level through MVCC (Multi-version concurrency control. Actually, it is wrong. MVCC uses a mechanism similar to optimistic locks to solve phantom read in principle. At the same time, it can be seen from the above example that the Phantom read problem is not solved at the repeatable read level.

Some articles use A description like this: When transaction (A) reads A certain range of data, another transaction (B) inserts some data in this range, when the original transaction (A) updates the data within this range, it will be "surprised" to find the data, just like "phantom. This type of description is vague. What is "surprise" and "phantom "? Based on the preceding example, we can see that transaction A is surprised to perceive the data because it does not SELECT the data updated by transaction B, however, this data will affect A's operations. Like A phantom, it does not exist, but it already exists.

Finally, let's summarize the ease of memory for transactions:

"Read not committed" means that data can be read regardless of whether other transactions are committed.

"Read committed" refers to the data that can be read only after other transactions are committed.

"Repeatable" refers to the data that can only be read from other transactions after being committed.

"Serialization" refers to the execution of transactions one by one. Before the current transaction is committed, other transactions are suspended.