WL#6049: Meta-data locking for FOREIGN KEY tables

This worklog consists of two parts:
1) Acquiring metadata locks on tables across foreign key relationships so
   that conflicting operations are blocked.
2) Updating FK metadata if a parent table changes.

Currently, InnoDB handles FOREIGN KEY constraints internally. This complicates a
few tasks, because MySQL meta-data locking is handled at the SQL layer 
and will not protect the FOREIGN KEY parent tables of a table during statement
execution. E.g. the parent table can be dropped while a InnoDB is running a
foreign key check. Taking metadata locks is required to fix these issues.

Also, foreign key definitions in the new DD are now not updated
if the parent changes. This is because FK metadata is owned by the child and
thus needs the child to be locked to be updated safely. Doing such FK updates
when the parent changes, will also be part of this worklog.

F1: Operations which insert or update rows in the child table of foreign
    key and which because of this have to check for presence of matching
    values in parent table must:
    1) be blocked by already running concurrent DDL statement on the parent
       table, provided that it reached the point where such statement
       acquires strong locks on the table.
    2) block concurrent DDL statements on the parent table which update
       its metadata until the end of transaction.
    3) be compatible with READ lock acquired on parent table by LOCK TABLES
       statement in other connection. They also must be compatible with
       ALTER TABLE ...ALGORITHM=COPY copying phase carried out on parent
       table in other connection (but not with metadata update phase of
       such ALTER TABLE).
    4) be incompatible (i.e. be blocked or block) with WRITE lock acquired
       on parent table by LOCK TABLES statement in other connection.
    5) in cases when FK check is not necessary due to FOREIGN_KEY_CHECKS=0
       mode the concurrent DDL statements on parent table can be allowed
       (but not LOCK TABLES WRITE).

F2: Operations which update or delete rows in the parent table of foreign
    key and which because of this have to check for absence of matching
    values in or carry out other referential action on child table must:
    1) be blocked by already running concurrent DDL statement on the child
       table, provided that it reached the point where such statement
       acquires strong locks on the table.
    2) block concurrent DDL statements on the child table which update
       its metadata until the end of transaction.
    3) for FKs with NO ACTION or RESTRICT as refential actions be compatible
       with READ lock acquired on child table by LOCK TABLES statement in
       other connection.
    4) for FKS with CASCADE/SET NULL and SET DEFAULT as referential actions
       be incompatible (i.e. be blocked or block) with READ lock acquired
       on child table by LOCK TABLES statement in other connection.
    5) be incompatible (i.e. be blocked or block) with WRITE lock acquired
       on child table by LOCK TABLES statement in other connection.
    6) in cases when FK check/referential action is not necessary due to
       FOREIGN_KEY_CHECKS=0 mode the concurrent DDL statements on child
       table can be allowed (but not LOCK TABLES WRITE statements).

F3: DDL statements which add or delete foreign key, such as CREATE TABLE,
    CREATE TABLE ... SELECT, ALTER TABLE ... ADD/DROP FOREIGN KEY,
    DROP TABLE/DATABASE deleting child table, must block concurrent DDL
    and DML on its parent table before they reach their conceptual commit
    point (or be blocked if concurrent statement has started earlier and
    reached certain point in its execution).

F4: DDL statements which change parent table definition and because of
    this change some metadata associated with FK must block concurrent
    DDL and DML on its child table before they reach their conceptual
    commit point (or be blocked if concurrent statement has started
    earlier and reached certain point in its execution). The examples
    of such statements are ALTER TABLE ... RENAME and RENAME TABLES
    on parent table; ALTER TABLE on the parent table which changes name
    of the unique constraint used by FK; CREATE TABLE variants, ALTER
    TABLE ... RENAME and RENAME TABLES which add valid parent table to
    FK when proper parent table was missing (this is possible in
    FOREIGN_KEY_CHECKS=0 mode); DROP TABLES which removes parent table
    without deleting child (again this is possible in FOREIGN_KEY_CHECKS=0
    mode).
    
F5: ALTER TABLE ... ADD FOREIGN KEY must block concurrent DDL and LOCK
    TABLES WRITE on the parent table (or be blocked by them if they
    have started earlier) starting from the beginning of the phase
    when foreign key being added is checked (this is table copying
    phase for ALTER TABLE ... ALGORITHM=COPY and main in-place phase
    for ALTER TABLE ... ALGORITHM=INPLACE) until end of statement.

F6: CREATE TABLE variants and ALTER TABLE which add new FK must correctly
    evaluate name of unique constraint in the parent table which will be
    used for FK and store this value in UNIQUE_CONSTRAINT_NAME column of
    mysql.foreign_keys data-dictionary table. If there is no such
    constraint, e.g. because parent table is missing (this is possible
    in FOREIGN_KEY_CHECKS=0 mode) or because non-unique key will be
    used (InnoDB allows this) NULL value must be stored.

F7: When CREATE TABLE variant, RENAME TABLES or ALTER TABLE ... RENAME
    add parent table for the FK which previously missed it (orphan FKs
    are possible in FOREIGN_KEY_CHECKS=0 mode) they need to set value
    of UNIQUE_CONSTRAINT_NAME column in row describing FK in the 
    mysql.foreign_keys data-dictionary table to the name of unique
    constraint in the parent table which will be used for FK checks
    or to NULL if no such unique constraint exists.

F7: When ALTER TABLE on the parent table for the FK changes table
    definition in such a way that name of unique constraint used
    for FK checks is changed (e.g. by renaming it, creating, deleting
    or re-creating constraint) this change of name should be
    correctly reflected in value of UNIQUE_CONSTRAINT_NAME column
    for row describing FK in mysql.foreign_keys data-dictionary
    table. NULL should be used if no such unique constraint exists
    as result of the ALTER TABLE.

F8: In cases when parent table for the FK is dropped without dropping
    the child table (this is possible when DROP TABLE or DROP DATABASE
    which delete parent table are executed in FOREIGN_KEY_CHECKS=0 mode)
    value of UNIQUE_CONSTRAINT_NAME column in row describing this FK
    in the mysql.foreign_keys data-dictionary table must be set to NULL.

F9: ALTER TABLE ... RENAME and RENAME TABLES which change the name of
    parent table for the FK must update REFERENCED_TABLE_SCHEMA and
    REFERENCED_TABLE_NAME values for row describing this FK in the
    mysql.foreign_keys data-dictionary table to reflect new parent
    schema and table name.

NF1: Implementation of this changes should not cause serious degradation
     of performance and scalability for DML workloads involving tables with
     foreign keys (the exact margin to be allowed to be determined after
     initial benchmarks).

The primary goal of this task is to ensure that foreign keys checks,
which are performed by storage engine (e.g. InnoDB), do not access
tables which are concurrently modified by DDL statements.

N0: Note that it is not a goal of this task to move FK processing
    to the SQL-layer.

Such an isolation of FK checks/actions from DDL statements can be
achieved by ensuring that FKs are taken into account when we acquire
metadata "operation-type aware" locks for DML and DDL statements.

This requires the following steps:

1) We should ensure that DML statements acquire appropriate metadata
   locks on all tables which they implicitly use for foreign key
   processing.

   *) If some statement is going to update or insert into a child
      table in a FK relationship, it should also acquire a SR lock
      on its parent as it is  going to be used for checks.
   *) If some statement updates or deletes rows from a parent table in
      a FK relationship, then it should also acquire SR or SW lock on
      the child table. The type of lock in this case depends on the
      referential action specified for this foreign key. If it is
      NO ACTION or RESTRICT, then the child table is going to be used
      for check only and SR lock is sufficient. If the FK has SET NULL,
      SET DEFAULT or CASCADE as referential action, then the statement
      might have to update child table and therefore SW lock on it is
      required.
   *) The above rules should be applied recursively in order to
      correctly handle situation when a cascading update or delete
      should trigger more checks or actions due to fact that child
      table serves as parent (or child) in a foreign key other than
      the one being currently processed.
   *) These rules should also take into account the value of the
      @@foreign_key_checks system variable which allows disabling of
      foreign keys check/actions for a particular connection. In this
      case there is no need to lock additional tables.

   We implement these rules by extending existing prelocking algorithm/
   process. Note that we don't plan to do full-blown table opens for
   parent/child tables on which MDL locks are acquired because of FKs.
   Instead we will acquire dd::Table
   objects for child tables which are going to be modified by cascading
   action and thus can cause more FK checks/actions.
  
   Also note that in theory it should be enough to acquire S lock to
   prevent concurrent DDL on parent/child tables. However, acquiring
   SR and SW locks as appropriate allows to properly isolate DML with
   FK checks/actions from concurrent LOCK TABLES on MDL layer. So we
   prefer the latter option.

2) DDL statements which add or drop foreign keys to the system (i.e.
   CREATE TABLE variants and ALTER TABLE ADD/DROP FOREIGN KEY) should
   acquire X lock on FK parent table before child table definition
   changes. This is because we want to guarantee that there can be no
   races between a DML statement, which analyzes foreign keys in which
   the table serves as a parent during building of the prelocking set
   and a DDL statement that adds/drops a foreign key referencing the
   table being analyzed by the first statement.

3) DDL statements which change parent table in FK might need to update
   FK metadata (e.g. RENAME TABLE on parent or ALTER TABLE which renames
   parent key of FK). To avoid races and to follow DD protocol such
   statements need to acquire X lock on FK child table before parent
   table definition changes.

4) DDL statements which add foreign keys to the system, like ALTER TABLE
   ADD FOREIGN KEY, or even simply check them need to get parent table's
   metadata and need to read data from it. Consequently, we need to
   acquire SR lock on parent table to ensure that is not being changed by
   concurrent DDL and to correctly isolate our DDL from concurrent LOCK
   TABLES.


The secondary goal of this task is to ensure that DDL on parent tables
correctly updates foreign key metadata. Specifically we need to update
the following metadata:

I)   Name of unique constraint in parent table for the FK.

     Current code misuses DD.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID to store
     id of supporting index for the FK in the child table. This is not
     what this column intended originally and will block implementation
     of I_S.REFERENTIAL_CONSTRAINTS as view on top of data-dictionary.
     
     This WL will replace this column with VARCHAR(64) fieldd which
     will store name of unique key in the parent table used for the FK --
     DD.FOREIGN_KEYS.UNIQUE_CONSTRAINT_NAME (storing id of unique key is
     technically possibly but is more cumbersome and requires extra join
     in I_S.REFERENTIAL_CONSTRAINTS implementation). Since InnoDB allows
     existence of child table and referencing non-unique keys we will
     make it NULLable and will store NULL value if parent key is non-unique
     or parent table doesn't exist.

     To keep UNIQUE_CONSTRAINT_NAME updated we need to:

     - Correctly evaluate its value when FK is added by CREATE TABLE or
       ALTER TABLE.
     - Replace NULL value with correct value when there is FK with
       missing parent table (this is possible if FOREIGN_KEY_CHECKS=0)
       and parent table is created (either by one of CREATE TABLE variants
       or by ALTER TABLE RENAME/RENAME TABLE).
     - Set it to NULL value when parent table is dropped by DROP TABLE or
       DROP DATABASE and child table is not (again this is only possible
       if FOREIGN_KEY_CHECKS=0).
     - Update name (possibly setting NULL value) if ALTER TABLE on
       parent table adds, drops of otherwise changes parent key for
       the FK.
     
II)  Referenced schema and table names (DD.FOREIGN_KEYS.REFERENCED_TABLE_SCHEMA
     and DD.FOREIGN_KEYS.REFERENCED_TABLE_NAME).

     Renaming of parent table by ALTER TABLE RENAME or RENAME TABLES need
     to update these fields to reflect new table names.

III) This WL is not going to update of name of referenced parent columns
     (DD.FOREIGN_KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME) when these
     columns are renamed in parent.
     

1) Changes to DML implementation
================================

We extend prelocking algorithm to take into account foreign keys
according to rules which were described in HLS.

a) We want to avoid processing of the same table in the
   same role towards FKs (and thus the same FKs!) by prelocking
   algorithm twice. To do this we will need to add  tuples already processed by algorithm
   to the hash representing prelocking set (currently called
   "Query_tables_list::sroutines").

   This is to be done by new add_used_fk_table() helper function
   which will also check if specific element is already present
   in the set.

   We introduce an enum with values representing various roles
   of table towards FKs in the statement:

   enum fk_table_role {
     /**
       There was insert or update on one of child tables.
       We will need to lookup new values in parent, so need
       to acquire SR lock on it.
      */
     FK_TABLE_ROLE_PARENT_CHECK= 0,
     /**
       There was delete or update on a parent table for FK which
       has RESTRICT/NO ACTION as corresponding rule. We will need
       to check if old parent key was referenced by child table,
       so need to acquire SR lock on it.
     */
     FK_TABLE_ROLE_CHILD_CHECK
     /**
       There was update on a parent table for FK with CASCADE,
       SET NULL, SET DEFAULT as on update rule or delete on a
       parent table for FK with SET NULL, SET DEFAULT as on delete
       rule. We might need to update rows in child table, so we need
       to acquire SW lock on it. We also need to take into account
       that child table might be parent for some other FKs, so
       such update needs to be handled recursively.
     */
     FK_TABLE_ROLE_CHILD_UPDATE,
     /**
       There was delete on a parent table for FK with CASCADE
       We might need to delete rows from child table, so we need to
       acquire SW lock on it. We also need to take into account
       that child table might be parent for some other FKs, so
       such update needs to be handled recursively.
     */
     FK_TABLE_ROLE_CHILD_DELETE};

   to be used as argument to this function.

   We need to adjust code dealing with "Query_tables_list::sroutines"
   hash to avoid construction of full-blown MDL_key just ot to identify
   objects in prelocking set.

   N1: To be able take into account that cascading actions update
       only subset of columns and thus in many cases do not require
       more cascading actions we need to use 
       in "Query_tables_list::sroutines" hash instead, as in this
       case it is important to differentiate actions on child tables
       which are caused by updates/deletes on parent tables of
       different FKs.

b) New process_table_fks() helper function is introduced
   with thefollowing logic:

  process_table_fks(..., table, is_insert, is_update, is_delete)
  {
    if ((@@foreign_key_checks are ON) && (table has foreign keys))
    {
      Mark statement as requiring prelocking if not already.

      for (Each foreign key in which this table is a child.)
      {
        if ((table is INSERTed into or UPDATED))
        {
          add_used_fk_table(parent_db, parent_name,
                            FK_TABLE_ROLE_PARENT_CHECK);
        }
      }

      for (Each foreign key in which this table is a parent.)
      {
        if (table is UPDATED &&
            on update rule is NO ACTION or RESTRICT ||
            table is DELETED &&
            on delete rule is NO ACTION or RESTRICT)
        {
          add_used_fk_table(child_db, child_table,
                            FK_TABLE_ROLE_CHILD_CHECK);
        }
        if (table is UPDATED &&
            on update rule is SET NULL, CASCADE or SET DEFAULT ||
            table is DELETED &&
            on delete rule is SET NULL, SET DEFAULT)
        {
            add_used_fk_table(child_db, child_table,
                              FK_TABLE_ROLE_CHILD_UPDATE);
        }
        if (table is DELETED && on delete rule is CASCADE)
        {
          add_used_fk_table(child_db, child_table,
                            FK_TABLE_ROLE_CHILD_DELETE);
        }
      }
    }
  }

c) We extend DML_prelocking_strategy::handle_table() method
   according to the following pseudo-code:

DML_prelocking_strategy::handle_table(... table ...)
{
  if (table is changed)
  {
    if (triggers are present)
    {
      // handle triggers
      ...
    }

+   process_table_fks(..., table, table is INSERT, is UPDATE, is DELETED)
  
  }
}

d) We extend open_and_process_routine() function
   according to the following pseudo-code:

open_and_process_routine(..., sroutines entry, ...)
{
  switch (sroutines entry type)
  {
  case FUNCTION:
  ...
  case PROCEDURE:
  ...
  case TRIGGER:
  ...
+ case FK_TABLE_ROLE_PARENT_CHECK:
+ case FK_TABLE_ROLE_CHILD_CHECK:
+
+   acquire MDL_SHARED_READ with MDL_TRANSACTION duration on
+   db_name(), entry->table_name()> table (might be
+   parent or child depending on entry type).
+
+   break;
+ case FK_TABLE_ROLE_CHILD_UPDATE:
+ case FK_TABLE_ROLE_CHILD_DELETE:
+
+   acquire MDL_SHARED_WRITE with MDL_TRANSACTION duration on
+   db_name(), entry->table_name()> table (always 
+   child table).
+
+   Acquire dd::Table for db_name(), entry->table_name()>
+ 
+   process_table_fks(..., child table,
+                     is NOT INSERT,
+                     entry type == FK_TABLE_ROLE_PARENT_UPDATE ?
+                     is UPDATE : is DELETE)
+   break;
  default:
  ...
  }
}

e) Cases for FK_TABLE_ROLE_CHILD_UPDATE/DELETE in open_and_process_routine()
   also need to take into account the fact that prelocking set is normally
   not rebuilt during prepared statement re-executions. So it might become
   invalid if definition of child table changes. To handle this fact we
   will use versioning scheme similar to one which is used for stored
   routines (see Sroutine_hash_entry::m_sp_cache_version) and tables
   (see TABLE_LIST::m_table_ref_version) which will trigger prepared
   statement re-prepare if table definition changes.

f) To preserve the expectation that table locked with LOCK TABLES WRITE
   can be modified without further waits we need to ensure that LOCK
   TABLES WRITE on the table also acquires strong locks on tables
   associated with it through FKs. Prelocking algorithm extension that
   handles FKs need to acquire SRO metadata lock instead of SR lock and
   SNRW lock instead of SW lock for LOCK TABLES statements.

N2: Acquisition of dd::Table objects in the above code and table version
    checks in e) might have negative impact on scalability and performance.
    This impact should be measured by QA for this WL. If it turns out to
    be too significant we might have to take some steps to alleviate it.

 
2) Changes to DDL implementation
================================

Code in mysql-trunk stores information about foreign keys added by
CREATE TABLE or ALTER TABLE statements even if table's storage engine
doesn't support them. Following this approach complicates DDL
implementation and will create problems for I_S.REFENTIAL_CONSTRAINTS
implementation on top of the data-dictionary. This WL will change the DDL
code (prepare_create_table() and mysql_prepare_alter_table() specifically)
to avoid storing information about FKs on tables which do not support them.

Also, currently we do not support cross-SE foreign keys. The code should
take this into account and do not treat, e.g. CREATE TABLE which adds
parent table to some pre-existing orphaned child table in different SE
as restoration of valid FK.

Also, at the moment, we don't have any in-house SEs which support foreign
keys but do not support atomic DDL. Assuming that FKs can only be added
to tables in SEs supporting atomic DDL allows to simplify the code (e.g.
in CREATE TABLE implementation), so this WL will do this. Indeed, there
are cases in which DDL behavior can't be fully atomic (e.g. cross-SE
ALTER TABLE which also adds FKs) which still might require special handling.
The code can be adjusted in future once there is need to support FKs for
non-atomic DDL SEs (for example, due 3rd-party SEs or new in-house SEs).

Note that DDL statements dealing with FKs have to acquire strong locks
on tables in order which is not always predictable. This means that
there is a chance of deadlocks related to these locks acquisition.
The problem can be alleviated in some cases by implementing some kind
of back-off and retry technique for DDL statements (or integrating
acquisition of these locks into open_and_lock_tables() call when
appropriate). However, we have decided to postpone implementation of
this feature until there is better understanding of how severe problem
is for our users.

2.1) Simple CREATE TABLE
------------------------
As described in HLS this statement needs to acquire X lock on all
parent tables participating in newly added foreign keys.
We also need to acquire X lock on all child tables which have FKs
referencing newly created table (which belong to same SE) since
we are going to update unique constraint names for these FKs (this
is possible if these child tables were created with
@@foreign_key_checks == 0 mode).

N3: Note that it is important to allow creation of foreign keys
    referencing non-existing table in @@foreign_key_checks == 0
    mode as mysqldump relies on this feature when restoring tables
    with foreign keys.

N4: We should not acquire any strong locks if table already exists.
    From our past experience it seems that people expect this.

In addition to changes in MDL handling we need to add code
which will update unique constraint names for FKs which
reference newly created table.

Also after CREATE TABLE is committed we need to invalidate DD
entries for parent tables to force their reload in order to get
them updated with information about FKs we have added.
Alternatively, this can be done right before commit when we
can be sure that there will be no accesses to parent tables.


2.2) CREATE TABLE LIKE
----------------------
Doesn't copy foreign key constraints and thus doesn't require
any additional locking on parent tables.

N5: It makes sense to change this. But since this is incompatible
    change separate FR to be filed.

We still need to acquire X lock on all child tables which have FKs
referencing newly created table to update unique constraint name
for such FKs. Naturally, code implementing such update should be
added too.

Similarly to simple CREATE TABLE no strong locks should be acquired
and updates should not happen if target table already exists.


2.3) CREATE TABLE SELECT
------------------------
Similarly to simple CREATE TABLE we need to acquire X lock
on all tables which serve as parents in FKs. We also need
to acquire X lock on all child tables for FKs which happen
to reference newly created table so we can update unique
constraint name for these FKs.

However, unlike simple CREATE TABLE, CREATE TABLE SELECT can
take quite some time due to rows being selected and inserted,
so acquiring these locks at the statements should be avoided
and postponed until before the point where CTS is conceptually
committed.

Since during insertion of rows into newly created table
implicit foreign key checks are to occur we also need to
acquire at least SR lock on parent tables for the duration of
these checks. In practice, to avoid some of potential deadlocks
due to concurrent DDL on parent tables, it makes sense to acquire
SU locks instead.

N6: Here we rely on the fact that InnoDB acquires row locks
    during FK checks. So while rows are inserted into target
    table we can check existence of matching records in parent
    tables, and rely on results of such check even though
    DML operations on parent tables won't see these FKs until
    DDL is committed.
    E.g AFAIR the same scheme was not acceptable for Falcon...

Update of unique constraint names for FKs which reference newly
created table needs to happen after X locks are acquired before
CTS conceptual commit point.

Similarly to simple CREATE TABLE, CREATE TABLE SELECT needs to
invalidate DD entries for parent tables to force their reload
in order to get them updated with information about FKs it has
added. It is convenient to do this after DDL commit or right
before it when we can be sure that there will be no additional
accesses to parent tables.


2.4) ALTER TABLE
----------------
ALTER TABLE ADD/DROP FOREIGN KEY variants affect FKs directly,
so they require acquiring of X lock on parent tables of FKs
being added/dropped before metadata update. 

ALTER TABLE RENAME affects FKs indirectly in several ways:
*) It will result in update of metadata for FKs which reference
   table being altered - referenced schema/table name needs
   to be updated, also by doing this rename we might add
   parent table for FKs which previously missed it, so we might
   need to update unique constraint name for such FKs.
   As result we need to acquire X lock on all child tables
   before updating metadata.
*) It also needs to acquire X lock on parent tables of all
   FKs from the table being altered, as otherwise we won't
   be able to cache metadata about these FKs in the description
   of parent tables correctly.

Other ALTER TABLE variants which affect unique keys might
affect FKs indirectly too by requiring updates of unique
constraint names for FKs which reference current table.
So they need to acquire X locks on child tables for FKs
which reference table being altered before updating metadata
for these FKs.

Similarly to CTS it makes sense to acquire these X locks
not at the start of the statement but just before we update
metadata for altered table.

Also similarly to CTS, ALTER TABLE variants which add (or
simply check) FKs have to acquire SU locks on parent tables.

N7: Currently ALTER TABLE COPY variant acquires SRO lock on
    all parent tables. This seems to be no longer necessary
    thanks to changes in InnoDB SE. We need to check this
    and remove this code.

After exclusive locks on all related tables have been acquired
and new definition of table was installed but prior to statement
commit, we need to update unique constraint names for FKs
referencing this table if change of table definition has caused
change of these names. In case of ALTER TABLE RENAME we also need
to adjust name of parent tables for FKs which reference our table.
Plus we need to update unique constraint names for pre-existing
FKs which reference to new table name and thus got proper parent
table.

Finally, after commit or right before it we need to invalidate
DD objects for all parent tables for FKs which were added or
dropped, so entries for these tables are reloaded and reflect
updated information about FKs from table which was altered.

Unlike CREATE TABLE variants ALTER TABLE is allowed under LOCK
TABLES. So we need to ensure that set of tables prelocked by
LOCK TABLES keeps FK-related invariants valid after ALTER TABLE.
This is to be achieved by requiring that for ALTER TABLE executed
under LOCK TABLES all parent tables for new FKs need to be already
locked in SRO mode. We will also prohibit ALTER TABLE RENAME under
LOCK TABLES on any table which participate or going to participate
in FK. Such ALTER unlocks the table at its end and thus is very
likely to break FK invariants for set of locked tables.

N8: This is rather severe restriction which we should consider
    lifting eventually (e.g. by keeping table locked in this case).

2.5) DROP TABLES/DROP DATABASE
------------------------------
We need to acquire X lock on parent tables in FKs for
tables being dropped. This is necessary to be able to
safely update cached info about FKs associated with
parent table DD objects.

We also need to acquire X lock on child tables in FKs
which reference tables being dropped, so we can update
unique constraint names for such FKs (this is only
possible if @@foreign_key_checks=0 as otherwise attempt
to drop parent table will fail).

We need to carry out the above-mentioned update along with
dropping table definitions from DD.

After the statement commit or right before it we need to invalidate
DD objects for all parent tables in FKs which child tables were
dropped. Reload of DD entries for these tables will ensure that
they reflect the fact that they no longer have FKs from dropped
tables.

DROP TABLES is also allowed under LOCK TABLES and therefore
such statement needs to take care about FK-related invariants
for set of tables prelocked by LOCK TABLES. We should not
release MDL on tables dropped (as we normally do) if these
tables participate as parents in some FKs and their child
tables are still kept locked in write mode (this is possible
with FOREIGN_KEY_CHECKS=0).

2.6) RENAME TABLES
------------------
Similarly to ALTER TABLE RENAME, we need to acquire X locks
on all child tables for FKs which reference table being
renamed, as we need to update info about referenced
schema/table name and possibly unique constraint names.
We also need to acquire X locks on all parent tables for
FKs in table being renamed to be able safely invalidate
these tables in the DD cache.

After all locks are acquired and table is renamed in SE
and data dictionary but before commit happens we need to
update child tables for FKs which reference renamed table
to reflect new schema/table name. We also need to update
unique constraint name for formerly orphan FKs which 
have new table name as parent table.

Then after statement commit or right before it we need to
invalidate DD cache objects for all parent tables in FKs
which have one of renamed tables as child. Again this
allows to ensure that DD objects for these tables are
reloaded and updated with new child table names as result.

N9: When renaming a mix of table in SEs with different level
    of support for DDL atomicity, we are facing rollback by
    compensation. When reverting the FK changes, we cannot
    re-establish the proper before image for tables that were
    FK orphans, because this information is lost in the
    rename process. For proper treatment, we should keep hold
    of the necessary information from the before image.