background

Because in ready to change my job recently, so start adding some basic knowledge, I always before going to hard back some knowledge points, this time spent a lot of time to ask ask why, years ago for the phantom read a little bit of experience, in order not to forget, but also to only speak out is one of the really understand, through this blog himself in review.

Definition of illusion

As for the four isolation levels of transactions in Mysql InnoDB storage engine, I will not elaborate on the specific content here. Here I will mainly say the word illusion, before I have not quite understood this word, now I try to explain it. In High Performance Mysql, illusory reads are defined as follows:

Phantom reading refers to that when a transaction reads a record in a range, another transaction inserts a new record in the range. When the previous transaction reads the record in the range again, phantom rows will be generated.

Although this is said in this book, and the official Myql document also roughly means this, but personally SPEAKING, I think this is more consistent with the characteristics of unrepeatable read, so I tend to understand the above as a kind of unrepeatable read. As for phantom read, I prefer to understand that the result set produced by one select cannot support the next operation (the operation here mainly refers to insert). More specifically, in the current transaction, select a record is found not to exist, then perform insert operation, indicating that the record already exists, and select again to find the record still does not exist. For the above phenomenon, I think it is a hallucination. This is my understanding of the concept of illusory reading.

When does phantasm occur

According to the four isolation levels of transaction, read uncommitted, read committed, repeatable read and serialization, phantom can not be avoided at the first three levels, but in reality, different databases can avoid phantom at the repeatable read (RR) level. For Mysql is the use of MVCC+ next-key implementation to avoid phantom reading. So what is MVCC and Next-key? Let’s say it one by one:

MVCC

Concurrency Control is called Multiversion Concurrency Control. The concurrency Control is designed to address the problem of repeating a transaction and ensuring that all the data read to the same transaction is the same. Unreal unreal, especially read, in MVCC there are two types of read: snapshot read and current read,

  1. Snapshot read: Reads a version of data
  2. Current read: Reads the latest data from the current database

Select reads snapshot reads. Insert, Update, and Delete are current reads. In simple terms, the database engine will implicitly add two fields to each record, like create time and delete time, but instead of storing the timestamp, it will store some version of the system (i.e. transaction ID).

Although the Select operation reads a snapshot, it can be understood that each read is a historical version of the read, which appears to solve the phantom problem, but it does not. Let’s assume that the MVCC is currently used to avoid phantom cases, such as the following case:

Current data;

id name age
1 John 13
2 Mike 14
3 Bob 12

Two transactions arrive

Transactions/Steps Transaction 1 Transaction 2
1 begin;
2 Select * from table; The begin.
3 Insert into table (name.age) values(‘Bruce’, 15);
4 commit;
5 Select * from table;
6 update table set age = 16 where id = 4;
7 Select * from table;

Database for repeatable read isolation level, we see that transaction 1 in 2 and 5 of the select result is consistent, is can’t see the transaction 2 submit data, by far is to avoid the repeatable read, but if the transaction 1 id for subsequent modifications we find can be updated, After the update is complete, the select operation is performed again. It is found that you can see the data submitted by transaction 2, and it is the updated version after transaction 1.

Note: the scene described above is simulated after understanding the realization principle of MVCC, and has not been tested in practice.

Although the above scenario is not tested, it is theoretically possible, so the conclusion is that MVCC alone cannot solve the illusion problem.

Next-Key Lock

Since MVCC alone cannot solve the illusion problem, Mysql introduces the concept of a next-key Lock. What is a next-key Lock?

  1. Record Lock: Lock the Mysql index Record
  2. Gap Lock: a Gap Lock

To be continued…

References:

Segmentfault.com/a/119000001…

Juejin. Cn/post / 684490…

www.jianshu.com/p/cef49aeff…