I understand database transactions from illusory reading

At the beginning of the database transaction, there will always be dirty read, unrepeatable read, unreal read, read uncommitted, read committed, repeatable read and so on. Only to memorize a database transaction level, and each level can solve the problem, and the transaction level of different database implementation, until completely finished innodb engine’s handling of the transaction, in order to be thoroughly these transactions, the relationship between the when others asked again, can immediately according to the principle of the underlying clear relationship with the theory.

This article uses the InnoDB engine as an example to illustrate that other databases or engines are not well known.

A transaction is a set of operations that either all succeed or all fail. ACID (atomic, consistent, isolated, durable) is the property of a transaction.

The SQL standard defines four isolation levels:

• Read-uncommitted: The lowest isolation level that allows UNCOMMITTED data changes to be READ, potentially resulting in dirty, illusory, or unrepeatable reads.
• Read-committed: Allows concurrent transactions to READ data that has been COMMITTED, preventing dirty reads, but magic or unrepeatable reads can still occur.
• REPEATABLE-READ: Multiple reads of the same field are consistent, unless the data is modified by the transaction itself. This can prevent dirty reads and unrepeatable reads, but phantom reads are still possible.
• SERIALIZABLE: Highest isolation level, fully subject to ACID isolation level. All transactions are executed one by one so that interference between transactions is completely impossible. That is, this level prevents dirty reads, unrepeatable reads, and phantom reads.

And here’s a deceptively simple but confusing chart

The default isolation level supported by the MySQL InnoDB storage engine isREPEATABLE-READ, you can query or modify transaction levels through commands.

We can analyze by each level. The lowest RU level does not need to be processed and dirty data or duplicate data will appear. At the RC and RR levels, InnoDB uses undo log to implement the consistent read view used in multi-version Concurrency Control (MVCC).

1. The RC transaction level allows reading of committed data, including modification and addition of data. This is also the transaction isolation level supported by most databases. At the RC transaction isolation level, only Record lock is used, except for uniqueness constraint check and foreign key constraint check need GAP lock, other places do not use gap lock lock.

2. Innodb uses next-key lock algorithm to avoid unrepeatable read problem, namely illusion problem, in RR repeatable read transaction level. Under next-key lock, index scanning not only locks the scanned index, but also locks the scanned index. It also locks the range covered by these indexes (an open and closed range, including the record itself) in which no inserts are allowed, avoiding the problem of unrepeatable reads caused by other transactions inserting data in this range.

3. At the SERIALIABLE transaction isolation level, InnoDB engine automatically adds LOCK IN SHARE MODE to each select statement and a shared LOCK to each read operation. Since the LOCK is occupied by reads, consistent reads and unlocked reads are no longer supported. Innodb can reach the SERIALIABLE level of SQL standard in RR isolation level, so the SERIALIABLE isolation level is generally not used in local transactions.

By default, innoDB can set the isolation level to REPEATABLE-READ. The lower the transaction level is, the less locks are involved. Although REPEATABLE-READ level will upgrade the Record Lock to next-key Lock. But there is no performance penalty compared to the Read-committed level.