MySQL Bugs: #53340: SET TRANSACTION ISOLATION LEVEL documentation (not allowed within active trans)

Bug #53340	SET TRANSACTION ISOLATION LEVEL documentation (not allowed within active trans)
Submitted:	1 May 2010 10:31	Modified:	4 May 2010 16:35
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
It is not allowed to issue SET TRANSACTION ISOLATION LEVEL while there is an active transaction.
This the implemented behaviour, and is standard.

However, it is not documented.

The manual says:

A change to the global default isolation level requires the SUPER privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction. 

However, it is not allowed to issue SET TRANSACTION ISOLATION LEVEL
in the middle of a transaction:

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

mysql> set transaction isolation level serializable;
ERROR 1568 (25001): Transaction isolation level can't be changed while a transaction is in progress
mysql> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.5.5-m3-valgrind-max-debug | 
+-----------------------------+
1 row in set (0.00 sec)

How to repeat:
Read http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

Suggested fix:
Update the manual.

People are actually getting this error (search for "transaction isolation level can't be changed while a transaction is in progress" online), which indicates that this statement is used in a wrong manner.

Indeed, this page, http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html, and corresponding pages for other versions should be clarified:

77-52-4-109:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.47-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> set transaction isolation level serializable;
ERROR 1568 (25001): Transaction isolation level can't be changed while a transaction is in progress

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

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

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

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

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

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

This behaviour was introduced by the fix for Bug#7955.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added to SET TRANSACTION section of manuals for 5.1 and up:

SET TRANSACTION ISOLATION LEVEL without GLOBAL or SESSION is not
allowed while there is an active transaction:

mysql> START TRANSACTION; Query OK, 0 rows affected (0.02 sec)

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
ERROR 1568 (25001): Transaction isolation level can't be changed
while a transaction is in progress