mysql: 8.0.23
transaction isolation level: repeatable read
test data:
create table test.test (
id int primary key,
value int
);
insert into test.test(id,value) values(1,0);
In same transaction, we get output value is 1 as we have execute update set value = 1.
start transaction;
select value from test.test where id = 1; -- output: value is 0
update test.test set value = 1 where id = 1;
select value from test.test where id = 1; -- output: value is 1
commit;
Whereas in different transaction, things become strange:
| A Transaction | B Transaction | |
|---|---|---|
| t1 | start transaction; |
|
| t2 | select value from test.test where id = 1; -- output: value is 0 |
|
| t3 | start transaction; |
|
| t4 | select value from test.test where id = 1; -- output: value is 0 |
|
| t5 | update test.test set value = 1 where id = 1; |
|
| t6 | select value from test.test where id = 1; -- output: value is 1 |
|
| t7 | commit; |
|
| t8 | select value from test.test where id = 1; -- output: value is 0 |
|
| t9 | update test.test set value = 1 where id = 1; -- 0 row affected |
|
| t10 | select value from test.test where id = 1; -- output: value is 0 (**so wired**) |
so what happen at t10, why the value output is 0 ?
Update statement of transaction A doesn't take effect at t9, because the value has been updated to a same value by transaction B. So t9 return "0 row affected" which means it's the other transaction (B) which modified the value rather then A transaction it self. So the select statement at t10 get the value in read view that is value 0.