From: Date: November 2 2007 4:28am
Subject: Patch for Bug #20837
List-Archive: http://lists.mysql.com/internals/35146
Message-Id: <472A994F.4050802@greatopensource.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

This patch for http://bugs.mysql.com/bug.php?id=20837
Bug #20837: Apparent change of isolation level during transaction

--- sql/handler.cc.orig 2007-11-01 15:23:45.000000000 +0800
+++ sql/handler.cc      2007-11-02 11:00:50.083770896 +0800
@@ -794,6 +794,9 @@
       if (thd->transaction.changed_tables)
         query_cache.invalidate(thd->transaction.changed_tables);
 #endif
+    }
+    if (is_real_trans)
+    {
       thd->variables.tx_isolation=thd->session_tx_isolation;
       thd->transaction.cleanup();
     }
@@ -890,7 +893,6 @@
     else
       (void) ha_rollback_stmt(thd);

-    thd->variables.tx_isolation=thd->session_tx_isolation;
   }
 #endif
   DBUG_RETURN(error);

The default value of "tx_isolation" variable is to set the isolation 
level for the next (not started) transaction and it will be set to 
session value after the next transaction is end.
I think we shouldn't set the session value to default value in function 
"ha_autocommit_or_rollback()" if autocommit is off. Because the 
transaction isn't end.

Test and result:
mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.21-beta-debug-log |
+-----------------------+
1 row in set (0.00 sec)

mysql> create table t1 (s1 int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set @@autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

mysql> insert into t1 values (-1);
Query OK, 1 row affected (0.05 sec)

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

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

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

mysql> set @@autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> insert into t1 values (-2);
Query OK, 1 row affected (0.05 sec)

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