MySQL Physical isolation level

Four levels of isolation are implemented by MySQL

READ UNCOMMITTED (UNCOMMITTED)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).

Read Committed (non-repeatable)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results. (The result of a thing being read multiple times may be different.) Also called submit read.

REPEATABLE READ (Repeatable Read)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same data row when concurrently reading data (MySQL solves the problem with a snapshot). In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reading refers to when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range again, a new phantom row is found (MySQL solves the problem with a gap lock). The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism. (The result of a thing being read multiple times).

Serializable (Serializable)
This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

--------------------------------------------------------------------------------------------------------------- ----------------------------------

REPEATABLE Read Isolation Level derivation issues summary

Phantom Read : a transaction during Operation! There are other transactions that modify and commit this DataSet, but the first transaction is not read, and when the transaction is committed, it is possible to cause the data that was inserted to be not queried, but there is a duplicate error!

the difference between non-repeatable reads and Phantom reads :

Non-repeatable reading is the ability to read the data that has been committed by other transactions, and the Phantom read is not able to read the data submitted by other transactions!

Gap Lock : The gap lock is mainly used to prevent phantom reading, used in the Repeatable-read isolation level, refers to when the data conditions, the scope of the search, its scope may exist in the value of lock!

---------------------------------------

For example, where ID <8 lock in Share mode, a gap lock is added to the value below 8.

Select Max (ID) ... lock in share mode, a gap lock is added to the nonexistent value above max (ID)!

SELECT * from E where id=20 lock in share mode is only for id=20 locking! (This may be just a common shared lock)

SELECT * FROM e-lock in share mode; Then the entire e plus gap table lock!

Phantom Read case: There is a table (ID field is unique constraint) before each insert to query the maximum value of this field, and then take the maximum value of +1 insert!

Transaction 1: Transaction 2:

Select Max (id) from E; INSERT into e values (11)

Ten commits;

INSERT into e values (11)

Commit

ERROR 1062 (23000): Duplicate entry ' One ' for key ' ID '

In the above transaction 1 clearly query maximum value is 10, but inserted the maximum value of +1 when the error!

Solution: Use MySQL gap lock

Transaction 1: Transaction 2:

Select Max (ID) from e-lock in share mode;

(a gap lock is added for all non-existent values with an ID of 10 or more)

INSERT into e values (11);

INSERT into e values (one) commit; At this point, the commit will be in a waiting state,

Commit

---------------------------------------

The magic read problem is solved, but the above example will cause the above INSERT statement to wait.

MySQL Physical isolation level