From: Jon Olav Hauglid Date: August 30 2010 10:58am Subject: bzr commit into mysql-5.5-runtime branch (jon.hauglid:3124) Bug#56292 List-Archive: http://lists.mysql.com/commits/117130 X-Bug: 56292 Message-Id: <201008301058.o7THlRO3004541@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4734155864113199100==" --===============4734155864113199100== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-5.5-runtime-bug56292/ based on revid:alik@stripped 3124 Jon Olav Hauglid 2010-08-30 Bug #56292 Deadlock with ALTER TABLE and MERGE tables ALTER TABLE on a MERGE table could cause a deadlock with two other connections if we reached a situation where: 1) A connection doing ALTER TABLE can't upgrade to MDL_EXCLUSIVE on the parent table, but holds TL_READ_NO_INSERT on the child tables. 2) A connection doing DELETE on a child table can't get TL_WRITE on it since ALTER TABLE holds TL_READ_NO_INSERT. 3) A connection doing SELECT on the parent table can't get TL_READ on the child tables since TL_WRITE is ahead in the lock queue, but holds MDL_SHARED_READ on the parent table preventing ALTER TABLE from upgrading. For regular tables, this deadlock is avoided by having ALTER TABLE take a MDL_SHARED_NO_WRITE metadata lock on the table. This prevents DELETE from acquiring MDL_SHARED_WRITE on the table before ALTER TABLE tries to upgrade to MDL_EXCLUSIVE. In the example above, SELECT would therefore not be blocked by the pending DELETE as DELETE would not be able to enter TL_WRITE in the table lock queue. This patch fixes the problem for merge tables by using the same metadata lock type for child tables as for the parent table. The child tables will in this case therefore be locked with MDL_SHARED_NO_WRITE, preventing DELETE from acquiring a metadata lock and enter into the table lock queue. Change in behavior: By taking the same metadata lock for child tables as for the parent table, LOCK TABLE on the parent table will now also implicitly lock the child tables. Merge.test/.result has been updated to reflect this change. Test case added to mdl_sync.test. modified: mysql-test/r/mdl_sync.result mysql-test/r/merge.result mysql-test/t/mdl_sync.test mysql-test/t/merge.test storage/myisammrg/ha_myisammrg.cc === modified file 'mysql-test/r/mdl_sync.result' --- a/mysql-test/r/mdl_sync.result 2010-08-12 13:50:23 +0000 +++ b/mysql-test/r/mdl_sync.result 2010-08-30 10:58:29 +0000 @@ -2913,3 +2913,38 @@ UNLOCK TABLES; # Connection default UNLOCK TABLES; DROP DATABASE db1; +# +# Bug#56292 Deadlock with ALTER TABLE and MERGE tables +# +DROP TABLE IF EXISTS t1, t2, m1; +CREATE TABLE t1(a INT) engine=MyISAM; +CREATE TABLE t2(a INT) engine=MyISAM; +CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (3), (4); +# Connection con1 +SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue'; +# Sending: +ALTER TABLE m1 engine=MERGE UNION=(t2, t1); +# Connection con2 +# Waiting for ALTER TABLE to try lock upgrade +SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; +# Sending: +DELETE FROM t2 WHERE a = 3; +# Connection default +# Check that DELETE is waiting on a metadata lock and not a table lock. +SELECT * FROM m1; +a +1 +2 +3 +4 +# Resuming ALTER TABLE +SET DEBUG_SYNC= 'now SIGNAL continue'; +# Connection con1 +# Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1) +# Connection con2 +# Reaping: DELETE FROM t2 WHERE a = 3 +# Connection default +DROP TABLE m1, t1, t2; +SET DEBUG_SYNC= 'RESET'; === modified file 'mysql-test/r/merge.result' --- a/mysql-test/r/merge.result 2010-08-18 09:35:41 +0000 +++ b/mysql-test/r/merge.result 2010-08-30 10:58:29 +0000 @@ -3444,13 +3444,12 @@ ALTER TABLE m1 ADD INDEX (c1); UNLOCK TABLES; DROP TABLE m1, t1; # -# If children are to be altered, they need an explicit lock. +# Locking the merge table will implicitly lock children. # CREATE TABLE t1 (c1 INT); CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); LOCK TABLE m1 WRITE; ALTER TABLE t1 ADD INDEX (c1); -ERROR HY000: Table 't1' was locked with a READ lock and can't be updated LOCK TABLE m1 WRITE, t1 WRITE; ALTER TABLE t1 ADD INDEX (c1); UNLOCK TABLES; === modified file 'mysql-test/t/mdl_sync.test' --- a/mysql-test/t/mdl_sync.test 2010-08-12 13:50:23 +0000 +++ b/mysql-test/t/mdl_sync.test 2010-08-30 10:58:29 +0000 @@ -4532,6 +4532,68 @@ disconnect con2; disconnect con3; +--echo # +--echo # Bug#56292 Deadlock with ALTER TABLE and MERGE tables +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2, m1; +--enable_warnings + +CREATE TABLE t1(a INT) engine=MyISAM; +CREATE TABLE t2(a INT) engine=MyISAM; +CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2); + +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (3), (4); + +connect(con1, localhost, root); +connect(con2, localhost, root); + +--echo # Connection con1 +connection con1; +SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue'; +--echo # Sending: +--send ALTER TABLE m1 engine=MERGE UNION=(t2, t1) + +--echo # Connection con2 +connection con2; +--echo # Waiting for ALTER TABLE to try lock upgrade +SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; +--echo # Sending: +--send DELETE FROM t2 WHERE a = 3 + +--echo # Connection default +connection default; +--echo # Check that DELETE is waiting on a metadata lock and not a table lock. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "DELETE FROM t2 WHERE a = 3"; +--source include/wait_condition.inc +# Now that DELETE blocks on a metadata lock, we should be able to do +# SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table +# lock request. +SELECT * FROM m1; +--echo # Resuming ALTER TABLE +SET DEBUG_SYNC= 'now SIGNAL continue'; + +--echo # Connection con1 +connection con1; +--echo # Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1) +--reap +--echo # Connection con2 +connection con2; +--echo # Reaping: DELETE FROM t2 WHERE a = 3 +--reap +--echo # Connection default +connection default; +DROP TABLE m1, t1, t2; +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +disconnect con2; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc === modified file 'mysql-test/t/merge.test' --- a/mysql-test/t/merge.test 2010-08-18 09:35:41 +0000 +++ b/mysql-test/t/merge.test 2010-08-30 10:58:29 +0000 @@ -2519,12 +2519,11 @@ UNLOCK TABLES; DROP TABLE m1, t1; --echo # ---echo # If children are to be altered, they need an explicit lock. +--echo # Locking the merge table will implicitly lock children. --echo # CREATE TABLE t1 (c1 INT); CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); LOCK TABLE m1 WRITE; ---error ER_TABLE_NOT_LOCKED_FOR_WRITE ALTER TABLE t1 ADD INDEX (c1); LOCK TABLE m1 WRITE, t1 WRITE; ALTER TABLE t1 ADD INDEX (c1); === modified file 'storage/myisammrg/ha_myisammrg.cc' --- a/storage/myisammrg/ha_myisammrg.cc 2010-07-27 14:32:42 +0000 +++ b/storage/myisammrg/ha_myisammrg.cc 2010-08-30 10:58:29 +0000 @@ -478,6 +478,8 @@ int ha_myisammrg::add_children_list(void /* Set the expected table version, to not cause spurious re-prepare. */ child_l->set_table_ref_id(mrg_child_def->get_child_table_ref_type(), mrg_child_def->get_child_def_version()); + /* Use the same MDL lock type for children. */ + child_l->mdl_request.set_type(parent_l->mdl_request.type); /* Link TABLE_LIST object into the children list. */ if (this->children_last_l) child_l->prev_global= this->children_last_l; --===============4734155864113199100== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-5.5-runtime-bug56292/ # testament_sha1: d4b55f2f5280357be13c21301cb776e6b42957e2 # timestamp: 2010-08-30 12:58:33 +0200 # source_branch: file:///export/home/x/mysql-5.5-bugfixing/ # base_revision_id: alik@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWcs2bfoABldfgEQQXPf//3// 3+C////wYA5L69697kPed54NAABzospqbKNFAU1oFC20VRpBJJNNRPJoZNDQ0yjJpPTUNAwgYjQA yepoNTU9RiaeqbVPU9R+qPSZHqAGEZDQaaGQBoDQSiE9JpTeqZNDaR6gAAAAAAADQCTUiET1E2gN T1TxA0mym9SHpqPSaBk0aANAOMmTRiGmhgJoYmjTJiBkYTRpphBkwkkBACYgEwI00ieSh6T0jI0G 9U0AaNIomGWeDEmLP+3tOOYqz6LjrFGQcm/HWdug399sltm3jhqpgbxAhTPlJJ9NYPNUhpJGM5Ap TUpEsg8zF1OJhhTInfwiVSqJMmnb7tdlKQyhYOKG+RoJD3SegybSyEjBQHCMARhzFcYoadG0YauZ cPeWxM2C9KV9G3WIolZyJphzmmAa9FgPvNoicfsmrrh4oa4MURFVQw/UC9em1U4DqIGk7IKYDZ7i lGVw/Kc0GeRVG0Y4XwV18ezx80Ga5b9ZRWPkP6OXpLbKJEDCxlyxl14V0lXpXEj+sXBXgstwFGMh TgdQrtyxktUHybyUgHuHuMIvHDv4vBxr1m6h9ZIWnUbCRaORhcTR+tdFuuTFmQMAty1JMCK8aN90 YLJRrxFKIEGfBa8YYKh0hIqCqdSP8SsjKURJqmcXgUHziJZilt5k+shnisU6xw+PsV1NB39Mn1MY aTFWjFC1v0sQIKYVMDTGygVtJNJndUWp93zkZGhxzIOMa6EVIm65nNFNbJ8hSIhK2j+oxEPkMPME QHiaenQ95QWReMDQURzEGMbBHhcahmMhlMlhTEjoYMknMIQKwGsFSCUhAOk2BZ1nuHS+Y3qRTya/ hr4r8S93wnD6kOzxaCjc3oZc/zfC/YQ0V1FiQe1RVEREYxURGK+AeTkYNAa5VAbpxicBZpCQTf2H RnIR44lCmsoekKk5et2yFINFSykcDwK68QCucQ2cugcIkYivnL3SUjxwl0EnYkEhqNDq5B4aQ8mg unUxyNHUjlJpWJjNbgzsz9EcFsv0yKYEUkwB26eEExZIc9Mmf1/c7qinTJGaG1HGQCZlqG3B5mQc SIkQRks3CREgREnA6gJciITOBQRCYxQTeAai4eQmyEVqonRAKx0SJalJfteXlEAoEuBhi3iwsXrF 4szqiZe4eijsOogOFgWh7MAtDGkYS/n2H9uCuwlGXUzJA1Tmpsrr13X5hfLRiPKUSmU0JC0dNyyZ bBoyskVn2XFIleGgsr748CuRsuN+9MzXowEs0OKzc4eKCmfC6g58nEjPRkPyXFKOxxQxyKXCyiag N7CRaW4EQcVnhwiF5I8KCdWglIkYo3kSmzLi6tUFdhWUhTISriFRtaokBksjl4H5Eyw5nQ1nxLr7 mN7bqoYayMt5aUmYlpDfvcVkNwlXvLaRxfA1EU+iTMrxQMBjaSIlA4szHDYXKul6lXviVFRQVWRI FB5nLgQLNVuInsNVqHmCCwxk9K4m4ktqYpMBpOIKZKA0VMwfBNViKkiNq0aSI6krMT2WZhfdZ5b3 RGqlorRiWP4DG+ZYVEYl59iv4zCZ4HzMzYbBKVpXwfbExNZiSLSBaRORYfUUlc66qh9riBu2GyBe UFiGEwOKnnfmWlAaDqUl7EjfuwTCjh90DVrMjMyHkCZqyOBA8hLsYHc4GneaTRkxjiPWIw9qd23W ZD+fXSUECsIz2pazXiZ1VDzEukcTumMDaGsgRM9TcjOsgWg8AsMtLiM+J4QVIkwlSyyKkuVbnlRU bBilnGuWy8gR72GfAdKovEnzBgugPhSyk8gz65kW8rTgnhRiTN5IyfjlI0TMi+7ApOK0H/lV5C82 MY3uQ6h6amFtbRGYnFUmFSOTgTADHJr3elYpv0ebTg2Krv+opMMpgXKxraTo6k94CTRx4iqi4Q5q VRA/QnCJvWj0cZBJaVmNamXOCXOHeEwPQqBjvD7gr9wyJwGIsQR9oSWgbcZpA9nwLaCYyNEmGRUh yXoRh0SUFzELz2hqgiT+CfhC0sKIggKnBVWoKuFFF7kPcBqwkiChJ4lBAUCmEPF4BAcIuCW8vHgO ByDCQiATAmGoUk4JBMeIyF/INJULMKCWvrAxY+YJa0Ik9CIkTNMDKBtQRByL29xMhgeLMG8CFIsQ YxOYRdkDJQgeJg/IX6gnaGhAMDCrCIrA7kBGwVKDIHipEwXFoPyGC0TgIlgQDAkPAeFoRAvRUo0S WuElod4iGQiojqgFyYhylwzgzRDSA4WLxyIjCLBMcKgpAkXhQYA9knIAlm1dR6zq8JaDIeWkryyx 3Hd9uV4YjDCRPMNFjltxE/IiFSMnbt3is5PWMPrC/033y0XtLhzHzOj3davLtPFiMqKEiilyVU7k h+TtnSURGQiA+Ah7Mj99coII+58kxQfgfWveVCtRFwS93qqQsi40sGPKBiCLyb8nVFBxim8AKLnP /BvukksguEkvvL3M87jrEu48TXjMg8mMpYbCUpYnMtwikLpuJ4Ae/4CWgiJHxZIim6cyMCsYH8AX 9WCHQYglxWvA0GuTBcY2CidTSZuPxVfQuNvSyJYYSpt1YuSzZbUBFVCkwnjIT2Aekp+JekjZQG1k WdxumwC0DSbZvSEoo1nTff70KsxqsXDwHqKNrcEMjAgV2S9CZzPUtsSoSboUEBCtrPpm7poA47/H OCDjiONZbzcKULzI51WCykweOkcaT0SIosDUK/yOb9Bn38dE6/YdM+XKmp1DnjFQKw4jx6b3DCjM tlYlO1izIXSZmEOaUuZa2DtpW+hq4oB6TIo3HhvYjq7kTZLX6Us1ByOJgVKwu9kaEcBF5Sy3s7FF iC0cU6x/6Uzz7Q2mrogJlpSwCtfxqbjS9+JYZ+Z4+Pn4Nw61imTKYMmw5FZPZQ3D/bjTm6taszr9 geYiKEx5CcluGTUtd0c8OiD3yLLL0vj5uBp8v2VipZhkQuS6lHkWRAmpJGPqUpEbbW4C5m/QaCDH gOJuNR6DHLwF3GKhh6F5M86kROKRf9R2Rmigan4HYHoHpj1fEtLW2MgJspidZ6C9iHQaJgWxiyed GBpOciREUAHY7m9IgXTDezSPobdx7EeoL5WH3wgYVr9SSda89GBGWykpITscYHb4kkiCqOPiJQQE fAtphkKy1IeSmwo01lg6CrarakOSXDFphLhteL6rTIqTJHZJbgpVJiwNx1D+oGoRH8oT4G3N+iuJ bQ3DNw6xDjDWZ9ugOryarEhmqkTvc5oOVDGot/EGJAbI+RtQyInNa7VpO5Zxgiv6HtsLzJdiJ/Y+ AmRs29T2JmQJXjcBKyCeamXkbj0PMCBH1PUvUNZrDlppMQtJAS4EAZI5/cwaEjzXbwYHoZJXJJwh xxYF5ZmJNegA9XtQEq9fYC9hQZOBgyLBzUTQswywXM0DK8xM2SRWhM7uH1nXqwzS9y9aWQ9Q6DMb YwAaOJ3teDxxI4ThxDsOkdYZZhuaEigT21QK8NZxqWAOQBUJMA+1HYn4JMUNEcJwDkSHCJiE6Q8Q DiJEQ8qzOglrOglQIXYyLg1A24jYktDDgtcmYBkmMD4EuXT44ICGCRMf3B5jvGYSX3jO2GdioUF0 AWX5sjFCGAY9VIlwBATv8JQfQCIIgkuhoDhJ72P8UlQgiS3OV8yrIGY3NymeF92HddR9KtRxQSOp QNqLm82R83JOsPGkXIIY9iKRaj6Ew1weTD6VwMD1UbDWcUnYdc3JsMTUXYjvop6C3QPcN/OMSqSG QcPrL9MFMu1Fo543wTwQ641JaVq3xFWFKFPLxLQrKiGapJwonc0xjeQyYg7B9bix7Pc5OOHV5BMM HsONqp0D/gb8U8NBi4q13fi1Q/M17GlM0yZ99xzAqi8NhqORhq9G6bvph2qr4Tphxl7LgMJYOWIF MEHwAfpOZkls0aAspmDFYR3HYZazwEvfcwMS5kUWEEzMrQ1AeYYAgnoSLbiYpmYhi+IFCEnAb3QK VEk24oN24CwKRDdy1DwsYEYM5lU/1q1jE0MGSESeM7BTIK0pFVQ5ZE0D1QdyT5DWMlTvGgqcPvVl FHAGNaj2HnF8lZkALB5tqDdGGZJhjYMBlxoNIz4nAodAxLTgQ9Hm0nmaU1B/A7HzKiIFx5VEPb1P ZWi6H1fnmahjSMI6MOLj7VzMlpGNB3M1rIrTmsNZBIgV2tKnGuIcdgcAYDLOlGUhGjQaSfSwtF8j OQSnnP+LuSKcKEhlmzb9AA== --===============4734155864113199100==--