PolarDB:SHOW METADATA LOCK

How it works

When PolarDB-X 1.0 creates a GSI, it uses a built-in metadata lock to maintain transaction and data consistency. Creating a GSI on a large existing table can take a long time. If a transaction is already holding a metadata lock when the GSI creation starts, schema changes are blocked until that transaction completes.

SHOW METADATA LOCK lets you find which connection and SQL statement are holding the lock, so you can decide whether to wait or stop the blocking transaction.

Syntax

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

schema_name and table_name are optional filters. Omit them to see all connections holding a metadata lock on the current node.

-- All connections holding a metadata lock on the node
SHOW METADATA LOCK;

-- Connections holding a metadata lock in the specified database
SHOW METADATA LOCK xxx_db;

-- Connections holding a metadata lock on the specified table
SHOW METADATA LOCK xxx_db.tb_name;

Query metadata locks

Run SHOW METADATA LOCK to see which connections are blocking schema changes:

mysql> SHOW METADATA LOCK;

Example output:

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