MySQL Bugs: #11207: SET GLOBAL TRANSACTION ISOLATION LEVEL seems to be broken

Bug #11207	SET GLOBAL TRANSACTION ISOLATION LEVEL seems to be broken
Submitted:	9 Jun 2005 14:11	Modified:	1 Sep 2005 2:15
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.6	OS:	Windows (Windows)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
SET GLOBAL with TRANSACTION ISOLATION LEVEL seems to not work at all.
Setting the leve will not change the server's global value at all (SESSION still works as it should).

I could only reproduce this under the Windows release, works fine under FreeBSD.

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

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

mysql> \r
Connection id:    19
Current database: world

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.01 sec)

mysql> \q

(Copy n' paste-friendly script below)

SELECT @@tx_isolation;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
\r
SELECT @@tx_isolation;

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

I suspect you just don't have SUPER privilege necessary to do SET GLOBAL TRANSACTION

SHOW GRANTS seems to disagree.

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

mysql> \r
Connection id:    3
Current database: *** NONE ***

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.08 sec)

mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Huh. Finally managed to reproduce that.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27993

pushed into 5.0.12

Noted in 5.0.12 changelog.

How did this past the test cases?

It's still there in 5.0.12:

mysql> SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 5.0.12-beta-nt-log |
+--------------------+
1 row in set (0.00 sec)

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

mysql> \r
Connection id:    11
Current database: world

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

mysql> \q

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

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

mysql> \r
Connection id:    11
Current database: test

mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.13-beta-nt |
+----------------+
1 row in set (0.02 sec)