How does MySQL solve the phantom read problem? The following article will let you talk about this issue. Let’s read the article with questions!
## Among the high-frequency interview questions of Jin Busan and Yin Busi, MySQL’s transaction characteristics, isolation level and other issues are also one of the very classic eight-part essays. Faced with this kind of Question, it is estimated that most friends are familiar with it:Transaction characteristics (ACID): Atomicity (
Atomicity),
Isolation(
Isolation),
Consistency(
Consistency) and
Persistence
Isolation Level: READ UNCOMMITTED(
READ UNCOMMITTED),
READ COMMITTED(
READ COMMITTED),
Repeatable Read (
REPEATABLE READ),
Serializable (
SERIALIZABLE)
Under the isolation level,
dirty reads,
non-repeatable reads and ## may occur. #phantom read
problem
non-repeatable read
and phantom read
problems may occur, but they are not possible Dirty read
problem
phantom read
problem may occur, but dirty read# cannot occur ## and
Non-repeatable readproblems
SERIALIZABLE
For MySQL InnoDB The default isolation level supported by the storage engine is REPEATABLE-READ (repeatable) It is impossible to prevent phantom reads, but we all know that the MySQL InnoDB storage engine solves the problem of phantom reads, so how does it solve it?
1. Row format
compact,
redundant,
dynamic ,
compress; Although there are many line formats, they are basically the same in principle, as follows, the
compact line format:
As can be seen from the figure, A complete record can actually be divided into two parts: recorded additional information
and recorded real data.
recorded additional information are respectively
changes. Long field length list ,
NULL value list and
record header information , and
recorded real data In addition to our own defined columns, MySQL will Add some default columns to each record. These default columns are also called
hidden columns. The specific columns are as follows:
Description | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Row ID, uniquely identifies one Record | transaction_id | |||||||||||||||||||||||||||||||||||||||||||||
Transaction ID | roll_pointer | |||||||||||||||||||||||||||||||||||||||||||||
Rollback pointer |
Time number | trx 100 | trx 200 | |
---|---|---|---|
① | BEGIN; | ||
| BEGIN;BEGIN; | ||
##update person set grade =40 where id =1; |
|||
##⑥ |
|||
##COMMIT; | ⑦ |
||
##update person set grade =70 where id =1; |
⑧ |
SELECT * FROM person WHERE id = 1; | |
COMMIT; |
? |
COMMIT; | |
trx 100 the transaction was executed Submit, the version chain recorded in the id=1 line is as follows: |
, the transaction commit was executed, the version chain recorded in the id=1 line As follows:
At time ⑤, transaction trx 100
will first generate a
select statement. #, the content of the
m_ids list of ReadView
is [100, 200]
, min_trx_id
is 100
,max_trx_id
is 201
, creator_trx_id
is 0
, at this time, select the visible record from the version chain, and traverse the version chain from top to bottom: Because grade=40, the value of trx_id
is 100
, which is in m_ids
, so the record is not visible. Similarly, the record with grade=20 is also invisible. Continue traversing down, grade=20, trx_id
value is 80
, which is less than min_trx_id
value 100# in
ReadView ##, so this version meets the requirements, and records with level 10 are returned to the user.
In time ⑧, if the isolation level of the transaction is
READ COMMITTED, a separate
ReadView will be generated, the ## of the
ReadView The content of the #m_ids
list is
, min_trx_id
is 200
, max_trx_id
is 201
, creator_trx_id
is 0
. At this time, select the visible record from the version chain, and the version chain is traversed from top to bottom: because grade=70, the value of trx_id
is 200
, in m_ids
, so the record is not visible, continue to traverse, grade=40, trx_id
value is 100
, It is less than the min_trx_id
value 200
in ReadView
, so this version meets the requirements, and a record with level 40 is returned to the user. In time ⑧, if the isolation level of the transaction is
REPEATABLE READ
, in time ⑧, a ReadView
will not be generated separately, but the one of time 5 will be used. ReadView
, so the level returned to the user is 10. The result of the two selects is the same. This is the meaning of repeatable reading
.
3. Summary
By analyzing the detailed explanation of MVCC, it can be concluded that based on MVCC, under the RR isolation level, it is very easy to solve
phantom reading
Problem, but we know that select for update
generates current reads and is no longer snapshot reads. In this case, how does MySQL solve the
Current reading
: Use Next-Key Lock (gap lock) to lock to ensure that phantom reading does not occur
How gap lock is used in the current reading situation If you want to solve the problem of phantom reading, interested friends can add a follow and like
[Related recommendations:
The above is the detailed content of An article briefly analyzing how MySQL solves the phantom reading problem. For more information, please follow other related articles on the PHP Chinese website!