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:Microsoft Windows (Windows)
Assigned to: Alexey Botchkov

[9 Jun 2005 14:11] Tobias Asplund
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;
[5 Aug 2005 13:32] Alexey Botchkov
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
[5 Aug 2005 21:10] Tobias Asplund
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)
[6 Aug 2005 14:42] Alexey Botchkov
Huh. Finally managed to reproduce that.
[8 Aug 2005 12:45] Bugs System
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
[27 Aug 2005 15:26] Alexey Botchkov
pushed into 5.0.12
[1 Sep 2005 2:15] Paul Dubois
Noted in 5.0.12 changelog.
[15 Sep 2005 19:31] Tobias Asplund
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
[15 Sep 2005 20:27] Miguel Solorzano
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)