SHOW METADATA LOCK

This topic describes how to execute the SHOW METADATA LOCK statement in PolarDB-X to query transactions that hold metadata locks.

Background information

PolarDB-X uses a built-in metadata lock when a global secondary index is created. This ensures transaction and data consistency. In most cases, a long period of time is required to create a global secondary index for an existing table. If a transaction that holds a metadata lock is running when a global secondary index is being created, you may not change the schema before the transaction is completed. You can execute the SHOW METADATA LOCK statement to query transactions that hold metadata locks and SQL statements that are being executed. This helps you troubleshoot long-running transactions that block schema changes. Note PolarDB-X supports online schema changes. In the process of creating a global secondary index, the metadata version is switched four times. During two of these switches, a write lock for the metadata is acquired, and is immediately released after the metadata is loaded. For the other two switches, a write lock is not acquired.

Syntax

SHOW METADATA {LOCK | LOCKS} [schema_name[.table_name]]

schema_name and tbl_name are optional and are used to filter database names or table names.

SHOW METADATA LOCK; # Queries all connections that hold metadata locks on the node.
SHOW METADATA LOCK xxx_db; # Queries all connections that hold metadata locks in the xxx_db database on the node.
SHOW METADATA LOCK xxx_db.tb_name; # Queries all connections that hold metadata locks in the tb_name table of the xxx_db database on the node.

Examples

SHOW METADATA LOCK;
+---------+--------+-----------------+---------------------+--------------+------------------+-----------------+----------+-------------------------------------+-----------------------------------------------+
| CONN_ID | TRX_ID | TRACE_ID        | SCHEMA              | TABLE        | TYPE             | DURATION        | VALIDATE | FRONTEND                            | SQL                                           |
+---------+--------+-----------------+---------------------+--------------+------------------+-----------------+----------+-------------------------------------+-----------------------------------------------+
| 4       |      0 | f88cf71cbc00001 | XXXX_DRDS_LOCAL_APP | full_gsi_ddl | MDL_SHARED_WRITE | MDL_TRANSACTION |        1 | XXXX_DRDS_LOCAL_APP@127.0.0.1:54788 | insert into `full_gsi_ddl` (id) VALUE (null); |
| 5       |      0 | f88cf71cbc00000 | XXXX_DRDS_LOCAL_APP | full_gsi_ddl | MDL_SHARED_WRITE | MDL_TRANSACTION |        1 | XXXX_DRDS_LOCAL_APP@127.0.0.1:54789 | insert into `full_gsi_ddl` (id) VALUE (null); |
+---------+--------+-----------------+---------------------+--------------+------------------+-----------------+----------+-------------------------------------+-----------------------------------------------+
2 rows in set (0.00 sec)

Note This statement is used only to query connections that hold metadata locks. This statement cannot be used to query connections that are waiting for a metadata lock. {#concept-1946551-entry-nn3-dfx-3wf}