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

This article mainly introduces Mysql transaction isolation level principle example analysis, the article introduced in great detail, has a certain reference value, interested friends must read!

What about the isolation level of transactions?

To be honest, the issue of business isolation level, whether it is school recruitment or social recruitment, interviewers love to ask! However, at present, there are many articles on the Internet. To be honest, after I read them, I doubt that the author understands them! Because their analysis of Repeatable Read and Serializable is really confusing to me!

In addition, many books say that repeated reading solves the problem of illusion reading, such as "mysql technology insider_innodb storage engine", etc., not listed one by one, so most of the articles on transaction isolation level on the Internet are problematic, so open another article to explain!

Most of the content mentioned in this article has official website as evidence, so after reading the content of this article, you can completely remember the concept in mind, unless the official website development manual is wrong, otherwise it should be correct!

In addition, this article will focus on

Repeatable Read: Does Repeatable Read really solve the problem of illusion reading?

text

Let me start by mentioning that there are three things that can happen depending on the isolation level of the transaction. That is dirty reading, non-repeatable reading, fantasy reading. I won't mention the definition of these three cases here, but I will add them later when I talk about isolation levels.

Here, we remember one point, according to the definition of dirty reading, non-repeatable reading, fantasy reading (their own summary, the official website does not), there are the following inclusion relations:

So, how do you understand this picture?

That is, if dirty reading occurs, then non-repeatable reading and phantom reading must occur. Because the phenomenon of dirty reading can be explained by the definition of non-repeatable reading and illusory reading. But conversely, take the phenomenon of non-repeatable reading, and the definition of dirty reading may not necessarily explain it!

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

undefined

pIdname1zhangsan

undefined

As shown, the data retrieved by one transaction has been modified by another uncommitted transaction.

The address of the official website is

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_dirty_read

the content of which is

**dirty read

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

**

translates as

The retrieved data is unreliable and can be modified by another uncommitted transaction!

You will find that our demo results are consistent with the official website's definition of dirty reading. According to our initial reasoning, if dirty reading exists, then irrepeatable reading and phantom reading must exist.

2. READ COMMITTED

It can also be seen that one transaction can read the committed data of another transaction! For ease of illustration, I simply draw a description!

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

The address of the official website cannot be read repeatedly.

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

the content of which 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).

**

translates as

A query statement retrieves data, and then another query statement retrieves data in the same transaction. The two data should be the same, but the actual situation returns different results.

ps: Author's note, the different results here refer to the change of the data content on the disk pointed to by the primary key index under the condition that the row remains unchanged (professional point: the primary key index does not change). If the primary key index changes, such as adding a piece of data or deleting a piece of data, it is not irreproducible.

Obviously, our phenomenon fits the definition of unrepeatable reading. Below is a thought:

This definition of non-repeatable reading can also be said to make sense in the phenomenon of dirty reading. Obviously dirty reads, that is, the example of ** READ_UNCOMMITTED **, is also consistent with returning different results in the same transaction! However, the reverse is not necessarily true. If the result of querying twice in a transaction A is changed by another transaction B, if transaction B changes the result of transaction A without committing, it belongs to dirty read and non-repeatable read. If transaction B commits to change the outcome of transaction A, it is not a dirty read, but an unrepeatable read. 3, Repeatable read (REPEATABLE_READ)

Here, I'm going to change the order, and I'm going to start with the definition of phantom reading.

The address of the official website for the definition of fantasy 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.

translates as

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

OK, next up, let's evaluate whether this phenomenon meets the definition of illusion reading.

Obviously, this phenomenon is consistent with the definition of phantom reading. Two identical queries for the same transaction have different rows. Below is a thought:

This definition of illusion reading can also be said to make sense in the phenomenon of non-repeatable reading. Think for yourselves! The reverse is not necessarily true. The second query of the transaction yielded data that did not appear in the result set of the first query. If the data is modified, then the phenomenon belongs to both non-repeatable reading and phantom reading. If the data is new or deleted, the phenomenon is not non-repeatable, but a phantom read.

Next, why do many articles have misinformation, saying that repeatable reading can solve the problem of illusion reading! The reason is a sentence from the official website

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

The original text reads 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

InnoDB uses REPEATABLE READ by default. In this case, use next-key locks to solve the magic reading problem!

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

InnoDB uses REPEATABLE READ by default. In this case, the illusion reading problem can be solved!

Then everyone continued to copy me, I copied you, and you know what happened!

The word "next-key locks" is missing. "After this condition, the meaning changes completely and we execute statements at that isolation level

select * from tx_tb where pId >= 1;

It is snapshot reading, it is without any locks, and it cannot solve the illusion reading problem unless you use

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

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

4, SERIAL READ (SERIALIZABLE_READ)

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

OK, watch the red part! It is because of the use of next-key locks,innodb that the index record PiD=1 and the gap (1,++∞) are locked. Other transactions that want to insert data into this gap block, preventing phantom reads from occurring!

Some people will say that the results of your second query have also changed, obviously different from the results of the first query. All I can say about this is, look carefully. It's been taken by myself.

Things are changed, not modified by other things. This is not an illusion, nor can it be repeated.

summary

There is a lot of nonsense above. Finally, come to a table to summarize it. You can answer this table during the interview. Everything above is prepared for this table!

Isolation Level Dirty Read Non-repeatable Read Fantasy Read Uncommitted Yes Yes Non-repeatable Read No Yes Repeatable Read No Serialization No The above is "Example Analysis of Mysql Transaction Isolation Level Principle" All the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.