[Java Interview] This should be the answer that the interviewer most wants to hear. How does Mysql solve the problem of phantom reading?

"How does Mysql solve the phantom reading problem"

A friend who has worked for 4 years went to a Meituan for an interview and encountered such a problem.

Hello everyone, I'm Mic, a Java programmer who has been working for 14 years

What is the interviewer looking for in this question? How should we answer this?

Problem Analysis

This question at least examines students with more than 3 years of development experience.

To solve the problem of concurrent transactions at the bottom of Mysql, at least a certain technical accumulation can be really understood.

And if you are a programmer who has not worked for a long time, you must know the transaction isolation level of the database.

Because different isolation levels have different effects on data security.

That is to say, there are problems such as dirty reads, phantom reads, and non-repeatable reads.

The so-called phantom read means that the number of data pieces read before and after a transaction is inconsistent.

Execute a range query in the first transaction, at this time there is only one piece of data that satisfies the query.

Then a piece of data is inserted into the second transaction and submitted, and then when queried again in the first transaction, it is found that two pieces of data meet the conditions.

Under the RR transaction isolation level, two methods, MVCC and LBCC, are introduced to solve the phantom read problem.

MVCC is similar to an optimistic locking design. In simple terms, a transaction version is generated for each transaction, and then access rules are defined for this version.

• A transaction can only see the transactions committed before the first query and the modifications of the current transaction.
• A transaction cannot see transactions created after the first query of the current transaction, as well as uncommitted transaction modifications.

However, if there is a current read in a transaction, MVCC will still have a phantom read problem, because the current read is not a snapshot read, but a direct memory read.

So in response to this situation, you can use LBCC, which is a lock-based mechanism, to solve the problem, that is, row locks, table locks, gap locks, etc.

Based on the understanding of the above knowledge, if students who do not have a clear understanding of the underlying implementation principles of different transaction isolation levels of Mysql answer this question, they will either be very rigid or unable to expand, and it will seem a bit like memorizing Answer.

Let's see how experts answer this question.

Expert:

Under the transaction isolation level of RR (that is, repeatable read), InnoDB adopts the MVCC mechanism to solve the phantom read problem.

MVCC is an optimistic locking mechanism. It generates different snapshot versions for different transactions and manages them through the UNDO version chain. In MVCC, it is stipulated that the higher version can see the transaction changes of the lower version, and the lower version cannot see the higher version. The transaction change of the version thus realizes the data isolation between different transactions and solves the problem of phantom reading.

However, in the current case of reading, the data in the memory is directly read, and the snapshot degree is skipped, so the phantom read problem still occurs.

I think it can be solved in two ways.

1. The first is to try to avoid the current reading situation
2. The second is the way to introduce LBCC

The above is my understanding of the problem.

Summarize

Well, today's sharing ends here

If you like my work, remember to like, favorite, and follow!!!

You need a collection of Java interviews, and scan the golden nine silver ten interview cheats below!

Copyright notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless otherwise stated. Please indicate the source of Mic带你学架构 !
If this article is helpful to you, please help to follow and like, your persistence is the driving force for my continuous creation. Welcome to follow the WeChat public account of the same name to get more technical dry goods!