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

[17 Jan 2005 14:25] Guilhem Bichot
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
[17 Jan 2005 14:26] Guilhem Bichot
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".
[4 Oct 2005 13:26] Mark Matthews
Reverify, please.
[5 Oct 2005 11:25] Valeriy Kravchuk
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.
[3 Nov 2005 0:36] 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/31847
[17 Feb 2006 22:44] Jim Winstead
Fixed in 5.1.8.
[28 Feb 2006 2:24] Paul DuBois
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)