WL#7957: Add MDL for tablespaces

Affects: Server-Prototype Only   —   Status: Complete   —   Priority: Medium


This worklog will define and implement meta data locking (MDL) for tablespaces. This is specifically needed for general tablespaces for InnoDB, and for the already existing NDB tablespaces.


The current implementation of tablespace support for InnoDB does not use MDL for controlling concurrent access to the tablespace meta data. Additionally, InnoDB is in the process of implementing general tablespaces (WL#6205), which also need to lock the meta data. This is particularly important with the advent of the new global data dictionary where we will have tablespace meta data at the SQL level, not only at the SE level.

Statements affected

The execution of the following tablespace related SQL DDL statements will need to take MDL into account:


Additionally, DDL statements affecting tablespace contents also must take MDL into account:


Existing DML statements will not need to take tablespace MDL into account, because the DML statements request MDL on the artifacts that are addressed (e.g. a table). Thus, the required synchronization is achieved by having the tablespace DDL operations also requesting MDL on the contents before allowing the execution to proceed.

By and large, the implementation of MDL support for tablespaces will be similar to the existing MDL support implementation for databases.


Meta data locks are characterized by:

  • A key which comprises a namespace, a schema and a name. For tablespaces, which are not related to a specific schema, the schema part of the key will be empty.
  • A duration, which specifies for how long the lock will be held, e.g. statement, transaction etc.
  • A type, which indicates the strength of the lock, and how it may coexist with concurrent locks on the same key.

An MDL request specifies the three lock characteristics listed above.

Functional requirements

A new locking namespace must be introduced for tablespaces.
The tablespace related DDL operations CREATE, ALTER and DROP TABLESPACE must take tablespace locking into account. Before accessing the tablespace, a global IX lock with transaction duration must first be requested (to synchronize this with other transactions requesting a global read lock), followed by a request for an exclusive lock with transaction duration for the tablespace name. Then, the dd::Tablespace and dd::Tabledpace_file objects may be manipulated and stored or deleted from the dd.tablespaces and dd.tablespace_files tables. Finally, the transaction must be committed (or rolled back), and the MDL locks must be released (this happens automatically for locks with non-explicit duration).
The tablespace related DDL operation DROP TABLESPACE must, in addition to F-2, request exclusive locks with transaction duration on the tables in the tablespace. This is necessary to syncronize DROP TABLESPACE (which implicitly drops the tables in the tablespace) with other operations on these tables. This will also properly synchronize DROP TABLESPACE with, e.g. DROP SCHEMA. These locks will also be released automatically since they have a non-explicit duration.
For the DDL operation ALTER TABLE, ... Alter table moving from one tablespace to another?? same as for schema?
The implementation of MDL for tablespaces shall apply regardless of storage engine. Specifically, it shall also work for NDB.
There shall be no dedicated MDL support for InnoDB file-per-table tablespaces. These tablespaces will have unique SQL level names in the new DD, and their files will be based on the schema and table name, hence making the file names unique as well. Even if specifying the data directory explicitly in the CREATE TABLE statement, the schema name will be appended to the path, so tables with the same name, but in different schemas, will also have their tablespace files in different subdirectories.
There shall be no dedicated MDL support for individual tablespace files. Tablespace file access will be synchronized by locks on the tablespace names. A future enhancement may provide this in order to support concurrent access to different files of the same tablespace, but for now, this is not supported.
There shall be no support for locking an explicit tablespace when managing indexes. The implementation will assume that the index is located in the same tablespace as the table which it is based on. In the future, individual indexes may be explicitly assigned to different tablespaces, but for now, this is not supported.

Overview of required changes

Overall, we need to change the following components:

  • Extend the MDL subsystem to support tablespaces in terms of lock key and appropriate method signatures for lock requests etc.
  • Extend the locking convenience functions to also support requesting locks on tablespace names.
  • Extend the DDL implementation functions by a new family of functions to provide support for manging tablespaces. We need to support e.g. creating, altering and dropping tablespaces.
  • Extend the DDL implementation functions that support management of the tablespace contents (e.g. tables) to also lock the relevant tablespace.
  • Extend the current DDL implementation for table management using file-per-table tablespaces to also propely use MDL.
  • Extend the current NDB implementation of tablespace support to also propely use MDL.
  • Possibly extend the DDL implementation functions that indirectly may affect the tablespace contents (e.g. tables) to also lock the relevant tablespace (e.g. for DROP SCHEMA).
  • Possibly extend the DML implementation functions that support using the tablespace contents (e.g. tables) to also lock the relevant tablespace.
  • Extend the dictionary access functions by a new family of functions supporting management of the tablespace meta data.
  • Extend the parser with a new function to validate the tablespace names.

Temporary changes required for testing

SQL commands for basic tablespace management:


Syntax extensions to support tesing the relevant DML and DDL for tablespace contents (directly and indirectly) will be deferred until WL#6205 for supporting general tablespaces in InnoDB is implemented


New definitions

Extensions to MDL_key

  • Add new TABLESPACE namespace.
  • Add new single name variant for initializing the mdl key, since the tablespace will not be relative to a schema:
      mdl_key_init(enum_mdl_namespace mdl_namespac
                   const char *name)
  • Introduce the convention that tablespaces use a zero length db name, so the format of a tablespace key will be:

Extensions to MDL_request

  • Add new initialization function for tablespace lock requests:
      void init_with_source(MDL_key::enum_mdl_namespace namespace_arg,
                            const char *name_arg,
                            enum_mdl_type mdl_type_arg,
                            enum_mdl_duration mdl_duration_arg,
                            const char *src_file, uint src_line);

Extensions to MDL_context

      bool owns_equal_or_stronger_lock(MDL_key::enum_mdl_namespace mdl_namespace,
                                       const char *name,
                                       enum_mdl_type mdl_type);

Extensions to lock.{h,cc}

  • Add a new method to lock a tablespace:
      bool lock_tablespace_name(THD *thd, const char *tablespace)

Extensions to current DDL implementation

  • Extend lock_table_names() to also lock the associated tablespace.

Extensions to sql_tablespace.{h,cc}

  • Add dedicated files for tablespace DDL operations.
  • Functions will resemble what we support for databases:
      int mysql_create_tablespace(THD *thd, const char *tablespace,
                                  HA_CREATE_INFO *create_info)

      int mysql_alter_tablespace(THD *thd, const char *tablespace,
                                 HA_CREATE_INFO *create_info)

      bool mysql_rm_tablespace(THD *thd, const LEX_CSTRING &tablespace)

New DD access methods

  • New tailored access methods for tablespace handling.
  • Functions:
      bool dd_create_tablespace(...)
      bool dd_alter_tablespace(...)
      bool dd_rm_tablespace(...)

New function for validating tablespace name

  • New function akin to what we have for dataabse names:
      enum_ident_name_check check_and_convert_tablespace_name(LEX_STRING *name,
                                                              bool preserve_lettercase)