MySQL Transaction Isolation Levels to Actually Solve
Among the many storage engines of MySQL, only InnoDB supports transactions, so the transaction isolation level mentioned here refers to the transaction isolation level under InnoDB.
MySQL does support concurrent execution of multiple transactions. Otherwise, one transaction will handle one request, and when one person’s request is processed, all other transactions will wait, and no one will dare to use MySQL as a database, because the user experience is so bad that they will smash the keyboard.
Since transactions can operate concurrently, here are some questions: a transaction is writing data, another transaction to read this line of data, how to deal with it? A transaction in the write data, another data to write this line of data, and how to deal with this conflict?
These are some of the problems that arise from concurrent transactions. Specifically: dirty reads, irreducible reads, and phantom reads.
Concept Description
The following concepts are the actual problems to be solved by the transaction isolation level, so you need to figure out what they all mean.
Dirty Reads
A dirty read is a read of data that is not committed by another transaction. Uncommitted means that the data may be rolled back, which means that it may not end up in the database, i.e., data that does not exist. Data that is read and must eventually exist is a dirty read.
The biggest problem with dirty reads is the possibility of reading data that does not exist. For example, in the figure above, the updated data from transaction B was read by transaction A, but transaction B rolled back and the updated data was all restored, meaning that the data just read by transaction A does not exist in the database.
From a macro perspective, it is the problem that transaction A reads a piece of data that does not exist.
Non-repeatable reads
A non-repeatable read refers to a situation where the data read at the very beginning of a transaction and the same batch of data read at any point before the end of the transaction appear to be inconsistent.
Transaction A reads the same data multiple times, but Transaction B updates and commits the data during the multiple reads of Transaction A, resulting in inconsistent results when Transaction A reads the same data multiple times.
Phantom Reads
Dirty reads, non-repeatable reads above the graphic are well understood, for phantom reads online there are many articles are so explained
Phantom read misunderstanding: phantom read is said to be the transaction A executes two select operations to get different data sets, that is, select 1 gets 10 records, select 2 gets 15 records. This is actually not a phantom read, since the first and second reads are not the same, it is still a non-repeatable read, so it is a non-repeatable read.
The correct understanding should be
Phantom reads do not mean that the two reads get different sets of results, but rather that the phantom read focuses on the fact that the data state characterized by the result of a select operation cannot support subsequent business operations. More specifically: select whether a record exists, does not exist, ready to insert this record, but the execution of insert found that this record already exists, can not be inserted, then a phantom read occurred.
Example
Suppose there is a user table, and the id of this table is the primary key. There are 4 rows of data in the table at the beginning.
Let’s look at the scenario where the phantom reading occurs
Here is a study under the RR level (repeatable read), because RU / RC will also exist under the dirty read, non-repeatable read, so we will study the RR level phantom read, excluding other interference.
1, transaction A, query whether there is a record id = 5, no, then insert, which is our normal business logic.
2, this time, transaction B adds a new record with id=5, and commits the transaction.
3, transaction A, then go to query id = 5, found that there is still no record (because here is the study under the RR level (repeatable read), so read still no data)
4, transaction A, insert an id = 5 data.
Eventually transaction A commits the transaction and finds that an error is reported. This is very strange, check when there is clearly no such record, but when inserted, but told me that the primary key conflict, it is like an illusion. That’s what all phantom reads are about.
A non-repeatable read focuses on a read-read, while a phantom read is a read-write, using a write to confirm that the read is a ghost.
Isolation level of transactions
The above-mentioned “dirty read”, “non-repeatable read”, “phantom read” these problems, in fact, is the database read consistency problems, must be provided by the database transaction isolation mechanism to solve.
