List:Commits« Previous MessageNext Message »
From:Magne Mahre Date:March 17 2010 7:15pm
Subject:bzr commit into mysql-5.1-bugteam branch (magne.mahre:3406) Bug#20837
View as plain text  
#At file:///data/z/mysql-5.1-bugteam-20837/ based on revid:joro@stripped

 3406 Magne Mahre	2010-03-17
      Bug#20837 Apparent change of isolation level during transaction
      
      SET TRANSACTION ISOLATION LEVEL <level> is used to
      temporarily change the transaction isolation level.  The
      life-time of the change is the duration of the _next_
      transaction.
      
      The bug is caused by setting the thd->variables.tx_isolation 
      field to the value of the session variable upon each
      statement commit.  It should only be set at the end of the
      full transaction.
      
      Since the SET TRANS ISO LEVEL statement itself causes
      an implicit commit after execution, 
      thd->set_isolation_statement was introduced to indicate 
      that the currently executing statement is a 
      "SET TRANSACTION ISOLATION LEVEL xxx". This enables
      us to determine that we should skip resetting back the 
      thd->tx_isolation immidiately.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/handler.cc
      sql/set_var.cc
      sql/sql_class.h
      sql/sql_parse.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2010-03-17 14:18:46 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-03-17 19:15:07 +0000
@@ -2350,4 +2350,53 @@ Null
 Index_type	BTREE
 Comment	
 DROP TABLE t1;
+#
+# Bug#20837 Apparent change of isolation level during 
+#           transaction
+#
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+CREATE TABLE t1 (s1 INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+COMMIT;
+SET @@autocommit = 1;
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should have been REPEATABLE READ
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+Should have been READ UNCOMMITTED
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should have been REPEATABLE READ;
+SET @@autocommit = 0;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+START TRANSACTION;
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+Should have been READ UNCOMMITTED
+SELECT * FROM t1;
+s1
+1
+2
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+Should have been READ UNCOMMITTED
+INSERT INTO t1 VALUES (-1);
+SELECT @@tx_isolation;
+@@tx_isolation
+READ-UNCOMMITTED
+Should have been READ UNCOMMITTED
+COMMIT;
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should have been REPEATABLE READ
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-03-17 14:18:46 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-03-17 19:15:07 +0000
@@ -589,4 +589,46 @@ ALTER TABLE t1 DROP INDEX k, ADD UNIQUE
 
 DROP TABLE t1;
 
+
+
+--echo #
+--echo # Bug#20837 Apparent change of isolation level during 
+--echo #           transaction
+--echo #
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+CREATE TABLE t1 (s1 INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+COMMIT;
+# Test setting isolation level in auto-commit
+# mode.  
+SET @@autocommit = 1;
+SELECT @@tx_isolation;
+--echo Should have been REPEATABLE READ
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SELECT @@tx_isolation;
+--echo Should have been READ UNCOMMITTED
+SELECT @@tx_isolation;
+--echo Should have been REPEATABLE READ;
+#
+# Test for non auto-commit mode
+#
+SET @@autocommit = 0;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+START TRANSACTION;
+SELECT @@tx_isolation;
+--echo Should have been READ UNCOMMITTED
+SELECT * FROM t1;
+SELECT @@tx_isolation;
+--echo Should have been READ UNCOMMITTED
+INSERT INTO t1 VALUES (-1);
+SELECT @@tx_isolation;
+--echo Should have been READ UNCOMMITTED
+COMMIT;
+SELECT @@tx_isolation;
+--echo Should have been REPEATABLE READ
+DROP TABLE t1;
+
+
+
 --echo End of 5.1 tests

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2009-12-01 09:19:51 +0000
+++ b/sql/handler.cc	2010-03-17 19:15:07 +0000
@@ -1369,9 +1369,14 @@ 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;
-  }
+  } 
+  else if (!(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)))
+  {
+    // reset tx_isolation after each statement in auto-commit
+    // mode, _unless_ it's the SET TRANS ISO LEVEL statement
+    if (!thd->set_isolation_statement)
+      thd->variables.tx_isolation= thd->session_tx_isolation;
+  }    
 #endif
   DBUG_RETURN(error);
 }

=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc	2010-03-10 12:26:39 +0000
+++ b/sql/set_var.cc	2010-03-17 19:15:07 +0000
@@ -1077,6 +1077,7 @@ static int check_tx_isolation(THD *thd,
 */
 static void fix_tx_isolation(THD *thd, enum_var_type type)
 {
+  thd->set_isolation_statement= true;
   if (type == OPT_SESSION)
     thd->session_tx_isolation= ((enum_tx_isolation)
 				thd->variables.tx_isolation);

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2010-02-26 13:16:46 +0000
+++ b/sql/sql_class.h	2010-03-17 19:15:07 +0000
@@ -1769,6 +1769,9 @@ public:
   uint       select_number;             //number of select (used for EXPLAIN)
   /* variables.transaction_isolation is reset to this after each commit */
   enum_tx_isolation session_tx_isolation;
+  /* is  current statement a "SET TRANS ISO LEVEL xxx"? */
+  bool       set_isolation_statement;  
+
   enum_check_fields count_cuted_fields;
 
   DYNAMIC_ARRAY user_var_events;        /* For user variables replication */

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2010-02-25 15:12:15 +0000
+++ b/sql/sql_parse.cc	2010-03-17 19:15:07 +0000
@@ -720,6 +720,8 @@ int end_trans(THD *thd, enum enum_mysql_
              xa_state_names[thd->transaction.xid_state.xa_state]);
     DBUG_RETURN(1);
   }
+  /* Reset trans iso level to the session default */
+  thd->variables.tx_isolation= thd->session_tx_isolation;
   switch (completion) {
   case COMMIT:
     /*
@@ -1632,6 +1634,7 @@ bool dispatch_command(enum enum_server_c
   thd_proc_info(thd, "cleaning up");
   thd->set_query(NULL, 0);
   thd->command=COM_SLEEP;
+  thd->set_isolation_statement= false;
   VOID(pthread_mutex_lock(&LOCK_thread_count)); // For process list
   thread_running--;
   VOID(pthread_mutex_unlock(&LOCK_thread_count));


Attachment: [text/bzr-bundle] bzr/magne.mahre@sun.com-20100317191507-t2eqpvmo0lyd67od.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (magne.mahre:3406) Bug#20837Magne Mahre17 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (magne.mahre:3406)Bug#20837Konstantin Osipov7 May