Author: Hu Chengqing
Source of this article: original submission
* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.
I read a few articles about phantom reading on the Internet. There are always something wrong. Either the explanation is too official to understand, or it is completely wrong. So I found the famous paper A Critique of ANSI SQL Isolation Levels, right The phantom reading questions are summarized and summarized, and I hope to help everyone truly understand the phantom reading. The outline is as follows:
What is phantom reading
The phantom reading was first proposed in the ANSI SQL-92 isolation level definition:
P3 (Phantom): Transaction T1 reads a set of data items satisfying some <search condition>. Transaction T2 then creates data items that satisfy T1’s <search condition> and commits. If T1 then repeats its read with the same <search condition>, it gets a set of data items different from the first read.
translate:
Transaction T1 reads a set of data that meet certain <search conditions>. Transaction T2 creates data items that meet the <search conditions> of T1 and submits it. If T1 is read again with the same <search condition>, a set of data different from the first read will be obtained. This is called phantom reading.
for example:
If you change the SQL executed by T2 to update t set a=2 where a>1 and a<5 ; is this also a phantom reading? Let's start answering questions.
Question 1: What is the difference between phantom reading and non-repeatable reading?
ANSI defines the following for non-repeatable reads:
P2 (Non-repeatable or Fuzzy Read): Transaction T1 reads a data item. Another transaction T2 then modifies or deletes that data item and commits. If T1 then attempts to reread the data item, it receives a modified value or discovers that the data item has been deleted.
Note the two differences from the definition of phantom reading:
- There is <search condition> in the phantom read definition
- T2 in the definition of phantom reading is "create data", and T2 in the definition of non-repeatable reading is to modify or delete data
What do you mean?
Within the scope of satisfying <search condition>, modifying and deleting data must be operations on existing data rows, and creating data means that the data item does not exist before creation. "Create data" is not only insert, but also update. update updates data items that do not meet the predicate range to data items that meet the predicate range. For example, the predicate range is a>1 and a<5, update a=4 where a=6 is such a case.
More intuitively (personal interpretation), non-repeatable reading means that the number of rows of the result of reading remains unchanged or reduced, and the content of the result changes; while for phantom reading, the number of rows of the result of reading has increased.
For example:
It is known that T1 executes two SQLs:
//T1
begin;
Q1:select a from t where a>1 and a<5;
Q2:select a from t where a>1 and a<5;T2 executes and submits any of the following SQL between Q1 and Q2 of T1, and causes Q2 of T1 to read different results:
//T2
Q1：insert into t(a) values(2); //这个符合幻读定义
Q2: update t set a=3 where a=6; //这个符合幻读定义
Q3: update t set a=2 where a>1 and a<5; //这个符合脏读定义
Q4: delete from t where a>1 and a<5; //这个符合脏读定义Question 2: Why does the ANSI repeatable-read isolation level solve non-repeatable reading but allow phantom reading?
1. ANSI isolation level definition
First, we must clarify a concept: that is, in ANSI, each isolation level is defined by a prohibited phenomenon:
it means:
- read-uncommitted corresponds to P1 dirty read;
- read-committed corresponds to P2 non-repeatable read;
- repeatable-read corresponds to P3 phantom reading;
- There is no abnormal phenomenon corresponding to serializable.
2. The inaccuracy of ANSI isolation level definition
A Critique of ANSI SQL Isolation Levels paper (hereinafter referred to as the paper), pointed out the incorrectness of this definition:
First of all, the ANSI repeatable-read level does not conform to the semantics. It cannot be repeatable and should be renamed to phantom. The paper briefly explains its background:
Date and IBM originally used the name “Repeatable Reads” [DAT, DB2] to mean serializable or Locking SERIALIZABLE. This seemed like a more comprehensible name than the [GLPT] term “Degree 3 isolation."
The ANSI SQL meaning of REPEATABLE READ is different from Date’s original definition, and we feel the terminology is unfortunate. Since anomaly P3 is specifically not ruled out by the ANSI SQL REPEATABLE READ isolation level, it is clear from the definition of P3 that reads are NOT repeatable! We repeat this misuse of the term with Locking REPEATABLE READ in Table 2, in order to parallel the ANSI definition.
translation:
Date and IBM originally used the name "Repeatable Reads" [DAT, DB2] to indicate serializable or lock serializable. This seems to be a more understandable name than the [GLPT] term "isolation 3", even though they are the same. The meaning of repeatable ANSI SQL is different from the original definition of Date, which we think is unfortunate. Phenomena P3 does not consider the ANSI SQL repeatable read isolation level, but it can be seen from the definition of P3 that the read is not repeatable! (Translator's Note: P3 violates the meaning of the Chinese word "repeatable reading") We still misuse the term "locked repeatable reading" in Table 2 to correspond to the ANSI definition.
Secondly, this definition has led to a common misunderstanding: that the three anomalies P1, P2, and P3 are not allowed to mean serialization. In fact, this is wrong because ANSI's definition of these three anomalies is not clear. (The paper gives a loose interpretation and believes that the loose interpretation is correct. I will explain what a loose interpretation is later), and there are other abnormal phenomena such as dirty writing, missing updates, writing offsets, etc. ANSI does not mention it at all. So in the above picture (from the paper), the ANSI prefix is added to the 4 isolation levels, and the serializable is added with ANOMALY, indicating that this is not truly serializable.
So for the question "Why the ANSI repeatable-read isolation level solves non-repeatable reading but allows phantom reading?", the first answer is: because ANSI definition is like this.
But we can still try to continue to study the reasons behind it.
3. Isolation level based on lock definition
There is a table in the paper indicating the isolation level based on the lock definition (note: this definition actually specifies the use of locks to achieve the isolation level, the ANSI definition does not consider the specific implementation, and the lock-based isolation level meets the requirements of the ANSI isolation level. But they are not the same, so the lock prefix is added to the 4 isolation levels):
In the locking repeatable-read isolation level, the range of locking for read operations: only long duration data-item Read locks for data items, and only short duration read predicate locks for the predicate range. ).
Explanation of predicate locks: read (or write) predicate locks (under a set of data items determined by a given <search condition>) are actually locks on all data items that meet <search condition> . This may be an infinite set because it includes the data that exists in the database and all phantom data items that are not currently in the database (if they are inserted, or the current data item is updated to meet the <search criteria>).
A simple example: for select a from t where a>1 and a<5;
- Before reading the data, request a read lock for the data item (2, 3, 4) or the predicate range (1, 5), which is called "Well-formed Reads";
- After the data is read, the read lock on the data item (2, 3, 4) is released until the transaction is terminated (commit or rollback). This is called "Long duration data-item Read locks";
- After reading the data, immediately release the read lock of the predicate range (1, 5), which is called "Short duration Read Predicate locks". If the lock in the predicate range (1,5) is not released until the transaction is terminated, it is called "Long duration Read Predicate locks".
Using the concepts in MySQL that we are about to talk about, locking data items is record lock, and locking predicate range is Next-key lock (record lock plus gap lock). So we know that in the ANSI definition, the read in the RR isolation level only adds a read lock to the data record, and the gap lock only exists during SQL execution, so that when transaction T2 modifies or deletes the data read by T1 Will be blocked, but inserting data in the gap can be executed. Therefore, according to "non-repeatable reading" and "definition of phantom reading", repeatable-read does not allow non-repeatable reading and allows phantom reading.
Question 3: Is MySQL's isolation level based on locks?
It is realized by the lock and MVCC together:
- The SERIALIZABLE isolation level is achieved through locks. Refer to the isolation level defined above based on locks: all reads will extend the lock on the predicate range until the transaction is terminated;
- Other isolation levels are implemented using MVCC. Ordinary select is called snapshot read, and read locks are not added. The purpose is to improve the performance of read and write concurrency.
Question 4: Does MySQL's repeatable-read isolation level allow phantom reading? Why?
Let me talk about the answer first: not allowed. But in fact, this issue is a bit complicated, so let's elaborate on it in several points.
1. MVCC can realize the "repeatable read" of snapshot read, without "non-repeatable read" and "phantom read"
This is obvious, because MVCC actually has multiple versions of data. It only needs to be implemented to ensure that two snapshots of the same transaction read the same data version, and there is no need to use locks (Review Question 2).
2. The gap lock (or next-key lock) realizes the "repeatable read" of the current read, and there will be no "non-repeatable read" and "phantom read".
Here is an explanation of the current read: read the latest version of the data (meaning that even if it is repeatable-read, the current read can read the data submitted by other things), and the corresponding lock will be added. The following operations in MySQL are all current reads:
select * from t where a>1 and a<5 lock in share mode; //加共享锁
select * from t a>1 and a<5 for update; //下面的都加排他锁
update ...
insert ...
delete ...Question 2 describes the principle of why ANSI repeatable-read allows phantom reads: because the read of transaction T1 only locks the data rows that meet the conditions, and the position where transaction T2 is inserted is the gap between the data rows and will not be blocked. In the implementation of MySQL repeatable-read, the current read lock range is data row + gap, so the insertion of transaction T2 will be blocked, and the two current read results of T1 will not change. Give a simple example:
3. Exceptions caused by the mixed use of snapshot reads and current reads cannot be regarded as phantom reads
There are some articles on the Internet that say that MySQL repeatable-read will have phantom reading. The recurring use cases are as follows:
The effect of the current read is to read the latest version. In fact, the isolation level is downgraded from repeatable-read to read-committed, so the mixed use of snapshot read and current read is not considered a phantom read. If this does not convince you, then I will give a simple example:
Do we have to say that this example shows that repeatable-read allows "P2 non-repeatable read abnormality"? Obviously not. For non-repeatable reads, the repeatable-read isolation level is clearly not allowed. This shows that the exception caused by the current read and snapshot read mixing cannot be called a phantom read.
Question 5: Does the repeatable-read isolation level of MySQL allow loosely interpreted phantom reads?
I have to remind everyone that the phantom readings we mentioned above are all phantom readings defined by ANSI (strict phantom reading), and the expression is abstracted as:
A3：r1[P] ... w2[y in P] ... c2 ... r1[P] ... c1
Abbreviation meaning: w1[x] means transaction 1 writes record x, r1[x] means transaction 1 reads record x, c1 means transaction 1 commits, a1 means transaction 1 rolls back, r1[P] means transaction 1 follows the predicate P The condition of reading several records, w2[y in P] means that transaction 2 satisfies the condition of predicate P to write record y.
The paper proposes a loose interpretation of phantom reading:
H3：r1 [P] w2 [y in P] r2 [z] w2[z] c2 r1 [z] c1
Transaction 2 adds new employees and updates the total number of employees z. Transaction 1 reads data that destroys the consistency between the employee list and the total number of employees before and after transaction 2 is submitted. Because transaction 1 did not repeatedly read the data set specified by the predicate P, the behavior of H3 does not conform to the formal definition of A3, so A3 can only be regarded as a phantom reading in a strict sense, so the paper supplements the anomaly of phantom reading A loose interpretation of the phenomenon.
In order to make it easier for everyone to understand the meaning of the loose explanation above, I refined the following example:
//tt是员工表
mysql> select * from tt;
+----------+------------+
| name | department |
+----------+------------+
| zhangsan | developer |
| lisi | developer |
+----------+------------+
2 rows in set (0.00 sec)
//tt_count是各部门人数统计表
mysql> select * from tt_count;
+------------+--------+
| department | number |
+------------+--------+
| developer | 2 |
+------------+--------+
1 row in set (0.00 sec)
The loose interpretation of the phantom reading in the paper is: as long as transaction 2 writes the predicate range of transaction 1 (write success), no matter what query is made by transaction 1 later, it is considered a phantom reading.
Its expression is:
P3: r1[P]...w2[y in P]...((c1 or a1) and (c2 or a2) any order)
Personal understanding, we need to look at this problem from the perspective of lock implementation: based on the isolation level of the lock implementation, the way to solve the phantom reading is to lock the predicate range. In other words, the read request should block the write request for the range of the predicate it reads.
For MySQL, even loosely interpreted phantom reading can still be avoided:
- The current read lock of MySQL RR isolation level is Next-key lock, which is to lock the predicate range, so it is impossible to phantom read;
- If it is a snapshot read, because there is no lock, other transactions can write to the predicate range. According to the loose interpretation definition, it is in line with the phantom read, but because MVCC implements the "repeatable read" semantics, in fact It is impossible to read the phantom line.
Note: Personal understanding, as long as it is based on the RR isolation level implemented by MVCC, the phantom reading of loose interpretation cannot be ruled out, but this is only to satisfy the definition, and there will be no real abnormalities. Therefore, for the RR isolation level implemented based on MVCC like MySQL, everyone should not have phantom reading, and it is in line with the "repeatable-read" semantics.
Reference
Phantom Rows：https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html
A Critique of ANSI SQL Isolation Levels：https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf
A Critique of ANSI SQL Isolation Levels Paper translation: https://www.pianshen.com/article/2443612904/
A Critique of ANSI SQL Isolation Levels Paper reading: https://www.pianshen.com/article/51371657923/
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。