Bug #119407 Isolation Level Settings

• Submitted: 17 Nov 6:17
• Modified: 19 Nov 4:15
• Reporter: Trevor Trevor
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any
• Tags: Isolation level

[17 Nov 6:17] Trevor Trevor

Description:
Executing the following SQL statement should change the session-level isolation level, but in reality, the isolation level has not changed.
sql：set @@transaction_isolation= SERIALIZABLE;

How to repeat:
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> set @@transaction_isolation= SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

[19 Nov 4:15] Trevor Trevor

Found the cause.
set @@transaction_isolation equals set TRANSACTION ISOLATION LEVEL，without key word global or session，the scope is on the next trx.

https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html
Without any SESSION or GLOBAL keyword:

The statement applies only to the next single transaction performed within the session.

Subsequent transactions revert to using the session value of the named characteristics.

The statement is not permitted within transactions: