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.
ColumnDescription
Table 1. Column descriptions
Column Description
CONN_ID The ID of the connection that holds the metadata lock.
TRX_ID The ID of the transaction that holds the metadata lock.
TRACE_ID The trace ID of the SQL statement that holds the metadata lock.
SCHEMA The database name.
TABLE The table name.
TYPE The type of the metadata lock that is held.
DURATION The period for which the metadata lock is held.
VALIDATE Indicates whether the connection is valid.
FRONTEND The frontend connection information.
SQL The SQL statement that holds the metadata lock.