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

30 files changed, 535 insertions, 350 deletions

diff --git a/mysql-test/r/debug_sync.result b/mysql-test/r/debug_sync.result
index 8498a908000..bb9ae1a417d 100644
--- a/mysql-test/r/debug_sync.result
+++ b/mysql-test/r/debug_sync.result
@@ -265,17 +265,31 @@ connection default;
 DROP TABLE t1;
 SET DEBUG_SYNC= 'RESET';
 DROP TABLE IF EXISTS t1;
-CREATE TABLE t1 (c1 INT);
-LOCK TABLE t1 READ;
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+SELECT GET_LOCK('mysqltest_lock', 100);
+GET_LOCK('mysqltest_lock', 100)
+1
 connect  con1,localhost,root,,;
+# Sending:
+UPDATE t1 SET c1=GET_LOCK('mysqltest_lock', 100);;
+connect  con2,localhost,root,,;
 SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
 INSERT INTO t1 VALUES (1);
 connection default;
 SET DEBUG_SYNC= 'now WAIT_FOR locked';
-UNLOCK TABLES;
+SELECT RELEASE_LOCK('mysqltest_lock');
+RELEASE_LOCK('mysqltest_lock')
+1
 connection con1;
+# Reaping UPDATE
+SELECT RELEASE_LOCK('mysqltest_lock');
+RELEASE_LOCK('mysqltest_lock')
+1
+connection con2;
 retrieve INSERT result.
 disconnect con1;
+disconnect con2;
 connection default;
 DROP TABLE t1;
 SET DEBUG_SYNC= 'RESET';
diff --git a/mysql-test/r/innodb_mysql_lock.result b/mysql-test/r/innodb_mysql_lock.result
index 6d2eef2e62e..110fa50d544 100644
--- a/mysql-test/r/innodb_mysql_lock.result
+++ b/mysql-test/r/innodb_mysql_lock.result
@@ -36,6 +36,7 @@ connection con3;
 set @@autocommit=1;
 connection default;
 disconnect con1;
+disconnect con2;
 disconnect con3;
 #
 # Test for bug #37346 "innodb does not detect deadlock between update
@@ -72,34 +73,6 @@ connection default;
 disconnect con37346;
 drop table t1;
 #
-# Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB 
-#            table cause warnings in errlog
-#
-#
-# Note that this test for now relies on a global suppression of 
-# the warning "Found lock of type 6 that is write and read locked"
-# This suppression rule can be removed once Bug#42147 is properly
-# fixed. See bug page for more info.
-#
-DROP TABLE IF EXISTS t1;
-CREATE TABLE t1 (i INT) engine= innodb;
-# Get user-level lock
-connection con2;
-SELECT get_lock('bug42147_lock', 60);
-get_lock('bug42147_lock', 60)
-1
-connection default;
-INSERT INTO t1 SELECT get_lock('bug42147_lock', 60);
-connection con2;
-LOCK TABLES t1 READ;
-SELECT release_lock('bug42147_lock');
-release_lock('bug42147_lock')
-1
-UNLOCK TABLES;
-connection default;
-disconnect con2;
-DROP TABLE t1;
-#
 # Bug#53798 OPTIMIZE TABLE breaks repeatable read
 #
 DROP TABLE IF EXISTS t1;
diff --git a/mysql-test/r/insert_notembedded.result b/mysql-test/r/insert_notembedded.result
index d7ec70d36f8..d2733eac061 100644
--- a/mysql-test/r/insert_notembedded.result
+++ b/mysql-test/r/insert_notembedded.result
@@ -119,33 +119,5 @@ DROP USER  user20989@localhost;
 disconnect root;
 connection default;
 DROP DATABASE meow;
-connection: default
-set low_priority_updates=1;
-drop table if exists t1;
-create table t1 (a int, b int, unique key t1$a (a));
-lock table t1 read;
-connect  update,localhost,root,,;
-connection update;
-connection: update
-set low_priority_updates=1;
-show variables like 'low_priority_updates';
-Variable_name	Value
-low_priority_updates	ON
-insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;;
-connection default;
-connect  select,localhost,root,,;
-connection: select
-select * from t1;
-a	b
-connection default;
-connection: default
-select * from t1;
-a	b
-connection default;
-disconnect update;
-disconnect select;
-unlock tables;
-drop table t1;
-set low_priority_updates=default;
 set local sql_mode=default;
 set global sql_mode=default;
diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result
index 4cd726f0efc..7b61c5994b6 100644
--- a/mysql-test/r/lock_sync.result
+++ b/mysql-test/r/lock_sync.result
@@ -784,3 +784,74 @@ DROP VIEW v1;
 DROP TABLE t1;
 disconnect con1;
 disconnect con2;
+#
+# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
+#
+set low_priority_updates=1;
+drop table if exists t1;
+drop table if exists t2;
+set debug_sync='RESET';
+create table t1 (a int, b int, unique key t1$a (a));
+create table t2 (j int, k int);
+set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go';
+# Sending:
+insert into t2 select * from t1;;
+connect  update,localhost,root,,;
+connection update;
+set debug_sync='now WAIT_FOR parked';
+set low_priority_updates=1;
+show variables like 'low_priority_updates';
+Variable_name	Value
+low_priority_updates	ON
+insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;;
+connect  select,localhost,root,,;
+select * from t1;
+a	b
+set debug_sync='now SIGNAL go';
+connection default;
+disconnect update;
+disconnect select;
+# Reaping INSERT SELECT
+drop tables t1, t2;
+set low_priority_updates=default;
+set debug_sync='RESET';
+#
+# Additional test coverage for LOCK TABLES ... READ LOCAL
+# for InnoDB tables.
+#
+# Check that we correctly handle deadlocks which can occur
+# during metadata lock upgrade which happens when one tries
+# to use LOCK TABLES ... READ LOCAL for InnoDB tables.
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+CREATE TABLE t2 (j INT) ENGINE=InnoDB;
+# Execute LOCK TABLE READ LOCK which will pause after acquiring
+# SR metadata lock and before upgrading it to SRO lock.
+SET DEBUG_SYNC="after_open_table_mdl_shared SIGNAL locked WAIT_FOR go";
+# Sending:
+LOCK TABLE t1 READ LOCAL;
+connect  con1, localhost, root;
+SET DEBUG_SYNC="now WAIT_FOR locked";
+# Execute RENAME TABLE which will try to acquire X lock.
+# Sending:
+RENAME TABLE t1 TO t3, t2 TO t1, t3 TO t2;
+connect  con2, localhost, root;
+# Wait until RENAME TABLE is blocked.
+# Resume LOCK TABLE statement. It should try to
+# upgrade SR lock to SRO lock which will create
+# deadlock due to presence of pending X lock.
+# Deadlock should be detected and LOCK TABLES should
+# release its MDL and retry opening of tables.
+SET DEBUG_SYNC="now SIGNAL go";
+connection con1;
+# RENAME TABLE should be able to complete. Reap it.
+connection default;
+# Reap LOCK TABLES.
+# Check that we see new version of table.
+SELECT * FROM t1;
+j
+UNLOCK TABLES;
+# Clean-up.
+SET DEBUG_SYNC="RESET";
+disconnect con1;
+disconnect con2;
+DROP TABLES t1, t2;
diff --git a/mysql-test/r/lock_tables_lost_commit.result b/mysql-test/r/lock_tables_lost_commit.result
index 394ef0a9d1c..769e9734c7a 100644
--- a/mysql-test/r/lock_tables_lost_commit.result
+++ b/mysql-test/r/lock_tables_lost_commit.result
@@ -9,6 +9,7 @@ disconnect con1;
 connection con2;
 SELECT * FROM t1;
 a
+10
 DROP TABLE t1;
 connection default;
 disconnect con2;
diff --git a/mysql-test/r/partition_explicit_prune.result b/mysql-test/r/partition_explicit_prune.result
index 65b8e8083fe..1a3f5029978 100644
--- a/mysql-test/r/partition_explicit_prune.result
+++ b/mysql-test/r/partition_explicit_prune.result
@@ -281,7 +281,7 @@ UNLOCK TABLES;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	3
+HANDLER_COMMIT	2
 HANDLER_READ_RND_NEXT	54
 HANDLER_TMP_WRITE	75
 HANDLER_WRITE	2
@@ -440,7 +440,7 @@ UNLOCK TABLES;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	6
+HANDLER_COMMIT	5
 HANDLER_READ_FIRST	3
 HANDLER_READ_NEXT	4
 HANDLER_READ_RND_NEXT	108
@@ -665,7 +665,7 @@ UNLOCK TABLES;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	6
+HANDLER_COMMIT	5
 HANDLER_DELETE	2
 HANDLER_READ_FIRST	1
 HANDLER_READ_KEY	3
@@ -758,7 +758,7 @@ UNLOCK TABLES;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	3
+HANDLER_COMMIT	2
 HANDLER_READ_RND_NEXT	54
 HANDLER_TMP_WRITE	75
 HANDLER_WRITE	10
@@ -953,7 +953,7 @@ UNLOCK TABLES;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	4
+HANDLER_COMMIT	3
 HANDLER_DELETE	1
 HANDLER_READ_KEY	2
 HANDLER_READ_RND	2
@@ -1039,7 +1039,7 @@ UNLOCK TABLES;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	4
+HANDLER_COMMIT	3
 HANDLER_DELETE	2
 HANDLER_READ_KEY	3
 HANDLER_READ_NEXT	1
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result
index 9d75a262592..4966418dbfd 100644
--- a/mysql-test/r/status.result
+++ b/mysql-test/r/status.result
@@ -4,6 +4,7 @@ SET @old_log_output = @@global.log_output;
 SET GLOBAL LOG_OUTPUT = 'FILE';
 connect  con1,localhost,root,,;
 connect  con2,localhost,root,,;
+connection default;
 flush status;
 show status like 'Table_lock%';
 Variable_name	Value
@@ -13,33 +14,42 @@ select * from information_schema.session_status where variable_name like 'Table_
 VARIABLE_NAME	VARIABLE_VALUE
 TABLE_LOCKS_IMMEDIATE	0
 TABLE_LOCKS_WAITED	0
-connection con1;
 set sql_log_bin=0;
 set @old_general_log = @@global.general_log;
 set global general_log = 'OFF';
 drop table if exists t1;
 create table t1(n int) engine=myisam;
 insert into t1 values(1);
-select 1;
-1
+select get_lock('mysqltest_lock', 100);
+get_lock('mysqltest_lock', 100)
 1
 connection con2;
-lock tables t1 read;
-unlock tables;
-lock tables t1 read;
+# Sending:
+update t1 set n = get_lock('mysqltest_lock', 100);
 connection con1;
+# Wait for the first UPDATE to get blocked.
+# Sending:
 update t1 set n = 3;
+connection default;
+# wait for the second UPDATE to get blocked
+select release_lock('mysqltest_lock');
+release_lock('mysqltest_lock')
+1
 connection con2;
-unlock tables;
+# Reaping first UPDATE
+select release_lock('mysqltest_lock');
+release_lock('mysqltest_lock')
+1
 connection con1;
+# Reaping second UPDATE
 show status like 'Table_locks_waited';
 Variable_name	Value
 Table_locks_waited	1
+connection default;
 drop table t1;
 set global general_log = @old_general_log;
 disconnect con2;
 disconnect con1;
-connection default;
 select 1;
 1
 1
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index c1ec95beef7..78cb251ad11 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4792,21 +4792,24 @@ DROP TABLE t1, t2;
 # Bug#48315 Metadata lock is not taken for merged views that
 #           use an INFORMATION_SCHEMA table
 #
-DROP TABLE IF EXISTS t1;
 DROP VIEW IF EXISTS v1;
 DROP PROCEDURE IF EXISTS p1;
 connect  con2, localhost, root;
 connect  con3, localhost, root;
 connection default;
 CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata;
-CREATE TABLE t1 (str VARCHAR(50));
-CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM v1;
+CREATE PROCEDURE p1() SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1;
 # CALL p1() so the view is merged.
 CALL p1();
+SELECT RELEASE_LOCK('blocker');
+RELEASE_LOCK('blocker')
+1
 connection con3;
-LOCK TABLE t1 READ;
+SELECT GET_LOCK('blocker', 100);
+GET_LOCK('blocker', 100)
+1
 connection default;
-# Try to CALL p1() again, this time it should block for t1.
+# Try to CALL p1() again, this time it should block on "blocker".
 # Sending:
 CALL p1();
 connection con2;
@@ -4815,14 +4818,18 @@ connection con2;
 DROP VIEW v1;
 connection con3;
 # Now allow CALL p1() to complete
-UNLOCK TABLES;
+SELECT RELEASE_LOCK('blocker');
+RELEASE_LOCK('blocker')
+1
 connection default;
 # Reaping: CALL p1()
+SELECT RELEASE_LOCK('blocker');
+RELEASE_LOCK('blocker')
+1
 connection con2;
 # Reaping: DROP VIEW v1
 connection default;
 DROP PROCEDURE p1;
-DROP TABLE t1;
 disconnect con2;
 disconnect con3;
 #
diff --git a/mysql-test/suite/innodb/r/innodb-lock.result b/mysql-test/suite/innodb/r/innodb-lock.result
index 6604f2396a3..e63a7cd1505 100644
--- a/mysql-test/suite/innodb/r/innodb-lock.result
+++ b/mysql-test/suite/innodb/r/innodb-lock.result
@@ -36,8 +36,11 @@ drop table t1;
 # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
 # works when LOCK TABLE ... WRITE is used due to fix for bugs #46272
 # "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not
-# detect deadlock between update and alter table". But it still works
-# for LOCK TABLE ... READ.
+# detect deadlock between update and alter table".
+# After WL#6671 "Improve scalability by not using thr_lock.c locks
+# for InnoDB tables" was implemented it no longer works for LOCK TABLES
+# ,,, READ as well.
+# LOCK TABLES locks are now completely handled by MDL subsystem.
 #
 set @@innodb_table_locks=0;
 create table t1 (id integer primary key, x integer) engine=INNODB;
@@ -73,28 +76,26 @@ select * from t1 where id = 0 for update;
 id	x
 0	1
 connection con2;
-# The below statement should not be blocked as LOCK TABLES ... READ
-# does not take strong SQL-level lock on t1. SELECTs which do not
-# conflict with transaction in the first connections should not be
-# blocked.
-lock table t1 read;
-select * from t1;
-id	x
-0	1
-1	1
-2	2
-select * from t1 where id = 1 lock in share mode;
-id	x
-1	1
-unlock tables;
+# The following statement should block because SQL-level lock
+# is taken on t1 which will wait until concurrent transaction
+# is commited.
+# Sending:
+lock table t1 read;;
+connection con1;
+# Wait until LOCK TABLE is blocked on SQL-level lock.
+# We should be able to do UPDATEs and SELECTs within transaction.
+update t1 set x=2 where id = 0;
 select * from t1;
 id	x
-0	1
+0	2
 1	1
 2	2
+# Unblock LOCK TABLE.
 commit;
-connection con1;
-commit;
+connection con2;
+# Reap LOCK TABLE.
+unlock tables;
+connection default;
 drop table t1;
 #
 #Bug#12842206 INNODB LOCKING REGRESSION FOR INSERT IGNORE
diff --git a/mysql-test/suite/innodb/t/innodb-lock.test b/mysql-test/suite/innodb/t/innodb-lock.test
index 47246c53759..3e9b3dd7fe4 100644
--- a/mysql-test/suite/innodb/t/innodb-lock.test
+++ b/mysql-test/suite/innodb/t/innodb-lock.test
@@ -60,8 +60,11 @@ drop table t1;
 --echo # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
 --echo # works when LOCK TABLE ... WRITE is used due to fix for bugs #46272
 --echo # "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not
---echo # detect deadlock between update and alter table". But it still works
---echo # for LOCK TABLE ... READ.
+--echo # detect deadlock between update and alter table".
+--echo # After WL#6671 "Improve scalability by not using thr_lock.c locks
+--echo # for InnoDB tables" was implemented it no longer works for LOCK TABLES
+--echo # ,,, READ as well.
+--echo # LOCK TABLES locks are now completely handled by MDL subsystem.
 --echo #
 
 set @@innodb_table_locks=0;
@@ -104,20 +107,32 @@ connection con1;
 select * from t1 where id = 0 for update;
 
 connection con2;
---echo # The below statement should not be blocked as LOCK TABLES ... READ
---echo # does not take strong SQL-level lock on t1. SELECTs which do not
---echo # conflict with transaction in the first connections should not be
---echo # blocked.
-lock table t1 read;
-select * from t1;
-select * from t1 where id = 1 lock in share mode;
-unlock tables;
-select * from t1;
-commit;
+--echo # The following statement should block because SQL-level lock
+--echo # is taken on t1 which will wait until concurrent transaction
+--echo # is commited.
+--echo # Sending:
+--send lock table t1 read;
 
 connection con1;
+--echo # Wait until LOCK TABLE is blocked on SQL-level lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info = "lock table t1 read";
+--source include/wait_condition.inc
+--echo # We should be able to do UPDATEs and SELECTs within transaction.
+update t1 set x=2 where id = 0;
+select * from t1;
+--echo # Unblock LOCK TABLE.
 commit;
 
+connection con2;
+--echo # Reap LOCK TABLE.
+--reap
+unlock tables;
+
+connection default;
+
 drop table t1;
 
 # End of 4.1 tests
diff --git a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result
index 3b5f67efe78..6c709945111 100644
--- a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result
+++ b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result
@@ -410,7 +410,6 @@ LOCK TABLES t11 WRITE;
 SET SESSION BINLOG_FORMAT=ROW;
 INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
 SET SESSION BINLOG_FORMAT=STATEMENT;
-ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
 INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
 UNLOCK TABLES;
 SELECT * FROM t11;
diff --git a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test
index e5e2f7a381d..575fdb2e89d 100644
--- a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test
+++ b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test
@@ -524,7 +524,6 @@ CREATE TABLE t11 (song VARCHAR(255));
 LOCK TABLES t11 WRITE;
 SET SESSION BINLOG_FORMAT=ROW;
 INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
---error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
 SET SESSION BINLOG_FORMAT=STATEMENT;
 INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
 UNLOCK TABLES;
diff --git a/mysql-test/t/debug_sync.test b/mysql-test/t/debug_sync.test
index aead6f3ac2e..89414939f59 100644
--- a/mysql-test/t/debug_sync.test
+++ b/mysql-test/t/debug_sync.test
@@ -381,21 +381,40 @@ DROP TABLE IF EXISTS t1;
 --enable_warnings
 #
 # Test.
-CREATE TABLE t1 (c1 INT);
-LOCK TABLE t1 READ;
-    connect (con1,localhost,root,,);
-    # Retain action after use. First used by general_log.
-    SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
-    send INSERT INTO t1 VALUES (1);
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+SELECT GET_LOCK('mysqltest_lock', 100);
+
+connect (con1,localhost,root,,);
+--echo # Sending:
+--send UPDATE t1 SET c1=GET_LOCK('mysqltest_lock', 100);
+
+connect (con2,localhost,root,,);
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "User lock" and
+        info = "UPDATE t1 SET c1=GET_LOCK('mysqltest_lock', 100)";
+--source include/wait_condition.inc
+
+# Retain action after use. First used by general_log.
+SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
+send INSERT INTO t1 VALUES (1);
+
 connection default;
 # Wait until INSERT waits for lock.
 SET DEBUG_SYNC= 'now WAIT_FOR locked';
-# let INSERT continue.
-UNLOCK TABLES;
-    connection con1;
-    --echo retrieve INSERT result.
-    reap;
-    disconnect con1;
+# let UPDATE continue.
+SELECT RELEASE_LOCK('mysqltest_lock');
+connection con1;
+--echo # Reaping UPDATE
+reap;
+SELECT RELEASE_LOCK('mysqltest_lock');
+
+connection con2;
+--echo retrieve INSERT result.
+reap;
+disconnect con1;
+disconnect con2;
 connection default;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index 85ba41860df..39ea7e5df88 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -64,6 +64,7 @@ set @@autocommit=1;
 connection default;
 
 disconnect con1;
+disconnect con2;
 disconnect con3;
 
 
@@ -116,54 +117,6 @@ connection default;
 disconnect con37346;
 drop table t1;
 
-
---echo #
---echo # Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB 
---echo #            table cause warnings in errlog
---echo #
-
---echo #
---echo # Note that this test for now relies on a global suppression of 
---echo # the warning "Found lock of type 6 that is write and read locked"
---echo # This suppression rule can be removed once Bug#42147 is properly
---echo # fixed. See bug page for more info.
---echo #
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-CREATE TABLE t1 (i INT) engine= innodb;
-
---echo # Get user-level lock
-connection con2;
-SELECT get_lock('bug42147_lock', 60);
-
-connection default;
---send INSERT INTO t1 SELECT get_lock('bug42147_lock', 60)
-
-connection con2;
-let $wait_condition=
-  SELECT COUNT(*) > 0 FROM information_schema.processlist
-  WHERE state = 'User lock' 
-  AND info = 'INSERT INTO t1 SELECT get_lock(\'bug42147_lock\', 60)';
---source include/wait_condition.inc
-LOCK TABLES t1 READ;
-SELECT release_lock('bug42147_lock');
-let $wait_condition=
-  SELECT COUNT(*) > 0 FROM information_schema.processlist
-  WHERE state = 'executing'
-  AND info = 'INSERT INTO t1 SELECT get_lock(\'bug42147_lock\', 60)';
---source include/wait_condition.inc
-UNLOCK TABLES;
-
-connection default;
---reap
-
-disconnect con2;
-DROP TABLE t1;
-
-
 --echo #
 --echo # Bug#53798 OPTIMIZE TABLE breaks repeatable read
 --echo #
diff --git a/mysql-test/t/insert_notembedded.test b/mysql-test/t/insert_notembedded.test
index 713eaf5db40..2769aee8d8a 100644
--- a/mysql-test/t/insert_notembedded.test
+++ b/mysql-test/t/insert_notembedded.test
@@ -156,41 +156,5 @@ connection default;
 
 DROP DATABASE meow;
 
-#
-# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
-#
---echo connection: default
-set low_priority_updates=1;
---disable_warnings
-drop table if exists t1;
---enable_warnings
-create table t1 (a int, b int, unique key t1$a (a));
-lock table t1 read;
-connect (update,localhost,root,,);
-connection update;
---echo connection: update
-set low_priority_updates=1;
-show variables like 'low_priority_updates';
-let $ID= `select connection_id()`;
---send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
-connection default;
-# we must wait till the insert opens and locks the table
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table level lock" and id = $ID;
---source include/wait_condition.inc
-connect (select,localhost,root,,);
---echo connection: select
-select * from t1;
-connection default;
---echo connection: default
-select * from t1;
-connection default;
-disconnect update;
-disconnect select;
-unlock tables;
-drop table t1;
-set low_priority_updates=default;
-
 set local sql_mode=default;
 set global sql_mode=default;
diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test
index 07c16acc72a..af8435f7fbb 100644
--- a/mysql-test/t/lock_sync.test
+++ b/mysql-test/t/lock_sync.test
@@ -998,6 +998,102 @@ DROP TABLE t1;
 disconnect con1;
 disconnect con2;
 
+
+--echo #
+--echo # Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
+--echo #
+set low_priority_updates=1;
+--disable_warnings
+drop table if exists t1;
+drop table if exists t2;
+--enable_warnings
+set debug_sync='RESET';
+create table t1 (a int, b int, unique key t1$a (a));
+create table t2 (j int, k int);
+set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go';
+--echo # Sending:
+--send insert into t2 select * from t1;
+connect (update,localhost,root,,);
+connection update;
+set debug_sync='now WAIT_FOR parked';
+set low_priority_updates=1;
+show variables like 'low_priority_updates';
+let $ID= `select connection_id()`;
+--send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
+connect (select,localhost,root,,);
+# we must wait till the insert opens and locks the table
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table level lock" and id = $ID;
+--source include/wait_condition.inc
+select * from t1;
+set debug_sync='now SIGNAL go';
+connection default;
+disconnect update;
+disconnect select;
+--echo # Reaping INSERT SELECT
+--reap
+drop tables t1, t2;
+set low_priority_updates=default;
+set debug_sync='RESET';
+
+
+--echo #
+--echo # Additional test coverage for LOCK TABLES ... READ LOCAL
+--echo # for InnoDB tables.
+--echo #
+--echo # Check that we correctly handle deadlocks which can occur
+--echo # during metadata lock upgrade which happens when one tries
+--echo # to use LOCK TABLES ... READ LOCAL for InnoDB tables.
+
+--enable_connect_log
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+CREATE TABLE t2 (j INT) ENGINE=InnoDB;
+
+--echo # Execute LOCK TABLE READ LOCK which will pause after acquiring
+--echo # SR metadata lock and before upgrading it to SRO lock.
+SET DEBUG_SYNC="after_open_table_mdl_shared SIGNAL locked WAIT_FOR go";
+--echo # Sending:
+--send LOCK TABLE t1 READ LOCAL
+
+connect (con1, localhost, root);
+SET DEBUG_SYNC="now WAIT_FOR locked";
+--echo # Execute RENAME TABLE which will try to acquire X lock.
+--echo # Sending:
+--send RENAME TABLE t1 TO t3, t2 TO t1, t3 TO t2
+
+connect (con2, localhost, root);
+--echo # Wait until RENAME TABLE is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info = "RENAME TABLE t1 TO t3, t2 TO t1, t3 TO t2";
+--source include/wait_condition.inc
+--echo # Resume LOCK TABLE statement. It should try to
+--echo # upgrade SR lock to SRO lock which will create
+--echo # deadlock due to presence of pending X lock.
+--echo # Deadlock should be detected and LOCK TABLES should
+--echo # release its MDL and retry opening of tables.
+SET DEBUG_SYNC="now SIGNAL go";
+
+connection con1;
+--echo # RENAME TABLE should be able to complete. Reap it.
+--reap
+
+connection default;
+--echo # Reap LOCK TABLES.
+--reap
+--echo # Check that we see new version of table.
+SELECT * FROM t1;
+UNLOCK TABLES;
+
+--echo # Clean-up.
+SET DEBUG_SYNC="RESET";
+disconnect con1;
+disconnect con2;
+DROP TABLES t1, t2;
+--disable_connect_log
+
 # 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
diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test
index e0b0d9c8838..7ab32241bcb 100644
--- a/mysql-test/t/status.test
+++ b/mysql-test/t/status.test
@@ -23,13 +23,13 @@ SET GLOBAL LOG_OUTPUT = 'FILE';
 
 connect (con1,localhost,root,,);
 connect (con2,localhost,root,,);
+connection default;
 
 flush status;
 
 show status like 'Table_lock%';
 select * from information_schema.session_status where variable_name like 'Table_lock%';
 
-connection con1;
 set sql_log_bin=0;
 set @old_general_log = @@global.general_log;
 set global general_log = 'OFF';
@@ -39,35 +39,46 @@ drop table if exists t1;
 
 create table t1(n int) engine=myisam;
 insert into t1 values(1);
-# Execute dummy select in order to ensure that tables used in the
-# previous statement are unlocked and closed.
-select 1;
+select get_lock('mysqltest_lock', 100);
 
 connection con2;
-lock tables t1 read;
-unlock tables;
-lock tables t1 read;
+--echo # Sending:
+--send update t1 set n = get_lock('mysqltest_lock', 100)
 
 connection con1;
+--echo # Wait for the first UPDATE to get blocked.
+let $wait_condition= select count(*) from INFORMATION_SCHEMA.PROCESSLIST
+                     where STATE = "User lock" and
+                           INFO = "update t1 set n = get_lock('mysqltest_lock', 100)";
+--source include/wait_condition.inc
+
 let $ID= `select connection_id()`;
+--echo # Sending:
 --send update t1 set n = 3
 
-connection con2;
-# wait for the other query to start executing
+connection default;
+--echo # wait for the second UPDATE to get blocked
 let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST
                      where ID = $ID and STATE = "Waiting for table level lock";
 --source include/wait_condition.inc
-unlock tables;
+select release_lock('mysqltest_lock');
+
+connection con2;
+--echo # Reaping first UPDATE
+--reap
+select release_lock('mysqltest_lock');
 
 connection con1;
+--echo # Reaping second UPDATE
 reap;
 show status like 'Table_locks_waited';
+
+connection default;
 drop table t1;
 set global general_log = @old_general_log;
 
 disconnect con2;
 disconnect con1;
-connection default;
 
 # End of 4.1 tests
 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 59ff87f0bad..c3f462eab18 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4636,7 +4636,6 @@ DROP TABLE t1, t2;
 --echo #
 
 --disable_warnings
-DROP TABLE IF EXISTS t1;
 DROP VIEW IF EXISTS v1;
 DROP PROCEDURE IF EXISTS p1;
 --enable_warnings
@@ -4647,25 +4646,27 @@ connect (con3, localhost, root);
 connection default;
 
 CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata;
-CREATE TABLE t1 (str VARCHAR(50));
-CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM v1;
+CREATE PROCEDURE p1() SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1;
 
 --echo # CALL p1() so the view is merged.
+--disable_result_log
 CALL p1();
+--enable_result_log
+SELECT RELEASE_LOCK('blocker');
 
 connection con3;
-LOCK TABLE t1 READ;
+SELECT GET_LOCK('blocker', 100);
 
 connection default;
---echo # Try to CALL p1() again, this time it should block for t1.
+--echo # Try to CALL p1() again, this time it should block on "blocker".
 --echo # Sending:
 --send CALL p1()
 
 connection con2;
 let $wait_condition=
   SELECT COUNT(*) = 1 from information_schema.processlist
-  WHERE state = "Waiting for table level lock" AND
-        info = "INSERT INTO t1 SELECT * FROM v1";
+  WHERE state = "User lock" AND
+        info = "SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1";
 --source include/wait_condition.inc
 --echo # ... then try to drop the view. This should block.
 --echo # Sending:
@@ -4677,11 +4678,14 @@ let $wait_condition=
   WHERE state = "Waiting for table metadata lock" AND info = "DROP VIEW v1";
 --source include/wait_condition.inc
 --echo # Now allow CALL p1() to complete
-UNLOCK TABLES;
+SELECT RELEASE_LOCK('blocker');
 
 connection default;
 --echo # Reaping: CALL p1()
+--disable_result_log
 --reap
+--enable_result_log
+SELECT RELEASE_LOCK('blocker');
 
 connection con2;
 --echo # Reaping: DROP VIEW v1
@@ -4689,7 +4693,6 @@ connection con2;
 
 connection default;
 DROP PROCEDURE p1;
-DROP TABLE t1;
 disconnect con2;
 disconnect con3;
 
diff --git a/plugin/metadata_lock_info/metadata_lock_info.cc b/plugin/metadata_lock_info/metadata_lock_info.cc
index 6a9f6ef3f6c..d5308acb013 100644
--- a/plugin/metadata_lock_info/metadata_lock_info.cc
+++ b/plugin/metadata_lock_info/metadata_lock_info.cc
@@ -39,6 +39,7 @@ static const LEX_STRING metadata_lock_info_lock_mode[] = {
   { C_STRING_WITH_LEN("MDL_SHARED_READ") },
   { C_STRING_WITH_LEN("MDL_SHARED_WRITE") },
   { C_STRING_WITH_LEN("MDL_SHARED_UPGRADABLE") },
+  { C_STRING_WITH_LEN("MDL_SHARED_READ_ONLY") },
   { C_STRING_WITH_LEN("MDL_SHARED_NO_WRITE") },
   { C_STRING_WITH_LEN("MDL_SHARED_NO_READ_WRITE") },
   { C_STRING_WITH_LEN("MDL_EXCLUSIVE") },
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);
 
diff --git a/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result b/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result
index f1ea7e20147..ac1a13d5523 100644
--- a/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result
+++ b/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result
@@ -46,6 +46,7 @@ a	b
 1	10
 2	20
 3	30
+4	40
 insert into t2 values (1);
 ERROR HY000: Table 't2' was not locked with LOCK TABLES
 commit;
@@ -58,6 +59,7 @@ a	b
 1	10
 2	20
 3	30
+4	40
 select * from t2;
 a
 1
@@ -70,6 +72,7 @@ a	b
 1	10
 2	20
 3	30
+4	40
 5	50
 select * from t2;
 a
@@ -81,6 +84,7 @@ a	b
 1	10
 2	20
 3	30
+4	40
 5	50
 select * from t2;
 a
diff --git a/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result b/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result
index 8578bb3b36c..ae87dbab281 100644
--- a/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result
+++ b/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result
@@ -2,8 +2,8 @@ drop table if exists t1,t3;
 CREATE TABLE t3(a int,c int,d int)engine=TOKUDB;
 lock table t3 read;
 create temporary table t1 engine=tokudb as SELECT 1;
-unlock tables;
 select * from t1;
 1
 1
+unlock tables;
 drop table t1,t3;
diff --git a/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test b/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test
index 8dcebe1bb6b..3815e59f78c 100644
--- a/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test
+++ b/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test
@@ -7,7 +7,7 @@ enable_warnings;
 CREATE TABLE t3(a int,c int,d int)engine=TOKUDB;
 lock table t3 read;
 create temporary table t1 engine=tokudb as SELECT 1;
-unlock tables;
 select * from t1;
+unlock tables;
 
-drop table t1,t3;
+drop table t1,t3;
\ No newline at end of file