From: Dmitry Lenev Date: October 25 2010 6:30pm Subject: bzr commit into mysql-5.5-runtime branch (Dmitry.Lenev:3180) List-Archive: http://lists.mysql.com/commits/121864 Message-Id: <20101025183049.7B03C2F0E23@mockturtle> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1258767207==" --===============1258767207== 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 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 === 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 # --===============1258767207== 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: 45f99d239d19e9e28f076b8b1dc4c0fca9d68b4e # timestamp: 2010-10-25 22:30:49 +0400 # base_revision_id: dmitry.lenev@stripped\ # u0ucz9ygjq5om3hk # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZscx1MABZRfgCWQXPP//3/v 3qC////wYAz8+oACQdASswCgACgAAAAAAcZMmjEMTTAQMCaYIwTE000AGEGkIbUynhqp6noRptRo AAAAAAABxkyaMQxNMBAwJpgjBMTTTQAYQSnqU0yI1J6mTIxM1NAGgPUDTQ0aAHqAHGTJoxDE0wED AmmCMExNNNABhAqSQJoAIAEZBqaap+iYE0ynqfqMiMHlJ06hGiN7d03ZTPBKfTqHsM3DJxGstiLg nNOnLoNWoaGLi5IJPqWv59euujAmyZTlni9QzJ+NqaTP2YG1oy27dcx86M57Pb5dfhsrNRKiZLRo Xb7W4LWUJfarmuk3NzesGBiz8mM2/87UrCbMwE2mQZ25KJlsqt1wml0oo06+1OmJHRp7eeSookuT 8Wzyb3uVJVVWOmH9ozwyvj0XTrvttVamCZOAywwxtaZY9lysLtH+vS4u98Q7G1Of/2GTof3WXHb6 Kw/B8+rVrfmpsTS62KlzY1G48tq98nmyYqUpE1qcWD91x7GtycWt4869peKlfvobuWtS7Qze4XRO tiyuak8Va2KzSYNMpNDg51zFMkTdF0uXXLkaE1ZrmHMuYMrNWU2LrcNq5M8NTUbm9ZqXrzTcyUua sWdzWtMCn+ngxTe2tFVX/ztd7neTU1zg7e5w/xSOXYyXqNFcBTPBvdDCavClCi1sJpe5reTEsp1s Gtr2NLlNuCnFkxdSxR2tENI1YteDcYHUdjDtbVImdrZrb/u3MmRgnBkuclmVJtqq1MoYrnH6/bvZ 97J7fd372i37dhxatOXFMCqKo8MEwKqqqqqqIiIiIgMQJDCKiqqqhsEJCkXmExGxcxnmIZksqnaz qq1M9LRIUlOTQsOSvf+L9k1MTWn8WpIn2n2sHyeUT1axxT+USMXCunqbnrydjLRP0qemp2Tyvde/ 2eby0vnbxj4d1l2GFr1Z1hEuqcT6WGi5foWsrLoX8+3pxTl7UmwaLaF8Tc6k8WGGFmhS9Oej8FR6 mq6l1/4em0F6RhUTzHCbC2lsxoGGAZisCsnkSwcHU3DIWeDIpX0DCW0MFyjBaczuSj1pgbh4aGyI +yolmfqdX8NrB0mKpTDG/Io3qwmd7p6+fa6r2XVsbR1mX85wynlwt29/4aZHs5pi7VUqlNje1NzZ wHyd7jhlTDv1XzXjlBYxvcFx0qb+fcrowkaXRoaC8Y20uVo2Uwrf2NA5mc7HKt/yuFzAdjVvz1sM L+Dkv0Y3cbnC/ySbZZuad0pzPOfZ8mrQNjwVpuM2/f1XnJUyrc2lmR6Kw8Oh21s1PmMZGy/Wx+7s 7HEhiREa2xHgn46MsRvUITdsWFuOOkxTO0ARFoO0VYLtNirqbFRVgcvDICidVeExfuQRcsQYINW9 Zhiw8UYhHfrabtZZ+Ys5PyV07K5bYcMteRhOde4r6vGcaAgaj1VSdXN0MgU2DhFZmMwfxPG+DmhX pOZlIClU22pCsg2aRjBXB3ekORhqceOmplbtu44KsUs0s2W7K144lTClCo2o0wKiSXhj5Y4zhLgx NVJZVlNNrLVPBoZOlcqaB96S5u/Z7/FJg/+P+H+z4/NxDVyUqlf4aL3zQoufF89Nzf7/85MjV+ss f1iTZh3fiWE+JXMxY/gwLiyym8OdvN/+2yLlIp/WyK+xsfJzraq1B0Bbo+Zqvc7kG0OZrODS6tCW OLoYQa6kvTFUJR/xY2OhZOmlKRMCOlUKSWUxDWJY+Re4MwsmbgJY2qasX9GB1P4vpexJ7Hg/N+Xu PpxwXKimKK/JNFYLjXzkE8oFwiKqTGyJ3zEwIyPUeYwMuWp68xnVBubOKT1MI+OnEmqkaKMslYVH ifoU+jL+k/KStb7Uo6mH3tN8OMUlgybUdjtwcpNSkybV7NTS6nZeV2TRtbFJfdJP6rrnSh16TVId KTU6D+Hx0Mv1/Rdp3v7SZPR4L/i3Rwdp2FSKguR3vu6Ool/9n7Cz0z3SPVZn09P06+LgNmLJf+rN Gsc2U3l7ymlxX6jpc9zLn1R4P15mSJsCYpnJqqFxULqktKRck+emfySaj90oxiayz7XRZMkpfT8p M0lx2SXolJQ1LNYS0VSnCebNfReuUWq6RZLLpTa6VhZLmXG9w8rNBnifB2+TCaI7GSNitznZb5+1 8TmJScNqa4zv2PdLLzvwnTHa9EpbGY8d+l3PefQ54s9Dg+vFL75kxcKZMVO1SPeqSedKnw9vtbLv c8bvS+tpc7y983W3tzOOS5I+H8ON2NT9UpcTaye+JHRM5NDY2kjb8HMmdyym9Y+DW9305OjGtffx 4ofNu1XbPdV3ys8GCczjQhkkF9xxFYpyHJaCHtmg0iiOEJIZ4zQb6y1ejVMr07Xw2yaLJ04+lhFS 7dB7fa29rfTalqXTFP30yJpxvThZ59kk5mcYrTTGOs4tp9fnLvg7JCyMV+1WUwU/o+trn1Wk+q0q vhxmA+qyw+ufZf3sBHdZGDpqL4yp2y0nfR8OatHWJ4TwhXesLUn2LRoYFN38y+XKfSwRSlkSnZLR I0Nl/TD8Fu2sZzS0krxTm49MVhtO+PrXHoTnLMEbaky+qcF0O1PWx+a9obUpv8yT/DJO9kOj1d2G cI20KpUptPsR6GY1qT+Kk7o7HuXO5LIt6ilpJYo83ihLngXkfBoN0b1u5+PdPLbJ2qUqSVUH3uS5 cUottfV645bm18NC3MaEbdhxYRw5Xm70tizqUmp6kmxJVi2S/6vXExg8EwS8bGg34U09MmvmVumO 4XS9Kk1ore2M5Diu4TWnsknq7bi7WbsIjTRTmizFdJ48eGC3RJJ+LmiWjwYEM80tJM5ospLn0bsP QwyqscoHMvksi4ss6lWqIdC095KZVCkxlKq0WZRiXTkSLXyS4kWwi5nxTqaBiEyaTNRmnvKC9NKl MllksWS0q5ZKtPW1vZ1ad3C1reUSL1lyokpts6VJ1lJkzdsnJSIviXrutyS3g8bDvieMraVETlAy KQtJRSSpR5a/EuJGzPtVyuqP+JLRtfpb0n6YxhfK5I6fX1tji5S2Jwf3aXfDnanrzXPTcmr1UoqS yHd2stbPCHUpx89Xmw13bB/mb969zbZ6edueulQpisyut4VNSlyTxtaPbrCzRPNgzX2NDqJpSo4T Wtgyk2cZx+jGYsWepLHo+8ab43J0uFegZ20ppb7aO5sZ3YqskqmrFPNWDkLUaFLNmSxcYLUsdFlm K89CVCnVkpZdKVFF3d24b+DLS3uE6NSbjj0Ji+6yS30qwh+akzzmtgcos/7fTzHRycWhGmbluiXp qVyUl8aqYXNeyLL4q1lyPRqYJojk0rkllhzPBjgi5RxYPcKTB3YYsWD+dRodL150TIwXNuq+9W5J 9WSXxO/DfcWUz9hVl80SG6r4Y4MKRKYJTG251ssE2Jmyw4LvVNiyVqEscko1qmm6pJv2nO2tDlJD FUVYpaMr9EgyTfIWwvir5/0twC7OCjVRaNe69cZ5uycFp5u6zwnKe2k7Ynuczblzobm2PEYrHHHH rYR8U915skmx8XUvgdN4pJrHSXqubJqzdd8ed6YP5PWyNLso7EShMlF2bvSY93YL2pSfvk6+KpYo NBp+E6ybH/jL3Og9cx6pd1TVu4uH/4u5IpwoSE2OY6mA --===============1258767207==--