PolarDB:SHOW METADATA LOCK

Overview

When PolarDB-X 1.0 creates a global secondary index (GSI), it uses a built-in metadata lock to ensure transaction and data consistency. It usually takes a long time to create a GSI for an existing table. If a transaction that holds a metadata lock is running when a GSI is being created, you cannot make changes to the schema before the transaction is completed. In this case, you can use the SHOW METADATA LOCK statement to query the transaction that holds a metadata lock and the corresponding SQL statement that is being executed. This allows you to troubleshoot the long-running transaction that is blocking schema changes.

Syntax

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

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

show metadata lock; ## Displays all the connections that hold a metadata lock on the node.
show metadata lock xxx_db; # Displays all the connections that hold a metadata lock in xxx_db on the node.
show metadata lock xxx_db.tb_name; # Displays all the connections that hold a metadata lock in tb_name of xxx_db on the node.

Examples

mysql> 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)