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.

24 files changed, 5085 insertions, 337 deletions

diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc
index 8342a072ef9..e16c53bc1ee 100644
--- a/mysql-test/include/handler.inc
+++ b/mysql-test/include/handler.inc
@@ -732,10 +732,13 @@ connection default;
 --disable_warnings
 drop table if exists t1;
 --enable_warnings
-create table t1 (a int, key a (a));
+--echo # First test case which is supposed trigger the execution
+--echo # path on which problem was discovered.
+create table t1 (a int);
 insert into t1 values (1);
 handler t1 open;
 connection con1;
+lock table t1 write;
 send alter table t1 engine=memory;
 connection con2;
 let $wait_condition=
@@ -743,10 +746,34 @@ let $wait_condition=
   where state = "Waiting for table" and info = "alter table t1 engine=memory";
 --source include/wait_condition.inc
 connection default;
+--error ER_ILLEGAL_HA
 handler t1 read a next;
 handler t1 close;
 connection con1;
 --reap
+unlock tables;
+drop table t1;
+--echo # Now test case which was reported originally but which no longer
+--echo # triggers execution path which has caused the problem.
+connection default;
+create table t1 (a int, key(a));
+insert into t1 values (1);
+handler t1 open;
+connection con1;
+send alter table t1 engine=memory;
+connection con2;
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 engine=memory";
+--source include/wait_condition.inc
+connection default;
+--echo # Since S metadata lock was already acquired at HANDLER OPEN time
+--echo # and TL_READ lock requested by HANDLER READ is compatible with
+--echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed
+--echo # without waiting. The old version of table should be used in it.
+handler t1 read a next;
+handler t1 close;
+connection con1;
 drop table t1;
 disconnect con1;
 --source include/wait_until_disconnected.inc
@@ -1228,15 +1255,27 @@ create  table t2 like t1;
 handler t1 open;
 --echo # --> connection con1
 connection con1;
-lock table t2 read;
+lock table t1 write, t2 write;
 --echo # --> connection default
 connection default;
+send drop table t2;
+--echo # --> connection con2
+connection con2;
+--echo # Waiting for 'drop table t2' to get blocked...
+let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t2';
+--source include/wait_condition.inc
+--echo # --> connection con1
+connection con1;
 --error ER_LOCK_DEADLOCK
-drop table t2;
---error ER_LOCK_DEADLOCK
-rename table t2 to t3;
+drop table t1;
+unlock tables;
+--echo # --> connection default 
+connection default;
+reap;
+
 --echo # Demonstrate that there is no deadlock with FLUSH TABLE,
 --echo # even though it is waiting for the other table to go away
+create table t2 like t1;
 --echo # Sending:
 --send flush table t2
 --echo # --> connection con2
@@ -1256,6 +1295,7 @@ drop table t2;
 --echo #            lead to deadlocks
 --echo #
 create table t1 (a int, key a(a));
+insert into t1 values (1), (2);
 
 --echo # --> connection default
 connection default;
@@ -1265,7 +1305,31 @@ handler t1 open;
 
 --echo # --> connection con1
 connection con1;
-lock tables t1 write;
+--echo # Sending:
+--send lock tables t1 write
+
+--echo # --> connection con2
+connection con2;
+--echo # Check that 'lock tables t1 write' waits until transaction which
+--echo # has read from the table commits.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "lock tables t1 write";
+--source include/wait_condition.inc
+
+--echo # --> connection default
+connection default;
+--echo # The below 'handler t1 read ...' should not be blocked as
+--echo # 'lock tables t1 write' has not succeeded yet.
+handler t1 read a next;
+
+--echo # Unblock 'lock tables t1 write'.
+commit;
+
+--echo # --> connection con1
+connection con1;
+--echo # Reap 'lock tables t1 write'.
+--reap
 
 --echo # --> connection default
 connection default;
@@ -1279,29 +1343,18 @@ let $wait_condition=
   select count(*) = 1 from information_schema.processlist
   where state = "Table lock" and info = "handler t1 read a next";
 --source include/wait_condition.inc
---echo # Sending:
---send drop table t1
 
---echo # --> connection con2
-connection con2;
---echo # Waiting for 'drop table t1' to get blocked...
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table" and info = "drop table t1";
---source include/wait_condition.inc
+--echo # The below 'drop table t1' should be able to proceed without
+--echo # waiting as it will force HANDLER to be closed.
+drop table t1;
+unlock tables;
 
 --echo # --> connection default 
 connection default;
 --echo # Reaping 'handler t1 read a next'...
---error ER_LOCK_DEADLOCK
+--error ER_NO_SUCH_TABLE
 --reap
 handler t1 close;
-commit;
-
---echo # --> connection con1 
-connection con1;
---echo # Reaping 'drop table t1'...
---reap
 
 --echo # --> connection con1
 connection con1;
@@ -1357,3 +1410,84 @@ rename table t4 to t5, t3 to t4, t5 to t3;
 handler t1 read first;
 handler t2 read first;
 drop table t1, t2, t3, t4;
+
+--echo #
+--echo # A test for FLUSH TABLES WITH READ LOCK and HANDLER statements.
+--echo #
+set autocommit=0;
+create table t1 (a int, b int, key a (a));
+insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
+create table t2 like t1;
+insert into t2 (a, b) select a, b from t1;
+create table t3 like t1;
+insert into t3 (a, b) select a, b from t1;
+commit;
+flush tables with read lock;
+handler t1 open;
+lock table t1 read;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+handler t1 read next;
+--echo # This implicitly leaves LOCK TABLES but doesn't drop the GLR
+--error ER_NO_SUCH_TABLE
+lock table not_exists_write read;
+--echo # We still have the read lock.
+--error ER_CANT_UPDATE_WITH_READLOCK
+drop table t1;
+handler t1 open;
+select a from t2;
+handler t1 read next;
+flush tables with read lock;
+handler t2 open;
+flush tables with read lock;
+handler t1 read next;
+select a from t3;
+handler t2 read next;
+handler t1 close;
+rollback;
+handler t2 close;
+--error ER_CANT_UPDATE_WITH_READLOCK
+drop table t1;
+commit;
+flush tables;
+--error ER_CANT_UPDATE_WITH_READLOCK
+drop table t1;
+unlock tables;
+drop table t1;
+set autocommit=default;
+drop table t2, t3;
+
+--echo # 
+--echo # HANDLER statement and operation-type aware metadata locks.
+--echo # Check that when we clone a ticket for HANDLER we downrade
+--echo # the lock.
+--echo #
+--echo # Establish an auxiliary connection con1.
+connect (con1,localhost,root,,);
+--echo # -> connection default
+connection default;
+create table t1 (a int, b int, key a (a));
+insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
+begin;
+insert into t1 (a, b) values (6, 6);
+handler t1 open;
+handler t1 read a last;
+insert into t1 (a, b) values (7, 7);
+handler t1 read a last;
+commit;
+--echo # -> connection con1
+connection con1;
+--echo # Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE.
+lock table t1 write;
+unlock tables;
+--echo # -> connection default
+connection default;
+handler t1 read a prev;
+handler t1 close;
+--echo # Cleanup.
+drop table t1;
+--echo # -> connection con1
+connection con1;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+--echo # -> connection default
+connection default;
diff --git a/mysql-test/r/debug_sync.result b/mysql-test/r/debug_sync.result
index 8b46334204c..25fdf523200 100644
--- a/mysql-test/r/debug_sync.result
+++ b/mysql-test/r/debug_sync.result
@@ -263,7 +263,7 @@ DROP TABLE t1;
 SET DEBUG_SYNC= 'RESET';
 DROP TABLE IF EXISTS t1;
 CREATE TABLE t1 (c1 INT);
-LOCK TABLE t1 WRITE;
+LOCK TABLE t1 READ;
 connection con1
 SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/handler_innodb.result b/mysql-test/r/handler_innodb.result
index a3e3e325e7d..6ed7b572403 100644
--- a/mysql-test/r/handler_innodb.result
+++ b/mysql-test/r/handler_innodb.result
@@ -745,11 +745,29 @@ drop table t1;
 handler t1 read a next;
 ERROR 42S02: Unknown table 't1' in HANDLER
 drop table if exists t1;
-create table t1 (a int, key a (a));
+# First test case which is supposed trigger the execution
+# path on which problem was discovered.
+create table t1 (a int);
 insert into t1 values (1);
 handler t1 open;
+lock table t1 write;
 alter table t1 engine=memory;
 handler t1 read a next;
+ERROR HY000: Table storage engine for 't1' doesn't have this option
+handler t1 close;
+unlock tables;
+drop table t1;
+# Now test case which was reported originally but which no longer
+# triggers execution path which has caused the problem.
+create table t1 (a int, key(a));
+insert into t1 values (1);
+handler t1 open;
+alter table t1 engine=memory;
+# Since S metadata lock was already acquired at HANDLER OPEN time
+# and TL_READ lock requested by HANDLER READ is compatible with
+# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed
+# without waiting. The old version of table should be used in it.
+handler t1 read a next;
 a
 1
 handler t1 close;
@@ -1217,14 +1235,19 @@ create table t1 (a int, key a(a));
 create  table t2 like t1;
 handler t1 open;
 # --> connection con1
-lock table t2 read;
+lock table t1 write, t2 write;
 # --> connection default
 drop table t2;
+# --> connection con2
+# Waiting for 'drop table t2' to get blocked...
+# --> connection con1
+drop table t1;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
-rename table t2 to t3;
-ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+unlock tables;
+# --> connection default 
 # Demonstrate that there is no deadlock with FLUSH TABLE,
 # even though it is waiting for the other table to go away
+create table t2 like t1;
 # Sending:
 flush table t2;
 # --> connection con2
@@ -1239,29 +1262,43 @@ drop table t2;
 #            lead to deadlocks
 #
 create table t1 (a int, key a(a));
+insert into t1 values (1), (2);
 # --> connection default
 begin;
 select * from t1;
 a
+1
+2
 handler t1 open;
 # --> connection con1
+# Sending:
 lock tables t1 write;
+# --> connection con2
+# Check that 'lock tables t1 write' waits until transaction which
+# has read from the table commits.
+# --> connection default
+# The below 'handler t1 read ...' should not be blocked as
+# 'lock tables t1 write' has not succeeded yet.
+handler t1 read a next;
+a
+1
+# Unblock 'lock tables t1 write'.
+commit;
+# --> connection con1
+# Reap 'lock tables t1 write'.
 # --> connection default
 # Sending:
 handler t1 read a next;
 # --> connection con1
 # Waiting for 'handler t1 read a next' to get blocked...
-# Sending:
+# The below 'drop table t1' should be able to proceed without
+# waiting as it will force HANDLER to be closed.
 drop table t1;
-# --> connection con2
-# Waiting for 'drop table t1' to get blocked...
+unlock tables;
 # --> connection default 
 # Reaping 'handler t1 read a next'...
-ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+ERROR 42S02: Table 'test.t1' doesn't exist
 handler t1 close;
-commit;
-# --> connection con1 
-# Reaping 'drop table t1'...
 # --> connection con1
 # --> connection con2
 # --> connection con3
@@ -1324,3 +1361,98 @@ a	b
 handler t2 read first;
 a	b
 drop table t1, t2, t3, t4;
+#
+# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements.
+#
+set autocommit=0;
+create table t1 (a int, b int, key a (a));
+insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
+create table t2 like t1;
+insert into t2 (a, b) select a, b from t1;
+create table t3 like t1;
+insert into t3 (a, b) select a, b from t1;
+commit;
+flush tables with read lock;
+handler t1 open;
+lock table t1 read;
+handler t1 read next;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+# This implicitly leaves LOCK TABLES but doesn't drop the GLR
+lock table not_exists_write read;
+ERROR 42S02: Table 'test.not_exists_write' doesn't exist
+# We still have the read lock.
+drop table t1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+handler t1 open;
+select a from t2;
+a
+1
+2
+3
+4
+5
+handler t1 read next;
+a	b
+1	1
+flush tables with read lock;
+handler t2 open;
+flush tables with read lock;
+handler t1 read next;
+a	b
+1	1
+select a from t3;
+a
+1
+2
+3
+4
+5
+handler t2 read next;
+a	b
+1	1
+handler t1 close;
+rollback;
+handler t2 close;
+drop table t1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+commit;
+flush tables;
+drop table t1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+unlock tables;
+drop table t1;
+set autocommit=default;
+drop table t2, t3;
+# 
+# HANDLER statement and operation-type aware metadata locks.
+# Check that when we clone a ticket for HANDLER we downrade
+# the lock.
+#
+# Establish an auxiliary connection con1.
+# -> connection default
+create table t1 (a int, b int, key a (a));
+insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
+begin;
+insert into t1 (a, b) values (6, 6);
+handler t1 open;
+handler t1 read a last;
+a	b
+6	6
+insert into t1 (a, b) values (7, 7);
+handler t1 read a last;
+a	b
+7	7
+commit;
+# -> connection con1
+# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE.
+lock table t1 write;
+unlock tables;
+# -> connection default
+handler t1 read a prev;
+a	b
+6	6
+handler t1 close;
+# Cleanup.
+drop table t1;
+# -> connection con1
+# -> connection default
diff --git a/mysql-test/r/handler_myisam.result b/mysql-test/r/handler_myisam.result
index f5c5bfebd15..0dd039eeb23 100644
--- a/mysql-test/r/handler_myisam.result
+++ b/mysql-test/r/handler_myisam.result
@@ -743,11 +743,29 @@ drop table t1;
 handler t1 read a next;
 ERROR 42S02: Unknown table 't1' in HANDLER
 drop table if exists t1;
-create table t1 (a int, key a (a));
+# First test case which is supposed trigger the execution
+# path on which problem was discovered.
+create table t1 (a int);
 insert into t1 values (1);
 handler t1 open;
+lock table t1 write;
 alter table t1 engine=memory;
 handler t1 read a next;
+ERROR HY000: Table storage engine for 't1' doesn't have this option
+handler t1 close;
+unlock tables;
+drop table t1;
+# Now test case which was reported originally but which no longer
+# triggers execution path which has caused the problem.
+create table t1 (a int, key(a));
+insert into t1 values (1);
+handler t1 open;
+alter table t1 engine=memory;
+# Since S metadata lock was already acquired at HANDLER OPEN time
+# and TL_READ lock requested by HANDLER READ is compatible with
+# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed
+# without waiting. The old version of table should be used in it.
+handler t1 read a next;
 a
 1
 handler t1 close;
@@ -1214,14 +1232,19 @@ create table t1 (a int, key a(a));
 create  table t2 like t1;
 handler t1 open;
 # --> connection con1
-lock table t2 read;
+lock table t1 write, t2 write;
 # --> connection default
 drop table t2;
+# --> connection con2
+# Waiting for 'drop table t2' to get blocked...
+# --> connection con1
+drop table t1;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
-rename table t2 to t3;
-ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+unlock tables;
+# --> connection default 
 # Demonstrate that there is no deadlock with FLUSH TABLE,
 # even though it is waiting for the other table to go away
+create table t2 like t1;
 # Sending:
 flush table t2;
 # --> connection con2
@@ -1236,29 +1259,43 @@ drop table t2;
 #            lead to deadlocks
 #
 create table t1 (a int, key a(a));
+insert into t1 values (1), (2);
 # --> connection default
 begin;
 select * from t1;
 a
+1
+2
 handler t1 open;
 # --> connection con1
+# Sending:
 lock tables t1 write;
+# --> connection con2
+# Check that 'lock tables t1 write' waits until transaction which
+# has read from the table commits.
+# --> connection default
+# The below 'handler t1 read ...' should not be blocked as
+# 'lock tables t1 write' has not succeeded yet.
+handler t1 read a next;
+a
+1
+# Unblock 'lock tables t1 write'.
+commit;
+# --> connection con1
+# Reap 'lock tables t1 write'.
 # --> connection default
 # Sending:
 handler t1 read a next;
 # --> connection con1
 # Waiting for 'handler t1 read a next' to get blocked...
-# Sending:
+# The below 'drop table t1' should be able to proceed without
+# waiting as it will force HANDLER to be closed.
 drop table t1;
-# --> connection con2
-# Waiting for 'drop table t1' to get blocked...
+unlock tables;
 # --> connection default 
 # Reaping 'handler t1 read a next'...
-ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+ERROR 42S02: Table 'test.t1' doesn't exist
 handler t1 close;
-commit;
-# --> connection con1 
-# Reaping 'drop table t1'...
 # --> connection con1
 # --> connection con2
 # --> connection con3
@@ -1322,6 +1359,101 @@ handler t2 read first;
 a	b
 drop table t1, t2, t3, t4;
 #
+# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements.
+#
+set autocommit=0;
+create table t1 (a int, b int, key a (a));
+insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
+create table t2 like t1;
+insert into t2 (a, b) select a, b from t1;
+create table t3 like t1;
+insert into t3 (a, b) select a, b from t1;
+commit;
+flush tables with read lock;
+handler t1 open;
+lock table t1 read;
+handler t1 read next;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+# This implicitly leaves LOCK TABLES but doesn't drop the GLR
+lock table not_exists_write read;
+ERROR 42S02: Table 'test.not_exists_write' doesn't exist
+# We still have the read lock.
+drop table t1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+handler t1 open;
+select a from t2;
+a
+1
+2
+3
+4
+5
+handler t1 read next;
+a	b
+1	1
+flush tables with read lock;
+handler t2 open;
+flush tables with read lock;
+handler t1 read next;
+a	b
+1	1
+select a from t3;
+a
+1
+2
+3
+4
+5
+handler t2 read next;
+a	b
+1	1
+handler t1 close;
+rollback;
+handler t2 close;
+drop table t1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+commit;
+flush tables;
+drop table t1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+unlock tables;
+drop table t1;
+set autocommit=default;
+drop table t2, t3;
+# 
+# HANDLER statement and operation-type aware metadata locks.
+# Check that when we clone a ticket for HANDLER we downrade
+# the lock.
+#
+# Establish an auxiliary connection con1.
+# -> connection default
+create table t1 (a int, b int, key a (a));
+insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
+begin;
+insert into t1 (a, b) values (6, 6);
+handler t1 open;
+handler t1 read a last;
+a	b
+6	6
+insert into t1 (a, b) values (7, 7);
+handler t1 read a last;
+a	b
+7	7
+commit;
+# -> connection con1
+# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE.
+lock table t1 write;
+unlock tables;
+# -> connection default
+handler t1 read a prev;
+a	b
+6	6
+handler t1 close;
+# Cleanup.
+drop table t1;
+# -> connection con1
+# -> connection default
+#
 # BUG #46456: HANDLER OPEN + TRUNCATE + DROP (temporary) TABLE, crash 
 #
 CREATE TABLE t1 AS SELECT 1 AS f1;
diff --git a/mysql-test/r/innodb-lock.result b/mysql-test/r/innodb-lock.result
index 4ace4065c34..ab7e9aa7b25 100644
--- a/mysql-test/r/innodb-lock.result
+++ b/mysql-test/r/innodb-lock.result
@@ -25,6 +25,12 @@ id	x
 0	2
 commit;
 drop table t1;
+#
+# Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
+# works due to fix for bugs  #46272 "MySQL 5.4.4, new MDL: unnecessary
+# deadlock" and bug #37346 "innodb does not detect deadlock between
+# update and alter table".
+#
 set @@innodb_table_locks=0;
 create table t1 (id integer primary key, x integer) engine=INNODB;
 insert into t1 values(0, 0),(1,1),(2,2);
@@ -32,26 +38,27 @@ commit;
 SELECT * from t1 where id = 0 FOR UPDATE;
 id	x
 0	0
+# Connection 'con2'.
 set autocommit=0;
 set @@innodb_table_locks=0;
-lock table t1 write;
-update t1 set x=10 where id = 2;
-SELECT * from t1 where id = 2;
-id	x
-2	2
-UPDATE t1 set x=3 where id = 2;
-commit;
-SELECT * from t1;
+# 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 write;;
+# 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=1 where id = 0;
+select * from t1;
 id	x
-0	0
+0	1
 1	1
-2	3
+2	2
+# Unblock LOCK TABLE.
 commit;
+# Connection 'con2'.
+# Reap LOCK TABLE.
 unlock tables;
-commit;
-select * from t1;
-id	x
-0	0
-1	1
-2	10
+# Connection 'con1'.
 drop table t1;
diff --git a/mysql-test/r/innodb_mysql_lock.result b/mysql-test/r/innodb_mysql_lock.result
index 374f67358eb..375ae8aeb12 100644
--- a/mysql-test/r/innodb_mysql_lock.result
+++ b/mysql-test/r/innodb_mysql_lock.result
@@ -26,6 +26,38 @@ commit;
 set @@autocommit=1;
 set @@autocommit=1;
 #
+# Test for bug #37346 "innodb does not detect deadlock between update
+#                      and alter table".
+#
+drop table if exists t1;
+create table t1 (c1 int primary key, c2 int, c3 int) engine=InnoDB;
+insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
+begin;
+# Run statement which acquires X-lock on one of table's rows.
+update t1 set c3=c3+1 where c2=3;
+#
+# Switching to connection 'con37346'.
+# The below ALTER TABLE statement should wait till transaction
+# in connection 'default' is complete and then succeed.
+# It should not deadlock or fail with ER_LOCK_DEADLOCK error.
+# Sending:
+alter table t1 add column c4 int;;
+#
+# Switching to connection 'default'.
+# Wait until the above ALTER TABLE gets blocked because this
+# connection holds SW metadata lock on table to be altered.
+# The below statement should succeed. It should not
+# deadlock or end with ER_LOCK_DEADLOCK error.
+update t1 set c3=c3+1 where c2=4;
+# Unblock ALTER TABLE by committing transaction.
+commit;
+#
+# Switching to connection 'con37346'.
+# Reaping ALTER TABLE.
+#
+# Switching to connection 'default'.
+drop table t1;
+#
 # Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB 
 #            table cause warnings in errlog
 #
diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index 5dbfa66b43e..c1e1ccb5bce 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -151,6 +151,12 @@ select * from t2;
 a
 select * from t3;
 ERROR HY000: Table 't3' was not locked with LOCK TABLES
+Dropping of implicitly locked table is disallowed.
+drop table t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+Now let us also lock table explicitly and drop it.
+lock tables t1 write, v_bug5719 write;
 drop table t1;
 
 sic: left LOCK TABLES mode
@@ -282,6 +288,79 @@ insert into t1 values (1);
 # Ensure that metadata locks held by the transaction are released.
 drop table t1;
 #
+# Coverage for situations when we try to execute DDL on tables
+# which are locked by LOCK TABLES only implicitly.
+#
+drop tables if exists t1, t2;
+drop view if exists v1;
+drop function if exists f1;
+create table t1 (i int);
+create table t2 (j int);
+#
+# Try to perform DDL on table which is locked through view.
+create view v1 as select * from t2;
+lock tables t1 write, v1 write;
+flush table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+drop table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+alter table t2 add column k int;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+# Repair produces error as part of its result set.
+repair table t2;
+Table	Op	Msg_type	Msg_text
+test.t2	repair	Error	Table 't2' was locked with a READ lock and can't be updated
+test.t2	repair	status	Operation failed
+unlock tables;
+drop view v1;
+#
+# Now, try  DDL on table which is locked through routine.
+create function f1 () returns int
+begin
+insert into t2 values (1);
+return 0;
+end|
+create view v1 as select f1() from t1;
+lock tables v1 read;
+flush table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+drop table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+alter table t2 add column k int;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+# Repair produces error as part of its result set.
+repair table t2;
+Table	Op	Msg_type	Msg_text
+test.t2	repair	Error	Table 't2' was locked with a READ lock and can't be updated
+test.t2	repair	status	Operation failed
+unlock tables;
+drop view v1;
+drop function f1;
+#
+# Finally, try DDL on table which is locked thanks to trigger.
+create trigger t1_ai after insert on t1 for each row insert into t2 values (1);
+lock tables t1 write;
+flush table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+drop table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+alter table t2 add column k int;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+# Repair produces error as part of its result set.
+repair table t2;
+Table	Op	Msg_type	Msg_text
+test.t2	repair	Error	Table 't2' was locked with a READ lock and can't be updated
+test.t2	repair	status	Operation failed
+unlock tables;
+drop trigger t1_ai;
+drop tables t1, t2;
+#
 # Bug#45035 " Altering table under LOCK TABLES results in 
 # "Error 1213 Deadlock found..."
 #
diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result
index 5d12e0efd64..4b08c175ee2 100644
--- a/mysql-test/r/lock_multi.result
+++ b/mysql-test/r/lock_multi.result
@@ -1,21 +1,39 @@
 drop table if exists t1,t2;
 create table t1(n int);
 insert into t1 values (1);
-lock tables t1 write;
+select get_lock("mysqltest_lock", 100);
+get_lock("mysqltest_lock", 100)
+1
+update t1 set n = 2 and get_lock('mysqltest_lock', 100);
 update low_priority t1 set n = 4;
 select n from t1;
-unlock tables;
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
 n
 4
 drop table t1;
 create table t1(n int);
 insert into t1 values (1);
-lock tables t1 read;
+select get_lock("mysqltest_lock", 100);
+get_lock("mysqltest_lock", 100)
+1
+select n from t1 where get_lock('mysqltest_lock', 100);
 update low_priority t1 set n = 4;
 select n from t1;
 n
 1
-unlock tables;
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
+n
+1
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
 drop table t1;
 create table t1 (a int, b int);
 create table t2 (c int, d int);
@@ -35,6 +53,7 @@ create table t2 (a int);
 lock table t1 write, t2 write;
 insert t1 select * from t2;
 drop table t2;
+unlock tables;
 ERROR 42S02: Table 'test.t2' doesn't exist
 drop table t1;
 create table t1 (a int);
@@ -42,6 +61,7 @@ create table t2 (a int);
 lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write;
 insert t1 select * from t2;
 drop table t2;
+unlock tables;
 ERROR 42S02: Table 'test.t2' doesn't exist
 drop table t1;
 End of 4.1 tests
@@ -221,6 +241,36 @@ connection: default
 flush tables;
 drop table t1;
 #
+# Test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock".
+#
+drop table if exists t1;
+create table t1 (c1 int primary key, c2 int, c3 int);
+insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
+begin;
+update t1 set c3=c3+1 where c2=3;
+#
+# Switching to connection 'con46272'.
+# The below ALTER TABLE statement should wait till transaction
+# in connection 'default' is complete and then succeed.
+# It should not deadlock or fail with ER_LOCK_DEADLOCK error.
+# Sending:
+alter table t1 add column c4 int;;
+#
+# Switching to connection 'default'.
+# Wait until the above ALTER TABLE gets blocked because this
+# connection holds SW metadata lock on table to be altered.
+# The below statement should succeed. It should not
+# deadlock or end with ER_LOCK_DEADLOCK error.
+update t1 set c3=c3+1 where c2=4;
+# Unblock ALTER TABLE by committing transaction.
+commit;
+#
+# Switching to connection 'con46272'.
+# Reaping ALTER TABLE.
+#
+# Switching to connection 'default'.
+drop table t1;
+#
 # Bug#47249 assert in MDL_global_lock::is_lock_type_compatible
 #
 DROP TABLE IF EXISTS t1;
@@ -228,14 +278,15 @@ DROP VIEW  IF EXISTS v1;
 #
 # Test 1: LOCK TABLES v1 WRITE, t1 READ;
 #
+# Thanks to the fact that we no longer allow DDL on tables
+# which are locked for write implicitly, the exact scenario
+# in which assert was failing is no longer repeatable.
 CREATE TABLE t1 ( f1 integer );
 CREATE VIEW v1 AS SELECT f1 FROM t1 ;
-# Connection 2
 LOCK TABLES v1 WRITE, t1 READ;
 FLUSH TABLE t1;
-# Connection 1
-LOCK TABLES t1 WRITE;
-FLUSH TABLE t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+UNLOCK TABLES;
 DROP TABLE t1;
 DROP VIEW v1;
 #
diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result
index fc4e8c850f6..0b57b38f5ec 100644
--- a/mysql-test/r/lock_sync.result
+++ b/mysql-test/r/lock_sync.result
@@ -6,6 +6,7 @@
 # statements which tried to acquire stronger write lock (TL_WRITE,
 # TL_WRITE_ALLOW_READ) on this table might have led to deadlock.
 drop table if exists t1;
+drop view if exists v1;
 # Create auxiliary connections used through the test.
 # Reset DEBUG_SYNC facility before using it.
 set debug_sync= 'RESET';
@@ -14,6 +15,9 @@ set debug_sync= 'RESET';
 set @old_general_log = @@global.general_log;
 set @@global.general_log= OFF;
 create table t1 (i int) engine=InnoDB;
+# We have to use view in order to make LOCK TABLES avoid
+# acquiring SNRW metadata lock on table.
+create view v1 as select * from t1;
 insert into t1 values (1);
 # Prepare user lock which will be used for resuming execution of
 # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock.
@@ -36,7 +40,7 @@ select count(*) > 0 from t1 as a, t1 as b for update;;
 # acquiring lock for the the first instance of 't1'.
 set debug_sync= 'now WAIT_FOR parked';
 # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1':
-lock table t1 write;;
+lock table v1 write;;
 # Switch to connection 'default'.
 # Wait until this LOCK TABLES statement starts waiting for table lock.
 # Allow SELECT ... FOR UPDATE to resume.
@@ -63,4 +67,5 @@ unlock tables;
 # Do clean-up.
 set debug_sync= 'RESET';
 set @@global.general_log= @old_general_log;
+drop view v1;
 drop table t1;
diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result
index 8c4d7272e29..8d8672377f0 100644
--- a/mysql-test/r/mdl_sync.result
+++ b/mysql-test/r/mdl_sync.result
@@ -10,7 +10,7 @@ alter table t1 rename t3;
 connection: default
 set debug_sync= 'now WAIT_FOR parked';
 connection: con2
-set debug_sync='mdl_acquire_exclusive_locks_wait SIGNAL go';
+set debug_sync='mdl_acquire_lock_wait SIGNAL go';
 drop table t1,t2;
 connection: con1
 connection: default
@@ -20,6 +20,1740 @@ ERROR 42S02: Unknown table 't1'
 drop table t3;
 SET DEBUG_SYNC= 'RESET';
 #
+# Basic test coverage for type-of-operation aware metadata locks.
+#
+drop table if exists t1, t2, t3;
+set debug_sync= 'RESET';
+create table t1 (c1 int);
+# 
+# A) First let us check compatibility rules between differend kinds of
+#    type-of-operation aware metadata locks.
+#    Of course, these rules are already covered by the tests scattered
+#    across the test suite. But it still makes sense to have one place
+#    which covers all of them.
+#
+# 1) Acquire S (simple shared) lock on the table (by using HANDLER):
+#
+handler t1 open;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+0
+insert into t1 values (1), (1);
+# Check that SNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail after opening the table and thus obtaining SNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that SNRW lock is compatible with S lock.
+lock table t1 write;
+insert into t1 values (1);
+unlock tables;
+# Check that X lock is incompatible with S lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above RENAME is blocked because of S lock.
+# 
+# Switching to connection 'default'.
+# Unblock RENAME TABLE.
+handler t1 close;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+# 
+# Switching to connection 'default'.
+handler t1 open;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that upgrade from SNW to X is blocked by presence of S lock.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER TABLE is blocked because of S lock.
+# 
+# Switching to connection 'default'.
+# Unblock ALTER TABLE.
+handler t1 close;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+# 
+# Switching to connection 'default'.
+handler t1 open;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that upgrade from SNRW to X is blocked by presence of S lock.
+lock table t1 write;
+# Sending:
+alter table t1 add column c2 int;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
+# because of S lock.
+# 
+# Switching to connection 'default'.
+# Unblock ALTER TABLE.
+handler t1 close;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+unlock tables;
+# 
+# Switching to connection 'default'.
+#
+# 2) Acquire SH (shared high-priority) lock on the table.
+#    We have to involve DEBUG_SYNC facility for this as usually
+#    such kind of locks are short-lived.
+#
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';;
+# 
+# Switching to connection 'mdl_con1'.
+set debug_sync= 'now WAIT_FOR locked';
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+3
+insert into t1 values (1);
+# Check that SNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail after opening the table and thus obtaining SNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that SNRW lock is compatible with SH lock.
+lock table t1 write;
+delete from t1 limit 1;
+unlock tables;
+# Check that X lock is incompatible with SH lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above RENAME is blocked because of SH lock.
+# Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'default'.
+# Reaping SELECT ... FROM I_S.
+table_name	table_type	auto_increment	table_comment
+t1	BASE TABLE	NULL	
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+# 
+# Switching to connection 'default'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';;
+# 
+# Switching to connection 'mdl_con1'.
+set debug_sync= 'now WAIT_FOR locked';
+# Check that upgrade from SNW to X is blocked by presence of SH lock.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER TABLE is blocked because of SH lock.
+# Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'default'.
+# Reaping SELECT ... FROM I_S.
+table_name	table_type	auto_increment	table_comment
+t1	BASE TABLE	NULL	
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+# 
+# Switching to connection 'default'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';;
+# 
+# Switching to connection 'mdl_con1'.
+set debug_sync= 'now WAIT_FOR locked';
+# Check that upgrade from SNRW to X is blocked by presence of S lock.
+lock table t1 write;
+# Sending:
+alter table t1 add column c2 int;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
+# because of S lock.
+# Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'default'.
+# Reaping SELECT ... FROM I_S.
+table_name	table_type	auto_increment	table_comment
+t1	BASE TABLE	NULL	
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+unlock tables;
+# 
+# Switching to connection 'default'.
+#
+#
+# 3) Acquire SR lock on the table.
+#
+#
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+3
+insert into t1 values (1);
+# Check that SNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail after opening the table and thus obtaining SNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that SNRW lock is not compatible with SR lock.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'default'.
+# Check that the above LOCK TABLES is blocked because of SR lock.
+# Unblock LOCK TABLES.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping LOCK TABLES.
+delete from t1 limit 1;
+unlock tables;
+# 
+# Switching to connection 'default'.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Check that X lock is incompatible with SR lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above RENAME is blocked because of SR lock.
+# 
+# Switching to connection 'default'.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+# 
+# Switching to connection 'default'.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Check that upgrade from SNW to X is blocked by presence of SR lock.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER TABLE is blocked because of SR lock.
+# 
+# Switching to connection 'default'.
+# Unblock ALTER TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+#
+# There is no need to check that upgrade from SNRW to X is blocked
+# by presence of SR lock because SNRW is incompatible with SR anyway.
+# 
+# 
+# Switching to connection 'default'.
+#
+#
+# 4) Acquire SW lock on the table.
+#
+#
+begin;
+insert into t1 values (1);
+# 
+# Switching to connection 'mdl_con1'.
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+4
+insert into t1 values (1);
+# Check that SNW lock is not compatible with SW lock.
+# Again we use ALTER TABLE which fails after opening
+# the table to avoid upgrade of SNW -> X.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'default'.
+# Check that the above ALTER TABLE is blocked because of SW lock.
+# Unblock ALTER TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'default'.
+begin;
+insert into t1 values (1);
+# 
+# Switching to connection 'mdl_con1'.
+# Check that SNRW lock is not compatible with SW lock.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'default'.
+# Check that the above LOCK TABLES is blocked because of SW lock.
+# Unblock LOCK TABLES.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping LOCK TABLES.
+delete from t1 limit 2;
+unlock tables;
+# 
+# Switching to connection 'default'.
+begin;
+insert into t1 values (1);
+# 
+# Switching to connection 'mdl_con1'.
+# Check that X lock is incompatible with SW lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above RENAME is blocked because of SW lock.
+# 
+# Switching to connection 'default'.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+#
+# There is no need to check that upgrade from SNW/SNRW to X is
+# blocked by presence of SW lock because SNW/SNRW is incompatible
+# with SW anyway.
+# 
+# 
+# Switching to connection 'default'.
+#
+#
+# 5) Acquire SNW lock on the table. We have to use DEBUG_SYNC for
+#    this, to prevent SNW from being immediately upgraded to X.
+#
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con1'.
+set debug_sync= 'now WAIT_FOR locked';
+# Check that S, SH and SR locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+5
+# Check that SW lock is incompatible with SNW lock.
+# Sending:
+delete from t1 limit 2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above DELETE is blocked because of SNW lock.
+# Unblock ALTER and thus DELETE.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'default'.
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping DELETE.
+# 
+# Switching to connection 'default'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con1'.
+set debug_sync= 'now WAIT_FOR locked';
+# Check that SNW lock is incompatible with SNW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER is blocked because of SNW lock.
+# Unblock ALTERs.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'default'.
+# Reaping first ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping another ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'default'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con1'.
+set debug_sync= 'now WAIT_FOR locked';
+# Check that SNRW lock is incompatible with SNW lock.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above LOCK TABLES is blocked because of SNW lock.
+# Unblock ALTER and thus LOCK TABLES.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'default'.
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping LOCK TABLES
+insert into t1 values (1);
+unlock tables;
+# 
+# Switching to connection 'default'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con1'.
+set debug_sync= 'now WAIT_FOR locked';
+# Check that X lock is incompatible with SNW lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above RENAME is blocked because of SNW lock.
+# Unblock ALTER and thus RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'default'.
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping RENAME TABLE
+# Revert back to original state of things.
+rename table t2 to t1;
+#
+# There is no need to check that upgrade from SNW/SNRW to X is
+# blocked by presence of another SNW lock because SNW/SNRW is
+# incompatible with SNW anyway.
+# 
+# Switching to connection 'default'.
+#
+#
+# 6) Acquire SNRW lock on the table. 
+#
+#
+lock table t1 write;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that S and SH locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+# Check that SR lock is incompatible with SNRW lock.
+# Sending:
+select count(*) from t1;;
+# 
+# Switching to connection 'default'.
+# Check that the above SELECT is blocked because of SNRW lock.
+# Unblock SELECT.
+unlock tables;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping SELECT.
+count(*)
+4
+# 
+# Switching to connection 'default'.
+lock table t1 write;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that SW lock is incompatible with SNRW lock.
+# Sending:
+delete from t1 limit 1;;
+# 
+# Switching to connection 'default'.
+# Check that the above DELETE is blocked because of SNRW lock.
+# Unblock DELETE.
+unlock tables;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping DELETE.
+# 
+# Switching to connection 'default'.
+lock table t1 write;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that SNW lock is incompatible with SNRW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'default'.
+# Check that the above ALTER is blocked because of UNWR lock.
+# Unblock ALTER.
+unlock tables;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'default'.
+lock table t1 write;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that SNRW lock is incompatible with SNRW lock.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'default'.
+# Check that the above LOCK TABLES is blocked because of SNRW lock.
+# Unblock waiting LOCK TABLES.
+unlock tables;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping LOCK TABLES
+insert into t1 values (1);
+unlock tables;
+# 
+# Switching to connection 'default'.
+lock table t1 write;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that X lock is incompatible with SNRW lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'default'.
+# Check that the above RENAME is blocked because of SNRW lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping RENAME TABLE
+# Revert back to original state of things.
+rename table t2 to t1;
+#
+# There is no need to check that upgrade from SNW/SNRW to X is
+# blocked by presence of another SNRW lock because SNW/SNRW is
+# incompatible with SNRW anyway.
+# 
+# Switching to connection 'default'.
+#
+#
+# 7) Now do the same round of tests for X lock. We use additional
+#    table to get long-lived lock of this type.
+#
+create table t2 (c1 int);
+# 
+# Switching to connection 'mdl_con2'.
+# Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked
+# after acquiring X lock on t1.
+lock tables t2 read;
+# 
+# Switching to connection 'default'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that S lock in incompatible with X lock.
+# Sending:
+handler t1 open;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above HANDLER statement is blocked because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping HANDLER.
+handler t1 close;
+# 
+# Switching to connection 'mdl_con2'.
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+# Switching to connection 'default'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SH lock in incompatible with X lock.
+# Sending:
+select column_name from information_schema.columns where table_schema='test' and table_name='t1';;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above SELECT ... FROM I_S ... statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping SELECT ... FROM I_S.
+column_name
+c1
+# 
+# Switching to connection 'mdl_con2'.
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+# Switching to connection 'default'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SR lock in incompatible with X lock.
+# Sending:
+select count(*) from t1;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above SELECT statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping SELECT.
+count(*)
+4
+# 
+# Switching to connection 'mdl_con2'.
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+# Switching to connection 'default'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SW lock in incompatible with X lock.
+# Sending:
+delete from t1 limit 1;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above DELETE statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping DELETE.
+# 
+# Switching to connection 'mdl_con2'.
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+# Switching to connection 'default'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SNW lock is incompatible with X lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'mdl_con2'.
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+# Switching to connection 'default'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SNRW lock is incompatible with X lock.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above LOCK TABLE statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping LOCK TABLE.
+unlock tables;
+# 
+# Switching to connection 'mdl_con2'.
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+# Switching to connection 'default'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that X lock is incompatible with X lock.
+# Sending:
+rename table t1 to t3;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above RENAME statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping RENAME.
+rename table t3 to t1;
+#
+# B) Now let us test compatibility in cases when both locks
+#    are pending. I.e. let us test rules for priorities between
+#    different types of metadata locks.
+#
+# 
+# Switching to connection 'mdl_con2'.
+#
+# 1) Check compatibility for pending SNW lock.
+#
+# Acquire SW lock in order to create pending SNW lock later.
+begin;
+insert into t1 values (1);
+# 
+# Switching to connection 'default'.
+# Add pending SNW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that ALTER TABLE is waiting with pending SNW lock.
+# Check that S, SH and SR locks are compatible with pending SNW
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+4
+# Check that SW is incompatible with pending SNW
+# Sending:
+delete from t1 limit 1;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above DELETE is blocked because of pending SNW lock.
+# Unblock ALTER TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping ALTER.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping DELETE.
+#
+# We can't do similar check for SNW, SNRW and X locks because
+# they will also be blocked by active SW lock.
+#
+# 
+# Switching to connection 'mdl_con2'.
+#
+# 2) Check compatibility for pending SNRW lock.
+#
+# Acquire SR lock in order to create pending SNRW lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'default'.
+# Add pending SNRW lock.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that LOCK TABLE is waiting with pending SNRW lock.
+# Check that S and SH locks are compatible with pending SNRW
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+# Check that SR is incompatible with pending SNRW
+# Sending:
+select count(*) from t1;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above SELECT is blocked because of pending SNRW lock.
+# Unblock LOCK TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping LOCK TABLE.
+unlock tables;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping SELECT.
+count(*)
+3
+# Restore pending SNRW lock.
+# 
+# Switching to connection 'mdl_con2'.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'default'.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that LOCK TABLE is waiting with pending SNRW lock.
+# Check that SW is incompatible with pending SNRW
+# Sending:
+insert into t1 values (1);;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above INSERT is blocked because of pending SNRW lock.
+# Unblock LOCK TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping LOCK TABLE.
+unlock tables;
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping INSERT.
+# Restore pending SNRW lock.
+# 
+# Switching to connection 'mdl_con2'.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'default'.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that LOCK TABLE is waiting with pending SNRW lock.
+# Check that SNW is compatible with pending SNRW
+# So ALTER TABLE statements are not starved by LOCK TABLEs.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'mdl_con2'.
+# Unblock LOCK TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping LOCK TABLE.
+unlock tables;
+#
+# We can't do similar check for SNRW and X locks because
+# they will also be blocked by active SR lock.
+#
+# 
+# Switching to connection 'mdl_con2'.
+#
+# 3) Check compatibility for pending X lock.
+#
+# Acquire SR lock in order to create pending X lock.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'default'.
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SH locks are compatible with pending X
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+# Check that S is incompatible with pending X
+# Sending:
+handler t1 open;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above HANDLER OPEN is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping HANDLER t1 OPEN.
+handler t1 close;
+# Restore pending X lock.
+# 
+# Switching to connection 'mdl_con2'.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'default'.
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SR is incompatible with pending X
+# Sending:
+select count(*) from t1;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above SELECT is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping SELECT.
+count(*)
+4
+# Restore pending X lock.
+# 
+# Switching to connection 'mdl_con2'.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'default'.
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SW is incompatible with pending X
+# Sending:
+delete from t1 limit 1;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above DELETE is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping DELETE.
+# Restore pending X lock.
+# 
+# Switching to connection 'mdl_con2'.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'default'.
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SNW is incompatible with pending X
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER TABLE is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Restore pending X lock.
+# 
+# Switching to connection 'mdl_con2'.
+handler t1 open;
+# 
+# Switching to connection 'default'.
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SNRW is incompatible with pending X
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'mdl_con3'.
+# Check that the above LOCK TABLES is blocked because of pending X lock.
+# 
+# Switching to connection 'mdl_con2'.
+# Unblock RENAME TABLE.
+handler t1 close;
+# 
+# Switching to connection 'default'.
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'mdl_con1'.
+# Reaping LOCK TABLES.
+unlock tables;
+# 
+# Switching to connection 'default'.
+#
+#
+# C) Now let us test how type-of-operation locks are handled in
+#    transactional context. Obviously we are mostly interested
+#    in conflicting types of locks.
+#
+#
+# 1) Let us check how various locks used within transactional
+#    context interact with active/pending SNW lock.
+#
+#    We start with case when we are acquiring lock on the table
+#    which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Create an active SNW lock on t2.
+# We have to use DEBUG_SYNC facility as otherwise SNW lock
+# will be immediately released (or upgraded to X lock).
+insert into t2 values (1), (1);
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t2 add primary key (c1);;
+# 
+# Switching to connection 'default'.
+set debug_sync= 'now WAIT_FOR locked';
+# SR lock should be acquired without any waiting.
+select count(*) from t2;
+count(*)
+2
+commit;
+# Now let us check that we will wait in case of SW lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+# Sending:
+insert into t2 values (1);;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above INSERT is blocked.
+# Unblock ALTER TABLE and thus INSERT.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'mdl_con1'.
+# Reap ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'default'.
+# Reap INSERT.
+commit;
+#
+# Now let us see what happens when we are acquiring lock on the table
+# which is already used in transaction.
+#
+# *) First, case when transaction which has SR lock on the table also
+#    locked in SNW mode acquires yet another SR lock and then tries
+#    to acquire SW lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Create an active SNW lock on t1.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'default'.
+set debug_sync= 'now WAIT_FOR locked';
+# We should still be able to get SR lock without waiting.
+select count(*) from t1;
+count(*)
+3
+# Since the above ALTER TABLE is not upgrading SNW lock to X by waiting
+# for SW lock we won't create deadlock.
+# So the below INSERT should not end-up with ER_LOCK_DEADLOCK error.
+# Sending:
+insert into t1 values (1);;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above INSERT is blocked.
+# Unblock ALTER TABLE and thus INSERT.
+set debug_sync= 'now SIGNAL finish';
+# 
+# Switching to connection 'mdl_con1'.
+# Reap ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'default'.
+# Reap INSERT.
+commit;
+#
+# **) Now test in which transaction that has SW lock on the table
+#     against which there is pending SNW lock acquires SR and SW
+#     locks on this table.
+#
+begin;
+insert into t1 values (1);
+# 
+# Switching to connection 'mdl_con1'.
+# Create pending SNW lock on t1.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+# Switching to connection 'default'.
+# Wait until ALTER TABLE starts waiting for SNW lock.
+# We should still be able to get both SW and SR locks without waiting.
+select count(*) from t1;
+count(*)
+5
+delete from t1 limit 1;
+# Unblock ALTER TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Switching to connection 'default'.
+#
+# 2) Now similar tests for active SNW lock which is being upgraded
+#    to X lock.
+#
+#    Again we start with case when we are acquiring lock on the
+#    table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con2'.
+# Start transaction which will prevent SNW -> X upgrade from
+# completing immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Create SNW lock pending upgrade to X on t2.
+# Sending:
+alter table t2 add column c2 int;;
+# 
+# Switching to connection 'default'.
+# Wait until ALTER TABLE starts waiting X lock.
+# Check that attempt to acquire SR lock on t2 causes waiting.
+# Sending:
+select count(*) from t2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above SELECT is blocked.
+# Unblock ALTER TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap ALTER TABLE.
+# 
+# Switching to connection 'default'.
+# Reap SELECT.
+count(*)
+3
+commit;
+# Do similar check for SW lock.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con2'.
+# Start transaction which will prevent SNW -> X upgrade from
+# completing immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Create SNW lock pending upgrade to X on t2.
+# Sending:
+alter table t2 drop column c2;;
+# 
+# Switching to connection 'default'.
+# Wait until ALTER TABLE starts waiting X lock.
+# Check that attempt to acquire SW lock on t2 causes waiting.
+# Sending:
+insert into t2 values (1);;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above INSERT is blocked.
+# Unblock ALTER TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap ALTER TABLE.
+# 
+# Switching to connection 'default'.
+# Reap INSERT.
+commit;
+#
+# Test for the case in which we are acquiring lock on the table
+# which is already used in transaction.
+#
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con1'.
+# Create SNW lock pending upgrade to X.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+# Switching to connection 'default'.
+# Wait until ALTER TABLE starts waiting X lock.
+# Check that transaction is still able to acquire SR lock.
+select count(*) from t1;
+count(*)
+4
+# Waiting trying to acquire SW lock will cause deadlock and
+# therefore should cause an error.
+delete from t1 limit 1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock ALTER TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap ALTER TABLE.
+# 
+# Switching to connection 'default'.
+#
+# 3) Check how various locks used within transactional context
+#    interact with active/pending SNRW lock.
+# 
+#    Once again we start with case when we are acquiring lock on
+#    the table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con1'.
+lock table t2 write;
+# 
+# Switching to connection 'default'.
+# Attempt to acquire SR should be blocked. It should
+# not cause errors as it does not creates deadlock.
+# Sending:
+select count(*) from t2;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that the above SELECT is blocked 
+# Unblock SELECT.
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reap SELECT.
+count(*)
+4
+commit;
+# Repeat the same test for SW lock.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con1'.
+lock table t2 write;
+# 
+# Switching to connection 'default'.
+# Again attempt to acquire SW should be blocked and should
+# not cause any errors.
+# Sending:
+delete from t2 limit 1;;
+# 
+# Switching to connection 'mdl_con1'.
+# Check that the above DELETE is blocked 
+# Unblock DELETE.
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reap DELETE.
+commit;
+#
+# Now coverage for the case in which we are acquiring lock on
+# the table which is already used in transaction and against
+# which there is a pending SNRW lock request.
+#
+# *) Let us start with case when transaction has only a SR lock.
+#
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con1'.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'default'.
+# Wait until LOCK TABLE is blocked creating pending request for X lock.
+# Check that another instance of SR lock is granted without waiting.
+select count(*) from t1;
+count(*)
+4
+# Attempt to wait for SW lock will lead to deadlock, thus
+# the below statement should end with ER_LOCK_DEADLOCK error.
+delete from t1 limit 1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock LOCK TABLES.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap LOCK TABLES.
+unlock tables;
+# 
+# Switching to connection 'default'.
+#
+# **) Now case when transaction has a SW lock.
+#
+begin;
+delete from t1 limit 1;
+# 
+# Switching to connection 'mdl_con1'.
+# Sending:
+lock table t1 write;;
+# 
+# Switching to connection 'default'.
+# Wait until LOCK TABLE is blocked creating pending request for X lock.
+# Check that both SR and SW locks are granted without waiting
+# and errors.
+select count(*) from t1;
+count(*)
+3
+insert into t1 values (1, 1);
+# Unblock LOCK TABLES.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap LOCK TABLES.
+unlock tables;
+# 
+# Switching to connection 'default'.
+#
+# 4) Check how various locks used within transactional context
+#    interact with active/pending X lock.
+# 
+#    As usual we start with case when we are acquiring lock on
+#    the table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con2'.
+# Start transaction which will prevent X lock from going away
+# immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Create pending X lock on t2.
+# Sending:
+rename table t2 to t3;;
+# 
+# Switching to connection 'default'.
+# Wait until RENAME TABLE starts waiting with pending X lock.
+# Check that attempt to acquire SR lock on t2 causes waiting.
+# Sending:
+select count(*) from t2;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above SELECT is blocked.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap RENAME TABLE.
+# 
+# Switching to connection 'default'.
+# Reap SELECT.
+ERROR 42S02: Table 'test.t2' doesn't exist
+commit;
+rename table t3 to t2;
+# The same test for SW lock.
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con2'.
+# Start transaction which will prevent X lock from going away
+# immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+# Switching to connection 'mdl_con1'.
+# Create pending X lock on t2.
+# Sending:
+rename table t2 to t3;;
+# 
+# Switching to connection 'default'.
+# Wait until RENAME TABLE starts waiting with pending X lock.
+# Check that attempt to acquire SW lock on t2 causes waiting.
+# Sending:
+delete from t2 limit 1;;
+# 
+# Switching to connection 'mdl_con2'.
+# Check that the above DELETE is blocked.
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap RENAME TABLE.
+# 
+# Switching to connection 'default'.
+# Reap DELETE.
+ERROR 42S02: Table 'test.t2' doesn't exist
+commit;
+rename table t3 to t2;
+#
+# Coverage for the case in which we are acquiring lock on
+# the table which is already used in transaction and against
+# which there is a pending X lock request.
+#
+# *) The first case is when transaction has only a SR lock.
+#
+begin;
+select count(*) from t1;
+count(*)
+4
+# 
+# Switching to connection 'mdl_con1'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'default'.
+# Wait until RENAME TABLE is blocked creating pending request for X lock.
+# Check that another instance of SR lock is granted without waiting.
+select count(*) from t1;
+count(*)
+4
+# Attempt to wait for SW lock will lead to deadlock, thus
+# the below statement should end with ER_LOCK_DEADLOCK error.
+delete from t1 limit 1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'default'.
+#
+# **) The second case is when transaction has a SW lock.
+#
+begin;
+delete from t1 limit 1;
+# 
+# Switching to connection 'mdl_con1'.
+# Sending:
+rename table t1 to t2;;
+# 
+# Switching to connection 'default'.
+# Wait until RENAME TABLE is blocked creating pending request for X lock.
+# Check that both SR and SW locks are granted without waiting
+# and errors.
+select count(*) from t1;
+count(*)
+3
+insert into t1 values (1, 1);
+# Unblock RENAME TABLE.
+commit;
+# 
+# Switching to connection 'mdl_con1'.
+# Reap RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+# Switching to connection 'default'.
+# Clean-up.
+set debug_sync= 'RESET';
+drop table t1, t2;
+#
+# Additional coverage for some scenarios in which not quite
+# correct use of S metadata locks by HANDLER statement might
+# have caused deadlocks.
+#
+drop table if exists t1, t2;
+create table t1 (i int);
+create table t2 (j int);
+insert into t1 values (1);
+#
+# First, check scenario in which we upgrade SNRW lock to X lock
+# on a table while having HANDLER READ trying to acquire TL_READ
+# on the same table.
+#
+handler t1 open;
+# 
+# Switching to connection 'handler_con1'.
+lock table t1 write;
+# Upgrade SNRW to X lock.
+# Sending:
+alter table t1 add column j int;;
+# 
+# Switching to connection 'handler_con2'.
+# Wait until ALTER is blocked during upgrade.
+# 
+# Switching to connection 'default'.
+# The below statement should not cause deadlock.
+handler t1 read first;;
+# 
+# Switching to connection 'handler_con1'.
+# Reap ALTER TABLE.
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reap HANDLER READ.
+i	j
+1	NULL
+handler t1 close;
+#
+# Now, check scenario in which upgrade of SNRW lock to X lock
+# can be blocked by HANDLER which is open in connection currently
+# waiting to get table-lock owned by connection doing upgrade.
+#
+handler t1 open;
+# 
+# Switching to connection 'handler_con1'.
+lock table t1 write, t2 read;
+# 
+# Switching to connection 'default'.
+# Execute statement which will be blocked on table-level lock
+# owned by connection 'handler_con1'.
+# Sending:
+insert into t2 values (1);;
+# 
+# Switching to connection 'handler_con1'.
+# Wait until INSERT is blocked on table-level lock.
+# The below statement should not cause deadlock.
+alter table t1 drop column j;
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Reap INSERT.
+handler t1 close;
+#
+# Then, check the scenario in which upgrade of SNRW lock to X
+# lock is blocked by HANDLER which is open in connection currently
+# waiting to get SW lock on the same table.
+#
+handler t1 open;
+# 
+# Switching to connection 'handler_con1'.
+lock table t1 write;
+# 
+# Switching to connection 'default'.
+# The below insert should be blocked because active SNRW lock on 't1'.
+# Sending:
+insert into t1 values (1);;
+# 
+# Switching to connection 'handler_con1'.
+# Wait until INSERT is blocked because of SNRW lock.
+# The below ALTER TABLE will be blocked because of presence of HANDLER.
+# Sending:
+alter table t1 add column j int;;
+# 
+# Switching to connection 'default'.
+# INSERT should be chosen as victim for resolving deadlock.
+# Reaping INSERT.
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Close HANDLER to unblock ALTER TABLE.
+handler t1 close;
+# 
+# Switching to connection 'handler_con1'.
+# Reaping ALTER TABLE.
+unlock tables;
+# 
+# Switching to connection 'default'.
+#
+# Finally, test in which upgrade of SNRW lock to X lock is blocked
+# by HANDLER which is open in connection currently waiting to get
+# SR lock on the table on which lock is upgraded.
+#
+handler t1 open;
+# 
+# Switching to connection 'handler_con1'.
+lock table t1 write, t2 write;
+# 
+# Switching to connection 'default'.
+# The below insert should be blocked because active SNRW lock on 't1'.
+# Sending:
+insert into t2 values (1);;
+# 
+# Switching to connection 'handler_con1'.
+# Wait until INSERT is blocked because of SNRW lock.
+# The below ALTER TABLE will be blocked because of presence of HANDLER.
+# Sending:
+alter table t1 drop column j;;
+# 
+# Switching to connection 'default'.
+# INSERT should be chosen as victim for resolving deadlock.
+# Reaping INSERT.
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Close HANDLER to unblock ALTER TABLE.
+handler t1 close;
+# 
+# Switching to connection 'handler_con1'.
+# Reaping ALTER TABLE.
+unlock tables;
+# 
+# Switching to connection 'default'.
+# Clean-up.
+drop tables t1, t2;
+#
 # Test coverage for basic deadlock detection in metadata
 # locking subsystem.
 #
@@ -118,53 +1852,46 @@ commit;
 #
 # Switching to connection 'deadlock_con1'.
 begin;
-insert into t1 values (1);
-#
-# Switching to connection 'deadlock_con2'.
-begin;
-insert into t3 values (1);
+insert into t2 values (1);
 #
 # Switching to connection 'default'.
-# Send:
-rename table t2 to t0, t3 to t2, t0 to t3;;
+lock table t1 write;
 #
 # Switching to connection 'deadlock_con1'.
-# Wait until the above RENAME TABLE is blocked because it has to wait
-# for 'deadlock_con2' which holds shared metadata lock on 't3'.
 # The below SELECT statement should wait for metadata lock
-# on table 't2' and should not produce ER_LOCK_DEADLOCK
+# on table 't1' and should not produce ER_LOCK_DEADLOCK
 # immediately as no deadlock is possible at the moment.
-select * from t2;;
+select * from t1;;
 #
-# Switching to connection 'deadlock_con3'.
-# Wait until the above SELECT * FROM t2 is starts waiting
-# for an exclusive metadata lock to go away.
+# Switching to connection 'deadlock_con2'.
+# Wait until the above SELECT * FROM t1 is starts waiting
+# for an UNRW metadata lock to go away.
 # Send RENAME TABLE statement that will deadlock with the
 # SELECT statement and thus should abort the latter.
-rename table t1 to t5, t2 to t1, t5 to t2;;
+rename table t1 to t0, t2 to t1, t0 to t2;;
+#
+# Switching to connection 'default'.
+# Wait till above RENAME TABLE is blocked while holding
+# pending X lock on t1.
+# Allow the above RENAME TABLE to acquire lock on t1 and
+# create pending lock on t2 thus creating deadlock.
+unlock tables;
 #
 # Switching to connection 'deadlock_con1'.
 # Since the latest RENAME TABLE entered in deadlock with SELECT
 # statement the latter should be aborted and emit ER_LOCK_DEADLOCK
 # error.
-# Reap SELECT * FROM t2.
+# Reap SELECT * FROM t1.
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 #
 # Again let us check that failure of the SELECT statement has not
-# released metadata lock on table 't1', i.e. that the latest RENAME
+# released metadata lock on table 't2', i.e. that the latest RENAME
 # is blocked.
 # Commit transaction to unblock this RENAME TABLE.
 commit;
 #
 # Switching to connection 'deadlock_con2'.
-# Commit transaction to unblock the first RENAME TABLE.
-commit;
-#
-# Switching to connection 'default'.
-# Reap RENAME TABLE t2 TO t0 ... .
-#
-# Switching to connection 'deadlock_con3'.
-# Reap RENAME TABLE t1 TO t5 ... .
+# Reap RENAME TABLE ... .
 #
 # Switching to connection 'default'.
 drop tables t1, t2, t3, t4;
@@ -173,10 +1900,19 @@ drop tables t1, t2, t3, t4;
 # also takes into account requests for metadata lock upgrade.
 #
 create table t1 (i int);
+insert into t1 values (1);
+# Avoid race which occurs when SELECT in 'deadlock_con1' connection
+# accesses table before the above INSERT unlocks the table and thus
+# its result becomes visible to other connections.
+select * from t1;
+i
+1
 #
 # Switching to connection 'deadlock_con1'.
 begin;
-insert into t1 values (1);
+select * from t1;
+i
+1
 #
 # Switching to connection 'default'.
 # Send:
@@ -200,42 +1936,6 @@ commit;
 # Reap ALTER TABLE ... RENAME.
 drop table t2;
 #
-# Finally, test case in which deadlock (or potentially livelock) occurs
-# between metadata locking subsystem and table definition cache/table
-# locks, but which should still be detected by our empiric.
-#
-create table t1 (i int);
-#
-# Switching to connection 'deadlock_con1'.
-begin;
-insert into t1 values (1);
-#
-# Switching to connection 'default'.
-lock tables t1 write;
-#
-# Switching to connection 'deadlock_con1'.
-# Send:
-insert into t1 values (2);;
-#
-# Switching to connection 'default'.
-# Wait until INSERT in connection 'deadlock_con1' is blocked on
-# table-level lock.
-# Send:
-alter table t1 add column j int;;
-#
-# Switching to connection 'deadlock_con1'.
-# The above ALTER TABLE statement should cause INSERT statement in
-# this connection to be aborted and emit ER_LOCK_DEADLOCK error.
-# Reap INSERT
-ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
-# Commit transaction to unblock ALTER TABLE.
-commit;
-#
-# Switching to connection 'default'.
-# Reap ALTER TABLE.
-unlock tables;
-drop table t1;
-#
 # Test for bug #46748 "Assertion in MDL_context::wait_for_locks()
 # on INSERT + CREATE TRIGGER".
 #
@@ -297,7 +1997,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked';
 # Now INSERT has a MDL on the non-existent table t1.
 #
 # Continue the INSERT once CREATE waits for exclusive lock
-SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish';
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
 # Try to create that table.
 CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1));
 # Connection 2
@@ -323,7 +2023,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked';
 # Now INSERT has a MDL on the non-existent table t1.
 #
 # Continue the INSERT once CREATE waits for exclusive lock
-SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish';
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
 # Try to create that table.
 CREATE TABLE t1 LIKE t2;
 # Connection 2
@@ -347,10 +2047,10 @@ create table t1 (i int);
 # Let us check that we won't deadlock if during filling
 # of I_S table we encounter conflicting metadata lock
 # which owner is in its turn waiting for our connection.
-lock tables t1 write;
+lock tables t1 read;
 # Switching to connection 'con46044'.
 # Sending:
-create table t2 select * from t1;;
+create table t2 select * from t1 for update;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # First let us check that SHOW FIELDS/DESCRIBE doesn't
@@ -386,10 +2086,10 @@ drop table t2;
 #
 # We check same three queries to I_S in this new situation.
 # Switching to connection 'con46044_2'.
-lock tables t1 write;
+lock tables t1 read;
 # Switching to connection 'con46044'.
 # Sending:
-create table t2 select * from t1;;
+create table t2 select * from t1 for update;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # Let us check that SHOW FIELDS/DESCRIBE gets blocked.
@@ -406,10 +2106,10 @@ Field	Type	Null	Key	Default	Extra
 i	int(11)	YES		NULL	
 drop table t2;
 # Switching to connection 'con46044_2'.
-lock tables t1 write;
+lock tables t1 read;
 # Switching to connection 'con46044'.
 # Sending:
-create table t2 select * from t1;;
+create table t2 select * from t1 for update;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # Check that I_S query which reads only .FRMs gets blocked.
@@ -426,10 +2126,10 @@ column_name
 i
 drop table t2;
 # Switching to connection 'con46044_2'.
-lock tables t1 write;
+lock tables t1 read;
 # Switching to connection 'con46044'.
 # Sending:
-create table t2 select * from t1;;
+create table t2 select * from t1 for update;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # Finally, check that I_S query which does full-blown table open
@@ -458,7 +2158,9 @@ set debug_sync= 'RESET';
 create table t1 (c1 int primary key, c2 int, c3 int);
 insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
 begin;
-update t1 set c3=c3+1 where c2=3;
+select * from t1 where c2 = 3;
+c1	c2	c3
+3	3	0
 #
 # Switching to connection 'con46273'.
 set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR alter_go';
@@ -466,11 +2168,11 @@ alter table t1 add column e int, rename to t2;;
 #
 # Switching to connection 'default'.
 set debug_sync='now WAIT_FOR alter_table_locked';
-set debug_sync='wait_for_lock SIGNAL alter_go';
+set debug_sync='before_open_table_wait_refresh SIGNAL alter_go';
 # The below statement should get ER_LOCK_DEADLOCK error
 # (i.e. it should not allow ALTER to proceed, and then
 # fail due to 't1' changing its name to 't2').
-update t1 set c3=c3+1 where c2=4;
+update t1 set c3=c3+1 where c2 = 3;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 #
 # Let us check that failure of the above statement has not released
diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result
index d974cfb9605..a14d099c673 100644
--- a/mysql-test/r/sp-threads.result
+++ b/mysql-test/r/sp-threads.result
@@ -35,7 +35,7 @@ call bug9486();
 show processlist;
 Id	User	Host	db	Command	Time	State	Info
 #	root	localhost	test	Sleep	#		NULL
-#	root	localhost	test	Query	#	Table lock	update t1, t2 set val= 1 where id1=id2
+#	root	localhost	test	Query	#	Waiting for table	update t1, t2 set val= 1 where id1=id2
 #	root	localhost	test	Query	#	NULL	show processlist
 #	root	localhost	test	Sleep	#		NULL
 unlock tables;
diff --git a/mysql-test/r/truncate_coverage.result b/mysql-test/r/truncate_coverage.result
index bb036329f6f..7a5021f55e2 100644
--- a/mysql-test/r/truncate_coverage.result
+++ b/mysql-test/r/truncate_coverage.result
@@ -7,18 +7,20 @@ CREATE TABLE t1 (c1 INT);
 INSERT INTO t1 VALUES (1);
 #
 # connection con1
-START TRANSACTION;
-INSERT INTO t1 VALUES (2);
+HANDLER t1 OPEN;
 #
 # connection default
 LOCK TABLE t1 WRITE;
 SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting';
 TRUNCATE TABLE t1;
 #
-# connection con1
+# connection con2
 SET DEBUG_SYNC='now WAIT_FOR waiting';
 KILL QUERY @id;
-COMMIT;
+#
+# connection con1
+# Release shared metadata lock by closing HANDLER.
+HANDLER t1 CLOSE;
 #
 # connection default
 ERROR 70100: Query execution was interrupted
@@ -29,17 +31,18 @@ CREATE TABLE t1 (c1 INT);
 INSERT INTO t1 VALUES (1);
 #
 # connection con1
-START TRANSACTION;
-INSERT INTO t1 VALUES (2);
+HANDLER t1 OPEN;
 #
 # connection default
 LOCK TABLE t1 WRITE;
 SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting';
 TRUNCATE TABLE t1;
 #
-# connection con1
+# connection con2
 SET DEBUG_SYNC='now WAIT_FOR waiting';
-COMMIT;
+#
+# connection con1
+HANDLER t1 CLOSE;
 #
 # connection default
 ERROR 42S02: Table 'test.t1' doesn't exist
@@ -55,7 +58,7 @@ START TRANSACTION;
 INSERT INTO t1 VALUES (2);
 #
 # connection default
-SET DEBUG_SYNC='mdl_acquire_exclusive_locks_wait SIGNAL waiting';
+SET DEBUG_SYNC='mdl_acquire_lock_wait SIGNAL waiting';
 TRUNCATE TABLE t1;
 #
 # connection con1
diff --git a/mysql-test/suite/funcs_1/datadict/processlist_val.inc b/mysql-test/suite/funcs_1/datadict/processlist_val.inc
index 8b10cfc5e97..6fcaf45c848 100644
--- a/mysql-test/suite/funcs_1/datadict/processlist_val.inc
+++ b/mysql-test/suite/funcs_1/datadict/processlist_val.inc
@@ -425,7 +425,7 @@ echo
 # Poll till INFO is no more NULL and State = 'Table Lock'.
 ;
 let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
-        WHERE INFO IS NOT NULL AND STATE = 'Table Lock';
+        WHERE INFO IS NOT NULL AND STATE = 'Waiting for table';
 --source include/wait_condition.inc
 echo
 # Expect result:
diff --git a/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result b/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result
index 34b2e48fc7e..e8ee784bec4 100644
--- a/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result
+++ b/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result
@@ -195,9 +195,11 @@ SELECT COUNT(*) FROM test.t1;
 
 # Poll till INFO is no more NULL and State = 'Table Lock'.
 
+Timeout in wait_condition.inc for SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE INFO IS NOT NULL AND STATE = 'Table Lock'
 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
 ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
-<ID>	test_user	<HOST_NAME>	information_schema	Query	<TIME>	Table lock	SELECT COUNT(*) FROM test.t1
+<ID>	test_user	<HOST_NAME>	information_schema	Query	<TIME>	Waiting for table	SELECT COUNT(*) FROM test.t1
 <ID>	test_user	<HOST_NAME>	information_schema	Sleep	<TIME>		NULL
 <ID>	root	<HOST_NAME>	information_schema	Query	<TIME>	executing	SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
 UNLOCK TABLES;
diff --git a/mysql-test/suite/rpl/t/rpl_sp.test b/mysql-test/suite/rpl/t/rpl_sp.test
index 231f0c6bcc0..3d94415fbb5 100644
--- a/mysql-test/suite/rpl/t/rpl_sp.test
+++ b/mysql-test/suite/rpl/t/rpl_sp.test
@@ -655,7 +655,7 @@ connection master;
 connection master1;
 --echo # Waitng for 'insert into t1 ...' to get blocked on table lock...
 let $wait_condition=select count(*)=1 from information_schema.processlist
-where state='Table lock' and info='insert into t1 (a) values (f1())';
+where state='Waiting for table' and info='insert into t1 (a) values (f1())';
 --source include/wait_condition.inc
 --echo # Sending 'drop function f1'. It will abort the table lock wait.
 drop function f1;
diff --git a/mysql-test/t/debug_sync.test b/mysql-test/t/debug_sync.test
index 514e471b603..ebeeec61632 100644
--- a/mysql-test/t/debug_sync.test
+++ b/mysql-test/t/debug_sync.test
@@ -390,7 +390,7 @@ DROP TABLE IF EXISTS t1;
 #
 # Test.
 CREATE TABLE t1 (c1 INT);
-LOCK TABLE t1 WRITE;
+LOCK TABLE t1 READ;
     --echo connection con1
     connect (con1,localhost,root,,);
     # Retain action after use. First used by general_log.
diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test
index eacf7e562be..d2f630ccaba 100644
--- a/mysql-test/t/innodb-lock.test
+++ b/mysql-test/t/innodb-lock.test
@@ -56,9 +56,12 @@ commit;
 
 drop table t1;
 
-#
-# Try with old lock method (where LOCK TABLE is ignored by InnoDB)
-#
+--echo #
+--echo # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
+--echo # works due to fix for bugs  #46272 "MySQL 5.4.4, new MDL: unnecessary
+--echo # deadlock" and bug #37346 "innodb does not detect deadlock between
+--echo # update and alter table".
+--echo #
 
 set @@innodb_table_locks=0;
 
@@ -67,36 +70,38 @@ insert into t1 values(0, 0),(1,1),(2,2);
 commit;
 SELECT * from t1 where id = 0 FOR UPDATE;
 
+--echo # Connection 'con2'.
 connection con2;
 set autocommit=0;
 set @@innodb_table_locks=0;
 
-# The following statement should work becase innodb doesn't check table locks
-lock table t1 write;
+--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 write;
 
+--echo # Connection 'con1'.
 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" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # We should be able to do UPDATEs and SELECTs within transaction.
+update t1 set x=1 where id = 0;
+select * from t1;
+--echo # Unblock LOCK TABLE.
+commit;
 
-# This will be locked by MySQL
---send
-update t1 set x=10 where id = 2;
---sleep 2
-
+--echo # Connection 'con2'.
 connection con2;
-
-# Note that we will get a deadlock if we try to select any rows marked
-# for update by con1 !
-
-SELECT * from t1 where id = 2;
-UPDATE t1 set x=3 where id = 2;
-commit;
-SELECT * from t1;
-commit;
+--echo # Reap LOCK TABLE.
+--reap
 unlock tables;
 
+--echo # Connection 'con1'.
 connection con1;
-reap;
-commit;
-select * from t1;
 drop table t1;
 
 # End of 4.1 tests
diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index c8c38cd1ab1..6469ef2d229 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -66,6 +66,60 @@ connection default;
 disconnect con1;
 disconnect con3;
 
+
+--echo #
+--echo # Test for bug #37346 "innodb does not detect deadlock between update
+--echo #                      and alter table".
+--echo #
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (c1 int primary key, c2 int, c3 int) engine=InnoDB;
+insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
+begin;
+--echo # Run statement which acquires X-lock on one of table's rows.
+update t1 set c3=c3+1 where c2=3;
+
+--echo #
+--echo # Switching to connection 'con37346'.
+connect (con37346,localhost,root,,test,,);
+connection con37346;
+--echo # The below ALTER TABLE statement should wait till transaction
+--echo # in connection 'default' is complete and then succeed.
+--echo # It should not deadlock or fail with ER_LOCK_DEADLOCK error.
+--echo # Sending:
+--send alter table t1 add column c4 int;
+
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until the above ALTER TABLE gets blocked because this
+--echo # connection holds SW metadata lock on table to be altered.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column c4 int";
+--source include/wait_condition.inc
+
+--echo # The below statement should succeed. It should not
+--echo # deadlock or end with ER_LOCK_DEADLOCK error.
+update t1 set c3=c3+1 where c2=4;
+
+--echo # Unblock ALTER TABLE by committing transaction.
+commit;
+
+--echo #
+--echo # Switching to connection 'con37346'.
+connection con37346;
+--echo # Reaping ALTER TABLE.
+--reap
+
+--echo #
+--echo # Switching to connection 'default'.
+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
diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test
index bc9d1ea8245..eda3e8451dd 100644
--- a/mysql-test/t/lock.test
+++ b/mysql-test/t/lock.test
@@ -202,6 +202,12 @@ select * from t1;
 select * from t2;
 --error ER_TABLE_NOT_LOCKED
 select * from t3;
+--echo Dropping of implicitly locked table is disallowed.
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1;
+unlock tables;
+--echo Now let us also lock table explicitly and drop it.
+lock tables t1 write, v_bug5719 write;
 drop table t1;
 --echo
 --echo sic: left LOCK TABLES mode
@@ -349,6 +355,76 @@ drop table t1;
 
 
 --echo #
+--echo # Coverage for situations when we try to execute DDL on tables
+--echo # which are locked by LOCK TABLES only implicitly.
+--echo #
+--disable_warnings
+drop tables if exists t1, t2;
+drop view if exists v1;
+drop function if exists f1;
+--enable_warnings
+create table t1 (i int);
+create table t2 (j int);
+--echo #
+--echo # Try to perform DDL on table which is locked through view.
+create view v1 as select * from t2;
+lock tables t1 write, v1 write;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop view v1;
+--echo #
+--echo # Now, try  DDL on table which is locked through routine.
+delimiter |;
+create function f1 () returns int
+begin
+  insert into t2 values (1);
+  return 0;
+end|
+delimiter ;|
+create view v1 as select f1() from t1;
+lock tables v1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop view v1;
+drop function f1;
+--echo #
+--echo # Finally, try DDL on table which is locked thanks to trigger.
+create trigger t1_ai after insert on t1 for each row insert into t2 values (1);
+lock tables t1 write;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop trigger t1_ai;
+drop tables t1, t2;
+
+
+--echo #
 --echo # Bug#45035 " Altering table under LOCK TABLES results in 
 --echo # "Error 1213 Deadlock found..."
 --echo #
diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
index 31a10f89796..b924923233b 100644
--- a/mysql-test/t/lock_multi.test
+++ b/mysql-test/t/lock_multi.test
@@ -8,14 +8,24 @@ drop table if exists t1,t2;
 # Test to see if select will get the lock ahead of low priority update
 
 connect (locker,localhost,root,,);
+connect (locker2,localhost,root,,);
 connect (reader,localhost,root,,);
 connect (writer,localhost,root,,);
 
 connection locker;
 create table t1(n int);
 insert into t1 values (1);
-lock tables t1 write;
+connection locker2;
+select get_lock("mysqltest_lock", 100);
+connection locker;
+send
+update t1 set n = 2 and get_lock('mysqltest_lock', 100);
 connection writer;
+# Wait till above update gets blocked on a user lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "User lock" and info = "update t1 set n = 2 and get_lock('mysqltest_lock', 100)";
+--source include/wait_condition.inc
 send
 update low_priority t1 set n = 4;
 connection reader;
@@ -26,13 +36,16 @@ let $wait_condition=
 --source include/wait_condition.inc
 send
 select n from t1;
-connection locker;
+connection locker2;
 # Sleep a bit till the select of connection reader is in work and hangs
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
   where state = "Table lock" and info = "select n from t1";
 --source include/wait_condition.inc
-unlock tables;
+select release_lock("mysqltest_lock");
+connection locker;
+reap;
+select release_lock("mysqltest_lock");
 connection writer;
 reap;
 connection reader;
@@ -42,8 +55,17 @@ drop table t1;
 connection locker;
 create table t1(n int);
 insert into t1 values (1);
-lock tables t1 read;
+connection locker2;
+select get_lock("mysqltest_lock", 100);
+connection locker;
+send
+select n from t1 where get_lock('mysqltest_lock', 100);
 connection writer;
+# Wait till above select gets blocked on a user lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "User lock" and info = "select n from t1 where get_lock('mysqltest_lock', 100)";
+--source include/wait_condition.inc
 send
 update low_priority t1 set n = 4;
 connection reader;
@@ -53,8 +75,11 @@ let $wait_condition=
   where state = "Table lock" and info = "update low_priority t1 set n = 4";
 --source include/wait_condition.inc
 select n from t1;
+connection locker2;
+select release_lock("mysqltest_lock");
 connection locker;
-unlock tables;
+reap;
+select release_lock("mysqltest_lock");
 connection writer;
 reap;
 drop table t1;
@@ -95,9 +120,10 @@ insert t1 select * from t2;
 connection locker;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "insert t1 select * from t2";
+  where state = "Waiting for table" and info = "insert t1 select * from t2";
 --source include/wait_condition.inc
 drop table t2;
+unlock tables;
 connection reader;
 --error ER_NO_SUCH_TABLE
 reap;
@@ -119,9 +145,10 @@ connection locker;
 # Sleep a bit till the insert of connection reader is in work and hangs
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "insert t1 select * from t2";
+  where state = "Waiting for table" and info = "insert t1 select * from t2";
 --source include/wait_condition.inc
 drop table t2;
+unlock tables;
 connection reader;
 --error ER_NO_SUCH_TABLE
 reap;
@@ -164,7 +191,7 @@ connection locker;
 # Sleep a bit till the select of connection reader is in work and hangs
 let $wait_condition=
   SELECT COUNT(*) = 1 FROM information_schema.processlist
-  WHERE state = "Table lock" AND info =
+  WHERE state = "Waiting for table" AND info =
   "SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1";
 --source include/wait_condition.inc
 # Make test case independent from earlier grants.
@@ -299,7 +326,7 @@ connection reader;
 # Wait till connection writer is blocked
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "alter table t1 auto_increment=0";
+  where state = "Waiting for table" and info = "alter table t1 auto_increment=0";
 --source include/wait_condition.inc
 send
 alter table t1 auto_increment=0;
@@ -307,7 +334,7 @@ connection locker;
 # Wait till connection reader is blocked
 let $wait_condition=
   select count(*) = 2 from information_schema.processlist
-  where state = "Table lock" and info = "alter table t1 auto_increment=0";
+  where state = "Waiting for table" and info = "alter table t1 auto_increment=0";
 --source include/wait_condition.inc
 unlock tables;
 connection writer;
@@ -502,6 +529,7 @@ drop table t1;
 
 # Disconnect sessions used in many subtests above
 disconnect locker;
+disconnect locker2;
 disconnect reader;
 disconnect writer;
 
@@ -668,6 +696,57 @@ disconnect flush;
 
 
 --echo #
+--echo # Test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock".
+--echo #
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (c1 int primary key, c2 int, c3 int);
+insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
+begin;
+update t1 set c3=c3+1 where c2=3;
+
+--echo #
+--echo # Switching to connection 'con46272'.
+connect (con46272,localhost,root,,test,,);
+connection con46272;
+--echo # The below ALTER TABLE statement should wait till transaction
+--echo # in connection 'default' is complete and then succeed.
+--echo # It should not deadlock or fail with ER_LOCK_DEADLOCK error.
+--echo # Sending:
+--send alter table t1 add column c4 int;
+
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until the above ALTER TABLE gets blocked because this
+--echo # connection holds SW metadata lock on table to be altered.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column c4 int";
+--source include/wait_condition.inc
+
+--echo # The below statement should succeed. It should not
+--echo # deadlock or end with ER_LOCK_DEADLOCK error.
+update t1 set c3=c3+1 where c2=4;
+
+--echo # Unblock ALTER TABLE by committing transaction.
+commit;
+
+--echo #
+--echo # Switching to connection 'con46272'.
+connection con46272;
+--echo # Reaping ALTER TABLE.
+--reap
+
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+disconnect con46272;
+drop table t1;
+
+
+--echo #
 --echo # Bug#47249 assert in MDL_global_lock::is_lock_type_compatible
 --echo #
 
@@ -679,21 +758,17 @@ DROP VIEW  IF EXISTS v1;
 --echo #
 --echo # Test 1: LOCK TABLES v1 WRITE, t1 READ;
 --echo #
+--echo # Thanks to the fact that we no longer allow DDL on tables
+--echo # which are locked for write implicitly, the exact scenario
+--echo # in which assert was failing is no longer repeatable.
 
 CREATE TABLE t1 ( f1 integer );
 CREATE VIEW v1 AS SELECT f1 FROM t1 ;
 
---echo # Connection 2
-connect (con2,localhost,root);
 LOCK TABLES v1 WRITE, t1 READ;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
 FLUSH TABLE t1;
-disconnect con2;
---source include/wait_until_disconnected.inc
-
---echo # Connection 1
-connection default;
-LOCK TABLES t1 WRITE;
-FLUSH TABLE t1;                                    # Assertion happened here
+UNLOCK TABLES;
 
 # Cleanup
 DROP TABLE t1;
diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test
index de8a1d7e43e..460c0175808 100644
--- a/mysql-test/t/lock_sync.test
+++ b/mysql-test/t/lock_sync.test
@@ -21,6 +21,7 @@
 --echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock.
 --disable_warnings
 drop table if exists t1;
+drop view if exists v1;
 --enable_warnings
 --echo # Create auxiliary connections used through the test.
 connect (con_bug45143_1,localhost,root,,test,,);
@@ -35,6 +36,9 @@ set @old_general_log = @@global.general_log;
 set @@global.general_log= OFF;
 
 create table t1 (i int) engine=InnoDB;
+--echo # We have to use view in order to make LOCK TABLES avoid
+--echo # acquiring SNRW metadata lock on table.
+create view v1 as select * from t1;
 insert into t1 values (1);
 --echo # Prepare user lock which will be used for resuming execution of
 --echo # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock.
@@ -65,14 +69,14 @@ connection con_bug45143_3;
 --echo # acquiring lock for the the first instance of 't1'.
 set debug_sync= 'now WAIT_FOR parked';
 --echo # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1':
---send lock table t1 write;
+--send lock table v1 write;
 
 --echo # Switch to connection 'default'.
 connection default;
 --echo # Wait until this LOCK TABLES statement starts waiting for table lock.
 let $wait_condition= select count(*)= 1 from information_schema.processlist
                        where state= 'Table lock' and
-                             info='lock table t1 write';
+                             info='lock table v1 write';
 --source include/wait_condition.inc
 --echo # Allow SELECT ... FOR UPDATE to resume.
 --echo # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance
@@ -110,6 +114,7 @@ disconnect con_bug45143_2;
 disconnect con_bug45143_3;
 set debug_sync= 'RESET';
 set @@global.general_log= @old_general_log;
+drop view v1;
 drop table t1;
 
 
diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test
index 4cbaa689339..dda9ba991cf 100644
--- a/mysql-test/t/mdl_sync.test
+++ b/mysql-test/t/mdl_sync.test
@@ -44,7 +44,7 @@ set debug_sync= 'now WAIT_FOR parked';
 
 connection con2;
 --echo connection: con2
-set debug_sync='mdl_acquire_exclusive_locks_wait SIGNAL go';
+set debug_sync='mdl_acquire_lock_wait SIGNAL go';
 --send drop table t1,t2
 
 connection con1;
@@ -74,6 +74,2313 @@ SET DEBUG_SYNC= 'RESET';
 
 
 --echo #
+--echo # Basic test coverage for type-of-operation aware metadata locks.
+--echo #
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+connect(mdl_con1,localhost,root,,);
+connect(mdl_con2,localhost,root,,);
+connect(mdl_con3,localhost,root,,);
+connection default;
+set debug_sync= 'RESET';
+create table t1 (c1 int);
+
+--echo # 
+--echo # A) First let us check compatibility rules between differend kinds of
+--echo #    type-of-operation aware metadata locks.
+--echo #    Of course, these rules are already covered by the tests scattered
+--echo #    across the test suite. But it still makes sense to have one place
+--echo #    which covers all of them.
+--echo #
+
+--echo # 1) Acquire S (simple shared) lock on the table (by using HANDLER):
+--echo #
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+select count(*) from t1;
+insert into t1 values (1), (1);
+--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail after opening the table and thus obtaining SNW metadata
+--echo # lock.
+--error ER_DUP_ENTRY
+alter table t1 add primary key (c1);
+--echo # Check that SNRW lock is compatible with S lock.
+lock table t1 write;
+insert into t1 values (1);
+unlock tables;
+--echo # Check that X lock is incompatible with S lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above RENAME is blocked because of S lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Unblock RENAME TABLE.
+handler t1 close;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping RENAME TABLE.
+--reap
+--echo # Restore the original state of the things.
+rename table t2 to t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that upgrade from SNW to X is blocked by presence of S lock.
+--echo # Sending:
+--send alter table t1 add column c2 int;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above ALTER TABLE is blocked because of S lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column c2 int";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Unblock ALTER TABLE.
+handler t1 close;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--reap
+--echo # Restore the original state of the things.
+alter table t1 drop column c2;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that upgrade from SNRW to X is blocked by presence of S lock.
+lock table t1 write;
+--echo # Sending:
+--send alter table t1 add column c2 int;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
+--echo # because of S lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column c2 int";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Unblock ALTER TABLE.
+handler t1 close;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--reap
+--echo # Restore the original state of the things.
+alter table t1 drop column c2;
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo # 2) Acquire SH (shared high-priority) lock on the table.
+--echo #    We have to involve DEBUG_SYNC facility for this as usually
+--echo #    such kind of locks are short-lived.
+--echo #
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+select count(*) from t1;
+insert into t1 values (1);
+--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail after opening the table and thus obtaining SNW metadata
+--echo # lock.
+--error ER_DUP_ENTRY
+alter table t1 add primary key (c1);
+--echo # Check that SNRW lock is compatible with SH lock.
+lock table t1 write;
+delete from t1 limit 1;
+unlock tables;
+--echo # Check that X lock is incompatible with SH lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above RENAME is blocked because of SH lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping SELECT ... FROM I_S.
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping RENAME TABLE.
+--reap
+--echo # Restore the original state of the things.
+rename table t2 to t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Check that upgrade from SNW to X is blocked by presence of SH lock.
+--echo # Sending:
+--send alter table t1 add column c2 int;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above ALTER TABLE is blocked because of SH lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column c2 int";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping SELECT ... FROM I_S.
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--reap
+--echo # Restore the original state of the things.
+alter table t1 drop column c2;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Check that upgrade from SNRW to X is blocked by presence of S lock.
+lock table t1 write;
+--echo # Sending:
+--send alter table t1 add column c2 int;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
+--echo # because of S lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column c2 int";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping SELECT ... FROM I_S.
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--reap
+--echo # Restore the original state of the things.
+alter table t1 drop column c2;
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo #
+--echo # 3) Acquire SR lock on the table.
+--echo #
+--echo #
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+select count(*) from t1;
+insert into t1 values (1);
+--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail after opening the table and thus obtaining SNW metadata
+--echo # lock.
+--error ER_DUP_ENTRY
+alter table t1 add primary key (c1);
+--echo # Check that SNRW lock is not compatible with SR lock.
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above LOCK TABLES is blocked because of SR lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Unblock LOCK TABLES.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping LOCK TABLES.
+--reap
+delete from t1 limit 1;
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that X lock is incompatible with SR lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above RENAME is blocked because of SR lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping RENAME TABLE.
+--reap
+--echo # Restore the original state of the things.
+rename table t2 to t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that upgrade from SNW to X is blocked by presence of SR lock.
+--echo # Sending:
+--send alter table t1 add column c2 int;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above ALTER TABLE is blocked because of SR lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column c2 int";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Unblock ALTER TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--reap
+--echo # Restore the original state of the things.
+alter table t1 drop column c2;
+--echo #
+--echo # There is no need to check that upgrade from SNRW to X is blocked
+--echo # by presence of SR lock because SNRW is incompatible with SR anyway.
+--echo # 
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo #
+--echo # 4) Acquire SW lock on the table.
+--echo #
+--echo #
+begin;
+insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+select count(*) from t1;
+insert into t1 values (1);
+--echo # Check that SNW lock is not compatible with SW lock.
+--echo # Again we use ALTER TABLE which fails after opening
+--echo # the table to avoid upgrade of SNW -> X.
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above ALTER TABLE is blocked because of SW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add primary key (c1)";
+--source include/wait_condition.inc
+--echo # Unblock ALTER TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+begin;
+insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that SNRW lock is not compatible with SW lock.
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above LOCK TABLES is blocked because of SW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Unblock LOCK TABLES.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping LOCK TABLES.
+--reap
+delete from t1 limit 2;
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+begin;
+insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that X lock is incompatible with SW lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above RENAME is blocked because of SW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping RENAME TABLE.
+--reap
+--echo # Restore the original state of the things.
+rename table t2 to t1;
+--echo #
+--echo # There is no need to check that upgrade from SNW/SNRW to X is
+--echo # blocked by presence of SW lock because SNW/SNRW is incompatible
+--echo # with SW anyway.
+--echo # 
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo #
+--echo # 5) Acquire SNW lock on the table. We have to use DEBUG_SYNC for
+--echo #    this, to prevent SNW from being immediately upgraded to X.
+--echo #
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Check that S, SH and SR locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+select count(*) from t1;
+--echo # Check that SW lock is incompatible with SNW lock.
+--echo # Sending:
+--send delete from t1 limit 2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above DELETE is blocked because of SNW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "delete from t1 limit 2";
+--source include/wait_condition.inc
+--echo # Unblock ALTER and thus DELETE.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping DELETE.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Check that SNW lock is incompatible with SNW lock.
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above ALTER is blocked because of SNW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add primary key (c1)";
+--source include/wait_condition.inc
+--echo # Unblock ALTERs.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping first ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping another ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Check that SNRW lock is incompatible with SNW lock.
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above LOCK TABLES is blocked because of SNW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Unblock ALTER and thus LOCK TABLES.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping LOCK TABLES
+--reap
+insert into t1 values (1);
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Check that X lock is incompatible with SNW lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above RENAME is blocked because of SNW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Unblock ALTER and thus RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping RENAME TABLE
+--reap
+--echo # Revert back to original state of things.
+rename table t2 to t1;
+--echo #
+--echo # There is no need to check that upgrade from SNW/SNRW to X is
+--echo # blocked by presence of another SNW lock because SNW/SNRW is
+--echo # incompatible with SNW anyway.
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo #
+--echo # 6) Acquire SNRW lock on the table. 
+--echo #
+--echo #
+lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that S and SH locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+--echo # Check that SR lock is incompatible with SNRW lock.
+--echo # Sending:
+--send select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above SELECT is blocked because of SNRW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "select count(*) from t1";
+--source include/wait_condition.inc
+--echo # Unblock SELECT.
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping SELECT.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that SW lock is incompatible with SNRW lock.
+--echo # Sending:
+--send delete from t1 limit 1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above DELETE is blocked because of SNRW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "delete from t1 limit 1";
+--source include/wait_condition.inc
+--echo # Unblock DELETE.
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping DELETE.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that SNW lock is incompatible with SNRW lock.
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above ALTER is blocked because of UNWR lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add primary key (c1)";
+--source include/wait_condition.inc
+--echo # Unblock ALTER.
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that SNRW lock is incompatible with SNRW lock.
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above LOCK TABLES is blocked because of SNRW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Unblock waiting LOCK TABLES.
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping LOCK TABLES
+--reap
+insert into t1 values (1);
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that X lock is incompatible with SNRW lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Check that the above RENAME is blocked because of SNRW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping RENAME TABLE
+--reap
+--echo # Revert back to original state of things.
+rename table t2 to t1;
+--echo #
+--echo # There is no need to check that upgrade from SNW/SNRW to X is
+--echo # blocked by presence of another SNRW lock because SNW/SNRW is
+--echo # incompatible with SNRW anyway.
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo #
+--echo # 7) Now do the same round of tests for X lock. We use additional
+--echo #    table to get long-lived lock of this type.
+--echo #
+create table t2 (c1 int);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked
+--echo # after acquiring X lock on t1.
+lock tables t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that S lock in incompatible with X lock.
+--echo # Sending:
+--send handler t1 open;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above HANDLER statement is blocked because of X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "handler t1 open";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping HANDLER.
+--reap
+handler t1 close;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SH lock in incompatible with X lock.
+--echo # Sending:
+--send select column_name from information_schema.columns where table_schema='test' and table_name='t1';
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above SELECT ... FROM I_S ... statement is blocked
+--echo # because of X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info like "select column_name from information_schema.columns%";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping SELECT ... FROM I_S.
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SR lock in incompatible with X lock.
+--echo # Sending:
+--send select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above SELECT statement is blocked
+--echo # because of X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "select count(*) from t1";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping SELECT.
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SW lock in incompatible with X lock.
+--echo # Sending:
+--send delete from t1 limit 1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above DELETE statement is blocked
+--echo # because of X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "delete from t1 limit 1";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping DELETE.
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SNW lock is incompatible with X lock.
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above ALTER statement is blocked
+--echo # because of X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "alter table t1 add primary key (c1)";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SNRW lock is incompatible with X lock.
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above LOCK TABLE statement is blocked
+--echo # because of X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping LOCK TABLE.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that X lock is incompatible with X lock.
+--echo # Sending:
+--send rename table t1 to t3;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above RENAME statement is blocked
+--echo # because of X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t3";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping RENAME.
+--reap
+rename table t3 to t1;
+
+--echo #
+--echo # B) Now let us test compatibility in cases when both locks
+--echo #    are pending. I.e. let us test rules for priorities between
+--echo #    different types of metadata locks.
+--echo #
+
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo #
+--echo # 1) Check compatibility for pending SNW lock.
+--echo #
+--echo # Acquire SW lock in order to create pending SNW lock later.
+begin;
+insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending SNW lock.
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that ALTER TABLE is waiting with pending SNW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "alter table t1 add primary key (c1)";
+--source include/wait_condition.inc
+--echo # Check that S, SH and SR locks are compatible with pending SNW
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+select count(*) from t1;
+--echo # Check that SW is incompatible with pending SNW
+--echo # Sending:
+--send delete from t1 limit 1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above DELETE is blocked because of pending SNW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "delete from t1 limit 1";
+--source include/wait_condition.inc
+--echo # Unblock ALTER TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping ALTER.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping DELETE.
+--reap
+--echo #
+--echo # We can't do similar check for SNW, SNRW and X locks because
+--echo # they will also be blocked by active SW lock.
+--echo #
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo #
+--echo # 2) Check compatibility for pending SNRW lock.
+--echo #
+--echo # Acquire SR lock in order to create pending SNRW lock.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending SNRW lock.
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that LOCK TABLE is waiting with pending SNRW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Check that S and SH locks are compatible with pending SNRW
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+--echo # Check that SR is incompatible with pending SNRW
+--echo # Sending:
+--send select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above SELECT is blocked because of pending SNRW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "select count(*) from t1";
+--source include/wait_condition.inc
+--echo # Unblock LOCK TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping LOCK TABLE.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping SELECT.
+--reap
+--echo # Restore pending SNRW lock.
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that LOCK TABLE is waiting with pending SNRW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Check that SW is incompatible with pending SNRW
+--echo # Sending:
+--send insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above INSERT is blocked because of pending SNRW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "insert into t1 values (1)";
+--source include/wait_condition.inc
+--echo # Unblock LOCK TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping LOCK TABLE.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping INSERT.
+--reap
+--echo # Restore pending SNRW lock.
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that LOCK TABLE is waiting with pending SNRW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Check that SNW is compatible with pending SNRW
+--echo # So ALTER TABLE statements are not starved by LOCK TABLEs.
+--error ER_DUP_ENTRY
+alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Unblock LOCK TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping LOCK TABLE.
+--reap
+unlock tables;
+--echo #
+--echo # We can't do similar check for SNRW and X locks because
+--echo # they will also be blocked by active SR lock.
+--echo #
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo #
+--echo # 3) Check compatibility for pending X lock.
+--echo #
+--echo # Acquire SR lock in order to create pending X lock.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending X lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME TABLE is waiting with pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SH locks are compatible with pending X
+select column_name from information_schema.columns where
+  table_schema='test' and table_name='t1';
+--echo # Check that S is incompatible with pending X
+--echo # Sending:
+--send handler t1 open;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above HANDLER OPEN is blocked because of pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "handler t1 open";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping HANDLER t1 OPEN.
+--reap
+handler t1 close;
+--echo # Restore pending X lock.
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending X lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME TABLE is waiting with pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SR is incompatible with pending X
+--echo # Sending:
+--send select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above SELECT is blocked because of pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "select count(*) from t1";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping SELECT.
+--reap
+--echo # Restore pending X lock.
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending X lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME TABLE is waiting with pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SW is incompatible with pending X
+--echo # Sending:
+--send delete from t1 limit 1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above DELETE is blocked because of pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "delete from t1 limit 1";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping DELETE.
+--reap
+--echo # Restore pending X lock.
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending X lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME TABLE is waiting with pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SNW is incompatible with pending X
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above ALTER TABLE is blocked because of pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "alter table t1 add primary key (c1)";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # Restore pending X lock.
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending X lock.
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that RENAME TABLE is waiting with pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that SNRW is incompatible with pending X
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'mdl_con3'.
+connection mdl_con3;
+--echo # Check that the above LOCK TABLES is blocked because of pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Unblock RENAME TABLE.
+handler t1 close;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reaping LOCK TABLES.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+
+--echo #
+--echo #
+--echo # C) Now let us test how type-of-operation locks are handled in
+--echo #    transactional context. Obviously we are mostly interested
+--echo #    in conflicting types of locks.
+--echo #
+
+--echo #
+--echo # 1) Let us check how various locks used within transactional
+--echo #    context interact with active/pending SNW lock.
+--echo #
+--echo #    We start with case when we are acquiring lock on the table
+--echo #    which was not used in the transaction before.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create an active SNW lock on t2.
+--echo # We have to use DEBUG_SYNC facility as otherwise SNW lock
+--echo # will be immediately released (or upgraded to X lock).
+insert into t2 values (1), (1);
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send alter table t2 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # SR lock should be acquired without any waiting.
+select count(*) from t2;
+commit;
+--echo # Now let us check that we will wait in case of SW lock.
+begin;
+select count(*) from t1;
+--echo # Sending:
+--send insert into t2 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above INSERT is blocked.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "insert into t2 values (1)";
+--source include/wait_condition.inc
+--echo # Unblock ALTER TABLE and thus INSERT.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap INSERT.
+--reap
+commit;
+--echo #
+--echo # Now let us see what happens when we are acquiring lock on the table
+--echo # which is already used in transaction.
+--echo #
+--echo # *) First, case when transaction which has SR lock on the table also
+--echo #    locked in SNW mode acquires yet another SR lock and then tries
+--echo #    to acquire SW lock.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create an active SNW lock on t1.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+set debug_sync= 'now WAIT_FOR locked';
+--echo # We should still be able to get SR lock without waiting.
+select count(*) from t1;
+--echo # Since the above ALTER TABLE is not upgrading SNW lock to X by waiting
+--echo # for SW lock we won't create deadlock.
+--echo # So the below INSERT should not end-up with ER_LOCK_DEADLOCK error.
+--echo # Sending:
+--send insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above INSERT is blocked.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "insert into t1 values (1)";
+--source include/wait_condition.inc
+--echo # Unblock ALTER TABLE and thus INSERT.
+set debug_sync= 'now SIGNAL finish';
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap INSERT.
+--reap
+commit;
+--echo #
+--echo # **) Now test in which transaction that has SW lock on the table
+--echo #     against which there is pending SNW lock acquires SR and SW
+--echo #     locks on this table.
+--echo #
+begin;
+insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create pending SNW lock on t1.
+--echo # Sending:
+--send alter table t1 add primary key (c1);
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until ALTER TABLE starts waiting for SNW lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "alter table t1 add primary key (c1)";
+--source include/wait_condition.inc
+--echo # We should still be able to get both SW and SR locks without waiting.
+select count(*) from t1;
+delete from t1 limit 1;
+--echo # Unblock ALTER TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo # 2) Now similar tests for active SNW lock which is being upgraded
+--echo #    to X lock.
+--echo #
+--echo #    Again we start with case when we are acquiring lock on the
+--echo #    table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Start transaction which will prevent SNW -> X upgrade from
+--echo # completing immediately.
+begin;
+select count(*) from t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create SNW lock pending upgrade to X on t2.
+--echo # Sending:
+--send alter table t2 add column c2 int;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until ALTER TABLE starts waiting X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "alter table t2 add column c2 int";
+--source include/wait_condition.inc
+--echo # Check that attempt to acquire SR lock on t2 causes waiting.
+--echo # Sending:
+--send select count(*) from t2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above SELECT is blocked.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "select count(*) from t2";
+--source include/wait_condition.inc
+--echo # Unblock ALTER TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap ALTER TABLE.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap SELECT.
+--reap
+commit;
+--echo # Do similar check for SW lock.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Start transaction which will prevent SNW -> X upgrade from
+--echo # completing immediately.
+begin;
+select count(*) from t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create SNW lock pending upgrade to X on t2.
+--echo # Sending:
+--send alter table t2 drop column c2;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until ALTER TABLE starts waiting X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "alter table t2 drop column c2";
+--source include/wait_condition.inc
+--echo # Check that attempt to acquire SW lock on t2 causes waiting.
+--echo # Sending:
+--send insert into t2 values (1);
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above INSERT is blocked.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "insert into t2 values (1)";
+--source include/wait_condition.inc
+--echo # Unblock ALTER TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap ALTER TABLE.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap INSERT.
+--reap
+commit;
+--echo #
+--echo # Test for the case in which we are acquiring lock on the table
+--echo # which is already used in transaction.
+--echo #
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create SNW lock pending upgrade to X.
+--echo # Sending:
+--send alter table t1 add column c2 int;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until ALTER TABLE starts waiting X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "alter table t1 add column c2 int";
+--source include/wait_condition.inc
+--echo # Check that transaction is still able to acquire SR lock.
+select count(*) from t1;
+--echo # Waiting trying to acquire SW lock will cause deadlock and
+--echo # therefore should cause an error.
+--error ER_LOCK_DEADLOCK
+delete from t1 limit 1;
+--echo # Unblock ALTER TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap ALTER TABLE.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo # 3) Check how various locks used within transactional context
+--echo #    interact with active/pending SNRW lock.
+--echo # 
+--echo #    Once again we start with case when we are acquiring lock on
+--echo #    the table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+lock table t2 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Attempt to acquire SR should be blocked. It should
+--echo # not cause errors as it does not creates deadlock.
+--echo # Sending:
+--send select count(*) from t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that the above SELECT is blocked 
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "select count(*) from t2";
+--source include/wait_condition.inc
+--echo # Unblock SELECT.
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap SELECT.
+--reap
+commit;
+--echo # Repeat the same test for SW lock.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+lock table t2 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Again attempt to acquire SW should be blocked and should
+--echo # not cause any errors.
+--echo # Sending:
+--send delete from t2 limit 1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Check that the above DELETE is blocked 
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "delete from t2 limit 1";
+--source include/wait_condition.inc
+--echo # Unblock DELETE.
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap DELETE.
+--reap
+commit;
+--echo #
+--echo # Now coverage for the case in which we are acquiring lock on
+--echo # the table which is already used in transaction and against
+--echo # which there is a pending SNRW lock request.
+--echo #
+--echo # *) Let us start with case when transaction has only a SR lock.
+--echo #
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until LOCK TABLE is blocked creating pending request for X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Check that another instance of SR lock is granted without waiting.
+select count(*) from t1;
+--echo # Attempt to wait for SW lock will lead to deadlock, thus
+--echo # the below statement should end with ER_LOCK_DEADLOCK error.
+--error ER_LOCK_DEADLOCK
+delete from t1 limit 1;
+--echo # Unblock LOCK TABLES.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap LOCK TABLES.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo # **) Now case when transaction has a SW lock.
+--echo #
+begin;
+delete from t1 limit 1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Sending:
+--send lock table t1 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until LOCK TABLE is blocked creating pending request for X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "lock table t1 write";
+--source include/wait_condition.inc
+--echo # Check that both SR and SW locks are granted without waiting
+--echo # and errors.
+select count(*) from t1;
+insert into t1 values (1, 1);
+--echo # Unblock LOCK TABLES.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap LOCK TABLES.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo # 4) Check how various locks used within transactional context
+--echo #    interact with active/pending X lock.
+--echo # 
+--echo #    As usual we start with case when we are acquiring lock on
+--echo #    the table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Start transaction which will prevent X lock from going away
+--echo # immediately.
+begin;
+select count(*) from t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create pending X lock on t2.
+--echo # Sending:
+--send rename table t2 to t3;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until RENAME TABLE starts waiting with pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t2 to t3";
+--source include/wait_condition.inc
+--echo # Check that attempt to acquire SR lock on t2 causes waiting.
+--echo # Sending:
+--send select count(*) from t2;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above SELECT is blocked.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "select count(*) from t2";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap RENAME TABLE.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap SELECT.
+--error ER_NO_SUCH_TABLE
+--reap
+commit;
+rename table t3 to t2;
+--echo # The same test for SW lock.
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Start transaction which will prevent X lock from going away
+--echo # immediately.
+begin;
+select count(*) from t2;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create pending X lock on t2.
+--echo # Sending:
+--send rename table t2 to t3;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until RENAME TABLE starts waiting with pending X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t2 to t3";
+--source include/wait_condition.inc
+--echo # Check that attempt to acquire SW lock on t2 causes waiting.
+--echo # Sending:
+--send delete from t2 limit 1;
+--echo # 
+--echo # Switching to connection 'mdl_con2'.
+connection mdl_con2;
+--echo # Check that the above DELETE is blocked.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "delete from t2 limit 1";
+--source include/wait_condition.inc
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap RENAME TABLE.
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap DELETE.
+--error ER_NO_SUCH_TABLE
+--reap
+commit;
+rename table t3 to t2;
+--echo #
+--echo # Coverage for the case in which we are acquiring lock on
+--echo # the table which is already used in transaction and against
+--echo # which there is a pending X lock request.
+--echo #
+--echo # *) The first case is when transaction has only a SR lock.
+--echo #
+begin;
+select count(*) from t1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until RENAME TABLE is blocked creating pending request for X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that another instance of SR lock is granted without waiting.
+select count(*) from t1;
+--echo # Attempt to wait for SW lock will lead to deadlock, thus
+--echo # the below statement should end with ER_LOCK_DEADLOCK error.
+--error ER_LOCK_DEADLOCK
+delete from t1 limit 1;
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo # **) The second case is when transaction has a SW lock.
+--echo #
+begin;
+delete from t1 limit 1;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Sending:
+--send rename table t1 to t2;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait until RENAME TABLE is blocked creating pending request for X lock.
+let $wait_condition=
+select count(*) = 1 from information_schema.processlist
+where state = "Waiting for table" and info = "rename table t1 to t2";
+--source include/wait_condition.inc
+--echo # Check that both SR and SW locks are granted without waiting
+--echo # and errors.
+select count(*) from t1;
+insert into t1 values (1, 1);
+--echo # Unblock RENAME TABLE.
+commit;
+--echo # 
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Reap RENAME TABLE.
+--error ER_TABLE_EXISTS_ERROR
+--reap
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+
+--echo # Clean-up.
+disconnect mdl_con1;
+disconnect mdl_con2;
+disconnect mdl_con3;
+set debug_sync= 'RESET';
+drop table t1, t2;
+
+
+--echo #
+--echo # Additional coverage for some scenarios in which not quite
+--echo # correct use of S metadata locks by HANDLER statement might
+--echo # have caused deadlocks.
+--echo #
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+connect(handler_con1,localhost,root,,);
+connect(handler_con2,localhost,root,,);
+connection default;
+create table t1 (i int);
+create table t2 (j int);
+insert into t1 values (1);
+
+--echo #
+--echo # First, check scenario in which we upgrade SNRW lock to X lock
+--echo # on a table while having HANDLER READ trying to acquire TL_READ
+--echo # on the same table.
+--echo #
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+lock table t1 write;
+--echo # Upgrade SNRW to X lock.
+--echo # Sending:
+--send alter table t1 add column j int;
+--echo # 
+--echo # Switching to connection 'handler_con2'.
+connection handler_con2;
+--echo # Wait until ALTER is blocked during upgrade.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 add column j int";
+--source include/wait_condition.inc
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # The below statement should not cause deadlock.
+--send handler t1 read first;
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+--echo # Reap ALTER TABLE.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap HANDLER READ.
+--reap
+handler t1 close;
+
+--echo #
+--echo # Now, check scenario in which upgrade of SNRW lock to X lock
+--echo # can be blocked by HANDLER which is open in connection currently
+--echo # waiting to get table-lock owned by connection doing upgrade.
+--echo #
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+lock table t1 write, t2 read;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Execute statement which will be blocked on table-level lock
+--echo # owned by connection 'handler_con1'.
+--echo # Sending:
+--send insert into t2 values (1);
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+--echo # Wait until INSERT is blocked on table-level lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Table lock" and info = "insert into t2 values (1)";
+--source include/wait_condition.inc
+--echo # The below statement should not cause deadlock.
+alter table t1 drop column j;
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap INSERT.
+--reap
+handler t1 close;
+
+--echo #
+--echo # Then, check the scenario in which upgrade of SNRW lock to X
+--echo # lock is blocked by HANDLER which is open in connection currently
+--echo # waiting to get SW lock on the same table.
+--echo #
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+lock table t1 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # The below insert should be blocked because active SNRW lock on 't1'.
+--echo # Sending:
+--send insert into t1 values (1);
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+--echo # Wait until INSERT is blocked because of SNRW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "insert into t1 values (1)";
+--echo # The below ALTER TABLE will be blocked because of presence of HANDLER.
+--echo # Sending:
+--send alter table t1 add column j int;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # INSERT should be chosen as victim for resolving deadlock.
+--echo # Reaping INSERT.
+--error ER_LOCK_DEADLOCK
+--reap
+--echo # Close HANDLER to unblock ALTER TABLE.
+handler t1 close;
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+--echo # Reaping ALTER TABLE.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+
+--echo #
+--echo # Finally, test in which upgrade of SNRW lock to X lock is blocked
+--echo # by HANDLER which is open in connection currently waiting to get
+--echo # SR lock on the table on which lock is upgraded.
+--echo #
+handler t1 open;
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+lock table t1 write, t2 write;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # The below insert should be blocked because active SNRW lock on 't1'.
+--echo # Sending:
+--send insert into t2 values (1);
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+--echo # Wait until INSERT is blocked because of SNRW lock.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "insert into t2 values (1)";
+--echo # The below ALTER TABLE will be blocked because of presence of HANDLER.
+--echo # Sending:
+--send alter table t1 drop column j;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+--echo # INSERT should be chosen as victim for resolving deadlock.
+--echo # Reaping INSERT.
+--error ER_LOCK_DEADLOCK
+--reap
+--echo # Close HANDLER to unblock ALTER TABLE.
+handler t1 close;
+--echo # 
+--echo # Switching to connection 'handler_con1'.
+connection handler_con1;
+--echo # Reaping ALTER TABLE.
+--reap
+unlock tables;
+--echo # 
+--echo # Switching to connection 'default'.
+connection default;
+
+--echo # Clean-up.
+disconnect handler_con1;
+disconnect handler_con2;
+drop tables t1, t2;
+
+
+--echo #
 --echo # Test coverage for basic deadlock detection in metadata
 --echo # locking subsystem.
 --echo #
@@ -236,47 +2543,47 @@ connection default;
 --echo # Switching to connection 'deadlock_con1'.
 connection deadlock_con1;
 begin;
-insert into t1 values (1);
-
---echo #
---echo # Switching to connection 'deadlock_con2'.
-connection deadlock_con2;
-begin;
-insert into t3 values (1);
+insert into t2 values (1);
 
 --echo #
 --echo # Switching to connection 'default'.
 connection default;
---echo # Send:
---send rename table t2 to t0, t3 to t2, t0 to t3;
+lock table t1 write;
 
 --echo #
 --echo # Switching to connection 'deadlock_con1'.
 connection deadlock_con1;
---echo # Wait until the above RENAME TABLE is blocked because it has to wait
---echo # for 'deadlock_con2' which holds shared metadata lock on 't3'.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table" and info = "rename table t2 to t0, t3 to t2, t0 to t3";
---source include/wait_condition.inc
 --echo # The below SELECT statement should wait for metadata lock
---echo # on table 't2' and should not produce ER_LOCK_DEADLOCK
+--echo # on table 't1' and should not produce ER_LOCK_DEADLOCK
 --echo # immediately as no deadlock is possible at the moment.
---send select * from t2;
+--send select * from t1;
 
 --echo #
---echo # Switching to connection 'deadlock_con3'.
-connection deadlock_con3;
---echo # Wait until the above SELECT * FROM t2 is starts waiting
---echo # for an exclusive metadata lock to go away.
+--echo # Switching to connection 'deadlock_con2'.
+connection deadlock_con2;
+--echo # Wait until the above SELECT * FROM t1 is starts waiting
+--echo # for an UNRW metadata lock to go away.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table" and info = "select * from t2";
+  where state = "Waiting for table" and info = "select * from t1";
 --source include/wait_condition.inc
 
 --echo # Send RENAME TABLE statement that will deadlock with the
 --echo # SELECT statement and thus should abort the latter.
---send rename table t1 to t5, t2 to t1, t5 to t2;
+--send rename table t1 to t0, t2 to t1, t0 to t2;
+
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Wait till above RENAME TABLE is blocked while holding
+--echo # pending X lock on t1.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "rename table t1 to t0, t2 to t1, t0 to t2";
+--source include/wait_condition.inc
+--echo # Allow the above RENAME TABLE to acquire lock on t1 and
+--echo # create pending lock on t2 thus creating deadlock.
+unlock tables;
 
 --echo #
 --echo # Switching to connection 'deadlock_con1'.
@@ -284,17 +2591,17 @@ connection deadlock_con1;
 --echo # Since the latest RENAME TABLE entered in deadlock with SELECT
 --echo # statement the latter should be aborted and emit ER_LOCK_DEADLOCK
 --echo # error.
---echo # Reap SELECT * FROM t2.
+--echo # Reap SELECT * FROM t1.
 --error ER_LOCK_DEADLOCK
 --reap
 
 --echo #
 --echo # Again let us check that failure of the SELECT statement has not
---echo # released metadata lock on table 't1', i.e. that the latest RENAME
+--echo # released metadata lock on table 't2', i.e. that the latest RENAME
 --echo # is blocked.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table" and info = "rename table t1 to t5, t2 to t1, t5 to t2";
+  where state = "Waiting for table" and info = "rename table t1 to t0, t2 to t1, t0 to t2";
 --source include/wait_condition.inc
 --echo # Commit transaction to unblock this RENAME TABLE.
 commit;
@@ -302,19 +2609,7 @@ commit;
 --echo #
 --echo # Switching to connection 'deadlock_con2'.
 connection deadlock_con2;
---echo # Commit transaction to unblock the first RENAME TABLE.
-commit;
-
---echo #
---echo # Switching to connection 'default'.
-connection default;
---echo # Reap RENAME TABLE t2 TO t0 ... .
---reap
-
---echo #
---echo # Switching to connection 'deadlock_con3'.
-connection deadlock_con3;
---echo # Reap RENAME TABLE t1 TO t5 ... .
+--echo # Reap RENAME TABLE ... .
 --reap;
 
 --echo #
@@ -328,12 +2623,17 @@ drop tables t1, t2, t3, t4;
 --echo # also takes into account requests for metadata lock upgrade.
 --echo #
 create table t1 (i int);
+insert into t1 values (1);
+--echo # Avoid race which occurs when SELECT in 'deadlock_con1' connection
+--echo # accesses table before the above INSERT unlocks the table and thus
+--echo # its result becomes visible to other connections.
+select * from t1;
 
 --echo #
 --echo # Switching to connection 'deadlock_con1'.
 connection deadlock_con1;
 begin;
-insert into t1 values (1);
+select * from t1;
 
 --echo #
 --echo # Switching to connection 'default'.
@@ -376,62 +2676,6 @@ connection default;
 
 drop table t2;
 
---echo #
---echo # Finally, test case in which deadlock (or potentially livelock) occurs
---echo # between metadata locking subsystem and table definition cache/table
---echo # locks, but which should still be detected by our empiric.
---echo #
-create table t1 (i int);
-
---echo #
---echo # Switching to connection 'deadlock_con1'.
-connection deadlock_con1;
-begin;
-insert into t1 values (1);
-
---echo #
---echo # Switching to connection 'default'.
-connection default;
-lock tables t1 write;
-
---echo #
---echo # Switching to connection 'deadlock_con1'.
-connection deadlock_con1;
---echo # Send:
---send insert into t1 values (2);
-
---echo #
---echo # Switching to connection 'default'.
-connection default;
---echo # Wait until INSERT in connection 'deadlock_con1' is blocked on
---echo # table-level lock.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "insert into t1 values (2)";
---source include/wait_condition.inc
-
---echo # Send:
---send alter table t1 add column j int;
-
---echo #
---echo # Switching to connection 'deadlock_con1'.
-connection deadlock_con1;
---echo # The above ALTER TABLE statement should cause INSERT statement in
---echo # this connection to be aborted and emit ER_LOCK_DEADLOCK error.
---echo # Reap INSERT
---error ER_LOCK_DEADLOCK
---reap
---echo # Commit transaction to unblock ALTER TABLE.
-commit;
-
---echo #
---echo # Switching to connection 'default'.
-connection default;
---echo # Reap ALTER TABLE.
---reap
-unlock tables;
-
-drop table t1;
 disconnect deadlock_con1;
 disconnect deadlock_con2;
 disconnect deadlock_con3;
@@ -535,7 +2779,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked';
 
 --echo #
 --echo # Continue the INSERT once CREATE waits for exclusive lock
-SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish';
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
 --echo # Try to create that table.
 --send CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1))
 
@@ -575,7 +2819,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked';
 
 --echo #
 --echo # Continue the INSERT once CREATE waits for exclusive lock
-SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish';
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
 --echo # Try to create that table.
 --send CREATE TABLE t1 LIKE t2
 
@@ -615,19 +2859,19 @@ create table t1 (i int);
 --echo # Let us check that we won't deadlock if during filling
 --echo # of I_S table we encounter conflicting metadata lock
 --echo # which owner is in its turn waiting for our connection.
-lock tables t1 write;
+lock tables t1 read;
 
 --echo # Switching to connection 'con46044'.
 connection con46044;
 --echo # Sending:
---send create table t2 select * from t1;
+--send create table t2 select * from t1 for update;
 
 --echo # Switching to connection 'default'.
 connection default;
 --echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "create table t2 select * from t1";
+  where state = "Table lock" and info = "create table t2 select * from t1 for update";
 --source include/wait_condition.inc
 
 --echo # First let us check that SHOW FIELDS/DESCRIBE doesn't
@@ -668,19 +2912,19 @@ drop table t2;
 
 --echo # Switching to connection 'con46044_2'.
 connection con46044_2;
-lock tables t1 write;
+lock tables t1 read;
 
 --echo # Switching to connection 'con46044'.
 connection con46044;
 --echo # Sending:
---send create table t2 select * from t1;
+--send create table t2 select * from t1 for update;
 
 --echo # Switching to connection 'default'.
 connection default;
 --echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "create table t2 select * from t1";
+  where state = "Table lock" and info = "create table t2 select * from t1 for update";
 --source include/wait_condition.inc
 
 --echo # Let us check that SHOW FIELDS/DESCRIBE gets blocked.
@@ -710,19 +2954,19 @@ drop table t2;
 
 --echo # Switching to connection 'con46044_2'.
 connection con46044_2;
-lock tables t1 write;
+lock tables t1 read;
 
 --echo # Switching to connection 'con46044'.
 connection con46044;
 --echo # Sending:
---send create table t2 select * from t1;
+--send create table t2 select * from t1 for update;
 
 --echo # Switching to connection 'default'.
 connection default;
 --echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "create table t2 select * from t1";
+  where state = "Table lock" and info = "create table t2 select * from t1 for update";
 --source include/wait_condition.inc
 
 --echo # Check that I_S query which reads only .FRMs gets blocked.
@@ -753,19 +2997,19 @@ drop table t2;
 
 --echo # Switching to connection 'con46044_2'.
 connection con46044_2;
-lock tables t1 write;
+lock tables t1 read;
 
 --echo # Switching to connection 'con46044'.
 connection con46044;
 --echo # Sending:
---send create table t2 select * from t1;
+--send create table t2 select * from t1 for update;
 
 --echo # Switching to connection 'default'.
 connection default;
 --echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Table lock" and info = "create table t2 select * from t1";
+  where state = "Table lock" and info = "create table t2 select * from t1 for update";
 --source include/wait_condition.inc
 
 --echo # Finally, check that I_S query which does full-blown table open
@@ -817,7 +3061,7 @@ create table t1 (c1 int primary key, c2 int, c3 int);
 insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
 
 begin;
-update t1 set c3=c3+1 where c2=3;
+select * from t1 where c2 = 3;
 
 --echo #
 --echo # Switching to connection 'con46273'.
@@ -829,12 +3073,12 @@ set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR
 --echo # Switching to connection 'default'.
 connection default;
 set debug_sync='now WAIT_FOR alter_table_locked';
-set debug_sync='wait_for_lock SIGNAL alter_go';
+set debug_sync='before_open_table_wait_refresh SIGNAL alter_go';
 --echo # The below statement should get ER_LOCK_DEADLOCK error
 --echo # (i.e. it should not allow ALTER to proceed, and then
 --echo # fail due to 't1' changing its name to 't2').
 --error ER_LOCK_DEADLOCK
-update t1 set c3=c3+1 where c2=4;
+update t1 set c3=c3+1 where c2 = 3;
 
 --echo #
 --echo # Let us check that failure of the above statement has not released
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 6f8cc94e6b7..68b44a33428 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -474,7 +474,8 @@ drop table t1,t2;
 
 #
 # Test alter table and a concurrent multi update
-# (This will force update to reopen tables)
+# (Before we have introduced data-lock-aware metadata locks
+#  this test case forced update to reopen tables).
 #
 
 create table t1 (a int, b int);
@@ -494,9 +495,9 @@ send alter table t1 add column c int default 100 after a;
 connect (updater,localhost,root,,test);
 connection updater;
 # Wait till "alter table t1 ..." of session changer is in work.
-# = There is one session is in state "Locked".
+# = There is one session waiting.
 let $wait_condition= select count(*)= 1 from information_schema.processlist
-                     where state= 'Table lock';
+                     where state= 'Waiting for table';
 --source include/wait_condition.inc
 send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;
 
@@ -505,9 +506,9 @@ connection locker;
 # - "alter table t1 ..." of session changer and
 # - "update t1, v1 ..." of session updater
 # are in work.
-# = There are two session is in state "Locked".
+# = There are two session waiting.
 let $wait_condition= select count(*)= 2 from information_schema.processlist
-                     where state= 'Table lock';
+                     where state= 'Waiting for table';
 --source include/wait_condition.inc
 unlock tables;
 
diff --git a/mysql-test/t/truncate_coverage.test b/mysql-test/t/truncate_coverage.test
index 9870fbb5ebf..b7c08b03c8b 100644
--- a/mysql-test/t/truncate_coverage.test
+++ b/mysql-test/t/truncate_coverage.test
@@ -23,14 +23,14 @@ DROP TABLE IF EXISTS t1;
 CREATE TABLE t1 (c1 INT);
 INSERT INTO t1 VALUES (1);
 #
-# Start a transaction and execute a DML in it. Since 5.4.4 this leaves
-# a shared meta data lock (MDL) behind. TRUNCATE shall block on it.
+# Acquire a shared metadata lock on table by opening HANDLER for it and wait.
+# TRUNCATE shall block on this metadata lock.
+# We can't use normal DML as such statements would also block LOCK TABLES.
 #
 --echo #
 --echo # connection con1
 --connect (con1, localhost, root,,)
-START TRANSACTION;
-INSERT INTO t1 VALUES (2);
+HANDLER t1 OPEN;
 #
 # Get connection id of default connection.
 # Lock the table and start TRUNCATE, which will block on MDL upgrade.
@@ -48,12 +48,17 @@ send TRUNCATE TABLE t1;
 # from wait_while_table_is_used().
 #
 --echo #
---echo # connection con1
---connection con1
+--echo # connection con2
+--connect (con2, localhost, root,,)
 SET DEBUG_SYNC='now WAIT_FOR waiting';
 let $invisible_assignment_in_select = `SELECT @id := $ID`;
 KILL QUERY @id;
-COMMIT;
+--disconnect con2
+--echo #
+--echo # connection con1
+--connection con1
+--echo # Release shared metadata lock by closing HANDLER.
+HANDLER t1 CLOSE;
 --disconnect con1
 --echo #
 --echo # connection default
@@ -69,14 +74,14 @@ SET DEBUG_SYNC='RESET';
 CREATE TABLE t1 (c1 INT);
 INSERT INTO t1 VALUES (1);
 #
-# Start a transaction and execute a DML in it. Since 5.4.4 this leaves
-# a shared meta data lock (MDL) behind. TRUNCATE shall block on it.
+# Acquire a shared metadata lock on table by opening HANDLER for it and wait.
+# TRUNCATE shall block on this metadata lock.
+# We can't use normal DML as such statements would also block LOCK TABLES.
 #
 --echo #
 --echo # connection con1
 --connect (con1, localhost, root,,)
-START TRANSACTION;
-INSERT INTO t1 VALUES (2);
+HANDLER t1 OPEN;
 #
 # Lock the table and start TRUNCATE, which will block on MDL upgrade.
 #
@@ -91,11 +96,15 @@ send TRUNCATE TABLE t1;
 # Commit to let TRUNCATE continue.
 #
 --echo #
---echo # connection con1
---connection con1
+--echo # connection con2
+--connect (con2, localhost, root,,)
 SET DEBUG_SYNC='now WAIT_FOR waiting';
 --remove_file $MYSQLD_DATADIR/test/t1.frm
-COMMIT;
+--disconnect con2
+--echo #
+--echo # connection con1
+--connection con1
+HANDLER t1 CLOSE;
 --disconnect con1
 --echo #
 --echo # connection default
@@ -129,7 +138,7 @@ INSERT INTO t1 VALUES (2);
 --echo # connection default
 --connection default
 let $ID= `SELECT @id := CONNECTION_ID()`;
-SET DEBUG_SYNC='mdl_acquire_exclusive_locks_wait SIGNAL waiting';
+SET DEBUG_SYNC='mdl_acquire_lock_wait SIGNAL waiting';
 send TRUNCATE TABLE t1;
 #
 # Get the default connection ID into a variable in an invisible statement.