PolarDB:SHOW METADATA LOCK

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.

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)