MySQL Bugs: #39170: SET TRANSACTION ISOLATION LEVEL persists through session

Bug #39170	SET TRANSACTION ISOLATION LEVEL persists through session
Submitted:	1 Sep 2008 23:01	Modified:	21 Jul 2010 15:31
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	4.1.22, 5.0.66a, 5.1.26, 6.0.6	OS:	Any
Assigned to:	Magne Mæhre	CPU Architecture:	Any
Tags:	transaction isolation level

Description:
SET TRANSACTION ISOLATION LEVEL (the one that just selects the level for the *next* transaction, not session/global) persists for session.

How to repeat:
set isolation level, start transaction in two connection. do test insert in one, see results on other connection, also persisting into the following transaction.
tested for both read uncommitted and read committed.

Suggested fix:
I know 4.1.22 is end-of-life.
But the 4.1 docs say this works, and it doesn't.

Thank you for a bug report. Verified just as described. All versions are affected. 

Looks like read-only/formal transactions are affected. If you really do some change to transactional (InnoDB) table and commit it, default level is restored. Compare this:

C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.6-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%isola%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> set transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%isola%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

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

mysql> select user from mysql.user;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

mysql> select  * from t1;
Empty set (0.00 sec)

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

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

mysql> show variables like '%isola%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> exit
Bye

and this (test case that demonstrates the problem on all versions):

C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 6.0.6-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%isola%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> set transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%isola%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

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

mysql> select user from mysql.user;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

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

mysql> show variables like '%isola%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

Fixed by patch for Bug #20837
Setting state to patch pending awaiting review of #20837

See Bug#20837

Noted in 5.5.5 changelog.

When SET TRANSACTION ISOLATIO LEVEL was used to set the isolation
level for the next transaction, the level could persist for 
subsequent transactions.