# lock-ddl-per-table option improvements

To block DDL statements on an instance, Percona Server implemented LOCK
TABLES FOR BACKUP. *Percona XtraBackup* uses this lock for the
duration of the backup. This lock does not affect DML statements.

*Percona XtraBackup* has also implemented `--lock-ddl-per-table`, which
blocks DDL statements by using metadata locks (MDL).

The following procedures describe a simplified backup operation when using
`--lock-ddl-per-table`:

1. Parse and copy all redo logs after the checkpoint mark

2. Fork a dedicated thread to continue following new redo log entries

3. List the tablespaces required to copy

4. Iterate through the list. The following steps occur with each listed tablespace:

   * Query INFORMATION_SCHEMA.INNODB_TABLES to find which tables belong
   to the tablespace ID and take an MDL on the underlying table or tables in
   case there is a shared tablespace.

   * Copy the tablespace `.ibd` files.

The backup process may encounter a redo log event, generated by the bulk
load
operations, which notifies backup tools that data file changes have been
omitted from the redo log. This event is an `MLOG_INDEX_LOAD`. If this
event is found by the redo follow thread, the backup continues and assumes
the backup is safe because the MDL protects tablespaces already copied and
the MLOG_INDEX_LOAD event is for a tablespace that is not copied.

These assumptions may not be correct and may lead to inconsistent backups.

## `--lock-ddl-per-table` redesign

Implemented in *Percona XtraBackup* version 8.0.22-15.0, the `--lock-ddl-per-table` has been redesigned to minimize inconsistent backups.

The following procedure reorders the steps:

* The MDL lock acquired at the beginning of the backup

* Scan the redo logs. An `MLOG_INDEX_LOAD` event may be recorded if a `CREATE INDEX` statement has occurred before the backup starts. At this time, the backup process is safe and can parse and accept the event.

* After the first scan has completed, the follow redo log thread is initiated. This thread stops the backup process if an `MLOG_INDEX_LOAD` event is found.

* Gather the tablespace list to copy

* Copy the `.ibd` files.

## Other improvements

The following improvements have been added:

* If the `.ibd` file belongs to a temporary table, the `SELECT` query is skipped.

* For a FullText Index, an MDL is acquired on the base table.

* A `SELECT` query that acquires an MDL does not retrieve any data.

!!! warning
   
    The `lock-ddl-per-table` variable is deprecated in *Percona Server for MySQL* 8.0. Use `--lock-ddl` instead of this variable.