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.

1 files changed, 1 insertions, 2 deletions

diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index c7e54288f8b..5d96970ce74 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -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;