#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
