0

I found a difference of snapshot between mysql5.7.34 and mysql8.0.25 and I want to know why.

Transaction Isolation：REPEATABLE READS

this is the table I used

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `yn` int(11) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

Initial data

id yn name
1 1 w
2 0 w
3 0 w
Number Transaction1 Transaction2
1 begin
2 select * from test
3 begin
4 update test set yn=0 where id=1
5 commit
6 select * from test
7 update test set yn=0 where yn=1
8 select * from test
9 update test set yn=0
10 select * from test
11 commit

In step 10, if I use mysql8, the output is

id yn name
1 1 w
2 0 w
3 0 w

But when I use mysql5.7, the output of step 10 is

id yn name
1 0 w
2 0 w
3 0 w

I can understand the behavior of mysql5.7. The operation of step 9 update the snapshot of data whose id is 1 if I use mysql5.7. I want to know why mysql8 does not update snapshot like mysql5.7? what is the defference between mysql5 and mysql8 which lead to the different behavior?

Any input will be appreciated.

Thanks

jianan shang
  • 1
  • 1
  • I tested this on MySQL 5.7.27 and 8.0.23, which are the versions I have installed right now. There is no difference, they both behave the same. Have you read the [release notes for MySQL 8.0](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/) to see if there is any mention of a change in transaction sematics? – Bill Karwin May 23 '21 at 03:14
  • what is the result of your test? same as my test in mysql5.7 or the other? I have not read release notes and I will read it later.@BillKarwin – jianan shang May 23 '21 at 05:03
  • Both versions show the result you refer to as the mysql8 result. – Bill Karwin May 23 '21 at 05:39
  • @BillKarwin I make a video of my test in mysql57 and the result is different to yours. Counld you please spare time to watch this video?[link](https://www.youtube.com/watch?v=U5OO1DVYFus) – jianan shang May 23 '21 at 06:38
  • Here's my test on 5.7.27: https://imgur.com/o71sTCZ – Bill Karwin May 23 '21 at 16:31
  • I think this is consistent with how InnoDB works, for example I answered a similar question in 2019: https://stackoverflow.com/questions/59287861/repeatable-read-isolation-level-select-vs-update-where/59296526#59296526 – Bill Karwin May 23 '21 at 16:32
  • But I cannot explain why you got different results. That is not how I understand InnoDB to work. AFAIK, the repeatable-read snapshot should be refreshed only if that transaction executes an update that changes rows. If it changes zero rows, the snapshot is not updated. But that's not what you are seeing. – Bill Karwin May 23 '21 at 16:35
  • @BillKarwin Would you please try again to make sure the version you used is 5.7? One of my friend use 5.7.31 and have the same result as me. – jianan shang May 26 '21 at 01:28
  • I already ran the test twice. It is 5.7.27 as I described above. – Bill Karwin May 27 '21 at 20:11
  • @BillKarwin Here is my test on [mysql5.7.27](https://www.youtube.com/watch?v=izyuNmGrfIM). We use the same version but the result is different. Is there any difference between your test and my test? – jianan shang May 29 '21 at 01:16

0 Answers0