bzr commit into mysql-5.1-bugteam branch (magne.mahre:3197) Bug#20837

[Magne Mahre]

#At file:///data/z/mysql-5.1-bugteam-20837/ based on revid:joro@stripped

 3197 Magne Mahre	2009-10-26
      Bug #20837 Apparent change of isolation level during transaction
      
      SET TRANSACTION ISOLATION LEVEL is used to temporarily set
      the trans.iso.level for the next transaction.  After the
      transaction, the iso.level is (re-)set to value of the session
      variable 'tx_isolation'.
      
      This bug is caused by resetting the transaction isolation
      even on a statement commit.   The fix is to check if this
      is a full transaction commit/rollback, and only reset the
      value if so.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/handler.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2009-10-13 04:43:27 +0000
+++ b/mysql-test/r/innodb_mysql.result	2009-10-26 11:43:02 +0000
@@ -2251,4 +2251,56 @@ c >= '2009-10-09 00:00:00.001' AND c <= 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 DROP TABLE t1;
+#
+# #20837 Apparent change of isolation level during transaction
+#
+CREATE TABLE t1
+(
+id smallint not null,
+primary key (id)
+) engine=innodb;
+insert into t1 values (1),(2),(3);
+set transaction isolation level read uncommitted;
+should be READ UNCOMMITTED
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+begin;
+should be READ UNCOMMITTED
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+select * from t1;
+id
+1
+2
+3
+should be READ UNCOMMITTED
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+COMMIT;
+set @@autocommit = 0;
+set transaction isolation level read uncommitted;
+start transaction;
+select * from t1;
+id
+1
+2
+3
+should be READ UNCOMMITTED
+select @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+insert into t1 values (-1);
+should be READ UNCOMMITTED
+select @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+COMMIT;
+should be REPEATABLE READ
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-COMMITTED
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2009-10-13 04:43:27 +0000
+++ b/mysql-test/t/innodb_mysql.test	2009-10-26 11:43:02 +0000
@@ -489,5 +489,42 @@ EXPLAIN SELECT * FROM t1 WHERE a = 'TEST
   c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
 DROP TABLE t1;
 
+--echo #
+--echo # #20837 Apparent change of isolation level during transaction
+--echo #
+
+CREATE TABLE t1
+(
+  id smallint not null,
+  primary key (id)
+) engine=innodb;
+
+insert into t1 values (1),(2),(3);
+
+set transaction isolation level read uncommitted;
+--echo should be READ UNCOMMITTED
+SELECT @@tx_isolation;
+begin;
+--echo should be READ UNCOMMITTED
+SELECT @@tx_isolation;
+select * from t1;
+--echo should be READ UNCOMMITTED
+SELECT @@tx_isolation;
+COMMIT;
+
+set @@autocommit = 0;
+set transaction isolation level read uncommitted;
+start transaction;
+select * from t1;
+--echo should be READ UNCOMMITTED
+select @@tx_isolation;
+insert into t1 values (-1);
+--echo should be READ UNCOMMITTED
+select @@tx_isolation;
+COMMIT;
+--echo should be REPEATABLE READ
+SELECT @@tx_isolation;
+DROP TABLE t1;
+
 
 --echo End of 5.1 tests

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2009-09-23 13:21:29 +0000
+++ b/sql/handler.cc	2009-10-26 11:43:02 +0000
@@ -1243,12 +1243,14 @@ int ha_commit_one_phase(THD *thd, bool a
       if (thd->transaction.changed_tables)
         query_cache.invalidate(thd->transaction.changed_tables);
 #endif
-      thd->variables.tx_isolation=thd->session_tx_isolation;
     }
   }
   /* Free resources and perform other cleanup even for 'empty' transactions. */
   if (is_real_trans)
+  {
+    thd->variables.tx_isolation=thd->session_tx_isolation;
     thd->transaction.cleanup();
+  }
 #endif /* USING_TRANSACTIONS */
   DBUG_RETURN(error);
 }
@@ -1313,7 +1315,7 @@ int ha_rollback_trans(THD *thd, bool all
     trans->no_2pc=0;
     if (is_real_trans && thd->transaction_rollback_request)
       thd->transaction.xid_state.rm_error= thd->main_da.sql_errno();
-    if (all)
+    if (all && is_real_trans)
       thd->variables.tx_isolation=thd->session_tx_isolation;
   }
   /* Always cleanup. Even if there nht==0. There may be savepoints. */
@@ -1368,8 +1370,6 @@ int ha_autocommit_or_rollback(THD *thd, 
       if (thd->transaction_rollback_request && !thd->in_sub_stmt)
         (void) ha_rollback(thd);
     }
-
-    thd->variables.tx_isolation=thd->session_tx_isolation;
   }
 #endif
   DBUG_RETURN(error);


Attachment: [text/bzr-bundle] bzr/[e-mail]