本文将介绍如何在PolarDB-X 1.0上使用SHOW METADATA LOCK语句查询持有锁的事务。
背景信息
PolarDB-X 1.0在创建全局二级索引时使用了内建的METADATA LOCK，保证事务以及数据的一致性。在已有表上建立全局二级索引通常需要较长的时间，若此时同时存在持有锁的事务在运行则可能出现SCHEMA变更等待事务完成的情况。此时您可以使用SHOW METADATA LOCK语句查询持有锁的事务以及对应正在执行的SQL语句，方便您排查阻塞SCHEMA变更的长时间事务。
说明
PolarDB-X 1.0支持Online Schema Change，添加全局二级索引过程中，会发生4次元数据版本切换，其中有两次会先获取METADATA LOCK的写锁加载元数据完成后立即解锁，其余的时间均不会持有METADATA LOCK的写锁。
语法
SHOW METADATA {LOCK | LOCKS} [schema_name[.table_name]]schema_name和tbl_name是可选的，用于过滤显示的数据库名或表名。
show metadata lock; # 显示该节点上所有持有metadata lock的连接
show metadata lock xxx_db; # 显示该节点上 xxx_db 中所有持有metadata lock的连接
show metadata lock xxx_db.tb_name; # 显示该节点上 xxx_db 中 tb_name 上所有持有metadata lock的连接示例
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)说明
该语句仅用于显示已持有锁的连接，不显示等待锁的连接。
| 列名 | 说明 |
|---|---|
| CONN_ID | 持有锁的连接ID |
| TRX_ID | 持有锁的事务ID |
| TRACE_ID | 持有锁的SQL的跟踪 ID |
| SCHEMA | 库名 |
| TABLE | 表名 |
| TYPE | 持有锁类型 |
| DURATION | 持有锁的周期 |
| VALIDATE | 是否有效 |
| FRONTEND | 前端连接信息 |
| SQL | 持有锁的SQL语句 |
SHOW HELP
本文介绍了如何使用SHOW HELP命令。
操作样例
SHOW HELP用于展示PolarDB-X 1.0所有辅助SQL指令及其说明。
mysql> show help;
+-----------------------------------------+---------------------------------------------------------+---------------------------------------------+
| STATEMENT | DESCRIPTION | EXAMPLE |
+-----------------------------------------+---------------------------------------------------------+---------------------------------------------+
| show rule | Report all table rule | |
| show rule from TABLE | Report table rule | show rule from user |
| show full rule from TABLE | Report table full rule | show full rule from user |
| show topology from TABLE | Report table physical topology | show topology from user |
| show partitions from TABLE | Report table dbPartition or tbPartition columns | show partitions from user |
| show broadcasts | Report all broadcast tables | |
| show datasources | Report all partition db threadPool info | |
| show node | Report master/slave read status | |
| show slow | Report top 100 slow sql | |
| show physical_slow | Report top 100 physical slow sql | |
| clear slow | Clear slow data | |
| trace SQL | Start trace sql, use show trace to print profiling data | trace select count(*) from user; show trace |
| show trace | Report sql execute profiling info | |
| explain SQL | Report sql plan info | explain select count(*) from user |
| explain detail SQL | Report sql detail plan info | explain detail select count(*) from user |
| explain execute SQL | Report sql on physical db plan info | explain execute select count(*) from user |
| show sequences | Report all sequences status | |
| create sequence NAME [start with COUNT] | Create sequence | create sequence test start with 0 |
| alter sequence NAME [start with COUNT] | Alter sequence | alter sequence test start with 100000 |
| drop sequence NAME | Drop sequence | drop sequence test |
+-----------------------------------------+---------------------------------------------------------+---------------------------------------------+
20 rows in set (0.00 sec)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
