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

This article mainly explains "what is dirty reading and illusory reading". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is dirty reading and phantom reading".

Select @ @ tx_isolation

MySQL contains four isolation levels, which are, of course, data. To change the isolation level, you can use the following SQL statement.

Set session transaction isolation level read uncommitted; set session transaction isolation level read committed; set session transaction isolation level repeatable read; set session transaction isolation level serializable

Ok, let's create a small test table to take a look at the magic effects in a concurrent environment.

CREATE TABLE `xjjdog_ tx` (`id` INT (11) NOT NULL, `name` VARCHAR (50) NOT NULL COLLATE 'utf8_general_ci', `money` BIGINT (20) NOT NULL DEFAULT' 0mm, PRIMARY KEY (`id`) USING BTREE) COLLATE='utf8_general_ci' ENGINE=InnoDB; INSERT INTO `xjjdog_ tx` (`id`, `name`, `money`) VALUES (2, 'xjjdog1',); INSERT INTO `xjjdog_ tx` (`id`, `name`, `money`) VALUES (1,' xjjdog0', 100); 1. Dirty reading

Dirty reading means reading out dirty data. What is dirty data? It is data that has not been committed by another transaction. Under the read uncommitted isolation level, dirty reads occur. For example, the following time series

Transaction A:set session transaction isolation level read uncommitted; transaction B:set session transaction isolation level read uncommitted; transaction A:START TRANSACTION; transaction B:START TRANSACTION; transaction A:UPDATE xjjdog_tx SET money=money+100 WHERE NAME='xjjdog0'; transaction B:UPDATE xjjdog_tx SET money=money+100 WHERE NAME='xjjdog0'; transaction A:ROLLBACK; transaction B:COMMIT; transaction B:SELECT * FROM xjjdog_tx

In this scenario, the original value of money is 100, which is added to each of the two session, and then one of the session transactions is rolled back. As a result, after querying, it is found that the value of money remains unchanged at 100. That is, one of the operations plus 100 is overwritten.

So there are several conditions for dirty reading to happen.

In the high concurrency scenario, before the end of one transaction A, another transaction participates in reading and writing the data rows involved in transaction A.

Read uncommitted with the lowest transaction isolation level

After you use the data, transaction A rolls back, causing the data you got earlier to no longer exist.

To solve the problem, you only need to set the isolation level to be higher than read uncommitted.

two。 Non-repeatable

Setting the isolation level to read committed avoids dirty reading, which is actually very understandable. The root cause of dirty reading is that other operations intrude during the execution of the transaction. This isolation level requires that after transaction A commits, the modified value can be read by transaction B, so dirty reading is impossible and fundamentally eliminated.

However, non-repeatable reading occurs in read commited.

As the name implies, the reading of a value produces two results in a transaction cycle.

Unrepeatable reading proves that the world does not always revolve around you. During the execution of your transaction, there will be countless other transactions executed, and if your transaction duration exceeds these transactions, then you may read two or more values.

Let me tell you a story.

Once upon a time, there was a peach tree with 12 peaches. There is a monkey named xjjdog. It wants to eat the peaches on it, but the peaches are not ripe yet.

When he went to see it the next day, he found that one peach was missing and became 11. After careful inquiry, it turned out that one was eaten first by Monkey A.

When I went to see it the next day, there was one less peach and became 10. It turned out that it was eaten by Chanzui Monkey B.

In this way, the peaches are missing day by day, leaving only the last two, but the peaches are still not ripe.

If you don't pick peaches, there will be no more. Xjjdog picked the last two peaches and was about to eat them, when a monkey X jumped out and said that I had been staring at these peaches for a year.

In this story, the transaction duration of monkey An and B is 1 day; the transaction duration of xjjdog is until the peach is ripe; and the duration of monkey X is longer, perhaps one year. They don't always see 12 peaches every day. Today's peaches may have been eaten by other monkeys, resulting in different observation results, which is the concept of unrepeatable reading.

Sometimes, even if the value read is the same, it does not prove that there is no problem. For example, some financial personnel misappropriated 200 million yuan to invest in stocks, and then returned 200 million at the end of the month. Although the final amount is the same, because of your long reconciliation period, you will not be able to find this difference.

How to solve the problem of unrepeatable reading? First of all, we have to see if it is a problem to read unrepeatable.

Some systems require such logic that each time a different value is read in a transaction, it is tolerable. But if you want to keep the number of peaches under your control before the peaches are ripe, not repeating them is a problem.

A very good way is that xjjdog stands under the peach tree all the time. When another monkey wants to pick peaches, drive it away. This approach works, but it is very inefficient in the database, which is an serializable-level approach.

MySQL has a default transaction isolation level, called repeatable read, which uses the MVCC approach (innodb) and is more lightweight.

3. Repeatable read

This is the merit of MVCC (Multi-Version Concurrency Control), which has three characteristics.

There is a version for each row of data, which is updated every time the data is updated

When modifying, make a copy, modify the current version at will, and there is no interference between transactions.

Compare the version number when saving. If commit overwrites the original record successfully, then rollback fails.

The main purpose of the implementation of MVCC in InnoDB is to improve the concurrency performance of the database and to deal with read-write conflicts in a better way, so that even when there are read-write conflicts, it can be unlocked and non-blocking. The key to its implementation also has three technologies:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Three implicit fields: DB_TRX_ID, which recently modified its transaction ID;DB_ROLL_PTR, rolls back the pointer to the previous version, and DB_ROW_ID, hides the primary key

Undo log: changes to the same record generate a linked list of version changes for this record

Read view: the read view generated during a snapshot read operation. In addition to using the additional information above, it also maintains an active transaction ID collection

The key to everything lies in the word snapshot.

For example, if transaction A takes a snapshot read of a record, a Read View is generated at the moment of the snapshot read. At this moment, transaction B and C, without commit, transaction D and E, the commit is completed before the ReadView is established, then the ReadView cannot read the modifications of B and C.

Unfortunately, repeatable reading can only solve the non-repeatable reading of snapshot reading, and the timing of snapshot reading will also affect the accuracy of reading. Please take a look at the following two situations.

In the following case, I read 500.

Transaction A transaction B Open transaction Snapshot read (No impact) query amount is 500Snapshot read query amount is 500update amount is 400commit transaction select Snapshot read amount is 500select lock in share mode current read amount is 400mm

In the following case, I read 400.

Transaction A transaction B open transaction snapshot read (no impact) query amount is 500update amount is 400commit transaction select snapshot read amount is 400select lock in share mode current read amount is 400mm

(the form is from [SnailMann]'s blog).

4. Illusory reading

Illusory reading, the word itself is very psychedelic. Phantom reading occurs at the RU, RC, and RR levels.

Take the simplest example. When you are asked to select whether a record exists and then plan to insert it later, if the record does not exist, then you perform the insert operation, but when you actually perform the insert operation, the result is an error, the record already exists, this is phantom reading.

First, confirm that the current tense can be read repeatedly. If not, modify it.

SELECT @ @ tx_isolation # set session transaction isolation level repeatable read

Let's take a look at this psychic process.

There are five steps, all of which I have marked for you. Let's introduce them one by one.

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Transaction A uses begin to open a transaction and then queries the record with id 3, which does not exist at this time. However, because snapshot read opens a read view for records with id 3, records with 3 cannot be read from beginning to end in this transaction. Well, that's what we need not to repeat it.

Next, transaction B inserts a record with an id of 3 and commits successfully

Transaction An also wants to insert this record at this time, so it performs the same insert operation, resulting in an error in the database, indicating that the record already exists.

Transaction A looked confused and wanted to see what the record was, but when it executed the select statement again, it could not find it.

But in other transactions, you can see this record because it has been committed correctly

This is phantom reading.

5. How to solve Phantom Reading

What's wrong with phantom reading? Most of the time, it's right, but it's a little weird. To prevent phantom reading, you need to turn on a high-intensity lock like FOR UPDATE, which is rarely used.

Why can insert report errors in the above operations, but select can not find the data? We have to mention two modes of database reading:

Snapshot read: a normal select operation that reads data from read view, possibly historical data

Current read: operations such as insert, update, delete and select..for update always read the latest data.

For the current read, the rows you read and the gaps between them are locked and will not be released until the transaction commits, and other transactions cannot be modified, so there will be no unrepeatable or phantom reading. So insert can find conflicts, while normal select can't. To solve the illusion, you need to add an X lock. In this case, it can be executed in transaction A:

SELECT * FROM xjjdog_tx WHERE id=3 FOR UPDATE

When doing so, it creates a lock even if a record with an id of 3 does not exist (a row X lock or next-key lock gap x lock may be added behind the record depending on whether the record exists or not).

Thank you for your reading. the above is the content of "what is dirty reading and illusory reading". After the study of this article, I believe you have a deeper understanding of what dirty reading and illusory reading are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.