Bug #100234 | a case that REPEATABLE READ fails to take effects | ||
---|---|---|---|
Submitted: | 16 Jul 2020 10:45 | Modified: | 20 Jul 2020 9:00 |
Reporter: | Dehao Wang (OCA) | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 8.0.16 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[16 Jul 2020 10:45]
Dehao Wang
[16 Jul 2020 14:12]
MySQL Verification Team
Hi Mr. Wang, Thank you for your bug report. However, this is not a bug. The isolation level set for one session does not apply to other sessions. As simple as that. This is described in our Reference Manual. Not a bug.
[17 Jul 2020 2:20]
Dehao Wang
Hi ,Thank you for replying. I know The isolation level set for one session does not apply to other sessions. Maybe I did not make myself clear. I mean in the same session ("session 1" in the case I described), after the session's isolation level is changed to RR, the next transaction in the same session("session 1") still acts like RC.
[17 Jul 2020 2:27]
Dehao Wang
I will repeat the case here with more details: ************ session 1 **************** // current isolation level is RC create table t1 (id int primary key,c1 int); insert into t1 values (1,1),(2,2),(3,3); begin; // first transaction in session 1 update t1 set c1 = 10 where id = 1; set session transaction isolation level repeatable read; begin; // second transaction in session 1 // the first transaction is committed implicitly select * from t1; +----+------+ | id | c1 | +----+------+ | 1 | 10 | | 2 | 2 | | 3 | 3 | +----+------+ show variables like "%iso%"; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ ******************session 2************** update t1 set c1 = 1 where id =1; ******************session 1*************** // here we back to session 1, still in the second tranction select * from t1; +----+------+ | id | c1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ // now we can not read (id:1 | c1:10), it is still not RR isolation in session 1!
[17 Jul 2020 13:14]
MySQL Verification Team
Hi, What we see is expected behaviour. It is the same session, so changes are visible and your status shows that it is still a repeatable read. Not a bug.
[20 Jul 2020 9:00]
Dehao Wang
Hi,Thank you again, Sesssion 1's isolation level is RR, why the same transaction of session 1 can not repeat read the record (id:1,c1:10)?