Fix for bug #50913 "Deadlock between open_and_lock_tables_derived

and MDL".

Concurrent execution of a multi-DELETE statement and ALTER
TABLE statement which affected one of the tables used in
the multi-DELETE sometimes led to deadlock.
Similar deadlocks might have occured when one performed
INSERT/UPDATE/DELETE on a view and concurrently executed
ALTER TABLE for the view's underlying table, or when one
concurrently executed TRUNCATE TABLE for InnoDB table and
ALTER TABLE for the same table.

These deadlocks were caused by a discrepancy between types of
metadata and thr_lock.cc locks acquired by those statements.

What happened was that multi-DELETE/TRUNCATE/DML-through-the-
view statement in the first connection acquired SR lock on a
table, then ALTER TABLE would come in in the second connection
and acquire SNW metadata lock and TL_WRITE_ALLOW_READ
thr_lock.c lock and then would start waiting for the first
connection during lock upgrade. After that the statement in
the first connection would try to acquire TL_WRITE lock on
table and would start waiting for the second connection,
creating a deadlock.

This patch solves this problem by ensuring that we acquire
SW metadata lock in all cases in which we acquiring write
thr_lock.c lock. This guarantees that deadlocks like the
one described above won't occur since all lock conflicts
in such situation are resolved within MDL subsystem.

This patch also adds assert which should guarantee that
such situations won't arise in future.

1 files changed, 19 insertions, 0 deletions

diff --git a/sql/lock.cc b/sql/lock.cc
index f34b6c80872..97756893e2b 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -153,6 +153,25 @@ int mysql_lock_tables_check(THD *thd, TABLE **tables, uint count, uint flags)
     {
       system_count++;
     }
+
+    /*
+      If we are going to lock a non-temporary table we must own metadata
+      lock of appropriate type on it (I.e. for table to be locked for
+      write we must own metadata lock of MDL_SHARED_WRITE or stronger
+      type. For table to be locked for read we must own metadata lock
+      of MDL_SHARED_READ or stronger type).
+      The only exception are HANDLER statements which are allowed to
+      lock table for read while having only MDL_SHARED lock on it.
+    */
+    DBUG_ASSERT(t->s->tmp_table ||
+                thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+                                 t->s->db.str, t->s->table_name.str,
+                                 t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE ?
+                                 MDL_SHARED_WRITE : MDL_SHARED_READ) ||
+                (t->open_by_handler &&
+                 thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+                                  t->s->db.str, t->s->table_name.str,
+                                  MDL_SHARED)));
   }
 
   /*