From: Dmitry Lenev Date: October 22 2010 8:22am Subject: bzr push into mysql-5.5-runtime branch (Dmitry.Lenev:3175 to 3176) List-Archive: http://lists.mysql.com/commits/121645 Message-Id: <20101022082249.ED569E5AA8@mockturtle> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3176 Dmitry Lenev 2010-10-22 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. Fixed problem exposed in the process. modified: mysql-test/r/flush_read_lock.result mysql-test/t/flush_read_lock.test sql/sql_update.cc 3175 Konstantin Osipov 2010-10-22 Bug#54673, code review: remove a special case for innobackup.pl, it's unnecessary. modified: mysql-test/r/flush_read_lock.result mysql-test/t/flush_read_lock.test sql/handler.cc sql/lock.cc sql/sql_class.h sql/transaction.cc === modified file 'mysql-test/r/flush_read_lock.result' --- a/mysql-test/r/flush_read_lock.result 2010-10-22 07:16:34 +0000 +++ b/mysql-test/r/flush_read_lock.result 2010-10-22 08:21:39 +0000 @@ -144,8 +144,9 @@ Success: Was not able to run 'alter even Success: 'alter event e1 comment 'test'' is blocked by FTWRL active in another connection. Success: FTWRL is blocked when 'alter event e1 comment 'test'' is active in another connection. # -# 1.x) The rest of ALTER statements are too special to -# be tested here. +# 1.x) The rest of ALTER statements (ALTER TABLESPACE, +# ALTER LOGFILE GROUP and ALTER SERVER) are too +# special to be tested here. # # # 2) ANALYZE TABLE statement is compatible with FTWRL. @@ -385,18 +386,39 @@ Success: Was not able to run 'create use Success: 'create user mysqltest_u1' is blocked by FTWRL active in another connection. Success: FTWRL is blocked when 'create user mysqltest_u1' is active in another connection. # -# 8.x) The rest of CREATE variants are too special to test here. +# 8.x) The rest of CREATE variants (CREATE LOGFILE GROUP, +# CREATE TABLESPACE and CREATE SERVER) are too special +# to test here. # # # 9) PREPARE, EXECUTE and DEALLOCATE PREPARE statements. # # 9.1) PREPARE statement is compatible with FTWRL as it # doesn't change any data. +# +# 9.1.a) Prepare of simple INSERT statement. +# # Skip last part of compatibility testing as this statement # releases metadata locks in non-standard place. Success: Was able to run 'prepare stmt1 from 'insert into t1_base values (1)'' under FTWRL. Success: Was able to run 'prepare stmt1 from 'insert into t1_base values (1)'' with FTWRL active in another connection. # +# 9.1.b) Prepare of multi-UPDATE. At some point such statements +# tried to acquire thr_lock.c locks during prepare phase. +# This no longer happens and thus it is compatible with +# FTWRL. +# Skip last part of compatibility testing as this statement +# releases metadata locks in non-standard place. +Success: Was able to run 'prepare stmt1 from 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j'' under FTWRL. +Success: Was able to run 'prepare stmt1 from 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j'' with FTWRL active in another connection. +# +# 9.1.c) Prepare of multi-DELETE. Again PREPARE of such +# statement should be compatible with FTWRL. +# Skip last part of compatibility testing as this statement +# releases metadata locks in non-standard place. +Success: Was able to run 'prepare stmt1 from 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j'' under FTWRL. +Success: Was able to run 'prepare stmt1 from 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j'' with FTWRL active in another connection. +# # 9.2) Compatibility of EXECUTE statement depends on statement # to be executed. # @@ -592,7 +614,8 @@ Success: 'drop trigger t1_bi' is blocked Success: FTWRL is blocked when 'drop trigger t1_bi' is active in another connection. drop trigger t1_bi; # -# 13.x) The rest of DROP variants are too special to test here. +# 13.x) The rest of DROP variants (DROP TABLESPACE, DROP LOGFILE +# GROUP and DROP SERVER) are too special to test here. # # # 14) FLUSH variants. === modified file 'mysql-test/t/flush_read_lock.test' --- a/mysql-test/t/flush_read_lock.test 2010-10-22 07:16:34 +0000 +++ b/mysql-test/t/flush_read_lock.test 2010-10-22 08:21:39 +0000 @@ -183,8 +183,9 @@ let $cleanup_stmt1= alter event e1 comme --source include/check_ftwrl_incompatible.inc --echo # ---echo # 1.x) The rest of ALTER statements are too special to ---echo # be tested here. +--echo # 1.x) The rest of ALTER statements (ALTER TABLESPACE, +--echo # ALTER LOGFILE GROUP and ALTER SERVER) are too +--echo # special to be tested here. --echo # @@ -493,7 +494,9 @@ let $cleanup_stmt1= drop user mysqltest_ --source include/check_ftwrl_incompatible.inc --echo # ---echo # 8.x) The rest of CREATE variants are too special to test here. +--echo # 8.x) The rest of CREATE variants (CREATE LOGFILE GROUP, +--echo # CREATE TABLESPACE and CREATE SERVER) are too special +--echo # to test here. --echo # @@ -502,6 +505,9 @@ let $cleanup_stmt1= drop user mysqltest_ --echo # --echo # 9.1) PREPARE statement is compatible with FTWRL as it --echo # doesn't change any data. +--echo # +--echo # 9.1.a) Prepare of simple INSERT statement. +--echo # let $statement= prepare stmt1 from 'insert into t1_base values (1)'; let $cleanup_stmt= deallocate prepare stmt1; --echo # Skip last part of compatibility testing as this statement @@ -511,6 +517,30 @@ let $skip_3rd_check= 1; let $skip_3rd_check= ; --echo # +--echo # 9.1.b) Prepare of multi-UPDATE. At some point such statements +--echo # tried to acquire thr_lock.c locks during prepare phase. +--echo # This no longer happens and thus it is compatible with +--echo # FTWRL. +let $statement= prepare stmt1 from 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j'; +let $cleanup_stmt= deallocate prepare stmt1; +--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 # 9.1.c) Prepare of multi-DELETE. Again PREPARE of such +--echo # statement should be compatible with FTWRL. +let $statement= prepare stmt1 from 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j'; +let $cleanup_stmt= deallocate prepare stmt1; +--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 # 9.2) Compatibility of EXECUTE statement depends on statement --echo # to be executed. --echo # @@ -755,7 +785,8 @@ let $cleanup_stmt1= create trigger t1_bi drop trigger t1_bi; --echo # ---echo # 13.x) The rest of DROP variants are too special to test here. +--echo # 13.x) The rest of DROP variants (DROP TABLESPACE, DROP LOGFILE +--echo # GROUP and DROP SERVER) are too special to test here. --echo # === modified file 'sql/sql_update.cc' --- a/sql/sql_update.cc 2010-10-07 10:01:51 +0000 +++ b/sql/sql_update.cc 2010-10-22 08:21:39 +0000 @@ -1026,9 +1026,17 @@ int mysql_multi_update_prepare(THD *thd) /* following need for prepared statements, to run next time multi-update */ thd->lex->sql_command= SQLCOM_UPDATE_MULTI; - /* open tables and create derived ones, but do not lock and fill them */ + /* + Open tables and create derived ones, but do not lock and fill them yet. + + During prepare phase acquire only S metadata locks instead of SW locks to + keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE + and global read lock. + */ if ((original_multiupdate && - open_tables(thd, &table_list, &table_count, 0)) || + open_tables(thd, &table_list, &table_count, + (thd->stmt_arena->is_stmt_prepare() ? + MYSQL_OPEN_FORCE_SHARED_MDL : 0))) || mysql_handle_derived(lex, &mysql_derived_prepare)) DBUG_RETURN(TRUE); /* No bundle (reason: useless for push emails).