Performance Schema metadata_locks Table

Description

The metadata_locks table contains information.

To enable metadata lock instrumentation at runtime, issue this statement:

To enable it permanently, add this to the [mariadb] section of the (for instance, my.cnf):

The table is by default autosized, but the size can be configured with the system variable.

The table is read-only, and cannot be used to empty the table.

The table contains the following columns:

• OBJECT_TYPE

  • VARCHAR(64)

  • Null: No

Example

Backup Lock Types

BACKUP_* lock types exposed through this table allow for granular locking, as opposed to the (FTWRL) statement which essentially freezes the entire database. With granular locking, backup tools like can take consistent backups while keeping the database functioning.

BACKUP_ALTER_COPY

This lock is acquired by the backup engine when it needs to perform an ALTER TABLE ... COPY operation.

BACKUP_BLOCK_DDL

This lock is acquired by other operations that need to block DDL statements from being executed during a backup.

BACKUP_COMMIT

This lock is acquired by the backup engine when it needs to commit a transaction.

BACKUP_DDL

This lock is acquired by the backup engine when it needs to process DDL statements.

BACKUP_DML

This lock is acquired by the backup engine when it needs to read data from tables.

BACKUP_FLUSH

This lock is acquired when the backup engine flushes data to disk.

BACKUP_FTWRL1

This is a more refined, less aggressive version of the FLUSH TABLES WITH READ LOCK statement. The "1" in the name signifies it is a specific stage of the backup lock hierarchy.

• This lock acts as a short-lived synchronization point. It ensures all non-transactional tables (like MyISAM) are flushed to disk and that the binary log position is captured accurately.

• Since InnoDB handles its own consistency, this lock is primarily for "everything else" in your database – ensuring that the metadata and non-InnoDB tables are in a fixed state for a split second.

BACKUP_START

As the name suggests, this is the initialization lock. When a backup starts, the system needs to set a baseline.

• This lock prevents DDL operations. This means you cannot CREATE, ALTER, RENAME, or DROP tables while this lock is held.

• This lock protects the table structure during file-copy operations.

BACKUP_TRANS_DML

This lock is the lightest of the three (BACKUP_START, BACKUP_FTWRL1). It is designed to ensure that DML operations – like INSERT, UPDATE, and DELETE – don't interfere with the backup of transactional tables (for instance, InnoDB).

• This lock prevents DML changes to transactional tables during a specific phase of the backup.

• It ensures that the backup tool can reach a consistent state without the data constantly shifting under its feet, but it doesn't block simple SELECT queries.

BACKUP_WAIT_COMMIT

This lock is acquired by other operations that need to wait for the backup engine to finish processing commit statements.

BACKUP_WAIT_DDL

This lock is acquired by other operations that need to wait for the backup engine to finish processing DDL statements.

BACKUP_WAIT_FLUSH

This lock is acquired by other operations that need to wait for the backup engine to finish flushing data.

_{This page is licensed: CC BY-SA / Gnu FDL}