List:Commits« Previous MessageNext Message »
From:Dmitry Lenev Date:October 26 2010 2:20pm
Subject:bzr push into mysql-5.5-runtime branch (Dmitry.Lenev:3181 to 3182)
View as plain text  
 3182 Dmitry Lenev	2010-10-26
      More changes to draft patch refactoring global read
      lock implementation. Makes GRL yet another type of
      metadata lock and thus exposes it to deadlock detector
      in MDL subsystem.
      
      Solves bugs #54673 "It takes too long to get readlock for
      'FLUSH TABLES WITH READ LOCK'" and #57006 "Deadlock between
      HANDLER and FLUSH TABLES WITH READ LOCK".
      
      Work-in-progress. Extended test coverage.

    modified:
      mysql-test/r/flush_read_lock.result
      mysql-test/t/flush_read_lock.test
 3181 Dmitry Lenev	2010-10-26
      More changes to draft patch refactoring global read
      lock implementation. Makes GRL yet another type of
      metadata lock and thus exposes it to deadlock detector
      in MDL subsystem.
      
      Solves bugs #54673 "It takes too long to get readlock for
      'FLUSH TABLES WITH READ LOCK'" and #57006 "Deadlock between
      HANDLER and FLUSH TABLES WITH READ LOCK".
      
      Work-in-progress. Get rid of special GRL handling in
      CREATE/DROP PROCEDURE/FUNCTION.

    modified:
      sql/lock.cc
      sql/sql_class.h
      sql/sql_parse.cc
=== modified file 'mysql-test/r/flush_read_lock.result'
--- a/mysql-test/r/flush_read_lock.result	2010-10-25 18:30:28 +0000
+++ b/mysql-test/r/flush_read_lock.result	2010-10-26 14:19:27 +0000
@@ -1362,6 +1362,159 @@ unlock tables;
 delete from t3_trans;
 set debug_sync= "RESET";
 #
+# 38) Test effect of auto-commit mode for DML on transactional
+#     temporary tables.
+# 
+# 38.1) When auto-commit is on each such a statement ends with commit
+#       of changes to temporary tables. But since transactions doing
+#       such changes are considered read only [sic!/QQ] this commit
+#       is compatible with FTWRL.
+#
+#       Let us demostrate this fact for some common DML statements.
+Success: Was able to run 'delete from t3_temp_trans' under FTWRL.
+Success: Was able to run 'delete from t3_temp_trans' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'delete from t3_temp_trans' was active in another connection.
+Success: Was able to run 'insert into t3_temp_trans values (1)' under FTWRL.
+Success: Was able to run 'insert into t3_temp_trans values (1)' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'insert into t3_temp_trans values (1)' was active in another connection.
+Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' under FTWRL.
+Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' was active in another connection.
+# 
+# 38.2) When auto-commit is off DML on transaction temporary tables
+#       is compatible with FTWRL.
+#
+set autocommit= 0;
+Success: Was able to run 'delete from t3_temp_trans' under FTWRL.
+Success: Was able to run 'delete from t3_temp_trans' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'delete from t3_temp_trans' was active in another connection.
+Success: Was able to run 'insert into t3_temp_trans values (1)' under FTWRL.
+Success: Was able to run 'insert into t3_temp_trans values (1)' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'insert into t3_temp_trans values (1)' was active in another connection.
+Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' under FTWRL.
+Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' was active in another connection.
+set autocommit= 1;
+#
+# 39) Test effect of DDL on transactional tables.
+#
+# 39.1) Due to implicit commit at the end of statement some of DDL
+#       statements which are compatible with FTWRL in non-transactional
+#       case are not compatible in case of transactional tables.
+#
+# 39.1.a) ANALYZE TABLE for transactional table is incompatible with
+#         FTWRL.
+flush tables with read lock;
+# Implicit commits are allowed under FTWRL.
+analyze table t3_trans;
+Table	Op	Msg_type	Msg_text
+test.t3_trans	analyze	status	OK
+unlock tables;
+#
+# Switching to connection 'con1'.
+flush tables with read lock;
+# Switching to connection 'default'.
+analyze table t3_trans;
+# Switching to connection 'con1'.
+# Check that ANALYZE TABLE is blocked.
+unlock tables;
+# Switching to connection 'default'.
+# Reap ANALYZE TABLE
+Table	Op	Msg_type	Msg_text
+test.t3_trans	analyze	status	OK
+#
+# 39.1.b) CHECK TABLE for transactional table is compatible with FTWRL.
+#         Although it does implicit commit at the end of statement it
+#         is considered to be read-only operation.
+# Skip last part of compatibility testing as this statement
+# releases metadata locks in non-standard place.
+Success: Was able to run 'check table t3_trans' under FTWRL.
+Success: Was able to run 'check table t3_trans' with FTWRL active in another connection.
+#
+# 39.2) Situation with DDL on temporary transactional tables is
+#       complex.
+#
+# 39.2.a) Some statements compatible with FTWRL since they don't
+#         do implicit commit.
+#
+# For example, CREATE TEMPORARY TABLE:
+Success: Was able to run 'create temporary table t4_temp_trans(i int) engine=innodb' under FTWRL.
+Success: Was able to run 'create temporary table t4_temp_trans(i int) engine=innodb' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'create temporary table t4_temp_trans(i int) engine=innodb' was active in another connection.
+#
+# Or DROP TEMPORARY TABLE:
+Success: Was able to run 'drop temporary tables t3_temp_trans' under FTWRL.
+Success: Was able to run 'drop temporary tables t3_temp_trans' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'drop temporary tables t3_temp_trans' was active in another connection.
+#
+# 39.2.b) Some statements do implicit commit but are considered
+#         read-only and so are compatible with FTWRL.
+#
+# For example, REPAIR TABLE:
+Success: Was able to run 'repair table t3_temp_trans' under FTWRL.
+Success: Was able to run 'repair table t3_temp_trans' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'repair table t3_temp_trans' was active in another connection.
+#
+# And ANALYZE TABLE:
+Success: Was able to run 'analyze table t3_temp_trans' under FTWRL.
+Success: Was able to run 'analyze table t3_temp_trans' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'analyze table t3_temp_trans' was active in another connection.
+#
+# 39.2.c) Some statements do implicit commit and not
+#         considered read-only. As result they are 
+#         not compatible with FTWRL.
+#
+flush tables with read lock;
+# Implicit commits are allowed under FTWRL.
+alter table t3_temp_trans add column c1 int;
+unlock tables;
+#
+# Switching to connection 'con1'.
+flush tables with read lock;
+# Switching to connection 'default'.
+alter table t3_temp_trans drop column c1;
+# Switching to connection 'con1'.
+# Check that ALTER TABLE is blocked.
+unlock tables;
+# Switching to connection 'default'.
+# Reap ALTER TABLE
+#
+# 40) Test effect of implicit commit for DDL which is otherwise
+#     compatible with FTWRL. Implicit commit at the start of DDL
+#     statement can make it incompatible with FTWRL if there are
+#     some changes to be commited even in case when DDL statement
+#     itself is compatible with FTWRL.
+#
+# For example CHECK TABLE for base non-transactional tables and
+# ALTER TABLE for temporary non-transactional tables are affected.
+begin;
+insert into t3_trans values (1);
+#
+# Switching to connection 'con1'.
+flush tables with read lock;
+# Switching to connection 'default'.
+check table t1_base;
+# Switching to connection 'con1'.
+# Check that CHECK TABLE is blocked.
+unlock tables;
+# Switching to connection 'default'.
+# Reap CHECK TABLE
+Table	Op	Msg_type	Msg_text
+test.t1_base	check	status	OK
+begin;
+delete from t3_trans;
+#
+# Switching to connection 'con1'.
+flush tables with read lock;
+# Switching to connection 'default'.
+alter table t1_temp add column c1 int;
+# Switching to connection 'con1'.
+# Check that ALTER TABLE is blocked.
+unlock tables;
+# Switching to connection 'default'.
+# Reap ALTER TABLE
+alter table t1_temp drop column c1;
+#
 # Check that FLUSH TABLES WITH READ LOCK is blocked by individual
 # statements and is not blocked in the presence of transaction which
 # has done some changes earlier but is idle now (or does only reads).

=== modified file 'mysql-test/t/flush_read_lock.test'
--- a/mysql-test/t/flush_read_lock.test	2010-10-25 18:30:28 +0000
+++ b/mysql-test/t/flush_read_lock.test	2010-10-26 14:19:27 +0000
@@ -1781,6 +1781,209 @@ set debug_sync= "RESET";
 
 
 --echo #
+--echo # 38) Test effect of auto-commit mode for DML on transactional
+--echo #     temporary tables.
+--echo # 
+--echo # 38.1) When auto-commit is on each such a statement ends with commit
+--echo #       of changes to temporary tables. But since transactions doing
+--echo #       such changes are considered read only [sic!/QQ] this commit
+--echo #       is compatible with FTWRL.
+--echo #
+--echo #       Let us demostrate this fact for some common DML statements.
+let $statement= delete from t3_temp_trans;
+let $cleanup_stmt= ;
+--source include/check_ftwrl_compatible.inc
+
+let $statement= insert into t3_temp_trans values (1);
+let $cleanup_stmt= delete from t3_temp_trans limit 1;
+--source include/check_ftwrl_compatible.inc
+
+let $statement= update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j;
+let $cleanup_stmt= ;
+--source include/check_ftwrl_compatible.inc
+
+--echo # 
+--echo # 38.2) When auto-commit is off DML on transaction temporary tables
+--echo #       is compatible with FTWRL.
+--echo #
+set autocommit= 0;
+let $statement= delete from t3_temp_trans;
+let $cleanup_stmt= ;
+--source include/check_ftwrl_compatible.inc
+
+let $statement= insert into t3_temp_trans values (1);
+let $cleanup_stmt= delete from t3_temp_trans limit 1;
+--source include/check_ftwrl_compatible.inc
+
+let $statement= update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j;
+let $cleanup_stmt= ;
+--source include/check_ftwrl_compatible.inc
+set autocommit= 1;
+
+
+--echo #
+--echo # 39) Test effect of DDL on transactional tables.
+--echo #
+--echo # 39.1) Due to implicit commit at the end of statement some of DDL
+--echo #       statements which are compatible with FTWRL in non-transactional
+--echo #       case are not compatible in case of transactional tables.
+--echo #
+--echo # 39.1.a) ANALYZE TABLE for transactional table is incompatible with
+--echo #         FTWRL.
+flush tables with read lock;
+--echo # Implicit commits are allowed under FTWRL.
+analyze table t3_trans;
+unlock tables;
+--echo #
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--send analyze table t3_trans
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Check that ANALYZE TABLE is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for global read lock" and
+        info = "analyze table t3_trans";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap ANALYZE TABLE
+--reap
+
+--echo #
+--echo # 39.1.b) CHECK TABLE for transactional table is compatible with FTWRL.
+--echo #         Although it does implicit commit at the end of statement it
+--echo #         is considered to be read-only operation.
+let $statement= check table t3_trans;
+let $cleanup_stmt= ;
+--echo # Skip last part of compatibility testing as this statement
+--echo # releases metadata locks in non-standard place.
+let $skip_3rd_check= 1;
+--source include/check_ftwrl_compatible.inc
+let $skip_3rd_check= ;
+
+--echo #
+--echo # 39.2) Situation with DDL on temporary transactional tables is
+--echo #       complex.
+--echo #
+--echo # 39.2.a) Some statements compatible with FTWRL since they don't
+--echo #         do implicit commit.
+--echo #
+--echo # For example, CREATE TEMPORARY TABLE:
+let $statement= create temporary table t4_temp_trans(i int) engine=innodb;
+let $cleanup_stmt= drop temporary tables t4_temp_trans;
+--source include/check_ftwrl_compatible.inc
+--echo #
+--echo # Or DROP TEMPORARY TABLE:
+let $statement= drop temporary tables t3_temp_trans;
+let $cleanup_stmt= create temporary table t3_temp_trans(i int) engine=innodb;
+--source include/check_ftwrl_compatible.inc
+--echo #
+--echo # 39.2.b) Some statements do implicit commit but are considered
+--echo #         read-only and so are compatible with FTWRL.
+--echo #
+--echo # For example, REPAIR TABLE:
+let $statement= repair table t3_temp_trans;
+let $cleanup_stmt= ;
+--source include/check_ftwrl_compatible.inc
+--echo #
+--echo # And ANALYZE TABLE:
+let $statement= analyze table t3_temp_trans;
+let $cleanup_stmt= ;
+--source include/check_ftwrl_compatible.inc
+--echo #
+--echo # 39.2.c) Some statements do implicit commit and not
+--echo #         considered read-only. As result they are 
+--echo #         not compatible with FTWRL.
+--echo #
+flush tables with read lock;
+--echo # Implicit commits are allowed under FTWRL.
+alter table t3_temp_trans add column c1 int;
+unlock tables;
+--echo #
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--send alter table t3_temp_trans drop column c1
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Check that ALTER TABLE is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for global read lock" and
+        info = "alter table t3_temp_trans drop column c1";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap ALTER TABLE
+--reap
+
+
+--echo #
+--echo # 40) Test effect of implicit commit for DDL which is otherwise
+--echo #     compatible with FTWRL. Implicit commit at the start of DDL
+--echo #     statement can make it incompatible with FTWRL if there are
+--echo #     some changes to be commited even in case when DDL statement
+--echo #     itself is compatible with FTWRL.
+--echo #
+--echo # For example CHECK TABLE for base non-transactional tables and
+--echo # ALTER TABLE for temporary non-transactional tables are affected.
+begin;
+insert into t3_trans values (1);
+--echo #
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--send check table t1_base
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Check that CHECK TABLE is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for global read lock" and
+        info = "check table t1_base";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap CHECK TABLE
+--reap
+begin;
+delete from t3_trans;
+--echo #
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--send alter table t1_temp add column c1 int
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Check that ALTER TABLE is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for global read lock" and
+        info = "alter table t1_temp add column c1 int";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap ALTER TABLE
+--reap
+alter table t1_temp drop column c1;
+
+
+--echo #
 --echo # Check that FLUSH TABLES WITH READ LOCK is blocked by individual
 --echo # statements and is not blocked in the presence of transaction which
 --echo # has done some changes earlier but is idle now (or does only reads).

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.5-runtime branch (Dmitry.Lenev:3181 to 3182) Dmitry Lenev26 Oct