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
Tags: transaction isolation level
Triage: Triaged: D2 (Serious)

[1 Sep 2008 23:01] Arjen Lentz
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.
[2 Sep 2008 3:22] Valerii Kravchuk
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)
[17 Mar 2010 19:17] Magne Mæhre
Fixed by patch for Bug #20837
Setting state to patch pending awaiting review of #20837
[8 Jun 2010 18:55] Magne Mæhre
See Bug#20837
[21 Jul 2010 15:31] Paul Dubois
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.