From: Dmitry Lenev Date: October 22 2010 1:27pm Subject: bzr push into mysql-5.5-runtime branch (Dmitry.Lenev:3176 to 3177) List-Archive: http://lists.mysql.com/commits/121697 Message-Id: <20101022132731.8A0AAE5AA8@mockturtle> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3177 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_base.cc 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 === modified file 'mysql-test/r/flush_read_lock.result' --- a/mysql-test/r/flush_read_lock.result 2010-10-22 08:21:39 +0000 +++ b/mysql-test/r/flush_read_lock.result 2010-10-22 13:26:29 +0000 @@ -48,6 +48,7 @@ # check that DDL statements on temporary tables # are compatible with FTRWL. drop tables if exists t1_base, t2_base, t3_trans; +drop tables if exists tm_base, tm_base_temp; drop database if exists mysqltest1; # We're going to test ALTER DATABASE UPGRADE drop database if exists `#mysql50#mysqltest-2`; @@ -547,7 +548,7 @@ Success: Was not able to run 'drop table Success: 'drop table t2_temp' is blocked by FTWRL active in another connection. Success: FTWRL is blocked when 'drop table t2_temp' is active in another connection. # -# 13.1.c) DROP TEMPORARY TABLES should becompatible with FTWRL. +# 13.1.c) DROP TEMPORARY TABLES should be compatible with FTWRL. Success: Was able to run 'drop temporary table t2_temp' under FTWRL. Success: Was able to run 'drop temporary table t2_temp' with FTWRL active in another connection. Success: Was able to run FTWRL while 'drop temporary table t2_temp' was active in another connection. @@ -1379,6 +1380,70 @@ unlock tables; # Switching to connection 'default'. unlock tables; # +# Check how FLUSH TABLE WITH READ LOCK is handled for MERGE tables. +# As usual there are tricky cases related to this type of tables. +# +# +# 1) Most typical case - base MERGE table with base underlying tables. +# +# 1.a) DML statements which change data should be incompatible with FTWRL. +create table tm_base (i int) engine=merge union=(t1_base) insert_method=last; +Success: Was not able to run 'insert into tm_base values (1)' under FTWRL. +Success: 'insert into tm_base values (1)' is blocked by FTWRL active in another connection. +Success: FTWRL is blocked when 'insert into tm_base values (1)' is active in another connection. +# +# 1.b) DDL statement on such table should be incompatible with FTWRL as well. +Success: Was not able to run 'alter table tm_base insert_method=first' under FTWRL. +Success: 'alter table tm_base insert_method=first' is blocked by FTWRL active in another connection. +Success: FTWRL is blocked when 'alter table tm_base insert_method=first' is active in another connection. +drop table tm_base; +# +# 2) Temporary MERGE table with base underlying tables. +# +# 2.a) DML statements which change data should be incompatible with FTWRL +# as they affect base tables. +create temporary table tm_temp_base (i int) engine=merge union=(t1_base) insert_method=last; +Success: Was not able to run 'insert into tm_temp_base values (1)' under FTWRL. +Success: 'insert into tm_temp_base values (1)' is blocked by FTWRL active in another connection. +Success: FTWRL is blocked when 'insert into tm_temp_base values (1)' is active in another connection. +# +# 2.b) Some of DDL statements on such table can be compatible with FTWRL +# as they don't affect base tables. +Success: Was able to run 'drop temporary tables tm_temp_base' under FTWRL. +Success: Was able to run 'drop temporary tables tm_temp_base' with FTWRL active in another connection. +Success: Was able to run FTWRL while 'drop temporary tables tm_temp_base' was active in another connection. +# +# 2.c) ALTER statement is incompatible with FTWRL. Even though it does +# not change data in base table it still acquires strong metadata +# locks on them. +Success: Was not able to run 'alter table tm_temp_base insert_method=first' under FTWRL. +Success: 'alter table tm_temp_base insert_method=first' is blocked by FTWRL active in another connection. +Success: FTWRL is blocked when 'alter table tm_temp_base insert_method=first' is active in another connection. +drop table tm_temp_base; +# +# 3) Temporary MERGE table with temporary underlying tables. +# +# 3.a) DML statements should be compatible with FTWRL as +# no base table is going to be affected. +create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last; +Success: Was able to run 'insert into tm_temp_temp values (1)' under FTWRL. +Success: Was able to run 'insert into tm_temp_temp values (1)' with FTWRL active in another connection. +Success: Was able to run FTWRL while 'insert into tm_temp_temp values (1)' was active in another connection. +# +# 3.b) DDL statements should be compatible with FTWRL as well +# as no base table is going to be affected too. +Success: Was able to run 'alter table tm_temp_temp union=(t1_temp) insert_method=first' under FTWRL. +Success: Was able to run 'alter table tm_temp_temp union=(t1_temp) insert_method=first' with FTWRL active in another connection. +Success: Was able to run FTWRL while 'alter table tm_temp_temp union=(t1_temp) insert_method=first' was active in another connection. +drop table tm_temp_temp; +# +# 4) For the sake of completeness let us check that base MERGE tables +# with temporary underlying tables are not functional. +create table tm_base_temp (i int) engine=merge union=(t1_temp) insert_method=last; +select * from tm_base_temp; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +drop table tm_base_temp; +# # Clean-up. # drop event e1; === modified file 'mysql-test/t/flush_read_lock.test' --- a/mysql-test/t/flush_read_lock.test 2010-10-22 08:21:39 +0000 +++ b/mysql-test/t/flush_read_lock.test 2010-10-22 13:26:29 +0000 @@ -62,6 +62,7 @@ --echo # are compatible with FTRWL. --disable_warnings drop tables if exists t1_base, t2_base, t3_trans; +drop tables if exists tm_base, tm_base_temp; drop database if exists mysqltest1; --echo # We're going to test ALTER DATABASE UPGRADE drop database if exists `#mysql50#mysqltest-2`; @@ -708,7 +709,7 @@ let $cleanup_stmt1= create temporary tab --source include/check_ftwrl_incompatible.inc --echo # ---echo # 13.1.c) DROP TEMPORARY TABLES should becompatible with FTWRL. +--echo # 13.1.c) DROP TEMPORARY TABLES should be compatible with FTWRL. let $statement= drop temporary table t2_temp; let $cleanup_stmt= create temporary table t2_temp(j int); --source include/check_ftwrl_compatible.inc @@ -1792,6 +1793,75 @@ unlock tables; --echo # +--echo # Check how FLUSH TABLE WITH READ LOCK is handled for MERGE tables. +--echo # As usual there are tricky cases related to this type of tables. +--echo # +--echo # +--echo # 1) Most typical case - base MERGE table with base underlying tables. +--echo # +--echo # 1.a) DML statements which change data should be incompatible with FTWRL. +create table tm_base (i int) engine=merge union=(t1_base) insert_method=last; +let $statement= insert into tm_base values (1); +let $cleanup_stmt1= delete from tm_base; +--source include/check_ftwrl_incompatible.inc +--echo # +--echo # 1.b) DDL statement on such table should be incompatible with FTWRL as well. +let $statement= alter table tm_base insert_method=first; +let $cleanup_stmt1= alter table tm_base insert_method=last; +--source include/check_ftwrl_incompatible.inc +drop table tm_base; + +--echo # +--echo # 2) Temporary MERGE table with base underlying tables. +--echo # +--echo # 2.a) DML statements which change data should be incompatible with FTWRL +--echo # as they affect base tables. +create temporary table tm_temp_base (i int) engine=merge union=(t1_base) insert_method=last; +let $statement= insert into tm_temp_base values (1); +let $cleanup_stmt1= delete from tm_temp_base; +--source include/check_ftwrl_incompatible.inc +--echo # +--echo # 2.b) Some of DDL statements on such table can be compatible with FTWRL +--echo # as they don't affect base tables. +let $statement= drop temporary tables tm_temp_base; +let $cleanup_stmt= create temporary table tm_temp_base (i int) engine=merge union=(t1_base) insert_method=last; +--source include/check_ftwrl_compatible.inc +--echo # +--echo # 2.c) ALTER statement is incompatible with FTWRL. Even though it does +--echo # not change data in base table it still acquires strong metadata +--echo # locks on them. +let $statement= alter table tm_temp_base insert_method=first; +let $cleanup_stmt1= alter table tm_temp_base insert_method=last; +--source include/check_ftwrl_incompatible.inc +drop table tm_temp_base; + +--echo # +--echo # 3) Temporary MERGE table with temporary underlying tables. +--echo # +--echo # 3.a) DML statements should be compatible with FTWRL as +--echo # no base table is going to be affected. +create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last; +let $statement= insert into tm_temp_temp values (1); +let $cleanup_stmt= delete from tm_temp_temp; +--source include/check_ftwrl_compatible.inc +--echo # +--echo # 3.b) DDL statements should be compatible with FTWRL as well +--echo # as no base table is going to be affected too. +let $statement= alter table tm_temp_temp union=(t1_temp) insert_method=first; +let $cleanup_stmt= alter table tm_temp_temp union=(t1_temp) insert_method=last; +--source include/check_ftwrl_compatible.inc +drop table tm_temp_temp; + +--echo # +--echo # 4) For the sake of completeness let us check that base MERGE tables +--echo # with temporary underlying tables are not functional. +create table tm_base_temp (i int) engine=merge union=(t1_temp) insert_method=last; +--error ER_WRONG_MRG_TABLE +select * from tm_base_temp; +drop table tm_base_temp; + + +--echo # --echo # Clean-up. --echo # drop event e1; === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-10-18 12:33:49 +0000 +++ b/sql/sql_base.cc 2010-10-22 13:26:29 +0000 @@ -2807,10 +2807,11 @@ bool open_table(THD *thd, TABLE_LIST *ta QQ: What about MYSQL_OPEN_HAS_MDL_LOCK flag? */ - if (table_list->lock_type >= TL_WRITE_ALLOW_WRITE && + if (table_list->mdl_request.type >= MDL_SHARED_WRITE && ! (flags & (MYSQL_OPEN_IGNORE_GLOBAL_READ_LOCK | MYSQL_OPEN_FORCE_SHARED_MDL | - MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL)) && + MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL | + MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK)) && ! thd->locked_tables_mode) { MDL_request protection_request; No bundle (reason: useless for push emails).