Implement new type-of-operation-aware metadata locks.

Add a wait-for graph based deadlock detector to the
MDL subsystem.

Fixes bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock" and
bug #37346 "innodb does not detect deadlock between update and
alter table".

The first bug manifested itself as an unwarranted abort of a
transaction with ER_LOCK_DEADLOCK error by a concurrent ALTER
statement, when this transaction tried to repeat use of a
table, which it has already used in a similar fashion before
ALTER started.

The second bug showed up as a deadlock between table-level
locks and InnoDB row locks, which was "detected" only after
innodb_lock_wait_timeout timeout.

A transaction would start using the table and modify a few
rows.
Then ALTER TABLE would come in, and start copying rows
into a temporary table. Eventually it would stumble on
the modified records and get blocked on a row lock.
The first transaction would try to do more updates, and get
blocked on thr_lock.c lock.
This situation of circular wait would only get resolved
by a timeout.

Both these bugs stemmed from inadequate solutions to the
problem of deadlocks occurring between different
locking subsystems.

In the first case we tried to avoid deadlocks between metadata
locking and table-level locking subsystems, when upgrading shared
metadata lock to exclusive one.
Transactions holding the shared lock on the table and waiting for
some table-level lock used to be aborted too aggressively.

We also allowed ALTER TABLE to start in presence of transactions
that modify the subject table. ALTER TABLE acquires
TL_WRITE_ALLOW_READ lock at start, and that block all writes
against the table (naturally, we don't want any writes to be lost
when switching the old and the new table). TL_WRITE_ALLOW_READ
lock, in turn, would block the started transaction on thr_lock.c
lock, should they do more updates. This, again, lead to the need
to abort such transactions.

The second bug occurred simply because we didn't have any
mechanism to detect deadlocks between the table-level locks
in thr_lock.c and row-level locks in InnoDB, other than
innodb_lock_wait_timeout.

This patch solves both these problems by moving lock conflicts
which are causing these deadlocks into the metadata locking
subsystem, thus making it possible to avoid or detect such
deadlocks inside MDL.

To do this we introduce new type-of-operation-aware metadata
locks, which allow MDL subsystem to know not only the fact that
transaction has used or is going to use some object but also what
kind of operation it has carried out or going to carry out on the
object.

This, along with the addition of a special kind of upgradable
metadata lock, allows ALTER TABLE to wait until all
transactions which has updated the table to go away.
This solves the second issue.
Another special type of upgradable metadata lock is acquired
by LOCK TABLE WRITE. This second lock type allows to solve the
first issue, since abortion of table-level locks in event of
DDL under LOCK TABLES becomes also unnecessary.

Below follows the list of incompatible changes introduced by
this patch:

- From now on, ALTER TABLE and CREATE/DROP TRIGGER SQL (i.e. those
  statements that acquire TL_WRITE_ALLOW_READ lock)
  wait for all transactions which has *updated* the table to
  complete.

- From now on, LOCK TABLES ... WRITE, REPAIR/OPTIMIZE TABLE
  (i.e. all statements which acquire TL_WRITE table-level lock) wait
  for all transaction which *updated or read* from the table
  to complete.
  As a consequence, innodb_table_locks=0 option no longer applies
  to LOCK TABLES ... WRITE.

- DROP DATABASE, DROP TABLE, RENAME TABLE no longer abort
  statements or transactions which use tables being dropped or
  renamed, and instead wait for these transactions to complete.

- Since LOCK TABLES WRITE now takes a special metadata lock,
  not compatible with with reads or writes against the subject table
  and transaction-wide, thr_lock.c deadlock avoidance algorithm
  that used to ensure absence of deadlocks between LOCK TABLES
  WRITE and other statements is no longer sufficient, even for
  MyISAM. The wait-for graph based deadlock detector of MDL
  subsystem may sometimes be necessary and is involved. This may
  lead to ER_LOCK_DEADLOCK error produced for multi-statement
  transactions even if these only use MyISAM:

  session 1:         session 2:
  begin;

  update t1 ...      lock table t2 write, t1 write;
                     -- gets a lock on t2, blocks on t1

  update t2 ...
  (ER_LOCK_DEADLOCK)

- Finally,  support of LOW_PRIORITY option for LOCK TABLES ... WRITE
  was abandoned.
  LOCK TABLE ... LOW_PRIORITY WRITE from now on has the same
  priority as the usual LOCK TABLE ... WRITE.
  SELECT HIGH PRIORITY no longer trumps LOCK TABLE ... WRITE  in
  the wait queue.

- We do not take upgradable metadata locks on implicitly
  locked tables. So if one has, say, a view v1 that uses
  table t1, and issues:
  LOCK TABLE v1 WRITE;
  FLUSH TABLE t1; -- (or just 'FLUSH TABLES'),
  an error is produced.
  In order to be able to perform DDL on a table under LOCK TABLES,
  the table must be locked explicitly in the LOCK TABLES list.

mysql-test/include/handler.inc:
  Adjusted test case to trigger an execution path on which bug 41110
  "crash with handler command when used concurrently with alter
  table" and bug 41112 "crash in mysql_ha_close_table/get_lock_data
  with alter table" were originally discovered. Left old test case
  which no longer triggers this execution path for the sake of
  coverage.
  Added test coverage for HANDLER SQL statements and type-aware
  metadata locks.
  Added a test for the global shared lock and HANDLER SQL.
  Updated tests to take into account that the old simple deadlock
  detection heuristics was replaced with a graph-based deadlock
  detector.
mysql-test/r/debug_sync.result:
  Updated results (see debug_sync.test).
mysql-test/r/handler_innodb.result:
  Updated results (see handler.inc test).
mysql-test/r/handler_myisam.result:
  Updated results (see handler.inc test).
mysql-test/r/innodb-lock.result:
  Updated results (see innodb-lock.test).
mysql-test/r/innodb_mysql_lock.result:
  Updated results (see innodb_mysql_lock.test).
mysql-test/r/lock.result:
  Updated results (see lock.test).
mysql-test/r/lock_multi.result:
  Updated results (see lock_multi.test).
mysql-test/r/lock_sync.result:
  Updated results (see lock_sync.test).
mysql-test/r/mdl_sync.result:
  Updated results (see mdl_sync.test).
mysql-test/r/sp-threads.result:
  SHOW PROCESSLIST output has changed due to the fact that waiting
  for LOCK TABLES WRITE now happens within metadata locking
  subsystem.
mysql-test/r/truncate_coverage.result:
  Updated results (see truncate_coverage.test).
mysql-test/suite/funcs_1/datadict/processlist_val.inc:
  SELECT FROM I_S.PROCESSLIST output has changed due to fact that
  waiting for LOCK TABLES WRITE now happens within metadata locking
  subsystem.
mysql-test/suite/funcs_1/r/processlist_val_no_prot.result:
  SELECT FROM I_S.PROCESSLIST output has changed due to fact that
  waiting for LOCK TABLES WRITE now happens within metadata locking
  subsystem.
mysql-test/suite/rpl/t/rpl_sp.test:
  Updated to a new SHOW PROCESSLIST state name.
mysql-test/t/debug_sync.test:
  Use LOCK TABLES READ instead of LOCK TABLES WRITE as the latter
  no longer allows to trigger execution path involving waiting on
  thr_lock.c lock and therefore reaching debug sync-point covered
  by this test.
mysql-test/t/innodb-lock.test:
  Adjusted test case to the fact that innodb_table_locks=0 option is
  no longer supported, since LOCK TABLES WRITE handles all its
  conflicts within MDL subsystem.
mysql-test/t/innodb_mysql_lock.test:
  Added test for bug #37346 "innodb does not detect deadlock between
  update and alter table".
mysql-test/t/lock.test:
  Added test coverage which checks the fact that we no longer support
  DDL under LOCK TABLES on tables which were locked implicitly.
  Adjusted existing test cases accordingly.
mysql-test/t/lock_multi.test:
  Added test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary
  deadlock".  Adjusted other test cases to take into account the
  fact that waiting for LOCK TABLES ... WRITE now happens within MDL
  subsystem.
mysql-test/t/lock_sync.test:
  Since LOCK TABLES ... WRITE now takes SNRW metadata lock for
  tables locked explicitly we have to implicitly lock InnoDB tables
  (through view) to trigger the table-level lock conflict between
  TL_WRITE and TL_WRITE_ALLOW_WRITE.
mysql-test/t/mdl_sync.test:
  Added basic test coverage for type-of-operation-aware metadata
  locks. Also covered with tests some use cases involving HANDLER
  statements in which a deadlock could arise.
  Adjusted existing tests to take type-of-operation-aware MDL into
  account.
mysql-test/t/multi_update.test:
  Update to a new SHOW PROCESSLIST state name.
mysql-test/t/truncate_coverage.test:
  Adjusted test case after making LOCK TABLES WRITE to wait until
  transactions that use the table to be locked are completed.
  Updated to the changed name of DEBUG_SYNC point.
sql/handler.cc:
  Global read lock functionality has been
  moved into a class.
sql/lock.cc:
  Global read lock functionality has been
  moved into a class.
  Updated code to use the new MDL API.
sql/mdl.cc:
  Introduced new type-of-operation aware metadata locks.
  To do this:
  - Changed MDL_lock to use one list for waiting requests and one
    list for granted requests. For each list, added a bitmap
    that holds information what lock types a list contains.
    Added a helper class MDL_lock::List to manipulate with granted
    and waited lists while keeping the bitmaps in sync
    with list contents.
  - Changed lock-compatibility functions to use bitmaps that
    define compatibility.
  - Introduced a graph based deadlock detector inspired by
    waiting_threads.c from Maria implementation.
  - Now that we have a deadlock detector, and no longer have
    a global lock to protect individual lock objects, but rather
    use an rw lock per object, removed redundant code for upgrade,
    and the global read lock. Changed the MDL API to
    no longer require the caller to acquire the global
    intention exclusive lock by means of a separate method.
    Removed a few more methods that became redundant.
  - Removed deadlock detection heuristic, it has been made
    obsolete by the deadlock detector.
  - With operation-type-aware metadata locks, MDL subsystem has
    become aware of potential conflicts between DDL and open
    transactions. This made it possible to remove calls to
    mysql_abort_transactions_with_shared_lock() from acquisition
    paths for exclusive lock and lock upgrade. Now we can simply
    wait for these transactions to complete without fear of
    deadlock. Function mysql_lock_abort() has also become
    unnecessary for all conflicting cases except when a DDL
    conflicts with a connection that has an open HANDLER.
sql/mdl.h:
  Introduced new type-of-operation aware metadata locks.
  Introduced a graph based deadlock detector and supporting
  methods.
  Added comments.
  God rid of redundant API calls.
  Renamed m_lt_or_ha_sentinel to m_trans_sentinel,
  since now it guards the global read lock as well as
  LOCK TABLES and HANDLER locks.
sql/mysql_priv.h:
  Moved the global read lock functionality into a
  class.
  Added MYSQL_OPEN_FORCE_SHARED_MDL flag which forces
  open_tables() to take MDL_SHARED on tables instead of
  metadata locks specified in the parser. We use this to
  allow PREPARE run concurrently in presence of
  LOCK TABLES ... WRITE.
  Added signature for find_table_for_mdl_ugprade().
sql/set_var.cc:
  Global read lock functionality has been
  moved into a class.
sql/sp_head.cc:
  When creating TABLE_LIST elements for prelocking or
  system tables set the type of request for metadata
  lock according to the operation that will be performed
  on the table.
sql/sql_base.cc:
  - Updated code to use the new MDL API.
  - In order to avoid locks starvation we take upgradable
    locks all at once. As result implicitly locked tables no
    longer get an upgradable lock. Consequently DDL and FLUSH
    TABLES for such tables is prohibited.
    find_write_locked_table() was replaced by
    find_table_for_mdl_upgrade() function.
    open_table() was adjusted to return TABLE instance with
    upgradable ticket when necessary.
  - We no longer wait for all locks on OT_WAIT back off
    action -- only on the lock that caused the wait
    conflict. Moreover, now we distinguish cases when we
    have to wait due to conflict in MDL and old version
    of table in TDC.
  - Upate mysql_notify_threads_having_share_locks()
    to only abort thr_lock.c waits of threads that
    have open HANDLERs, since lock conflicts with only
    these threads now can lead to deadlocks not detectable
    by the MDL deadlock detector.
  - Remove mysql_abort_transactions_with_shared_locks()
    which is no longer needed.
sql/sql_class.cc:
  Global read lock functionality has been moved into a class.
  Re-arranged code in THD::cleanup() to simplify assert.
sql/sql_class.h:
  Introduced class to incapsulate global read lock
  functionality.
  Now sentinel in MDL subsystem guards the global read lock
  as well as LOCK TABLES and HANDLER locks. Adjusted code
  accordingly.
sql/sql_db.cc:
  Global read lock functionality has been moved into a class.
sql/sql_delete.cc:
  We no longer acquire upgradable metadata locks on tables
  which are locked by LOCK TABLES implicitly. As result
  TRUNCATE TABLE is no longer allowed for such tables.
  Updated code to use the new MDL API.
sql/sql_handler.cc:
  Inform MDL_context about presence of open HANDLERs.
  Since HANLDERs break MDL protocol by acquiring table-level
  lock while holding only S metadata lock on a table MDL
  subsystem should take special care about such contexts (Now
  this is the only case when mysql_lock_abort() is used).
sql/sql_parse.cc:
  Global read lock functionality has been moved into a class.
  Do not take upgradable metadata locks when opening tables
  for CREATE TABLE SELECT as it is not necessary and limits
  concurrency.
  When initializing TABLE_LIST objects before adding them
  to the table list set the type of request for metadata lock
  according to the operation that will be performed on the
  table.
  We no longer acquire upgradable metadata locks on tables
  which are locked by LOCK TABLES implicitly. As result FLUSH
  TABLES is no longer allowed for such tables.
sql/sql_prepare.cc:
  Use MYSQL_OPEN_FORCE_SHARED_MDL flag when opening
  tables during PREPARE. This allows PREPARE to run
  concurrently in presence of LOCK TABLES ... WRITE.
sql/sql_rename.cc:
  Global read lock functionality has been moved into a class.
sql/sql_show.cc:
  Updated code to use the new MDL API.
sql/sql_table.cc:
  Global read lock functionality has been moved into a class.
  We no longer acquire upgradable metadata locks on tables
  which are locked by LOCK TABLES implicitly. As result DROP
  TABLE is no longer allowed for such tables.
  Updated code to use the new MDL API.
sql/sql_trigger.cc:
  Global read lock functionality has been moved into a class.
  We no longer acquire upgradable metadata locks on tables
  which are locked by LOCK TABLES implicitly. As result
  CREATE/DROP TRIGGER is no longer allowed for such tables.
  Updated code to use the new MDL API.
sql/sql_view.cc:
  Global read lock functionality has been moved into a class.
  Fixed results of wrong merge that led to misuse of GLR API.
  CREATE VIEW statement is not a commit statement.
sql/table.cc:
  When resetting TABLE_LIST objects for PS or SP re-execution
  set the type of request for metadata lock according to the
  operation that will be performed on the table. Do the same
  in auxiliary function initializing metadata lock requests
  in a table list.
sql/table.h:
  When initializing TABLE_LIST objects set the type of request
  for metadata lock according to the operation that will be
  performed on the table.
sql/transaction.cc:
  Global read lock functionality has been moved into a class.

1 files changed, 206 insertions, 179 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 2f891375163..8dced67dd44 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -131,8 +131,6 @@ static bool tdc_wait_for_old_versions(THD *thd,
 static bool
 has_write_table_with_auto_increment(TABLE_LIST *tables);
 
-TABLE *find_table_for_mdl_upgrade(TABLE *list, const char *db,
-                                  const char *table_name);
 
 uint cached_open_tables(void)
 {
@@ -477,8 +475,10 @@ TABLE_SHARE *get_table_share(THD *thd, TABLE_LIST *table_list, char *key,
     To be able perform any operation on table we should own
     some kind of metadata lock on it.
   */
-  DBUG_ASSERT(thd->mdl_context.is_lock_owner(MDL_key::TABLE, table_list->db,
-                                             table_list->table_name));
+  DBUG_ASSERT(thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+                                             table_list->db,
+                                             table_list->table_name,
+                                             MDL_SHARED));
 
   /* Read table definition from cache */
   if ((share= (TABLE_SHARE*) my_hash_search(&table_def_cache,(uchar*) key,
@@ -1003,7 +1003,7 @@ bool close_cached_tables(THD *thd, TABLE_LIST *tables, bool have_lock,
     {
       /* A check that the table was locked for write is done by the caller. */
       TABLE *table= find_table_for_mdl_upgrade(thd->open_tables, table_list->db,
-                                               table_list->table_name);
+                                               table_list->table_name, TRUE);
 
       /* May return NULL if this table has already been closed via an alias. */
       if (! table)
@@ -1084,7 +1084,7 @@ err_with_reopen:
       than picking only those tables that were flushed.
     */
     for (TABLE *tab= thd->open_tables; tab; tab= tab->next)
-      tab->mdl_ticket->downgrade_exclusive_lock();
+      tab->mdl_ticket->downgrade_exclusive_lock(MDL_SHARED_NO_READ_WRITE);
   }
   DBUG_RETURN(result);
 }
@@ -2145,7 +2145,7 @@ bool wait_while_table_is_used(THD *thd, TABLE *table,
                        table->s->table_name.str, (ulong) table->s,
                        table->db_stat, table->s->version));
 
-  if (table->mdl_ticket->upgrade_shared_lock_to_exclusive())
+  if (thd->mdl_context.upgrade_shared_lock_to_exclusive(table->mdl_ticket))
     DBUG_RETURN(TRUE);
 
   pthread_mutex_lock(&LOCK_open);
@@ -2336,6 +2336,7 @@ open_table_get_mdl_lock(THD *thd, TABLE_LIST *table_list,
 {
   if (table_list->lock_strategy)
   {
+    MDL_request_list mdl_requests;
     MDL_request *global_request;
     /*
       In case of CREATE TABLE .. If NOT EXISTS .. SELECT, the table
@@ -2350,67 +2351,51 @@ open_table_get_mdl_lock(THD *thd, TABLE_LIST *table_list,
     mdl_request->set_type(MDL_EXCLUSIVE);
     DBUG_ASSERT(! thd->mdl_context.has_locks() ||
                 thd->handler_tables_hash.records ||
-                thd->global_read_lock);
+                thd->global_read_lock.is_acquired());
 
     if (!(global_request= ot_ctx->get_global_mdl_request(thd)))
       return 1;
 
-    if (! global_request->ticket)
-    {
-      ot_ctx->add_request(global_request);
-      if (thd->mdl_context.acquire_global_intention_exclusive_lock(
-                             global_request))
-      return 1;
-    }
+    mdl_requests.push_front(mdl_request);
+    mdl_requests.push_front(global_request);
 
-    ot_ctx->add_request(mdl_request);
-    if (thd->mdl_context.acquire_exclusive_lock(mdl_request))
+    if (thd->mdl_context.acquire_locks(&mdl_requests))
       return 1;
   }
   else
   {
-    /*
-      There is no MDL_SHARED_UPGRADABLE_HIGH_PRIO type of metadata lock so we
-      want to be sure that caller doesn't pass us both flags simultaneously.
-    */
-    DBUG_ASSERT(!(flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) ||
-                !(flags & MYSQL_LOCK_IGNORE_FLUSH));
-
-    if (flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL &&
-        table_list->lock_type >= TL_WRITE_ALLOW_WRITE)
-      mdl_request->set_type(MDL_SHARED_UPGRADABLE);
-    if (flags & MYSQL_LOCK_IGNORE_FLUSH)
-      mdl_request->set_type(MDL_SHARED_HIGH_PRIO);
-
-    if (mdl_request->type == MDL_SHARED_UPGRADABLE)
+    if (flags & MYSQL_OPEN_FORCE_SHARED_MDL)
     {
-      MDL_request *global_request;
+      /*
+        While executing PREPARE for prepared statement we override
+        type-of-operation aware type of shared metadata lock which
+        was set in the parser with simple shared metadata lock.
+        This is necessary to allow concurrent execution of PREPARE
+        and LOCK TABLES WRITE statement which locks one of the tables
+        used in the statement being prepared.
+      */
+      DBUG_ASSERT(!(flags & (MYSQL_OPEN_TAKE_UPGRADABLE_MDL |
+                             MYSQL_LOCK_IGNORE_FLUSH)));
 
-      if (!(global_request= ot_ctx->get_global_mdl_request(thd)))
-        return 1;
-      if (! global_request->ticket)
-      {
-        ot_ctx->add_request(global_request);
-        if (thd->mdl_context.try_acquire_global_intention_exclusive_lock(
-                               global_request))
-          return 1;
-        if (! global_request->ticket)
-          goto failure;
-      }
+      mdl_request->set_type(MDL_SHARED);
+    }
+    else if (flags & MYSQL_LOCK_IGNORE_FLUSH)
+    {
+      DBUG_ASSERT(!(flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL));
+      mdl_request->set_type(MDL_SHARED_HIGH_PRIO);
     }
 
     ot_ctx->add_request(mdl_request);
 
-    if (thd->mdl_context.try_acquire_shared_lock(mdl_request))
+    if (thd->mdl_context.try_acquire_lock(mdl_request))
       return 1;
 
-failure:
     if (mdl_request->ticket == NULL)
     {
       if (flags & MYSQL_OPEN_FAIL_ON_MDL_CONFLICT)
         my_error(ER_WARN_I_S_SKIPPED_TABLE, MYF(0), table_list->db, table_list->table_name);
       else
-        (void) ot_ctx->request_backoff_action(Open_table_context::OT_WAIT);
+        ot_ctx->request_backoff_action(Open_table_context::OT_WAIT_MDL_LOCK);
       return 1;
     }
   }
@@ -2563,6 +2548,17 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
         {
           int distance= ((int) table->reginfo.lock_type -
                          (int) table_list->lock_type);
+
+          /*
+            If we are performing DDL operation we also should ensure
+            that we will find TABLE instance with upgradable metadata
+            lock,
+          */
+          if ((flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) &&
+              table_list->lock_type >= TL_WRITE_ALLOW_WRITE &&
+              ! table->mdl_ticket->is_upgradable_or_exclusive())
+            distance= -1;
+
           /*
             Find a table that either has the exact lock type requested,
             or has the best suitable lock. In case there is no locked
@@ -2596,6 +2592,13 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     }
     if (best_table)
     {
+      if ((flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) &&
+          table_list->lock_type >= TL_WRITE_ALLOW_WRITE &&
+          ! best_table->mdl_ticket->is_upgradable_or_exclusive())
+      {
+        my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), alias);
+        DBUG_RETURN(TRUE);
+      }
       table= best_table;
       table->query_id= thd->query_id;
       DBUG_PRINT("info",("Using locked table"));
@@ -2610,8 +2613,10 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
       TABLES breaks metadata locking protocol (potentially can lead
       to deadlocks) it should be disallowed.
     */
-    if (thd->mdl_context.is_lock_owner(MDL_key::TABLE, table_list->db,
-                                       table_list->table_name))
+    if (thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+                                       table_list->db,
+                                       table_list->table_name,
+                                       MDL_SHARED))
     {
       char path[FN_REFLEN + 1];
       enum legacy_db_type not_used;
@@ -2688,7 +2693,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
   {
     /* Someone did a refresh while thread was opening tables */
     pthread_mutex_unlock(&LOCK_open);
-    (void) ot_ctx->request_backoff_action(Open_table_context::OT_WAIT);
+    (void) ot_ctx->request_backoff_action(Open_table_context::OT_WAIT_TDC);
     DBUG_RETURN(TRUE);
   }
 
@@ -2828,7 +2833,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
        */
       release_table_share(share);
       pthread_mutex_unlock(&LOCK_open);
-      (void) ot_ctx->request_backoff_action(Open_table_context::OT_WAIT);
+      (void) ot_ctx->request_backoff_action(Open_table_context::OT_WAIT_TDC);
       DBUG_RETURN(TRUE);
     }
     /* Force close at once after usage */
@@ -2895,11 +2900,11 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
   /*
     In CREATE TABLE .. If NOT EXISTS .. SELECT we have found that
     table exists now we should downgrade our exclusive metadata
-    lock on this table to shared metadata lock.
+    lock on this table to SW metadata lock.
   */
   if (table_list->lock_strategy == TABLE_LIST::EXCLUSIVE_DOWNGRADABLE_MDL &&
       !(flags & MYSQL_OPEN_HAS_MDL_LOCK))
-    mdl_ticket->downgrade_exclusive_lock();
+    mdl_ticket->downgrade_exclusive_lock(MDL_SHARED_WRITE);
 
   table->mdl_ticket= mdl_ticket;
 
@@ -2982,33 +2987,43 @@ TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name)
 
 
 /**
-   Find write locked instance of table in the list of open tables,
-   emit error if no such instance found.
+   Find instance of TABLE with upgradable or exclusive metadata
+   lock from the list of open tables, emit error if no such table
+   found.
 
-   @param thd        List of TABLE objects to be searched
+   @param list       List of TABLE objects to be searched
    @param db         Database name.
    @param table_name Name of table.
+   @param no_error   Don't emit error if no suitable TABLE
+                     instance were found.
 
-   @return Pointer to write-locked TABLE instance, 0 - otherwise.
+   @return Pointer to TABLE instance with MDL_SHARED_NO_WRITE,
+           MDL_SHARED_NO_READ_WRITE, or MDL_EXCLUSIVE metadata
+           lock, NULL otherwise.
 */
 
-TABLE *find_write_locked_table(TABLE *list, const char *db, const char *table_name)
+TABLE *find_table_for_mdl_upgrade(TABLE *list, const char *db,
+                                  const char *table_name,
+                                  bool no_error)
 {
   TABLE *tab= find_locked_table(list, db, table_name);
 
   if (!tab)
   {
-    my_error(ER_TABLE_NOT_LOCKED, MYF(0), table_name);
-    return 0;
+    if (!no_error)
+      my_error(ER_TABLE_NOT_LOCKED, MYF(0), table_name);
+    return NULL;
   }
   else
   {
-    while (tab->reginfo.lock_type < TL_WRITE_LOW_PRIORITY &&
+    while (tab->mdl_ticket != NULL &&
+           !tab->mdl_ticket->is_upgradable_or_exclusive() &&
            (tab= find_locked_table(tab->next, db, table_name)))
       continue;
     if (!tab)
     {
-      my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_name);
+      if (!no_error)
+        my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_name);
       return 0;
     }
   }
@@ -3016,34 +3031,6 @@ TABLE *find_write_locked_table(TABLE *list, const char *db, const char *table_na
 }
 
 
-/**
-   Find instance of TABLE with MDL_SHARED_UPGRADABLE or
-   MDL_EXCLUSIVE lock from the list of open tables.
-
-   @param list       List of TABLE objects to be searched
-   @param db         Database name.
-   @param table_name Name of table.
-
-   @return Pointer to MDL_SHARED_UPGRADABLE or MDL_EXCLUSIVE
-           TABLE instance, NULL otherwise.
-*/
-
-TABLE *find_table_for_mdl_upgrade(TABLE *list, const char *db,
-                                  const char *table_name)
-{
-  TABLE *tab= find_locked_table(list, db, table_name);
-
-  while (tab != NULL)
-  {
-    if (tab->mdl_ticket != NULL &&
-        tab->mdl_ticket->is_upgradable_or_exclusive())
-      return tab;
-    tab= find_locked_table(tab->next, db, table_name);
-  }
-  return NULL;
-}
-
-
 /***********************************************************************
   class Locked_tables_list implementation. Declared in sql_class.h
 ************************************************************************/
@@ -3741,9 +3728,9 @@ end_with_lock_open:
 Open_table_context::Open_table_context(THD *thd)
   :m_action(OT_NO_ACTION),
    m_start_of_statement_svp(thd->mdl_context.mdl_savepoint()),
-   m_has_locks((thd->in_multi_stmt_transaction() ||
-                thd->mdl_context.lt_or_ha_sentinel()) &&
-               thd->mdl_context.has_locks()),
+   m_has_locks((thd->in_multi_stmt_transaction() &&
+                thd->mdl_context.has_locks()) ||
+                thd->mdl_context.trans_sentinel()),
    m_global_mdl_request(NULL)
 {}
 
@@ -3798,7 +3785,7 @@ request_backoff_action(enum_open_table_action action_arg)
     Waiting will be done after releasing metadata locks acquired
     by this statement.
   */
-  if (m_has_locks && action_arg != OT_WAIT)
+  if (m_has_locks && action_arg != OT_WAIT_MDL_LOCK)
   {
     my_error(ER_LOCK_DEADLOCK, MYF(0));
     return TRUE;
@@ -3838,33 +3825,28 @@ recover_from_failed_open(THD *thd, MDL_request *mdl_request,
   /* Execute the action. */
   switch (m_action)
   {
-    case OT_WAIT:
-      result= (thd->mdl_context.wait_for_locks(&m_mdl_requests) ||
-               tdc_wait_for_old_versions(thd, &m_mdl_requests));
+    case OT_WAIT_MDL_LOCK:
+      result= thd->mdl_context.wait_for_lock(mdl_request);
+      break;
+    case OT_WAIT_TDC:
+      result= tdc_wait_for_old_versions(thd, &m_mdl_requests);
       DBUG_ASSERT(thd->mysys_var->current_mutex == NULL);
       break;
     case OT_DISCOVER:
       {
         MDL_request mdl_global_request;
         MDL_request mdl_xlock_request(mdl_request);
+        MDL_request_list mdl_requests;
 
         mdl_global_request.init(MDL_key::GLOBAL, "", "",
                                 MDL_INTENTION_EXCLUSIVE);
         mdl_xlock_request.set_type(MDL_EXCLUSIVE);
 
+        mdl_requests.push_front(&mdl_xlock_request);
+        mdl_requests.push_front(&mdl_global_request);
 
-        if ((result= thd->mdl_context.acquire_global_intention_exclusive_lock(
-                                        &mdl_global_request)))
-          break;
-
-        if ((result=
-             thd->mdl_context.acquire_exclusive_lock(&mdl_xlock_request)))
-        {
-          /*
-            We rely on close_thread_tables() to release global lock eventually.
-          */
+        if ((result= thd->mdl_context.acquire_locks(&mdl_requests)))
           break;
-        }
 
         DBUG_ASSERT(mdl_request->key.mdl_namespace() == MDL_key::TABLE);
         pthread_mutex_lock(&LOCK_open);
@@ -3885,23 +3867,17 @@ recover_from_failed_open(THD *thd, MDL_request *mdl_request,
       {
         MDL_request mdl_global_request;
         MDL_request mdl_xlock_request(mdl_request);
+        MDL_request_list mdl_requests;
 
         mdl_global_request.init(MDL_key::GLOBAL, "", "",
                                 MDL_INTENTION_EXCLUSIVE);
         mdl_xlock_request.set_type(MDL_EXCLUSIVE);
 
-        if ((result= thd->mdl_context.acquire_global_intention_exclusive_lock(
-                                        &mdl_global_request)))
-          break;
+        mdl_requests.push_front(&mdl_xlock_request);
+        mdl_requests.push_front(&mdl_global_request);
 
-        if ((result=
-             thd->mdl_context.acquire_exclusive_lock(&mdl_xlock_request)))
-        {
-          /*
-            We rely on close_thread_tables() to release global lock eventually.
-          */
+        if ((result= thd->mdl_context.acquire_locks(&mdl_requests)))
           break;
-        }
 
         DBUG_ASSERT(mdl_request->key.mdl_namespace() == MDL_key::TABLE);
         pthread_mutex_lock(&LOCK_open);
@@ -4015,13 +3991,13 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx,
         */
         DBUG_ASSERT(rt->mdl_request.type == MDL_SHARED);
 
-        if (thd->mdl_context.try_acquire_shared_lock(&rt->mdl_request))
+        if (thd->mdl_context.try_acquire_lock(&rt->mdl_request))
           DBUG_RETURN(TRUE);
 
         if (rt->mdl_request.ticket == NULL)
         {
           /* A lock conflict. Someone's trying to modify SP metadata. */
-          ot_ctx->request_backoff_action(Open_table_context::OT_WAIT);
+          ot_ctx->request_backoff_action(Open_table_context::OT_WAIT_MDL_LOCK);
           DBUG_RETURN(TRUE);
         }
         DEBUG_SYNC(thd, "after_shared_lock_pname");
@@ -4345,6 +4321,66 @@ end:
 
 
 /**
+  Acquire upgradable (SNW, SNRW) metadata locks on tables to be opened
+  for LOCK TABLES or a DDL statement.
+
+  @param thd           Thread context.
+  @param tables_start  Start of list of tables on which upgradable locks
+                       should be acquired.
+  @param tables_end    End of list of tables.
+  @param ot_ctx        Context of open_tables() operation.
+
+  @retval FALSE  Success.
+  @retval TRUE   Failure (e.g. connection was killed)
+*/
+
+static bool
+open_tables_acquire_upgradable_mdl(THD *thd, TABLE_LIST *tables_start,
+                                   TABLE_LIST *tables_end,
+                                   Open_table_context *ot_ctx)
+{
+  MDL_request_list mdl_requests;
+  TABLE_LIST *table;
+
+  for (table= tables_start; table && table != tables_end;
+       table= table->next_global)
+  {
+    if (table->lock_type >= TL_WRITE_ALLOW_WRITE)
+    {
+      table->mdl_request.set_type(table->lock_type > TL_WRITE_ALLOW_READ ?
+                                  MDL_SHARED_NO_READ_WRITE :
+                                  MDL_SHARED_NO_WRITE);
+      mdl_requests.push_front(&table->mdl_request);
+    }
+  }
+
+  if (! mdl_requests.is_empty())
+  {
+    MDL_request *global_request= ot_ctx->get_global_mdl_request(thd);
+
+    if (global_request == NULL)
+      return TRUE;
+    mdl_requests.push_front(global_request);
+  }
+
+  if (thd->mdl_context.acquire_locks(&mdl_requests))
+    return TRUE;
+
+  for (table= tables_start; table && table != tables_end;
+       table= table->next_global)
+  {
+    if (table->lock_type >= TL_WRITE_ALLOW_WRITE)
+    {
+      table->mdl_request.ticket= NULL;
+      table->mdl_request.set_type(MDL_SHARED_WRITE);
+    }
+  }
+
+  return FALSE;
+}
+
+
+/**
   Open all tables in list
 
   @param[in]     thd      Thread context.
@@ -4419,6 +4455,29 @@ restart:
   thd_proc_info(thd, "Opening tables");
 
   /*
+    If we are executing LOCK TABLES statement or a DDL statement
+    (in non-LOCK TABLES mode) we might have to acquire upgradable
+    semi-exclusive metadata locks (SNW or SNRW) on some of the
+    tables to be opened.
+    So we acquire all such locks at once here as doing this in one
+    by one fashion may lead to deadlocks or starvation. Later when
+    we will be opening corresponding table pre-acquired metadata
+    lock will be reused (thanks to the fact that in recursive case
+    metadata locks are acquired without waiting).
+  */
+  if ((flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) &&
+      ! thd->locked_tables_mode)
+  {
+    if (open_tables_acquire_upgradable_mdl(thd, *start,
+                                           thd->lex->first_not_own_table(),
+                                           &ot_ctx))
+    {
+      error= TRUE;
+      goto err;
+    }
+  }
+
+  /*
     Perform steps of prelocking algorithm until there are unprocessed
     elements in prelocking list/set.
   */
@@ -4922,8 +4981,8 @@ retry:
   while ((error= open_table(thd, table_list, thd->mem_root, &ot_ctx, 0)) &&
          ot_ctx.can_recover_from_failed_open())
   {
-    /* We never have an open HANDLER or LOCK TABLES here. */
-    DBUG_ASSERT(thd->mdl_context.lt_or_ha_sentinel() == NULL);
+    /* We never have an open HANDLER, LOCK TABLES or GRL here. */
+    DBUG_ASSERT(thd->mdl_context.trans_sentinel() == NULL);
     /*
       Even though we have failed to open table we still need to
       call release_transactional_locks() to release metadata locks which
@@ -4974,8 +5033,8 @@ retry:
             close_thread_tables(thd);
             table_list->table= NULL;
             table_list->mdl_request.ticket= NULL;
-            /* We never have an open HANDLER or LOCK TABLES here. */
-            DBUG_ASSERT(thd->mdl_context.lt_or_ha_sentinel() == NULL);
+            /* We never have an open HANDLER, LOCK TABLES or GRL here. */
+            DBUG_ASSERT(thd->mdl_context.trans_sentinel() == NULL);
             thd->mdl_context.rollback_to_savepoint(ot_ctx.start_of_statement_svp());
             goto retry;
           }
@@ -8459,15 +8518,19 @@ void flush_tables()
 
    @param thd    Current thread context
    @param in_use The thread to wake up
+   @param needs_thr_lock_abort Indicates that to wake up thread
+                               this call needs to abort its waiting
+                               on table-level lock.
 
    @retval  TRUE  if the thread was woken up
-   @retval  FALSE otherwise (e.g. it was not waiting for a table-level lock).
+   @retval  FALSE otherwise.
 
    @note It is one of two places where border between MDL and the
          rest of the server is broken.
 */
 
-bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use)
+bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use,
+                                            bool needs_thr_lock_abort)
 {
   bool signalled= FALSE;
   if ((in_use->system_thread & SYSTEM_THREAD_DELAYED_INSERT) &&
@@ -8481,19 +8544,23 @@ bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use)
     signalled= TRUE;
   }
   pthread_mutex_lock(&LOCK_open);
-  for (TABLE *thd_table= in_use->open_tables;
-       thd_table ;
-       thd_table= thd_table->next)
+
+  if (needs_thr_lock_abort)
   {
-    /*
-      Check for TABLE::needs_reopen() is needed since in some places we call
-      handler::close() for table instance (and set TABLE::db_stat to 0)
-      and do not remove such instances from the THD::open_tables
-      for some time, during which other thread can see those instances
-      (e.g. see partitioning code).
-    */
-    if (!thd_table->needs_reopen())
-      signalled|= mysql_lock_abort_for_thread(thd, thd_table);
+    for (TABLE *thd_table= in_use->open_tables;
+         thd_table ;
+         thd_table= thd_table->next)
+    {
+      /*
+        Check for TABLE::needs_reopen() is needed since in some places we call
+        handler::close() for table instance (and set TABLE::db_stat to 0)
+        and do not remove such instances from the THD::open_tables
+        for some time, during which other thread can see those instances
+        (e.g. see partitioning code).
+        */
+      if (!thd_table->needs_reopen())
+        signalled|= mysql_lock_abort_for_thread(thd, thd_table);
+    }
   }
   /*
     Wake up threads waiting in tdc_wait_for_old_versions().
@@ -8512,28 +8579,6 @@ bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use)
 
 
 /**
-  Force transactions holding shared metadata lock on the table to call
-  MDL_context::can_wait_lead_to_deadlock() even if they don't need any
-  new metadata locks so they can detect potential deadlocks between
-  metadata locking subsystem and table-level locks.
-
-  @param mdl_key MDL key for the table on which we are upgrading
-                 metadata lock.
-*/
-
-void mysql_abort_transactions_with_shared_lock(const MDL_key *mdl_key)
-{
-  if (mdl_key->mdl_namespace() == MDL_key::TABLE)
-  {
-    pthread_mutex_lock(&LOCK_open);
-    tdc_remove_table(NULL, TDC_RT_REMOVE_UNUSED, mdl_key->db_name(),
-                     mdl_key->name());
-    pthread_mutex_unlock(&LOCK_open);
-  }
-}
-
-
-/**
    Remove all or some (depending on parameter) instances of TABLE and
    TABLE_SHARE from the table definition cache.
 
@@ -8574,8 +8619,8 @@ void tdc_remove_table(THD *thd, enum_tdc_remove_table_type remove_type,
   safe_mutex_assert_owner(&LOCK_open);
 
   DBUG_ASSERT(remove_type == TDC_RT_REMOVE_UNUSED ||
-              thd->mdl_context.is_exclusive_lock_owner(MDL_key::TABLE,
-                                                       db, table_name));
+              thd->mdl_context.is_lock_owner(MDL_key::TABLE, db, table_name,
+                                             MDL_EXCLUSIVE));
 
   key_length=(uint) (strmov(strmov(key,db)+1,table_name)-key)+1;
 
@@ -8639,24 +8684,6 @@ tdc_wait_for_old_versions(THD *thd, MDL_request_list *mdl_requests)
     */
     mysql_ha_flush(thd);
 
-    /*
-      Check if there is someone waiting for one of metadata locks
-      held by this connection and return an error if that's the
-      case, since this situation may lead to a deadlock.
-      This can happen, when, for example, this connection is
-      waiting for an old version of some table to go away and
-      another connection is trying to upgrade its shared
-      metadata lock to exclusive, and thus is waiting
-      for this to release its lock. We must check for
-      the condition on each iteration of the loop to remove
-      any window for a race.
-    */
-    if (thd->mdl_context.can_wait_lead_to_deadlock())
-    {
-      my_error(ER_LOCK_DEADLOCK, MYF(0));
-      return TRUE;
-    }
-
     pthread_mutex_lock(&LOCK_open);
 
     MDL_request_list::Iterator it(*mdl_requests);