From: Jon Olav Hauglid
Date: September 8 2010 8:25am
Subject: bzr commit into mysql-5.5-runtime branch (jon.hauglid:3133) Bug#56292
List-Archive: http://lists.mysql.com/commits/117756
X-Bug: 56292
Message-Id: <201009080826.o888QMqm013698@rcsinet15.oracle.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============5232692013894283832=="

--===============5232692013894283832==
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:jon.hauglid@stripped

 3133 Jon Olav Hauglid	2010-09-08
      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. Since LOCK TABLE on the parent table
      now takes more than one metadata lock, it is possible for LOCK TABLE
      ... WRITE on the parent table or child tables to give ER_LOCK_DEADLOCK
      error.
      
      Test case added to mdl_sync.test.
      Merge.test/.result has been updated to reflect the change to LOCK TABLE.

    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-09-08 08:25:37 +0000
@@ -2913,3 +2913,41 @@ 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.
+# 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;
+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-09-08 08:25:37 +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-09-08 08:25:37 +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
+--echo # Now that DELETE blocks on a metadata lock, we should be able to do
+--echo # SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table
+--echo # 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-09-08 08:25:37 +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-09-08 08:25:37 +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 metadata 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;


--===============5232692013894283832==
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: 9af5096dc8f6c113877b52c3d3834872dc5f148b
# timestamp: 2010-09-08 10:25:41 +0200
# source_branch: file:///export/home/x/mysql-5.5-bugfixing/
# base_revision_id: jon.hauglid@stripped\
#   zesazp04nton9ozx
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfJy+6cABnFfgEQQXPf//3//
3+C////wYA6rvoqqF2YhAKBSAVIKlSJBIUEJNtFKoBkkk9lT1NpqNqY0yNCPUGE2mSMjIBggyaBF
PVPRD0noQ9RoDQAaAxANMg0AAGOMmTRiGmhgJoYmjTJiBkYTRpphBkwkRCEaJgmJD1NNoqP9QaSZ
tTQ0MmnlDSZqZA4yZNGIaaGAmhiaNMmIGRhNGmmEGTCSQRoATJk0ExGjVMET0GiZPU9QBoHqPUaa
QIYW0Dj8P1QTkCkEFWKBkZbC1FNnpEN+31Dlp2PrkXznXIsC4gdY4mVlxQJBPMQySIeBAS8EUCig
UVFKIEwwSE5KrvkV9fPwqTltlJad2nwerZaw1TFQXvmNBYmLSgybVkRnMCkgBAp++S9Zsoo6M4oj
VPn+tEqpaXztRdhx6YhfwxYI8pgCN18ge87RjNnwDR6EeZG5DTYxjbYZ/kBv3ZrZM24i1Cx10voW
4wXq5wTxi1LSYNWus5weD3bOjj40NblPyUEW6z5m3sJ6IEmBF5PmDVHbaqvxMPeHVHzYkKwPDCjn
t2DE+V7sVE8rA41ymH9mUsFOXgHgMRpIIkkII37zVhN5cLbmbiYsHIvntJI/8T7BtjgxsIL5jX2U
L5pnk05vIuo8GrG0uCtClppTdsIbWQjAVzBs/FT4G4nponFYMuhoDA+ycXDEdcxi+ohlsWwpZ9Y4
hb1mOV0j9Nc5b4NHOtSzDMFuJljCVYMgwdh81DK7J2agz8UZ1Tm9lxqLiDzNujNWm/FpZUXw+aId
k89pLCsVRbPh+wyofQMk0ooSJmy96pkvMakjB0VSGqM8mzMIrJnNGDhmsaMC/RQlHBhzWuVYymcW
DpjdUSY4FkeBv0LqT4XFT7lWrkY9guTS4Vu2w/kQCa4jETseIldoy0JiAtV4NEkJOiBtsYxjGmNt
sY00xvpHt2tDgDeogDUYjNJNsBUWPbF2bAUkGFL9xWVTIH2fjHaF4azyY4UDgeBxiYpC6yZtSQHQ
SMThvK/wMiB+BT8RIpOhwdxpIByD04HMSoPFjs02p3GVtb3Wmu/pguMIumJmPecu1+FioROIGSD2
eXOCYtmHvYac9n3x2Iw3WRtQ951WATa3vyBoNQdJVFAaAsQkihIwch1FQ2HUlUvi49y2mcwMhNrZ
FAlAUEsMsoRIypmSqs5cGwgi1PKaNT3sWqVmlEj2HWK4ZCLv8kX0IFnL168QF9zPjyNO8oXbNv/C
ER08uxuKgprqNxCU3HTdw35RwUxwNrxyUnNGFtwrjjsdPLEwB6nqHWTPGtgoeOBeEvR3gZGlBx4p
ma1G+WSHltRwgKmcpPU+2o383ENG2WXEF4JQvcSY5lDhUbjaByYSKyi8iGu+JuIhEllE3l5WskOA
OXKmfm1OYoS0BwcJhwG1bR5gfMxvK9eZ6zU9ZdfWxxal99ZCPExJG8RvVMk1AUkxdUjYRHkDaMTC
VE8xTeRfrNNvrIExYUHocsyA2GNVYVDXYji5BYQiYPg9qKVcPJMRJhyewxeOo8pA7KDarSUVSTWZ
CDAMBXDwOszGyhgUFhksiFtjm8t15bSiZ0XXSinDGAxQ9OEvB5QW4E63SPDUyLD4LZxsD7SLpTm4
gQHOLh9WBmTn2G4cYkjSg1JzwLD2FRNXZXM59rnlY4rH6sicYYYlnIgGYx5VhILybqdTfTamFfR+
c1eZgVDi4yiczUS8yg7nEx1NxjsY2YapLYYP4T175U6G162nTloSStIUUapO6GetxspeeAWkA3Hc
iZ1DScaF5lCTWJVGWZaPKQSJWzheXBUerfUoYGeDKs8aMqB534nMSvKBkrbHCTypP0KDCLBCYgTF
nR2ER0RxK1cSREqKiN5W8mJHdUeIvNibKxGdA4OGZ1Q1Y0QlWEnc6iqBa5x8fpbTZzQbcKuSI2+0
hlblUoRaPSLya2eADFfssNtMdUb4bbGB+4oEZvqR9HUWEHUugxXckl3I/kMQohaRn8h+AR+4XC0K
zYMGfBJKUHrssAPeL5k3CsgcCVbj+pSOesJjFTgKQguCi/JGlDGL+BfFEqU2MYMBtmqIbmAgoEED
pQ/NgMDTRBIa0FACyA1izCKCyf8C4kReAaCp8yAgRosIoFwFwbRWUBYJx4jEXyDaUpZBIm06igHv
SS0QLKYK4oRYobEwaqdAioQHM+8TCICgugP6AtsFUG0TmEUFIZi2CICYP7hdmE9gYIBgYVQRFWHo
QEbkqBGIPSoEwWlgPMRkWCgCpiFA0FiQJRmRUDmRAQXpTRJKUZxNFwxtpp6EBQVg2lAy0bqHOBAt
UkIqMRiDYzhkDAD9rjQGBn0hRsW0BlHMcj7Dl5iUNI7IZHYpPrPf+F1EWGiqTPcOCTfPOl4CYQIY
HqxPZ9Jil7D7yEe4LaD2nuPu9vcfmafFVL9P0PpE/iX4Hx0EtDVyXLJwKHD5bRA8iQikbilRFG0V
hBPedyZcvid9bllRSAr37aLJcKSozYMvGBeJFpO/B1JI6xBtiRK1z/o3ymEG4XUptgV/dksDp1WH
0NsSwzOZC0iwxceU4+nm8uSoGufvLkuDSD0MFVD+HSWoZxongC+TCm0ZRKo8S0xDYQLTCsV5iMf3
WJ0LFnzprOkaKtMXAbWvECKrAVmlI0lLA4CTKCC/iaEkeLAPU0m19pmPGBKDAzJCZ6TvOvRnRaRg
4i0EoHoijg3ihkXlVcYm83nMshWlJJtx1Pae4qJISvvPy0d0MkjpXAekc7xxuKurhboXHX2Viyiw
eeY42nqSIoqRtFbuOry82u8e2E/1sw059nSgodO56Zghti1nHClArBoU6r4oA32tMavG6ZCMSwvE
obxJz0jpMZMCgIvSaL/OHZTpOOk29lryO0jh27dqy760bEaiMFMy4uxRWguHFGo/3pnn3hwMfIET
lhQwCdzKG5yH2wqOOa79td7Q40i9p3KYMmv6FZPu6NN/txt2upWe08vyDuIikmPMTktRgahrerno
6oP6Yle+y8XFuDejBWnU+H5WpVgyIXpdSjyLopE6mSMvcUJEbbG8RYFw7kMRzN4vMY7HiQMkTKi/
tHQjQjqHdyOsJESmeeakqdQlTa0gZthpnA9RSRHiKEVTaXaxoMDoKlRD50g8Tsb0iJdIN7NMfA11
PcR5gvdWUfKMS6AfaIHWdBKC82EjLWkpISLHGBr9RMCgUnN4IhJeZeUHA5pECk8jZylxOY+dcW3e
aQ4QdH8pq7+cBd7TES80lqElIxYG8MfU0PIDQRN84S5G/J+Ea25RkMrr2jDYG8y914efjokSMrbY
mZ+BebkzMaFn3AxMBxidjehkROq0s1YzPqLOcEV/ge3UvMl5kTRfqesTIdv8j7ScySSuGqYzYG8z
eew7gRI+4cJe4tTjMzDnlQYBYTATciAMkdPzZGILuvRcGB6GEFogcIccmQu2RsJ11SC6Egmq07AX
MlBk4GFiVpzHiJUSQtoZYLobBrQaznaSMRJxyD2HHixu36HMtrRKp1DZ4q0SHyErazlnkJLRcJ3L
XcH1HpH0BdiGW9pNiRgz4zAm3riicLMiEkiMyORbypNXOpAoAhFlAjMIUWJEBBUqIkyc5uLCFyNJ
WG0G57yapAXsOCv0T05gGSYvPAn6HXf5giGCSnH9weY8RmEB8Rh2pnWpKC8QFt+jC2IQxDO0QMoA
YqAIKejUSH1gxgxgxVYYBqRcz9Wpr/4QZEFFwYeqU+Y3rcHjODvCJscaqd9gOkV8xkEBpsJCxWJe
TqQidRKnRIwG0CGPoRSLEfAnDSeMBwfCqBISmPx7CUDgamKXvPOGK8ZY0qjBj0sbPWOekftHPpGI
MiQ0HP7y7SATq3IsHPG9aeJDi00SzWnGIqgoQp8fMsCopIZKgnh6UErmkMciGTEHYPrcWPZ7nDjm
/zIJwMMH1jiWA71nHYnhOsDY8fpZ/ZqR5iabmUNYJZTHfF2IEQVR0GhLZf04mPsR3NtvpOxGzyO9
k4YRmJMjB6xApgg9iH5nQySowwCuidDFQR8T3k+hLwAjGmYnIEhw8YGKid4rwO4yAYM9JIlYz4lw
qGwQzmqkXoAhI8cFCBFtSc37wKgkIb6itD0qmEi9nMUv7U6DE6GGtCLSOOZXFFmMEi5rWQrIGrjk
YzYeLJUcBoKi+rDCQYrqfce5L+C5bUg5EDvoj1RDckwx2GRnkgyGfE4knQWwsOJDu83KfI2ppHyO
x7ikiBaeVJD2+wrA5mYl6vpkZjLaMIwEujh5ceBkZGB2MlmZqHI9nI6k6ROcDwatOOrxx+8PASpD
4HDFKYzCKPAnMObC2rwuJjCZYEV/8XckU4UJDycvunA=


--===============5232692013894283832==--