Shulou(Shulou.com)06/02 Report--

This article introduces the knowledge of "what is the meaning of illusion in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

If you want to talk about phantom reading, you should start with the isolation level of MySQL. The four clock isolation levels of MySQL are:

Read Uncommitted (read uncommitted)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty Read.

Specific examples of dirty reading are as follows:

Time point

Transaction A

Transaction B

one

Open a transaction

two

Open transaction 3

The query data is 100.

four

Insert a piece of data

five

Re-query, the result is 101

At point 5, when transaction A queries the data again, transaction B does not commit the transaction, but the new data is also found by transaction A. This is dirty reading.

Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Time point

Transaction A

Transaction B

one

Open a transaction

two

Open transaction 3 to query data for 100 items

four

Insert one piece of data 5 query data for 100

six

Commit transaction

7 101 items of query data

We can see that before transaction B commits the transaction, the two query results of transaction An are consistent. After transaction B commits the transaction, transaction A queries again and finds the new data. In transaction A, the results of multiple queries are inconsistent, which is what we call "non-repeatable".

Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row.

The above paragraph is the official explanation given by MySQL. The isolation level of "readable" solves the problem in the above example and ensures that the results of multiple queries within the same transaction are consistent. In other words, after transaction B inserts data to commit the transaction, transaction An also has 100 query results, because when transaction A starts the transaction, the data inserted by transaction B has not yet been committed.

However, this leads to another situation, "phantom reading". There was a problem with my previous understanding of this illusion. During the interview, I was questioned by the other party. Now let's look at the correct understanding of phantom reading:

Time point

Transaction A

Transaction B

one

Open a transaction

two

Open transaction 3 to query data "Zhang San", which does not exist.

four

Insert data "Zhang San"

five

Commit transaction

6 query data "Zhang San", does not exist

7 insert data "Zhang San", unsuccessful

Transaction A queried "Zhang San", the query could not be found, and the insertion was not successful. The data of "Zhang San" appeared like an illusion. This is the so-called "fantasy reading". The online interpretation of "phantom reading" or other explanations is all wrong. For example, it is the same as "phantom reading" and "unrepeatable reading", except that "phantom reading" is for the number of data. These understandings are all wrong.

Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition. This isolation level is rarely used, so I won't give you too much introduction.

This is the end of the content of "what is the meaning of Phantom Reading in MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.