From: Dmitry Lenev Date: October 22 2010 1:26pm Subject: bzr commit into mysql-5.5-runtime branch (Dmitry.Lenev:3177) List-Archive: http://lists.mysql.com/commits/121698 Message-Id: <20101022132704.22657E5AA8@mockturtle> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0238488821==" --===============0238488821== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/dlenev/src/bzr/mysql-5.5-rt-grl/ based on revid:dmitry.lenev@stripped 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 === 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; --===============0238488821== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/dmitry.lenev@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: dmitry.lenev@stripped # target_branch: file:///home/dlenev/src/bzr/mysql-5.5-rt-grl/ # testament_sha1: afb408222cbd21d8e002fbc7de3510e2519df49c # timestamp: 2010-10-22 17:27:02 +0400 # base_revision_id: dmitry.lenev@stripped\ # 7uqbkwfu9x67hzxc # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWcWDyyUABlBfgFARXff//3/v /qC////0YA3r62n26pXPt3rIUPQt21Hrzxg9aruxQ4na7nSq66NAISJTRophTxMjJtU9NTamgM1N NBoaaAbU0ZGgEkgIaBMJoin6apjKP1QGmTTBB6Q00AAOAYRhNMQwCAZADCNMmTCMBDQan+qqQAAB oGgAAGmmgAAAABFJCZCbU9KegHqnlP1TGjU9Tammg00MjTQaANARREARqYg01MEwSnjKmymmj0TR o8oyMT1HqJAFsmsBn3cm+XS+Tm5Q9nt6qx25z/SssuOGmxvr/PyFHks2qYCeP75vh7HvcOc5zqqU KPJ0ajAT0uAqHh3d3XSz89MAaQMEgEgQPFNP1pSIxP0ycz1Q6W0v38JV7VuwSuBlX7fdKH0+ABLe ATUa+YgONm8Ssy5eJjZsY2QxshzBQjsDbw2Ejwq1ZUwiCIIgMPzQtrfuHfNNu6wpWOe+agUlLaAW UVDJUwaqIplEMZ+7+2WAzAMRnzrtMvYH9BoMwZFBNJhOszdPOFlfNHcbYzaLLD4Axh+TpKAruO0k jmLAghNRAalhmrIrDXHfyfzILeqLjAfuKniFpIcwUChvBwwQkMDWazAwCshgOo4CUcYCtqeqVSJx JS0QRrJ1qu0at/qGLjH53jceyScLhfeTKNTUyOpggIIXuQyBeoDUMpB+REePK1TDA4KBmwSS0YcV jE342XNtK8lMry0L0bSgcDMoQc5oDgWk7wxLhgg1J5EkHO0SSSUk2dAEElQ20azLNoUZEgqDLcLg W2jGZgGY8nYk8IDjysvsGcMBUKslJlaBBTQNdk1FuKQSY0PxtN65bibY2LCDbVJMgVCY6lQFgMJY 0wkmIIPT43KDLYclr18XMr3IaeBKWohC5YaQ0hsY2AAAAAApJOWJqS+kgggjdMRJM322InMwKXix 9luh8w2jJ9ZxkD6jQwQdSMWGBiSDFlcQGMRCLAocBFZ3FwBwE6T7EuRQJiaM9pacdHPv1LRpMBnv GLxcJ3UZhmZJwODo57D1uk8cQKEU+MoyG2l1m6l2NToOASSEl4JhC2AHGPJwarnZL3OGdomPpmYC kRAUijHu+DxdN4JJaCeJCphAd27dQt8RxK5FiCVGnUpQRBPe7YfyMUGD3Kq+YzUmRO8EweYmcplo heVgQ4l7eZl8l+LTKBGTo1g6tWPfLecWlBjdqIcyH7I0MQxl0w2LRqHY6TWF2uMXnhy4G7ebzmFE wlphtA4WaK5auP8LxFuIzIJBJDerxJtV3126zPmOUy3CXOFEnK4TkoKJVllobvByqvXadZA4RitW ePaG4eMFsb1lUxJIbA1zfq7S6ejIiYjKN2kDf463mphUb2o2ZrgzaZSQ4k2exA1YGbkXunPDRjob XYVFwXE3A8sl4e0zhz3G1phwc6RQfS7o4r9UTYW/Qz5GM+MC5xYYJMf7mMZIofkEb2Ouui9VtERR iwFzMaJCMc2UuTztW6ty5bgZIbO+u04nDcjLtKGo260z0tKvgqgRphMjcxfSwnejeRIFIGaqR6wX Yik4NqW1sxQNoygWxFexUZzUpE8TOmM90chjxFld1knQEWIugW9dRDbAhqZ42meRnAvpmYF5FdZ2 I7QLWQs63ozwq0olSLnVCJwhzcgrFVW+HIMGPujcDIabNY6IkNvmH4UziIYIGC2IPB5SSEiRZAZQ 7fk3+W7qQP2vQFB7Op9pMYTGwxEdq00fpKqSgIH5C6D9MoArWHl6ufAzsEXwwP6ukgpguCMN2Qdc D8fpICYCfOdlMQ4wxgut18t3x+M/QOTRMaonMGIYwcA2hcqhhdC9mO0Kf4On40xUNIkK0p8yvTB2 Xddx0JrdmJPSadF6bS2m4riw6gzKCXYciDzwLtNVtxCZZ/QIfyOjUQiUkbINpIbEQoFAhK9KIRKN uR07Dq0w3TtoW7tlLrLcPb3gAACTXpKk46LQIklSnZSmzqzaYqEL7Rog/Aa/xmg4LFskQhihMwwQ YoA88UDdwpTR5xl6yg89Jb6D67p0P+gd5eIRlzQnABl3oB4BG2iUkwQAiQYTHONp3/cYTt53AQwQ wdAM7VUYaHaQhXEtpjmDGheQJGpD4PcSCAmDGmEgACDeBy4iMyGGtCg10SidUlbunP4B636nbsnY dl8+WThPnzJthAkCwCHqyKCE7zMpdU2iWpYgvpAIGFjCAG4D8DI+/vqnav1q6/VzBJocx9/We3nL SNpmPYYtprROB4L+IUjlyRO3JOe0OOmpD5LrEDAOeSYIGyVkSSiB9F7712IHPOMQkEED1EESgXHA JgMgGwqLMMmgNFIYGDMoNBPPTpMFpYipFZFZSliAV0C8NhpORfMpfAQtkITwPAJ1ctha8g7z4hDN w7BNTKyzbdpa4DfDMTkSAzjM5nULatA0iUDcX7jzJfY2Fo4hAQkipcUDJCAKKkCShkA51hw2nDdW 1TUMEt6oim+ulsbUMt0PFOjkMXgflsXb+7SlqwheesQLVKmQGYue8IGd0elswmDG9fDsbrGp46uG VLvJ82nFur3OKjQrVoXtvcGkQxCDnBoVcg0GiS5pgD4EG+F5phiLmRJHNcBb1YnspbU6bkPZxXod cghr19sRaDwoZQWuskO2yRe6/0FvUKa9gZwTVvF92UlWHigkB7MRC4eR8YxXkC7XpAQQ9xmaDexT L/IcdOlSNZRQWoV4OMB27FjWNV9YLX5k36zhan1QmfvX9iVqWIHbsJHqQvIgZs7SUJ7+OQ8OuLen 0qnUncQkhHQJ4ATAGBK1TZ/cohQ3BCJAEGJQQJEbgbjXTrT0A/ohQ6I5IVOo8EpywcfBYtze9MxL 3ehYFhqhAp5VkrfDvjuIVU62hC0PM34YhgVMnBbVt2KwGwLEjQbxWDgRNg3R5tME6cYmZR0qGEXr xNVp5ZF24cZviWwuMhbDiJAVyNVw+Vhp6kk1hcmhMkaBvMi8a+FgXOi09B5A2G7EHe6yGEUNaBCy MStheYV9XiASA8xL3iaiaZ5EC0Ynd3V3KFYqAegjh2FdyS0NYpmBuHiulGyhZjoRIYnQWd0805U3 hTdQz79xrqAn6nmNgDllsX43iRgwhD0kXhRw9AYasSVWopMraG21W00BYWBDJJm8yhgHmQIfKDka rdCheSyN9AChRDsFi2wEkkhGKrRDG0XjLBbzpISCC5XhsxBDAEIQjAMMQrOsnQJ4Tk2uIQLA2V1w CwWm/s1EIlwDPB4gJ4g/xKelAkgRqNhnxIh43GNwutorfGlJkoes/8gWD4oHTLppZ5mCcxKmxoyC 3LXI9J0YDbCoWFClIBetYF2d0MJEqPBLTOSgJp5l9LjtwqJZreAmGytcutdkMbJYIFgqkrIjRDlJ kDNNI90IGa0JuDZeMYIQ0CyErgZeTs9l4XqT3FD4kq4oHrPpkTbkhxXmJQ8FeIJgWzIFTgJojjuC sIxKBFlIIjSEZQvIIIkkB5CGxQMe6SFM4SRAMoCCBUmwkEgkLad8IlOj9aajZdeqBr3nPvBClT+R sHjtDmcgdoy+7Ym/caYL8H7YnnmSSojjHERwQgpGwCjAS6w/msCXQaLxDJeWmwok19PNQX0lLg9J B5AIEbCTy8FsHOuR7mqHmrhEkDLGqlOkPtF+FFatWA0htAa+oYqhuQG+AHJiopdmJbYwhdMmwLZR MwAL0bbTUr6K+LQkYxUZXarAFoDD68awqaZnKaxawAWaXoLasKkgw1oMUfqJqUqZCEjJAYQlPuM+ VbBNLxvOexJfeidkhtX7IB89h01KHKVBwu1IyZiuxa8fMhtSL1tPPWMnt4xCQeQX5Gbfcb7h8qlg neYO2AsSFA+hB5FxYuN3uTJNWbmQhqvE3hypU0MpKIB5OJjFBvMV7CiXyiO5dUtTIK79TPgi//xd yRThQkMWDyyU --===============0238488821==--