This topic describes how to use the SHOW METADATA LOCK statement in DRDS to query a transaction that holds a metadata lock.

Overview

When DRDS 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.

Note DRDS provides the online schema change feature. During the creation of a GSI, the metadata version are switched four times. Two of these switches obtain the write lock of the metadata lock and are immediately unlocked after the metadata is loaded. The write lock is not held for the rest of the time.

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)
Note This statement is only used to display connections that already hold a metadata lock. It cannot be used to display connections that wait for a metadata lock.
Table 1. List of column names
Column name 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 name of the database.
TABLE The name of the table.
TYPE The type of the metadata lock that is held.
DURATION The period for which the metadata lock is held.
VALIDATE Indicates whether the metadata lock is released.
FRONTEND The frontend connection information.
SQL The SQL statement that holds the metadata lock.