MySQL Bugs: #100234: a case that REPEATABLE READ fails to take effects

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Usually, when one changes a session's isolation level by "set session transaction isolation level repeatable read;", it will take effect in next transaction. For example:
begin;
update xxx;
set session transaction isolation level repeatable read;
commit;
begin;
// the transaction isolation level is repeatable read

or:

begin;
update table_a xxx;
set session transaction isolation level repeatable read;
alter table_a  add column cc ...;
begin;
// the transaction isolation level is repeatable read

However, in the case below, something unexpected happens:

session1:
// 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;
update t1 set c1 = 10 where id = 1;
set session transaction isolation level repeatable read;
begin;
// last 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:
select * from t1;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+

Repeatable Read does not take effects! 

How to repeat:
As I described in Description. I don't know whether this phenomenon is a bug or not. It really bothers my colleagues and me.

Suggested fix:
When a "begin" statement triggers another transaction's commitment, the variable thd->tx_isolation should be set if the user has changed the session's isolation level just like a normal transaction commitment.

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.

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.

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!

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.

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)?