MySQL Bugs: #112897: next-transaction isolation level has problem if current transaction end by begin

Bug #112897	next-transaction isolation level has problem if current transaction end by begin
Submitted:	31 Oct 2023 11:44	Modified:	2 Nov 2023 2:41
Reporter:	weiquan wang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	isolation level begintransaction

Description:
mysql support to set the next-transaction isolation level, use this syntax:
SET @@transaction_isolation = value;
when we commit the next-transaction by explicit "commit" or implicit commit of "DDL", the following transaction won't be affected by the @@transaction_isolation value that we had been set before. But now it still affects if we end the transaction by "begin" untill the transaction end not by "begin".

How to repeat:
we use two session name session1 and session2, and both of them we ensure the transaction isolation level is "REPEATABLE-READ",
session1:
create table test.t1(c1 int primary key);
insert into test.t1 values(1);
begin;
insert into test.t1 values(2);
change to session2:
select * from test.t1; #has one value 1
set @@transaction_isolation = "read-uncommitted";
begin;
select * from test.t1; #has two values 1 and 2, here is ok
begin; #here we implicit commit the transaction and start a new transaction
select * from test.t1; #has two values 1, 2 and we expect one value 1
begin;
select * from test.t1; #still two values 1 and 2
insert into test.t1 values(3);
begin;
select * from test.t1; #has three values 1, 2 and 3

Suggested fix:
we expect to set next-transaction isolation level only, if the transaction end by begin, it will still work as the doc says.

Hi Mr. wang,

Thank you for your bug report.

First of all, have you tried it in the normal manner:

SET TRANSACTION ISOLATION LEVEL ..........

Second, have you checked that your user variable has been set properly.

Last, but not least, global isolation levels do not affect current connection. Hence, have you tried to set it at the session level.

Can't repeat.

Hi,

One more comment.

Have you checked the session transaction isolation level after you issued that command and before you ran your transaction(s) ?????

thank you for your comment,
I am sorry to hear that this bug can't repeat, considering that it may be due to insufficient information provided by me, now I describe it in detail, I hope it works:

before we start to test, I use root to login in, and it set no user variables, the global and session isolation is the same, it can be "REPEATABLE-READ" or "READ-COMMITTED", then in the normal transaction cases, we can't see the data that we haven't commit. now, let's start to test:

session 1:

mysql> select @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-COMMITTED                 | READ-COMMITTED                  |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> create table test.t1(c1 int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test.t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.t1 values(2);
Query OK, 1 row affected (0.00 sec)

#######################
#the session 1 we don't disconnect, it is in transaction, and data 2 haven't be committed, so it won't be seen in other transactions.

session 2:

mysql> select @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-COMMITTED                 | READ-COMMITTED                  |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> create table test.t2(c1 int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

###########################
let's focus on the select sqls: 
the 1st select sql we get one value, it's ok, becacuse now isolution is read-committed
the 2nd select sql we get two value and we see the data 2, it's ok, because we set next-transaction isolution is read-uncommitted
the 3rd select sql we get two value and we also see the data 2, it's wrong, because the begin implicit commit the transaction before, now isolution should be read-committed, and we can't see the data 2.
and now we go on to make a comparison, we commit the transaction and start to the 4th select sql, and we get the one value, it's what we expect. it seems that if we explicit commit, the next-transaction isolution end effective. test going on, let's test the implicit commit of ddls, we execute "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" before the 5th select, and we see the data 2, it's ok, the next-transaction isolution is effective, the we execute the ddl create table and 6th select sql, we only see one data, it illustrat implicit commit can make the next-transaction isolution end effective. but why the begin will not?
to confirm it, we execute "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" again, and we start the 6th select sql, it's ok we get data 2, then we execute begin to start a new transaction, the 7th select sql get data 2, and we begin to start a new transaction, the 8th select sql get data 2, the 8th, 9th is the same, it seems if we use begin to end the transaction, the next-transaction isolution will always effective. As a database development engineer， I feel it's a bug.
plz check what I say, and I am pleasure to wait for your more comment.

Hi,

We have analysed your output and all values are correct.

Can you please just extract the one that you think is wrong.

Because most of all transactions are returning correct results.

Do note that there are implicit commits on changing of the isolation level.

Also, be specific in specifying whether you are setting session or global variables. Your test case does not specify precisely which one are you setting.

However, most important is to come up with a single example, which is wrong in your opinion.

Hi,

first of all, this bug has no influence about setting session or global variables, I really don't set them all, and they are with default value "READ COMMITTED", (emmm, I doubt that do you really know about the difference between

"SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;",

"SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; ", and

"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"? a small doubt, ^-^)

now I will show you the bug come up with a single example:

three sessions with root, session 1, session 2 and session 3:

session 1: (here all data are correct)

mysql> select @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-COMMITTED                 | READ-COMMITTED                  |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> create table test.t1(str varchar(100));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.t1 values("Committed data, you can see me!");
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.t1 values("Uncommitted data, you can not see me!");
Query OK, 1 row affected (0.00 sec)

session 2:(here all data are correct, you need to come up with session 3)

mysql> select @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-COMMITTED                 | READ-COMMITTED                  |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+---------------------------------------+
| str                                   |
+---------------------------------------+
| Committed data, you can see me!       |
| Uncommitted data, you can not see me! |
+---------------------------------------+
2 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+---------------------------------+
| str                             |
+---------------------------------+
| Committed data, you can see me! |
+---------------------------------+
1 row in set (0.00 sec)

session 3:(here the data is from the result of mysql, all version is the same. plz tell me why the last result of select sql is different to session 2?)

mysql> select @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-COMMITTED                 | READ-COMMITTED                  |
+--------------------------------+---------------------------------+
1 row in set (0.01 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+---------------------------------------+
| str                                   |
+---------------------------------------+
| Committed data, you can see me!       |
| Uncommitted data, you can not see me! |
+---------------------------------------+
2 rows in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.t1;
+---------------------------------------+
| str                                   |
+---------------------------------------+
| Committed data, you can see me!       |
| Uncommitted data, you can not see me! |
+---------------------------------------+
2 rows in set (0.00 sec)

it's wrong in session 3 that the last select sql can see the uncommitted data. The correct data should be the data from the last select sql in session 2, that's my opinion.

Hi,

We used your test case fully.

We only took care to set isolation level whenever necessary, on session level. Since you do not run concurrent transactions, global isolation level is irrelevant. Also, do note that there are many commands that do implicit COMMIT, among which is BEIG/n or START TRANSACTION.

Hence, when we corrected your SET TRANSACTION LEVEL mistakes, we get this output:

@@global.transaction_isolation	@@session.transaction_isolation
READ-COMMITTED	READ-COMMITTED
@@global.transaction_isolation	@@session.transaction_isolation
READ-COMMITTED	READ-COMMITTED
str
Committed data, you can see me!
Uncommitted data, you can not see me!
str
Committed data, you can see me!
Uncommitted data, you can not see me!
str
Committed data, you can see me!
Uncommitted data, you can not see me!
@@global.transaction_isolation	@@session.transaction_isolation
READ-UNCOMMITTED	READ-UNCOMMITTED
str
Committed data, you can see me!
Uncommitted data, you can not see me!
str
Committed data, you can see me!
Uncommitted data, you can not see me!

Can't repeat.