MDEV-11227 - mysqlimport -l doesn't issue UNLOCK TABLES

Implementation of MDEV-7660 introduced unwanted incompatible change:
modifications under LOCK TABLES with autocommit enabled are rolled back on
disconnect. Previously everything was committed, because LOCK TABLES didn't
adjust autocommit setting.

This patch restores original behavior by reverting some changes done in
MDEV-7660:
- sql/sql_parse.cc: do not reset autocommit on LOCK TABLES
- sql/sql_base.cc: do not set autocommit on UNLOCK TABLES
- test cases: main.lock_tables_lost_commit, main.partition_explicit_prune,
  rpl.rpl_switch_stm_row_mixed, tokudb.nested_txn_implicit_commit,
  tokudb_bugs.db806

But it makes InnoDB tables under LOCK TABLES ... READ [LOCAL] not protected
against DML. To restore protection some changes from WL#6671 were merged,
specifically MDL_SHARED_READ_ONLY and test cases.

WL#6671 merge highlights:
- Not all tests merged.
- In MySQL LOCK TABLES ... READ acquires MDL_SHARED_READ_ONLY for all engines,
  in MariaDB MDL_SHARED_READ is always acquired first and then upgraded to
  MDL_SHARED_READ_ONLY for InnoDB only.
- The above allows us to omit MDL_SHARED_WRITE_LOW_PRIO implementation in
  MariaDB, which is rather useless with InnoDB. In MySQL it is needed to
  preserve locking behavior between low priority writes and LOCK TABLES ... READ
  for non-InnoDB engines (covered by sys_vars.sql_low_priority_updates_func).
- Omitted HA_NO_READ_LOCAL_LOCK, we rely on lock_count() instead.
- Omitted "piglets": in MariaDB stream of DML against InnoDB table may lead to
  concurrent LOCK TABLES ... READ starvation.
- HANDLER ... OPEN acquires MDL_SHARED_READ instead of MDL_SHARED in MariaDB.
- Omitted SNRW->X MDL lock upgrade for IMPORT/DISCARD TABLESPAECE under LOCK
  TABLES.
- Omitted strong locks for views, triggers and SP under LOCK TABLES.
- Omitted IX schema lock for LOCK TABLES READ.
- Omitted deadlock weight juggling for LOCK TABLES.

Full WL#6671 merge status:
- innodb.innodb-lock: fully merged
- main.alter_table: not merged due to different HANDLER solution
- main.debug_sync: fully merged
- main.handler_innodb: not merged due to different HANDLER solution
- main.handler_myisam: not merged due to different HANDLER solution
- main.innodb_mysql_lock: fully merged
- main.insert_notembedded: fully merged
- main.lock: not merged (due to no strong locks for views)
- main.lock_multi: not merged
- main.lock_sync: fully merged (partially in MDEV-7660)
- main.mdl_sync: not merged
- main.partition_debug_sync: not merged due to different HANDLER solution
- main.status: fully merged
- main.view: fully merged
- perfschema.mdl_func: not merged (no such test in MariaDB)
- perfschema.table_aggregate_global_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_global_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_global_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_global_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_4u_3t: not merged (didn't fail in MariaDB)
- sys_vars.sql_low_priority_updates_func: not merged
- include/thr_rwlock.h: not merged, rw_pr_lock_assert_write_owner and
  rw_pr_lock_assert_not_write_owner are macros in MariaDB
- sql/handler.h: not merged (HA_NO_READ_LOCAL_LOCK)
- sql/mdl.cc: partially merged (MDL_SHARED_READ_ONLY only)
- sql/mdl.h: partially merged (MDL_SHARED_READ_ONLY only)
- sql/lock.cc: fully merged
- sql/sp_head.cc: not merged
- sql/sp_head.h: not merged
- sql/sql_base.cc: partially merged (MDL_SHARED_READ_ONLY only)
- sql/sql_base.h: not merged
- sql/sql_class.cc: fully merged
- sql/sql_class.h: fully merged
- sql/sql_handler.cc: merged partially (different solution in MariaDB)
- sql/sql_parse.cc: partially merged, mostly omitted low priority write part
- sql/sql_reload.cc: not merged comment change
- sql/sql_table.cc: not merged SNRW->X upgrade for IMPORT/DISCARD TABLESPACE
- sql/sql_view.cc: not merged
- sql/sql_yacc.yy: not merged (MDL_SHARED_WRITE_LOW_PRIO, MDL_SHARED_READ_ONLY)
- sql/table.cc: not merged (MDL_SHARED_WRITE_LOW_PRIO)
- sql/table.h: not merged (MDL_SHARED_WRITE_LOW_PRIO)
- sql/trigger.cc: not merged
- storage/innobase/handler/ha_innodb.cc: merged store_lock()/lock_count()
  changes (in MDEV-7660), didn't merge HA_NO_READ_LOCAL_LOCK
- storage/innobase/handler/ha_innodb.h: fully merged in MDEV-7660
- storage/myisammrg/ha_myisammrg.cc: not merged comment change
- storage/perfschema/table_helper.cc: not merged (no MDL support in MariaDB PFS)
- unittest/gunit/mdl-t.cc: not merged
- unittest/gunit/mdl_sync-t.cc: not merged

MariaDB specific changes:
- handler.heap: different HANDLER solution, MDEV-7660
- handler.innodb: different HANDLER solution, MDEV-7660
- handler.interface: different HANDLER solution, MDEV-7660
- handler.myisam: different HANDLER solution, MDEV-7660
- main.mdl_sync: MDEV-7660 specific changes
- main.partition_debug_sync: removed test due to different HANDLER solution,
  MDEV-7660
- main.truncate_coverage: removed test due to different HANDLER solution,
  MDEV-7660
- mysql-test/include/mtr_warnings.sql: additional cleanup, MDEV-7660
- mysql-test/lib/v1/mtr_report.pl: additional cleanup, MDEV-7660
- plugin/metadata_lock_info/metadata_lock_info.cc: not in MySQL
- sql/sql_handler.cc: MariaDB specific fix for mysql_ha_read(), MDEV-7660

8 files changed, 190 insertions, 118 deletions

diff --git a/sql/lock.cc b/sql/lock.cc
index 2de2a80c95a..a51c34365fa 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -164,18 +164,12 @@ lock_tables_check(THD *thd, TABLE **tables, uint count, uint flags)
       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)));
+                                 MDL_SHARED_WRITE : MDL_SHARED_READ));
 
     /*
       Prevent modifications to base tables if READ_ONLY is activated.
diff --git a/sql/mdl.cc b/sql/mdl.cc
index 1d6b4f6ffc3..c05fdc0157b 100644
--- a/sql/mdl.cc
+++ b/sql/mdl.cc
@@ -391,7 +391,11 @@ public:
     virtual const bitmap_t *incompatible_waiting_types_bitmap() const
     { return m_waiting_incompatible; }
     virtual bool needs_notification(const MDL_ticket *ticket) const
-    { return (ticket->get_type() >= MDL_SHARED_NO_WRITE); }
+    {
+      return ticket->get_type() == MDL_SHARED_NO_WRITE ||
+             ticket->get_type() == MDL_SHARED_NO_READ_WRITE ||
+             ticket->get_type() == MDL_EXCLUSIVE;
+    }
 
     /**
       Notify threads holding a shared metadata locks on object which
@@ -1413,7 +1417,8 @@ const MDL_lock::bitmap_t
 MDL_lock::MDL_scoped_lock::m_granted_incompatible[MDL_TYPE_END]=
 {
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED),
-  MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_INTENTION_EXCLUSIVE), 0, 0, 0, 0, 0, 0,
+  MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_INTENTION_EXCLUSIVE),
+  0, 0, 0, 0, 0, 0, 0,
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED) | MDL_BIT(MDL_INTENTION_EXCLUSIVE)
 };
 
@@ -1421,7 +1426,7 @@ const MDL_lock::bitmap_t
 MDL_lock::MDL_scoped_lock::m_waiting_incompatible[MDL_TYPE_END]=
 {
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED),
-  MDL_BIT(MDL_EXCLUSIVE), 0, 0, 0, 0, 0, 0, 0
+  MDL_BIT(MDL_EXCLUSIVE), 0, 0, 0, 0, 0, 0, 0, 0
 };
 
 
@@ -1433,39 +1438,41 @@ MDL_lock::MDL_scoped_lock::m_waiting_incompatible[MDL_TYPE_END]=
   The first array specifies if particular type of request can be satisfied
   if there is granted lock of certain type.
 
-     Request  |  Granted requests for lock       |
-      type    | S  SH  SR  SW  SU  SNW  SNRW  X  |
-    ----------+----------------------------------+
-    S         | +   +   +   +   +   +    +    -  |
-    SH        | +   +   +   +   +   +    +    -  |
-    SR        | +   +   +   +   +   +    -    -  |
-    SW        | +   +   +   +   +   -    -    -  |
-    SU        | +   +   +   +   -   -    -    -  |
-    SNW       | +   +   +   -   -   -    -    -  |
-    SNRW      | +   +   -   -   -   -    -    -  |
-    X         | -   -   -   -   -   -    -    -  |
-    SU -> X   | -   -   -   -   0   0    0    0  |
-    SNW -> X  | -   -   -   0   0   0    0    0  |
-    SNRW -> X | -   -   0   0   0   0    0    0  |
+     Request  |  Granted requests for lock            |
+      type    | S  SH  SR  SW  SU  SRO  SNW  SNRW  X  |
+    ----------+---------------------------------------+
+    S         | +   +   +   +   +   +    +    +    -  |
+    SH        | +   +   +   +   +   +    +    +    -  |
+    SR        | +   +   +   +   +   +    +    -    -  |
+    SW        | +   +   +   +   +   -    -    -    -  |
+    SU        | +   +   +   +   -   +    -    -    -  |
+    SRO       | +   +   +   -   +   +    +    -    -  |
+    SNW       | +   +   +   -   -   +    -    -    -  |
+    SNRW      | +   +   -   -   -   -    -    -    -  |
+    X         | -   -   -   -   -   -    -    -    -  |
+    SU -> X   | -   -   -   -   0   -    0    0    0  |
+    SNW -> X  | -   -   -   0   0   -    0    0    0  |
+    SNRW -> X | -   -   0   0   0   0    0    0    0  |
 
   The second array specifies if particular type of request can be satisfied
   if there is waiting request for the same lock of certain type. In other
   words it specifies what is the priority of different lock types.
 
-     Request  |  Pending requests for lock      |
-      type    | S  SH  SR  SW  SU  SNW  SNRW  X |
-    ----------+---------------------------------+
-    S         | +   +   +   +   +   +     +   - |
-    SH        | +   +   +   +   +   +     +   + |
-    SR        | +   +   +   +   +   +     -   - |
-    SW        | +   +   +   +   +   -     -   - |
-    SU        | +   +   +   +   +   +     +   - |
-    SNW       | +   +   +   +   +   +     +   - |
-    SNRW      | +   +   +   +   +   +     +   - |
-    X         | +   +   +   +   +   +     +   + |
-    SU -> X   | +   +   +   +   +   +     +   + |
-    SNW -> X  | +   +   +   +   +   +     +   + |
-    SNRW -> X | +   +   +   +   +   +     +   + |
+     Request  |  Pending requests for lock           |
+      type    | S  SH  SR  SW  SU  SRO  SNW  SNRW  X |
+    ----------+--------------------------------------+
+    S         | +   +   +   +   +   +    +     +   - |
+    SH        | +   +   +   +   +   +    +     +   + |
+    SR        | +   +   +   +   +   +    +     -   - |
+    SW        | +   +   +   +   +   +    -     -   - |
+    SU        | +   +   +   +   +   +    +     +   - |
+    SRO       | +   +   +   -   +   +    +     -   - |
+    SNW       | +   +   +   +   +   +    +     +   - |
+    SNRW      | +   +   +   +   +   +    +     +   - |
+    X         | +   +   +   +   +   +    +     +   + |
+    SU -> X   | +   +   +   +   +   +    +     +   + |
+    SNW -> X  | +   +   +   +   +   +    +     +   + |
+    SNRW -> X | +   +   +   +   +   +    +     +   + |
 
   Here: "+" -- means that request can be satisfied
         "-" -- means that request can't be satisfied and should wait
@@ -1487,19 +1494,23 @@ MDL_lock::MDL_object_lock::m_granted_incompatible[MDL_TYPE_END]=
   MDL_BIT(MDL_EXCLUSIVE),
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE),
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
-    MDL_BIT(MDL_SHARED_NO_WRITE),
+    MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_READ_ONLY),
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
     MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_UPGRADABLE),
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
-    MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_UPGRADABLE) |
     MDL_BIT(MDL_SHARED_WRITE),
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
     MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_UPGRADABLE) |
-    MDL_BIT(MDL_SHARED_WRITE) | MDL_BIT(MDL_SHARED_READ),
+    MDL_BIT(MDL_SHARED_WRITE),
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
-    MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_UPGRADABLE) |
-    MDL_BIT(MDL_SHARED_WRITE) | MDL_BIT(MDL_SHARED_READ) |
-    MDL_BIT(MDL_SHARED_HIGH_PRIO) | MDL_BIT(MDL_SHARED)
+    MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_READ_ONLY) |
+    MDL_BIT(MDL_SHARED_UPGRADABLE) | MDL_BIT(MDL_SHARED_WRITE) |
+    MDL_BIT(MDL_SHARED_READ),
+  MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
+    MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_READ_ONLY) |
+    MDL_BIT(MDL_SHARED_UPGRADABLE) | MDL_BIT(MDL_SHARED_WRITE) |
+    MDL_BIT(MDL_SHARED_READ) | MDL_BIT(MDL_SHARED_HIGH_PRIO) |
+    MDL_BIT(MDL_SHARED)
 };
 
 
@@ -1513,6 +1524,8 @@ MDL_lock::MDL_object_lock::m_waiting_incompatible[MDL_TYPE_END]=
   MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
     MDL_BIT(MDL_SHARED_NO_WRITE),
   MDL_BIT(MDL_EXCLUSIVE),
+  MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) |
+    MDL_BIT(MDL_SHARED_WRITE),
   MDL_BIT(MDL_EXCLUSIVE),
   MDL_BIT(MDL_EXCLUSIVE),
   0
@@ -2306,10 +2319,11 @@ MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,
   if (mdl_ticket->has_stronger_or_equal_type(new_type))
     DBUG_RETURN(FALSE);
 
-  /* Only allow upgrades from SHARED_UPGRADABLE/NO_WRITE/NO_READ_WRITE */
+  /* Only allow upgrades from SHARED_UPGRADABLE/NO_WRITE/NO_READ_WRITE/READ */
   DBUG_ASSERT(mdl_ticket->m_type == MDL_SHARED_UPGRADABLE ||
               mdl_ticket->m_type == MDL_SHARED_NO_WRITE ||
-              mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE);
+              mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE ||
+              mdl_ticket->m_type == MDL_SHARED_READ);
 
   mdl_xlock_request.init(&mdl_ticket->m_lock->key, new_type,
                          MDL_TRANSACTION);
diff --git a/sql/mdl.h b/sql/mdl.h
index 7d659af86bc..97216b8e7b1 100644
--- a/sql/mdl.h
+++ b/sql/mdl.h
@@ -196,6 +196,12 @@ enum enum_mdl_type {
   */
   MDL_SHARED_UPGRADABLE,
   /*
+    A shared metadata lock for cases when we need to read data from table
+    and block all concurrent modifications to it (for both data and metadata).
+    Used by LOCK TABLES READ statement.
+  */
+  MDL_SHARED_READ_ONLY,
+  /*
     An upgradable shared metadata lock which blocks all attempts to update
     table data, allowing reads.
     A connection holding this kind of lock can read table metadata and read
@@ -467,6 +473,19 @@ public:
     type= type_arg;
   }
 
+  /**
+    Is this a request for a lock which allow data to be updated?
+
+    @note This method returns true for MDL_SHARED_UPGRADABLE type of
+          lock. Even though this type of lock doesn't allow updates
+          it will always be upgraded to one that does.
+  */
+  bool is_write_lock_request() const
+  {
+    return (type >= MDL_SHARED_WRITE &&
+            type != MDL_SHARED_READ_ONLY);
+  }
+
   /*
     This is to work around the ugliness of TABLE_LIST
     compiler-generated assignment operator. It is currently used
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 5e9ca14c2db..e947e9dd90a 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1495,7 +1495,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, Open_table_context *ot_ctx)
     Note that we allow write locks on log tables as otherwise logging
     to general/slow log would be disabled in read only transactions.
   */
-  if (table_list->mdl_request.type >= MDL_SHARED_WRITE &&
+  if (table_list->mdl_request.is_write_lock_request() &&
       thd->tx_read_only &&
       !(flags & (MYSQL_LOCK_LOG_TABLE | MYSQL_OPEN_HAS_MDL_LOCK)))
   {
@@ -1654,7 +1654,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, Open_table_context *ot_ctx)
             pre-acquiring metadata locks at the beggining of
             open_tables() call.
     */
-    if (table_list->mdl_request.type >= MDL_SHARED_WRITE &&
+    if (table_list->mdl_request.is_write_lock_request() &&
         ! (flags & (MYSQL_OPEN_IGNORE_GLOBAL_READ_LOCK |
                     MYSQL_OPEN_FORCE_SHARED_MDL |
                     MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL |
@@ -2146,11 +2146,6 @@ Locked_tables_list::unlock_locked_tables(THD *thd)
     request for metadata locks and TABLE_LIST elements.
   */
   reset();
-  if (thd->variables.option_bits & OPTION_AUTOCOMMIT)
-  {
-    thd->variables.option_bits&= ~(OPTION_NOT_AUTOCOMMIT);
-    thd->server_status|= SERVER_STATUS_AUTOCOMMIT;
-  }
 }
 
 
@@ -3605,6 +3600,7 @@ lock_table_names(THD *thd, const DDL_options_st &options,
        table= table->next_global)
   {
     if (table->mdl_request.type < MDL_SHARED_UPGRADABLE ||
+        table->mdl_request.type == MDL_SHARED_READ_ONLY ||
         table->open_type == OT_TEMPORARY_ONLY ||
         (table->open_type == OT_TEMPORARY_OR_BASE && is_temporary_table(table)))
     {
@@ -3728,6 +3724,11 @@ open_tables_check_upgradable_mdl(THD *thd, TABLE_LIST *tables_start,
   for (table= tables_start; table && table != tables_end;
        table= table->next_global)
   {
+    /*
+      Check below needs to be updated if this function starts
+      called for SRO locks.
+    */
+    DBUG_ASSERT(table->mdl_request.type != MDL_SHARED_READ_ONLY);
     if (table->mdl_request.type < MDL_SHARED_UPGRADABLE ||
         table->open_type == OT_TEMPORARY_ONLY ||
         (table->open_type == OT_TEMPORARY_OR_BASE && is_temporary_table(table)))
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 1b6692d9d2c..d7d2944ba89 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -651,6 +651,28 @@ bool Drop_table_error_handler::handle_condition(THD *thd,
 
 
 /**
+  Handle an error from MDL_context::upgrade_lock() and mysql_lock_tables().
+  Ignore ER_LOCK_ABORTED and ER_LOCK_DEADLOCK errors.
+*/
+
+bool
+MDL_deadlock_and_lock_abort_error_handler::
+handle_condition(THD *thd,
+                 uint sql_errno,
+                 const char *sqlstate,
+                 Sql_condition::enum_warning_level level,
+                 const char* msg,
+                 Sql_condition **cond_hdl)
+{
+  *cond_hdl= NULL;
+  if (sql_errno == ER_LOCK_ABORTED || sql_errno == ER_LOCK_DEADLOCK)
+    m_need_reopen= true;
+
+  return m_need_reopen;
+}
+
+
+/**
    Send timeout to thread.
 
    Note that this is always safe as the thread will always remove it's
diff --git a/sql/sql_class.h b/sql/sql_class.h
index f2e6f131f5f..f0052f8fe57 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1745,6 +1745,30 @@ private:
 
 
 /**
+  Internal error handler to process an error from MDL_context::upgrade_lock()
+  and mysql_lock_tables(). Used by implementations of HANDLER READ and
+  LOCK TABLES LOCAL.
+*/
+
+class MDL_deadlock_and_lock_abort_error_handler: public Internal_error_handler
+{
+public:
+  virtual
+  bool handle_condition(THD *thd,
+                        uint sql_errno,
+                        const char *sqlstate,
+                        Sql_condition::enum_warning_level level,
+                        const char* msg,
+                        Sql_condition **cond_hdl);
+
+  bool need_reopen() const { return m_need_reopen; };
+  void init() { m_need_reopen= FALSE; };
+private:
+  bool m_need_reopen;
+};
+
+
+/**
   Tables that were locked with LOCK TABLES statement.
 
   Encapsulates a list of TABLE_LIST instances for tables
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index 735adeadb11..58db104ec96 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -486,56 +486,6 @@ bool mysql_ha_close(THD *thd, TABLE_LIST *tables)
 
 
 /**
-  A helper class to process an error from mysql_lock_tables().
-  HANDLER READ statement's attempt to lock the subject table
-  may get aborted if there is a pending DDL. In that case
-  we close the table, reopen it, and try to read again.
-  This is implicit and obscure, since HANDLER position
-  is lost in the process, but it's the legacy server
-  behaviour we should preserve.
-*/
-
-class Sql_handler_lock_error_handler: public Internal_error_handler
-{
-public:
-  virtual
-  bool handle_condition(THD *thd,
-                        uint sql_errno,
-                        const char *sqlstate,
-                        Sql_condition::enum_warning_level level,
-                        const char* msg,
-                        Sql_condition **cond_hdl);
-
-  bool need_reopen() const { return m_need_reopen; };
-  void init() { m_need_reopen= FALSE; };
-private:
-  bool m_need_reopen;
-};
-
-
-/**
-  Handle an error from mysql_lock_tables().
-  Ignore ER_LOCK_ABORTED errors.
-*/
-
-bool
-Sql_handler_lock_error_handler::
-handle_condition(THD *thd,
-                 uint sql_errno,
-                 const char *sqlstate,
-                 Sql_condition::enum_warning_level level,
-                 const char* msg,
-                 Sql_condition **cond_hdl)
-{
-  *cond_hdl= NULL;
-  if (sql_errno == ER_LOCK_ABORTED)
-    m_need_reopen= TRUE;
-
-  return m_need_reopen;
-}
-
-
-/**
    Finds an open HANDLER table.
 
    @params name		Name of handler to open
@@ -731,7 +681,7 @@ bool mysql_ha_read(THD *thd, TABLE_LIST *tables,
   int           error, keyno;
   uint          num_rows;
   uchar		*UNINIT_VAR(key);
-  Sql_handler_lock_error_handler sql_handler_lock_error;
+  MDL_deadlock_and_lock_abort_error_handler sql_handler_lock_error;
   DBUG_ENTER("mysql_ha_read");
   DBUG_PRINT("enter",("'%s'.'%s' as '%s'",
                       tables->db, tables->table_name, tables->alias));
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 796c8f3386e..370a4bd7400 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2728,27 +2728,76 @@ bool sp_process_definer(THD *thd)
 static bool lock_tables_open_and_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   Lock_tables_prelocking_strategy lock_tables_prelocking_strategy;
+  MDL_deadlock_and_lock_abort_error_handler deadlock_handler;
+  MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint();
   uint counter;
   TABLE_LIST *table;
 
   thd->in_lock_tables= 1;
 
+retry:
+
   if (open_tables(thd, &tables, &counter, 0, &lock_tables_prelocking_strategy))
     goto err;
 
-  /*
-    We allow to change temporary tables even if they were locked for read
-    by LOCK TABLES. To avoid a discrepancy between lock acquired at LOCK
-    TABLES time and by the statement which is later executed under LOCK TABLES
-    we ensure that for temporary tables we always request a write lock (such
-    discrepancy can cause problems for the storage engine).
-    We don't set TABLE_LIST::lock_type in this case as this might result in
-    extra warnings from THD::decide_logging_format() even though binary logging
-    is totally irrelevant for LOCK TABLES.
-  */
   for (table= tables; table; table= table->next_global)
-    if (!table->placeholder() && table->table->s->tmp_table)
-      table->table->reginfo.lock_type= TL_WRITE;
+  {
+    if (!table->placeholder())
+    {
+      if (table->table->s->tmp_table)
+      {
+        /*
+          We allow to change temporary tables even if they were locked for read
+          by LOCK TABLES. To avoid a discrepancy between lock acquired at LOCK
+          TABLES time and by the statement which is later executed under LOCK
+          TABLES we ensure that for temporary tables we always request a write
+          lock (such discrepancy can cause problems for the storage engine).
+          We don't set TABLE_LIST::lock_type in this case as this might result
+          in extra warnings from THD::decide_logging_format() even though
+          binary logging is totally irrelevant for LOCK TABLES.
+        */
+        table->table->reginfo.lock_type= TL_WRITE;
+      }
+      else if (table->mdl_request.type == MDL_SHARED_READ &&
+               ! table->prelocking_placeholder &&
+               table->table->file->lock_count() == 0)
+      {
+        /*
+          In case when LOCK TABLE ... READ LOCAL was issued for table with
+          storage engine which doesn't support READ LOCAL option and doesn't
+          use THR_LOCK locks we need to upgrade weak SR metadata lock acquired
+          in open_tables() to stronger SRO metadata lock.
+          This is not needed for tables used through stored routines or
+          triggers as we always acquire SRO (or even stronger SNRW) metadata
+          lock for them.
+        */
+        deadlock_handler.init();
+        thd->push_internal_handler(&deadlock_handler);
+
+        bool result= thd->mdl_context.upgrade_shared_lock(
+                                        table->table->mdl_ticket,
+                                        MDL_SHARED_READ_ONLY,
+                                        thd->variables.lock_wait_timeout);
+
+        thd->pop_internal_handler();
+
+        if (deadlock_handler.need_reopen())
+        {
+          /*
+            Deadlock occurred during upgrade of metadata lock.
+            Let us restart acquring and opening tables for LOCK TABLES.
+          */
+          close_tables_for_reopen(thd, &tables, mdl_savepoint);
+          if (thd->open_temporary_tables(tables))
+            goto err;
+          goto retry;
+        }
+
+        if (result)
+          goto err;
+      }
+    }
+  }
 
   if (lock_tables(thd, tables, counter, 0) ||
       thd->locked_tables_list.init_locked_tables(thd))
@@ -4789,8 +4838,7 @@ end_with_restore_list:
     if (lock_tables_precheck(thd, all_tables))
       goto error;
 
-    thd->variables.option_bits|= OPTION_TABLE_LOCK | OPTION_NOT_AUTOCOMMIT;
-    thd->server_status&= ~SERVER_STATUS_AUTOCOMMIT;
+    thd->variables.option_bits|= OPTION_TABLE_LOCK;
 
     res= lock_tables_open_and_lock_tables(thd, all_tables);