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

Bug #39170	SET TRANSACTION ISOLATION LEVEL persists through session
Submitted:	2 Sep 2008 1:01	Modified:	2 Sep 2008 5:22
Reporter:	Arjen Lentz
Status:	Verified
Category:	Server: General	Severity:	S3 (Non-critical)
Version:	4.1.22, 5.0.66a, 5.1.26, 6.0.6	OS:	Any
Assigned to:	Davi Arnaut	Target Version:
Tags:	transaction isolation level
Triage:	Triaged: D2 (Serious)

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)