Shulou(Shulou.com)05/31 Report--

This article introduces you what the MySQL transaction isolation level is, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Can you tell me about the isolation level of the transaction?

To be honest, the interviewer likes to ask about the isolation level of affairs, whether it is school recruitment or social recruitment. However, at present, there are many articles on the Internet, to tell you the truth, ah, after I read it, I wonder if the author understood it! Because their parsing of readable (Repeatable Read) and serialization (serializable) really makes me confused!

In addition, many books say that repeatable reading solves the problem of phantom reading, such as "mysql Technology Insider-innodb Storage engine", which are not listed one by one, so most of the online articles about transaction isolation levels are problematic, so write another article to explain!

Most of the contents of this article are corroborated by the official website, so after reading the contents of this article, you can keep the concept in mind, unless the development manual of the official website is wrong, it should be correct!

In addition, this article will focus on

Does Repeatable Read really solve the problem of phantom reading?

Text

Let me start by mentioning that there are three situations that can happen depending on the isolation level of the transaction. That is, dirty reading, unrepeatable reading and phantom reading. I won't mention the definitions of these three situations here, but I'll add them later when I talk about the level of isolation.

Here, keep in mind that according to the definitions of dirty reading, non-repeatable reading and phantom reading (summed up by yourself, not on the official website), there are the following inclusion relationships:

So, how do you understand this picture?

That is, if dirty reading occurs, then unrepeatable reading and phantom reading must occur. Because the phenomenon of dirty reading can not be repeated, the definition of phantom reading can also be explained. But on the other hand, the phenomenon of unrepeatable reading may not necessarily be explained by the definition of dirty reading.

Suppose there is a table tx_tb as follows, with pId as the primary key

Read unsubmitted

That is, READ_UNCOMMITTED, in fact, you can see from the isolation name, one transaction can read the uncommitted data of another transaction! In order to facilitate the explanation, I simply draw a picture to explain!

As shown in the figure, the data retrieved by one transaction is modified by another uncommitted transaction.

The address defined by the official website for dirty reading is https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_dirty_read.

Its content is

Dirty read

An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed.

It translates to

The data from the retrieval operation is unreliable and can be modified by another uncommitted transaction!

You will find that the result of our demonstration is consistent with the definition of dirty reading on the official website. According to our initial reasoning, if there is dirty reading, then unrepeatable reading and illusory reading must exist.

Read submitted

That is, READ_COMMITTED, you can also see that one transaction can read the committed data of another transaction! In order to facilitate the explanation, I simply draw a picture to explain!

As shown in the figure, the data retrieved by one transaction can only be modified by another committed transaction.

The address defined by the official website for non-repeatable readings is

Https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_non_repeatable_read

Its content is

Non-repeatable read

The situation when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime).

It translates to

A query statement retrieves data, followed by a query statement that retrieves data in the same transaction. The two data should be the same, but the actual situation returns different results. (also modified by another transaction that is committing)!

Ps: the author's note that the different results here refer to the fact that the row remains the same (professionally speaking, the primary key index has not changed), but the data content on the disk pointed to by the primary key index has changed. If the primary key index changes, such as adding a piece of data or deleting a piece of data, it is not unrepeatable.

Obviously, our phenomenon is consistent with the definition of unrepeatable reading. Next, let's think about this:

Whether this definition of unrepeatable reading can be put into the phenomenon of dirty reading also makes sense. Obviously, the phenomenon of dirty reading, that is, the example of reading uncommitted, is also consistent with returning different results in the same transaction!

But the reverse does not necessarily make sense. When the result of two queries in one transaction An is changed by another transaction B, if transaction B changes the result of transaction A without committing, it belongs to dirty reading and can not be read repeatedly. If transaction B commits to change the result of transaction A, it is not a dirty read, but it is a non-repeatable read.

Repeatable read

That is, REPEATABLE_READ. Here, I'll change the order and start with the definition of phantom reading.

The address defined by the official website for phantom reading is

Https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_phantom

Phantom

A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.

It translates to

A row of data appears in the result set of a query, but it does not appear in an earlier query result set. For example, two queries are made in one transaction, while another transaction inserts or updates a row of data (which meets the criteria after where in the query statement) and commits!

All right, in the picture above, we evaluate whether this phenomenon meets the definition of phantom reading.

Obviously, this phenomenon is in line with the definition of illusion. Two identical queries for the same transaction are not in the same row. Next, let's think about this:

Whether this definition of illusion can be put into the above unrepeatable phenomenon also makes sense. Think for yourselves!

The reverse doesn't necessarily make sense. The transaction queried a data for the second time, but the data did not appear in the result set of the * query. If the data is modified, then the phenomenon belongs to both unrepeatable and illusory reading. If the data is added or deleted, then the phenomenon is not unrepeatable, but it is illusory.

Next, why do many articles have misrepresentation, saying that repeatable reading can solve the problem of phantom reading! The reason comes from a sentence on the official website.

Address: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks

The original text is as follows

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 14.7.4, "Phantom Rows").

According to the original meaning of this sentence, it should be

REPEATABLE READ is used by default in InnoDB. In this case, use next-key locks to solve the phantom reading problem!

As a result, it is estimated that a domestic translator turned it into

REPEATABLE READ is used by default in InnoDB. In this case, the problem of phantom reading can be solved!

Then everyone continues to copy me, I copy you, and you know!

Apparently, "next-key locks is used!" is omitted. After this condition, the meaning changes completely, and we execute the statement at that isolation level.

Select * from tx_tb where pId > = 1

It's a snapshot, it's unlocked, and it doesn't solve the problem of phantom reading at all, unless you use the

Select * from tx_tb where pId > = 1 lock in share mode

In this way, you can use next-key locks to solve the problem of phantom reading!

Serial reading

That is, SERIALIZABLE_READ. At this isolation level, all select statements are automatically followed by lock in share mode. Therefore, under this isolation level, no matter how you query, you will use next-key locks. All select operations are currently read!

OK, pay attention to the red part of the watch! It is because next-key locks,innodb is used under this isolation level to lock the index record of pId=1 and the gap of (1 ∞ + 1). If other transactions want to insert data into this gap, they will block, thus preventing the occurrence of phantom reading!

Some people will say that the result of your second query has also changed, which is obviously different from that of the * query. In this regard, I can only say, please take a good look. This is changed by one's own affairs, not by anything else. This is not an illusion, nor is it unrepeatable.

Summary

It keeps talking a lot. Please give me a form to sum up. Just answer this form for your interview. All the above is to prepare for this form!

What about the MySQL transaction isolation level is shared here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.