Question:

1. What is illusory? 2. In Mysql repeatable read isolation level, can phantom read be prevented? 3. What is current read and snapshot read?

Definition of illusion:

Transaction A reads data according to certain conditions, during which transaction B inserts new data with the same search conditions. When transaction A reads data according to the original conditions again, the newly inserted data of transaction B is found, which is called phantom read

If transaction A searches according to certain criteria, then transaction B deletes one of the data that meets the criteria, causing transaction A to read the data again without one of the data. This is classified as an unrepeatable read

Preparations:

Mysql isolation level is repeatable read;

CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL.`age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT into author VALUES (1.'g1'.20), (5.'g5'.20), (15.'g15'.30), (20.'g20'.30);
Copy the code

Find the author whose age is 20 and change the name to G0

time Session 1 Session2
begin;
T1 select * from author where age = 20;

Results: (1, G1,20),(5, G5,20)
T2 INSERT into author VALUES (25,’g25′,20);
T3 select * from author where age = 20;

Results: (1, G1,20),(5, G5,20)
T4 update author set name = ‘G0’ where age = 20

Result: The number of affected rows is 3.
T5 select * from author where age = 20;

Results :(1,g0,20),(5,g0,20),(25,g0,20)

To analyze the situation:

  1. T1 reads data at age 20, Session1 gets 2 records.
  2. At time T2 another process, Session2, inserts a new record, also aged 20
  3. At T3, Session1 reads the data with age of 20 again and finds that there are still two pieces of data.Looks likeThe newly inserted data of Session2 does not affect the transaction read of Session1.

For the case at time T1 — T3, it seems from the results that the illusion problem is solved at the repeatable read isolation level.

  1. At T4, Session1 modified the data of age 20 and found that the number of affected rows was 3. Why can only 2 pieces of data be checked at T3, but 3 pieces of data are modified now?
  2. At time T5, Session1 reads the data of age 20 again, and finds 3 results. We know that the third modified item is the one added by Session2 at time T2.

In T4 and T5, Session1 reads the new data inserted by Session2. Hallucination occurs

At repeatable read isolation level, have phantom read problems been solved?

Those of you who know MVCC know or have heard of current read and snapshot read. (If you don’t know, you can look it up. Of course, THERE will be an article about MVCC in the future.) The first thing to know is MVCC’s ability to create snapshots of InnoDB data in seconds. Snapshot reading refers to reading the transaction visible version of data according to certain rules. Current reading is reading the latest version of data. When is snapshot read used? (Snapshot read, without locking)

Select * from…. where … Statements are snapshot reads

When is current read used? (Current read, which is locked during search)

select * from …. where  … for update select * from …. where  … lock in share mode update …. set .. where … delete from. . where ..

If snapshot reads are used in all transactions, there will be no illusions, but mixing snapshot reads with current reads will produce illusions.

If I use the current read, can I solve the phantom read problem?

Let’s restore our data to its original state

TRUNCATE TABLE author;
INSERT into author VALUES (1.'g1'.20), (5.'g5'.20), (15.'g15'.30), (20.'g20'.30);
Copy the code
time Session 1 Session2
begin;
T1 select * from author where age = 20 for update;

Results: (1, G1,20),(5, G5,20)
T2 INSERT into author VALUES (25,’g25′,20);

Block, waiting for lock
T3 select * from author where age = 20 for update;

Results: (1, G1,20),(5, G5,20)

You can see Session 2 is blocked. Session1 commits the transaction before it completes. When we use the current read for each read in a transaction, we manually serialize InnoDB. This reduces concurrency to some extent, but also avoids phantom reading. Why the current read blocks the insertion of new data is mainly the locking mechanism of the gap lock. (Mysql locks are also a big problem, which will be covered in a later section)

Conclusion:

  • Hope to understand the definition of illusory reading through this article. Read about other thingsNewly inserted dataThis phenomenon is called phantasmagoria.
  • What is the difference between current read and snapshot read, and when is snapshot read and when is current read
  • At the repeatable read isolation level, phantom reads can be avoided by using only current reads or snapshot reads in a transaction.