WL#9826: Allow RENAME TABLES under LOCK TABLES
Affects: Server-8.0 — Status: Complete
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.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.