From: Dmitry Lenev Date: October 25 2010 6:31pm Subject: bzr push into mysql-5.5-runtime branch (Dmitry.Lenev:3179 to 3180) List-Archive: http://lists.mysql.com/commits/121857 Message-Id: <20101025183111.020052F0E23@mockturtle> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3180 Dmitry Lenev 2010-10-25 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. Extend test coverage for special kinds of SET statement. modified: mysql-test/r/flush_read_lock.result mysql-test/t/flush_read_lock.test 3179 Dmitry Lenev 2010-10-25 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. Fix sporadical failures of perfschema.global_read_lock test. modified: mysql-test/suite/perfschema/r/func_file_io.result mysql-test/suite/perfschema/r/func_mutex.result mysql-test/suite/perfschema/r/global_read_lock.result mysql-test/suite/perfschema/t/func_file_io.test mysql-test/suite/perfschema/t/func_mutex.test mysql-test/suite/perfschema/t/global_read_lock.test sql/sql_parse.cc === modified file 'mysql-test/r/flush_read_lock.result' --- a/mysql-test/r/flush_read_lock.result 2010-10-22 13:26:29 +0000 +++ b/mysql-test/r/flush_read_lock.result 2010-10-25 18:30:28 +0000 @@ -65,6 +65,7 @@ create table t2_base(j int) engine=myisa create table t3_trans(i int) engine=innodb; create temporary table t1_temp(i int) engine=myisam; create temporary table t2_temp(j int) engine=myisam; +create temporary table t3_temp_trans(i int) engine=innodb; create database mysqltest1; create database `#mysql50#mysqltest-2`; create procedure p1() begin end; @@ -1116,8 +1117,9 @@ Success: 'set @a:= f2_base()' is blocked Success: Was able to run 'set @a:= f2_temp()' under FTWRL. Success: Was able to run 'set @a:= f2_temp()' with FTWRL active in another connection. # -# 31.d) Special SET variants that cause implicit commit or change -# data are incompatible with GRL. +# 31.d) Special SET variants have different compatibility with FTWRL. +# +# 31.d.I) SET PASSWORD is incompatible with FTWRL as it changes data. create user mysqltest_u1; # Skip last part of compatibility testing as our helper debug # sync-point doesn't work for SET statements. @@ -1125,6 +1127,75 @@ Success: Was not able to run 'set passwo Success: 'set password for 'mysqltest_u1' = password('')' is blocked by FTWRL active in another connection. drop user mysqltest_u1; # +# 31.d.II) SET READ_ONLY is compatible with FTWRL (but has no +# effect when executed under it). +# Skip last part of compatibility testing as our helper debug +# sync-point doesn't work for SET statements. +Success: Was able to run 'set global read_only= 1' under FTWRL. +Success: Was able to run 'set global read_only= 1' with FTWRL active in another connection. +# +# 31.d.III) Situation with SET AUTOCOMMIT is complex. +# Turning auto-commit off is always compatible with FTWRL. +# Turning auto-commit on causes implicit commit and so +# is incompatible with FTWRL if there are changes to be +# committed. +flush tables with read lock; +set autocommit= 0; +# Turning auto-commit on causes implicit commit so can +# be incompatible with FTWRL if there is something to +# commit. But since even in this case we allow commits +# under active FTWRL such statement should always succeed. +insert into t3_temp_trans values (1); +set autocommit= 1; +unlock tables; +delete from t3_temp_trans; +# Check that SET AUTOCOMMIT=0 is not blocked and +# SET AUTOCOMMIT=1 is blocked by active FTWRL in +# another connection. +# +# Switching to connection 'con1'. +flush tables with read lock; +# Switching to connection 'default'. +set autocommit= 0; +# Switching to connection 'con1'. +unlock tables; +# Switching to connection 'default'. +# Do some work so implicit commit in SET AUTOCOMMIT=1 +# is not a no-op. +insert into t3_trans values (1); +# Switching to connection 'con1'. +flush tables with read lock; +# Switching to connection 'default'. +# Send: +set autocommit= 1; +# Switching to connection 'con1'. +# Wait until SET AUTOCOMMIT=1 is blocked. +unlock tables; +# Switching to connection 'default'. +# Reap SET AUTOCOMMIT=1. +delete from t3_trans; +# +# Check that SET AUTOCOMMIT=1 blocks FTWRL in another connection. +set autocommit= 0; +insert into t3_trans values (1); +set debug_sync='RESET'; +set debug_sync='ha_commit_trans_after_acquire_commit_lock SIGNAL parked WAIT_FOR go'; +set autocommit= 1; +# Switching to connection 'con1'. +set debug_sync='now WAIT_FOR parked'; +flush tables with read lock; +# Switching to connection 'con2'. +# Wait until FTWRL is blocked. +set debug_sync='now SIGNAL go'; +# Switching to connection 'default'. +# Reap SET AUTOCOMMIT=1. +# Switching to connection 'con1'. +# Reap FTWRL. +unlock tables; +# Switching to connection 'default'. +delete from t3_trans; +set debug_sync= "RESET"; +# # 32) SHOW statements are compatible with FTWRL. # Let us test _some_ of them. # === modified file 'mysql-test/t/flush_read_lock.test' --- a/mysql-test/t/flush_read_lock.test 2010-10-22 13:26:29 +0000 +++ b/mysql-test/t/flush_read_lock.test 2010-10-25 18:30:28 +0000 @@ -80,6 +80,7 @@ create table t2_base(j int) engine=myisa create table t3_trans(i int) engine=innodb; create temporary table t1_temp(i int) engine=myisam; create temporary table t2_temp(j int) engine=myisam; +create temporary table t3_temp_trans(i int) engine=innodb; create database mysqltest1; create database `#mysql50#mysqltest-2`; create procedure p1() begin end; @@ -1447,8 +1448,9 @@ let $skip_3rd_check= 1; let $skip_3rd_check= ; --echo # ---echo # 31.d) Special SET variants that cause implicit commit or change ---echo # data are incompatible with GRL. +--echo # 31.d) Special SET variants have different compatibility with FTWRL. +--echo # +--echo # 31.d.I) SET PASSWORD is incompatible with FTWRL as it changes data. create user mysqltest_u1; let $statement= set password for 'mysqltest_u1' = password(''); let $cleanup_stmt1= ; @@ -1458,6 +1460,104 @@ let $skip_3rd_check= 1; --source include/check_ftwrl_incompatible.inc let $skip_3rd_check= ; drop user mysqltest_u1; +--echo # +--echo # 31.d.II) SET READ_ONLY is compatible with FTWRL (but has no +--echo # effect when executed under it). +let $statement= set global read_only= 1; +let $cleanup_stmt= set global read_only= 0; +--echo # Skip last part of compatibility testing as our helper debug +--echo # sync-point doesn't work for SET statements. +let $skip_3rd_check= 1; +--source include/check_ftwrl_compatible.inc +let $skip_3rd_check= ; +--echo # +--echo # 31.d.III) Situation with SET AUTOCOMMIT is complex. +--echo # Turning auto-commit off is always compatible with FTWRL. +--echo # Turning auto-commit on causes implicit commit and so +--echo # is incompatible with FTWRL if there are changes to be +--echo # committed. +flush tables with read lock; +set autocommit= 0; +--echo # Turning auto-commit on causes implicit commit so can +--echo # be incompatible with FTWRL if there is something to +--echo # commit. But since even in this case we allow commits +--echo # under active FTWRL such statement should always succeed. +insert into t3_temp_trans values (1); +set autocommit= 1; +unlock tables; +delete from t3_temp_trans; +--echo # Check that SET AUTOCOMMIT=0 is not blocked and +--echo # SET AUTOCOMMIT=1 is blocked by active FTWRL in +--echo # another connection. +--echo # +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +flush tables with read lock; +--echo # Switching to connection 'default'. +connection default; +set autocommit= 0; +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +unlock tables; +--echo # Switching to connection 'default'. +connection default; +--echo # Do some work so implicit commit in SET AUTOCOMMIT=1 +--echo # is not a no-op. +insert into t3_trans values (1); +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +flush tables with read lock; +--echo # Switching to connection 'default'. +connection default; +--echo # Send: +--send set autocommit= 1 +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +--echo # Wait until SET AUTOCOMMIT=1 is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for global read lock" and + info = "set autocommit= 1"; +--source include/wait_condition.inc +unlock tables; +--echo # Switching to connection 'default'. +connection default; +--echo # Reap SET AUTOCOMMIT=1. +--reap +delete from t3_trans; +--echo # +--echo # Check that SET AUTOCOMMIT=1 blocks FTWRL in another connection. +set autocommit= 0; +insert into t3_trans values (1); +set debug_sync='RESET'; +set debug_sync='ha_commit_trans_after_acquire_commit_lock SIGNAL parked WAIT_FOR go'; +--send set autocommit= 1 +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +set debug_sync='now WAIT_FOR parked'; +--send flush tables with read lock +--echo # Switching to connection '$con_aux2'. +connection $con_aux2; +--echo # Wait until FTWRL is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for global read lock" and + info = "flush tables with read lock"; +--source include/wait_condition.inc +set debug_sync='now SIGNAL go'; +--echo # Switching to connection 'default'. +connection default; +--echo # Reap SET AUTOCOMMIT=1. +--reap +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +--echo # Reap FTWRL. +--reap +unlock tables; +--echo # Switching to connection 'default'. +connection default; +delete from t3_trans; +set debug_sync= "RESET"; --echo # No bundle (reason: useless for push emails).