MySQL Bugs: #7955: SET TRANSACTION ISOLATION LEVEL lives longer than next transaction

Bug #7955	SET TRANSACTION ISOLATION LEVEL lives longer than next transaction
Submitted:	17 Jan 2005 14:25	Modified:	28 Feb 2006 2:24
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.15-BK	OS:	Linux (linux)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
The manual says:
"SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
@{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE @}
This statement
sets the transaction isolation level for the next transaction, globally, 
or
for the current session.
The default behavior of @code{SET TRANSACTION} is to set the isolation 
level
for the next (not yet
started) transaction."
But (4.1.10):
MASTER> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

MASTER> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

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

MASTER> insert into ti1 values(12);
Query OK, 1 row affected (0.00 sec)

MASTER> insert into ti1 values(13);
Query OK, 1 row affected (0.00 sec)

MASTER> commit;
Query OK, 0 rows affected (0.14 sec)

MASTER> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED | !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
+----------------+
1 row in set (0.00 sec)

MASTER> show create table ti1;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| ti1   | CREATE TABLE `ti1` (
  `a` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+

How to repeat:
See description

The first question is: "is SELECT @@tx_isolation supposed to return the current isolation level?". Assuming that yes, I'm marking this bug as "verified".

Reverify, please.

mysql> CREATE TABLE `ti1` (
    ->    `a` int(11) default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.25 sec)

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

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

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

mysql> insert into ti1 values(12);
Query OK, 1 row affected (0.00 sec)

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

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

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

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

mysql> insert into ti1 values(13);
Query OK, 1 row affected (0.00 sec)

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

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

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.15-debug |
+--------------+
1 row in set (0.01 sec)

So, it's either a bug in code, or http://dev.mysql.com/doc/mysql/en/innodb-transaction-isolation.html page should be changed, bacause SET TRANSACTION ... really works as SET SESSION TRANSACTION ... now.

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/31847

Fixed in 5.1.8.

Noted in 5.1.7 changelog.

<literal>SET TRANSACTION ISOLATION LEVEL</literal> acted like
<literal>SET SESSION TRANSACTION ISOLATION LEVEL</literal>.
That is, it set the isolation level for longer than the next
transaction. (Bug #7955)