WL#9826: Allow RENAME TABLES under LOCK TABLES

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

There is a need to do:

LOCK TABLES tbl WRITE;
RENAME TABLE tbl TO tbl_del, new_tabl TO tbl;

The semantics of this operation should be similar to ALTER TABLE RENAME.
FR1) In LOCK TABLES mode it should be possible to rename table using
     RENAME TABLES statement if source table is locked for write
     or is product of previous steps in the same multi-table RENAME TABLES
     statement.

FR2) In LOCK TABLES mode attempt to rename table which is not locked for
     write and is not product of previous steps in the multi-table RENAME
     TABLES should result in appropriate errors.

FR3) In LOCK TABLES mode RENAME TABLES needs to acquire exclusive MDL on
     source and target table names for duration of statement. Failure to
     do so due to lock wait timeout or deadlock should be reported as
     error.

FR4) In LOCK TABLES mode successfull renaming of table using RENAME TABLES
     statement should keep table in the set of locked tables under new
     name, in appropriate mode. SNRW metadata locks (strong locks blocking
     concurrent reads and writes as well as any metadata changes, normally
     acquired by LOCK TABLES WRITE statement) should be kept on the
     new name. Metadata locks on source name should be released.
     Number of open and locked table instances should be preserved.
     Moreover, if table was open and locked under some alias which differs
     from table name this alias should be preserved too.

FR5) In LOCK TABLES mode failed RENAME TABLES which only involves tables
     in storage engines supporting atomic DDL should be fully rolled back,
     without anyside effect on set of locked tables/metadata locks.

FR6) In LOCK TABLES mode failed RENAME TABLES which involves table in SEs
     not supporting atomic DDL should be IF POSSIBLE fully rolled back,
     MOSTLY without any side effect on set of locked tables/metadata locks.
     However, side-effects related to changes of foreign key names and
     previously orphan foreign keys keeping their new parents are allowed.

FR7) In LOCK TABLES mode RENAME TABLES which introduces new parent table
     for previously orphan foreign key, needs to check that child table
     of this foreign key is locked for write (as otherwise it will break
     FK invariants for LOCK TABLES) and emit error if not.

FR8) In LOCK TABLES mode RENAME TABLES should be able to rename views.
     FR1) - FR6) apply to them as well.
On high-level to implement support for RENAME TABLES under LOCK TABLES
we need to:

1) Ensure that metadata locks, which are necessary for updating
   metadata, are acquired and held for duration of whole statement.

2) Ensure that TABLE instances for renamed tables which were open
   at LOCK TABLES time are closed and then reopened after carrying
   out renames.

3) Ensure that we keep correct metadata locks on tables renamed after
   statement end.

Let us dive into details now.

1) Changes to RENAME TABLES statement metadata locking
======================================================

In theory we can simply acquire exclusive metadata locks on both
source and target table names (and related schema/global locks),
similarly to how it is done for RENAME TABLES outside of LOCK TABLES
mode, without requiring tables to be previously locked.
However, such approach to acquiring locks is too prone to deadlocks.

We choose safer approach -- that is to require source table of rename
to be locked for write before acquiring exclusive MDL lock on them.
We can't put similar restriction on target table name since normally
table with such name do not exist and can't be locked with LOCK TABLES
(however, thanks to the latter fact deadlocks involving target names
are less likely).

To enable multi step RENAME TABLES we waive this requirement on source
table locking in cases when such a table is product of previous steps
of this RENAME TABLES.

Since RENAME TABLES works for both normal tables and views using
find_table_for_mdl_upgrade() for enforcing such restriction is
inconvenient. Instead we will implement helper function which
will check set of MDL acquired instead of looking at list of
TABLE instances for tables locked.

In LOCK TABLES mode we use the same lock_table_names() call to
acquire exclusive locks on source and target able names as we do
in non-LOCK TABLES. Acquisition of exclusive MDL on source tables
on which we are supposed to have SNRW MDL thanks to the check
described above is equivalent to metadata lock upgrade (in
fact MDL_context::upgrade_shared_lock() is implemented through
lock acquistion internally).

Conveniently, this function also acquires locks on schema names,
as well as global and backup locks necessary for RENAME TABLES
operation.

It should be mentioned that we need to acquire exclusive metadata
locks on child/parent tables related to tables being renamed through
foreign keys, to properly update/invalidate their definitions.
However, existing code doing this for case of normal RENAME TABLES
should work equally well for RENAME TABLES in LOCK TABLES mode case.

In cases when RENAME TABLES under LOCK TABLES adds new parent for
previously orphan foreign key we need to check that child table
of such foreign key is locked for write, in order to preserve
foreign key invariants for LOCK TABLES.

2) Closing and reopening tables
===============================

We want keep tables available as open and locked by LOCK TABLES
under new names after RENAME TABLES takes place.

To achieve this it is enough:

a) to use close_all_tables_for_name() for closing all TABLE instances
   for table renamed but keep it in locked tables list.
b) to use Locked_tables_list::reopen_tables() after statement commit
   or rollback to reopen tables closed.
c) in case of successful RENAME we also need to adjust names of
   tables in locked tables list before doing the latter. This is easy
   to do by calling existing Locked_tables_list::rename_locked_table()
   which is used in similar situation by ALTER TABLE RENAME under
   LOCK TABLES.

Technically, we also need to close and reopen TABLE instances for
tables linked through foreign keys to table being renamed since
their metadata might be changed. However, existing code in RENAME
TABLES implementation handling FKs should already handle closing
open tables in LOCK TABLES mode correctly. And reopen_tables() call
mentioned above will take care about reopening these tables as well.

3) Keeping correct metadata locks after RENAME TABLES
=====================================================

a) In case of successfull RENAME TABLES we need to keep metadata
   locks on new table names after statement end. We achieve this
   by setting explicit lock duration for locks on new table names
   acquired by earlier call to lock_table_names() we also downgrade
   them from X to SNRW lock type.
   We use MDL_context::release_all_locks_for_name() to release all
   locks on old table names.

   To make RENAME TABLES under LOCK TABLES behavior consistent with
   how LOCK TABLES for existing tables works we also need to keep
   IX locks on new schemas for renamed tables. So we set their duration
   to explicit as well. To do this lock_tables_name() is extended to
   return set of schema locks acquired in new out parameter.

   Note that we won't release old schema locks for tables renamed since
   figuring out if this can be done safely is non-trivial task.
   
   Also note that tablespace metadata locks are not an issue as necessary
   locks are acquired at LOCK TABLES time and RENAME TABLES do not change
   association of tables with tablespaces

b) Handling of metadata locks for failed RENAME TABLES involving only SEs
   supporting atomic DDL is simple. Since such statement is fully rolled
   back there is no need to keep any new locks around. So we simply let
   them be released at the end of statement execution.

c) Handling of metadata locks for failed RENAME TABLES involving
   SEs not supporting atomic DDL is more complex.

   It still pretty common for such statements to be fully reverted.
   However, our current code doesn't allow to check if such full
   reversal was successful, since errors in the process are ignored. 
   Doing the latter doesn't sounds like a good idea anymore, so the
   reversal code is changed to abort on first error.

   Thanks to this change now we are able differentiate case of full
   reversal which can be handled in the same way as b) and case of
   partial reversal. 
   
   In the latter case the state of metadata is unclear, so it safer
   to remove all tables involved in rename from the list of locked
   tables. However, we need to keep metadata locks on both old and new
   table names to avoid breaking foreign key invariants for LOCK TABLES.
   This is achieved by setting lock duration for new table names to
   explicit and downgrading them to SNRW type. We also keep lock
   on new schema names for the sake of consistency.