AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB MySQL版 用户指南 DDL优化 DDL易用性优化 查看DDL执行状态和MDL锁状态

查看DDL执行状态和MDL锁状态

更新时间：
产品详情
我的收藏

PolarDB支持Polar Performance Schema功能，它可以监测数据库中DDL语句的执行状态及MDL锁状态。Polar Performance Schema属于轻量化的状态监测功能，与MySQL的Performance Schema功能相比，该功能内存占用更小，性能开销更低。本文主要介绍如何借助Polar Performance Schema功能查看DDL语句的执行状态及MDL锁状态。

前提条件

PolarDB MySQL版集群版本需为：

  • 5.7版本且修订版本为5.7.1.0.35及以上。

  • 8.0.1版本且修订版本为8.0.1.1.21及以上。

  • 8.0.2版本且修订版本为8.0.2.2.8及以上。

如何查看集群版本，请参见查询版本号。

注意事项

由于DDL语句总是在集群的主节点上进行的。因此，您需要在主节点上查询DDL语句的执行状态。

  • 若您使用主地址连接集群，您可以直接查询主节点上的DDL语句的执行状态。关于如何查看主地址，请参见查看连接地址和端口。

  • 若您使用集群地址连接集群，您可以在SQL语句中使用Hint语法指定在主节点上查询DDL语句的执行状态。具体操作请参见什么是读写分离。

开启Performance Schema功能后，Polar Performance Schema功能会自动关闭。

操作步骤

说明

本文介绍的使用方法仅支持使用InnoDB存储引擎的表。

  1. 开启Polar Performance Schema功能。

    您需要在控制台上将loose_polar_performance_schema参数设为ON，该参数需重启集群后才能生效。具体操作请参见设置集群参数和节点参数。

    Polar Performance Schema功能的相关参数说明如下：

    参数

    说明

    loose_polar_performance_schema

    控制是否启用Polar Performance Schema功能。取值：

    • ON：开启Polar Performance Schema。

    • OFF：关闭Polar Performance Schema。

    loose_polar_performance_schema_enable_row_locks

    控制开启Polar Performance Schema功能时，是否支持查看行锁监控信息。

    • OFF（默认）：关闭查看行锁监控信息。

    • ON：开启查看行锁监控信息。

    说明

    该参数仅在PolarDB MySQL版8.0.1版本和8.0.2版本生效。

    performance_schema_max_thread_instances

    配置Polar Performance Schema监控的最大线程数。取值范围：-1~65536。取值为-1时，表示自适应。

    说明

    该参数已进行调优，不建议您自行修改。

    performance_schema_max_metadata_locks

    配置Polar Performance Schema监控的最大MDL锁数。取值范围：-1~1048576。取值为-1时，表示自适应。

    说明

    该参数已进行调优，不建议您自行修改。

    集群启动后，可通过如下指令，确认是否成功开启Polar Performance Schema功能。

    SHOW VARIABLES LIKE 'polar_performance_schema';

    显示结果如下：

    +--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| polar_performance_schema | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

  2. 查看DDL语句执行状态和MDL锁状态。

    • 在DDL语句执行过程中，通过执行以下命令查看performance_schema.events_stages_current表，可以获取当前DDL语句的执行状态：

      SELECT THREAD_ID,EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as PROGRESS FROM performance_schema.events_stages_current;

      查询结果如下：

      +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
| THREAD_ID | EVENT_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | PROGRESS |
+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
|   3057989 |       13 | stage/innodb/alter table (read PK and internal sort) |          56634 |         330135 |  17.1548 |
+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
1 row in set (0.00 sec)

      借助表performance_schema.threads和information_schema.PROCESSLIST，执行以下命令，您可以查看当前事件对应的SQL语句。

      SELECT esc.THREAD_ID, esc.EVENT_NAME, esc.WORK_COMPLETED, esc.WORK_ESTIMATED, pl.INFO FROM performance_schema.events_stages_current esc LEFT JOIN performance_schema.threads th ON esc.thread_id = th.thread_id LEFT JOIN information_schema.PROCESSLIST pl ON th.PROCESSLIST_ID = pl.ID;

      查询结果如下：

      +-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
| THREAD_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | INFO                                                                                    |
+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
|   3057989 | stage/innodb/alter table (read PK and internal sort) |          77034 |         330519 | ALTER TABLE test.test ALGORITHM=INPLACE, ADD testA VARCHAR(20) NOT NULL DEFAULT 'testA' |
+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    • 通过执行以下命令查看表performance_schema.metadata_locks，可以获取当前集群中MDL锁的使用情况：

      SELECT * FROM performance_schema.metadata_locks;

      查询结果如下：

      +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME      | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE                 | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL             | NULL        |       139949462878336 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:3103       |         3055785 |              1 |
| TABLE       | test               | test             | NULL        |       139931318980224 | SHARED_WRITE        | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3055785 |              1 |
| COMMIT      | NULL               | NULL             | NULL        |       139931318980480 | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     | handler.cc:1669        |         3055785 |              1 |
| TABLE       | performance_schema | metadata_locks   | NULL        |       139934227366144 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3057612 |              1 |
| GLOBAL      | NULL               | NULL             | NULL        |       139934216849664 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5519       |         3057989 |             13 |
| SCHEMA      | test               | NULL             | NULL        |       139934216849408 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5506       |         3057989 |             13 |
| TABLE       | test               | test             | NULL        |       139934216848640 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3057989 |             13 |
| BACKUP LOCK | NULL               | NULL             | NULL        |       139934216849280 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5526       |         3057989 |             13 |
| TABLESPACE  | NULL               | test/test        | NULL        |       139934216848384 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:815            |         3057989 |             13 |
| TABLE       | test               | #sql-17d9_2ea89a | NULL        |       139934216848896 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:15054     |         3057989 |             13 |
| GLOBAL      | NULL               | NULL             | NULL        |       139934216850176 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | dictionary_impl.cc:416 |         3057989 |             13 |
| TABLESPACE  | NULL               | test/test        | NULL        |       139934216849920 | EXCLUSIVE           | TRANSACTION   | GRANTED     | dictionary_impl.cc:397 |         3057989 |             13 |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
12 rows in set (0.00 sec)

      通过OWNER_THREAD_ID字段，执行以下命令查看表performance_schema.threads中持有MDL锁的线程信息。

      SELECT * FROM performance_schema.threads WHERE THREAD_ID = "OWNER_THREAD_ID in performance_schema.metadata_locks table ";

最佳实践

  • Waiting for table metadata lock

    在业务实践中，常见的DDL阻塞原因是由于无法获取到MDL锁，即Waiting for table metadata lock。借助metadata_lock表，可以快速定位到DDL阻塞的原因。

    示例：

    通过show processlist命令查询执行alter table t1 add column d varchar(10),algorithm = inplace后的状态。查询结果如下：

    /*force_node='pi-bp10k7631d6k3****'*/ SHOW PROCESSLIST;
+-----------+-----------------+-----------------------+--------------------+----------------+---------+---------------------------------+-------------------------------------------------------------+
| Id        | User            | Host                  | db                 | Command        | Time    | State                           | Info                                                        |
+-----------+-----------------+-----------------------+--------------------+----------------+---------+---------------------------------+-------------------------------------------------------------+
|        98 | event_scheduler | localhost             | NULL               | Daemon         | 1306586 | Waiting on empty queue          | NULL                                                        |
|       109 | replicator      | 11.111.XX.XX:62549    | NULL               | Polar Log Dump |       1 | Reading log from innodb         | NULL                                                        |
|       113 | replicator      | 11.111.XX.XX:62560    | NULL               | Polar Log Ack  |       1 | Receiving from client           | NULL                                                        |
|       133 | replicator      | 10.13.64.70:42712     | NULL               | Polar Log Dump |       1 | Reading log from innodb         | NULL                                                        |
|       138 | replicator      | 10.13.64.70:42723     | NULL               | Polar Log Ack  |       0 | Receiving from client           | NULL                                                        |
|       369 | aurora          | 10.111.211.209:33334  | NULL               | Sleep          |       0 |                                 | NULL                                                        |
|       370 | aurora          | 10.111.211.209:33336  | NULL               | RDS Push LSN   | 1306413 | starting                        | NULL                                                        |
|       372 | aurora          | 10.111.204.224:37010  | NULL               | Sleep          |       0 |                                 | NULL                                                        |
|       373 | aurora          | 10.111.204.224:37019  | NULL               | RDS Push LSN   | 1306413 | starting                        | NULL                                                        |
|   3064011 | root            | 127.0.0.1:59703       | NULL               | Sleep          |     716 |                                 | NULL                                                        |
|   3064013 | root            | 127.0.0.1:59710       | NULL               | Sleep          |      25 |                                 | NULL                                                        |
|   3064015 | root            | 127.0.0.1:59713       | NULL               | Sleep          |      55 |                                 | NULL                                                        |
|   3064018 | root            | 127.0.0.1:59716       | NULL               | Sleep          |       1 |                                 | NULL                                                        |
|   3067041 | zyg_root        | 172.17.XX.XX:48594    | test               | Query          |      22 | Waiting for table metadata lock | alter table t1 add column d varchar(10),algorithm = inplace |
|   3067443 | zyg_root        | 172.17.XX.XX:48602    | test               | Sleep          |      27 |                                 | NULL                                                        |
|   3069716 | aurora          | 100.104.XX.XX:33017   | information_schema | Sleep          |      30 |                                 | NULL                                                        |
|   3069859 | aurora          | 100.104.XX.XX:41872   | information_schema | Sleep          |      30 |                                 | NULL                                                        |
|   3069925 | aurora          | 10.111.204.224:20916  | NULL               | Sleep          |       2 |                                 | NULL                                                        |
|   3069932 | aurora          | 10.111.211.209:51263  | NULL               | Sleep          |       2 |                                 | NULL                                                        |
| 270526156 | zyg_root        | 172.17.28.253:46272   | test               | Query          |       0 | starting                        | /*force_node='pi-bp10k7631d6k3****'*/ show processlist      |
+-----------+-----------------+-----------------------+--------------------+----------------+---------+---------------------------------+-------------------------------------------------------------+
20 rows in set (0.00 sec)

    可以看到该DDL处于Waiting for table metadata lock状态。

    此时，通过performance_schema.metadata_locks表，可以查看上述DDL操作的MDL锁状态。查询结果如下：

    /*force_node='pi-bp10k7631d6k3****'*/ SELECT * FROM performance_schema.metadata_locks;
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME      | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE             | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| TABLE       | performance_schema | metadata_locks   | NULL        |       139742994307712 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3810041 |              1 |
| TABLE       | test               | t1               | NULL        |       139742992122240 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3810574 |              1 |
| GLOBAL      | NULL               | NULL             | NULL        |       139742992172544 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5637   |         3810086 |              3 |
| SCHEMA      | test               | NULL             | NULL        |       139742993150592 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5624   |         3810086 |              3 |
| TABLE       | test               | t1               | NULL        |       139742993150848 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3810086 |              3 |
| BACKUP LOCK | NULL               | NULL             | NULL        |       139742993844096 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5644   |         3810086 |              3 |
| TABLESPACE  | NULL               | test/t1          | NULL        |       139742991805696 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:815        |         3810086 |              3 |
| TABLE       | test               | #sql-1b34_2ecca1 | NULL        |       139742992091136 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:15532 |         3810086 |              3 |
| TABLE       | test               | t1               | NULL        |       140266021234688 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:4124        |         3810086 |              3 |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
9 rows in set (0.00 sec)

    可以看到线程3810574持有了test/t1表的SHARED_READ锁，导致线程3810086无法获取到EXCLUSIVE锁。借助performance_schema.threads表，进一步获取对应线程的详细信息。查询结果如下：

    /*force_node='pi-bp10k7631d6k3****'*/ SELECT * FROM performance_schema.threads WHERE THREAD_ID IN (3810086,3810574)\G
*************************** 1. row ***************************
          THREAD_ID: 3810086
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 3067041
   PROCESSLIST_USER: zyg_root
   PROCESSLIST_HOST: 172.17.28.253
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 41
  PROCESSLIST_STATE: Waiting for table metadata lock
   PROCESSLIST_INFO: alter table t1 add column d varchar(10),algorithm = inplace
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 64852
     RESOURCE_GROUP: NULL
*************************** 2. row ***************************
          THREAD_ID: 3810574
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 3067443
   PROCESSLIST_USER: zyg_root
   PROCESSLIST_HOST: 172.17.28.253
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 46
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 65845
     RESOURCE_GROUP: NULL
2 rows in set (0.01 sec)

    可以看到线程3810086即为被阻塞的DDL线程，而线程3810574则为一个慢查询。线程3810574持有了锁，导致alter table t1 add column d varchar(10),algorithm = inplace语句无法获取到MDL锁，因此处于阻塞状态。至此，您可以结合实际业务需求，等待事务提交或者使用KILL processlist_id命令中止事务后，重新执行alter table t1 add column d varchar(10),algorithm = inplace。

  • Wait for syncing with replicas

    PolarDB采用集群架构，因此在主节点上执行DDL操作时，需要等待所有只读节点释放对应的MDL锁。若您在使用show processlist时，观察到DDL操作处于Wait for syncing with replicas的状态，则说明在只读节点上持有了对应表的MDL锁。此时，您可以参考本实践快速了解只读节点上持有对应锁的线程信息。

    示例：

    在集群的主节点上，通过show processlist命令查询执行alter table t1 add column d varchar(10),algorithm = inplace后的状态。查询结果如下：

    /*force_node='pi-bp10k7631d6k3****'*/ SHOW PROCESSLIST;
+-----------+-----------------+-----------------------+--------------------+----------------+---------+--------------------------------+-------------------------------------------------------------+
| Id        | User            | Host                  | db                 | Command        | Time    | State                          | Info                                                        |
+-----------+-----------------+-----------------------+--------------------+----------------+---------+--------------------------------+-------------------------------------------------------------+
|        98 | event_scheduler | localhost             | NULL               | Daemon         | 1307512 | Waiting on empty queue         | NULL                                                        |
|       109 | replicator      | 11.111.XX.XX:62549    | NULL               | Polar Log Dump |       1 | Reading log from innodb        | NULL                                                        |
|       113 | replicator      | 11.111.XX.XX:62560    | NULL               | Polar Log Ack  |       1 | Receiving from client          | NULL                                                        |
|       133 | replicator      | 10.13.64.70:42712     | NULL               | Polar Log Dump |       1 | Reading log from innodb        | NULL                                                        |
|       138 | replicator      | 10.13.64.70:42723     | NULL               | Polar Log Ack  |       0 | Receiving from client          | NULL                                                        |
|       369 | aurora          | 10.111.211.209:33334  | NULL               | Sleep          |       0 |                                | NULL                                                        |
|       370 | aurora          | 10.111.211.209:33336  | NULL               | RDS Push LSN   | 1307339 | starting                       | NULL                                                        |
|       372 | aurora          | 10.111.204.224:37010  | NULL               | Sleep          |       0 |                                | NULL                                                        |
|       373 | aurora          | 10.111.204.224:37019  | NULL               | RDS Push LSN   | 1307339 | starting                       | NULL                                                        |
|   3064011 | root            | 127.0.0.1:59703       | NULL               | Sleep          |     742 |                                | NULL                                                        |
|   3064013 | root            | 127.0.0.1:59710       | NULL               | Sleep          |      21 |                                | NULL                                                        |
|   3064015 | root            | 127.0.0.1:59713       | NULL               | Sleep          |      21 |                                | NULL                                                        |
|   3064018 | root            | 127.0.0.1:59716       | NULL               | Sleep          |       1 |                                | NULL                                                        |
|   3067041 | zyg_root        | 172.17.28.253:48594   | test               | Query          |       6 | Wait for syncing with replicas | alter table t1 add column d varchar(10),algorithm = inplace |
|   3067443 | zyg_root        | 172.17.28.253:48602   | test               | Sleep          |     751 |                                | NULL                                                        |
|   3071863 | aurora          | 100.104.XX.XX:32615   | information_schema | Sleep          |      56 |                                | NULL                                                        |
|   3072000 | aurora          | 100.104.XX.XX:41585   | information_schema | Sleep          |      56 |                                | NULL                                                        |
|   3072126 | aurora          | 10.111.204.224:47050  | NULL               | Sleep          |       2 |                                | NULL                                                        |
|   3072127 | aurora          | 10.111.211.209:41026  | NULL               | Sleep          |       1 |                                | NULL                                                        |
| 270526156 | zyg_root        | 172.17.28.253:46272   | test               | Sleep          |     362 |                                | NULL                                                        |
| 270530026 | zyg_root        | 172.17.28.253:46390   | test               | Query          |       0 | starting                       | /*force_node='pi-bp10k7631d6k3****'*/ show processlist      |
+-----------+-----------------+-----------------------+--------------------+----------------+---------+--------------------------------+-------------------------------------------------------------+
21 rows in set (0.00 sec)

    可以看到该DDL处于Wait for syncing with replicas状态。

    此时，通过performance_schema.metadata_locks表，使用Hint语法查询指定只读节点上MDL锁的状态信息。查询结果如下：

    /*force_node='pi-bp186ko4o21wl****'*/ SELECT * FROM performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE             | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| TABLE       | test               | t1             | NULL        |       139394298895872 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3513381 |              1 |
| TABLE       | test               | t1             | NULL        |       139394298602240 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519277 |              1 |
| TABLE       | test               | t1             | NULL        |       139917548369664 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519279 |              1 |
| TABLE       | test               | t1             | NULL        |       139394296661888 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519278 |              1 |
| TABLE       | test               | t1             | NULL        |       139394297595520 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519276 |              1 |
| SCHEMA      | test               | NULL           | NULL        |       139464322084864 | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     | sql_table.cc:17404 |              57 |              1 |
| TABLE       | test               | t1             | NULL        |       139464322084992 | EXCLUSIVE           | EXPLICIT      | PENDING     | sql_table.cc:17410 |              57 |              1 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139394296038784 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3518506 |              1 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
8 rows in set (0.00 sec)

    可以看到只读节点上的3513381、3519277、3519279、3519278、3519276线程持有了表test/t1的SHARED_READ锁。借助performance_schema.threads表，进一步获取对应线程的详细信息。查询结果如下：

    /*force_node='pi-bp186ko4o21wl****'*/SELECT * FROM performance_schema.threads WHERE THREAD_ID IN (3519278,3513381,3519279,3519276,3519277)\G
*************************** 1. row ***************************
          THREAD_ID: 3513381
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 538961413
   PROCESSLIST_USER: zyg_root
   PROCESSLIST_HOST: 172.17.28.253
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Connect
   PROCESSLIST_TIME: 103
  PROCESSLIST_STATE: User sleep
   PROCESSLIST_INFO: select *,sleep(60) from t1
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 63826
     RESOURCE_GROUP: NULL
*************************** 2. row ***************************
          THREAD_ID: 3519276
               NAME: thread/sql/parallel_worker
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1855915
   PROCESSLIST_USER: zyg_root
   PROCESSLIST_HOST: 172.17.28.253
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 103
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select *,sleep(60) from t1
   PARENT_THREAD_ID: 3513381
               ROLE: NULL
       INSTRUMENTED: NO
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 7117
     RESOURCE_GROUP: NULL
*************************** 3. row ***************************
          THREAD_ID: 3519277
               NAME: thread/sql/parallel_worker
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1855917
   PROCESSLIST_USER: zyg_root
   PROCESSLIST_HOST: 172.17.28.253
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 103
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select *,sleep(60) from t1
   PARENT_THREAD_ID: 3513381
               ROLE: NULL
       INSTRUMENTED: NO
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 7116
     RESOURCE_GROUP: NULL
*************************** 4. row ***************************
          THREAD_ID: 3519278
               NAME: thread/sql/parallel_worker
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1855916
   PROCESSLIST_USER: zyg_root
   PROCESSLIST_HOST: 172.17.28.253
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 103
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select *,sleep(60) from t1
   PARENT_THREAD_ID: 3513381
               ROLE: NULL
       INSTRUMENTED: NO
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 7119
     RESOURCE_GROUP: NULL
*************************** 5. row ***************************
          THREAD_ID: 3519279
               NAME: thread/sql/parallel_worker
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1855918
   PROCESSLIST_USER: zyg_root
   PROCESSLIST_HOST: 172.17.28.253
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 103
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select *,sleep(60) from t1
   PARENT_THREAD_ID: 3513381
               ROLE: NULL
       INSTRUMENTED: NO
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 7118
     RESOURCE_GROUP: NULL
5 rows in set (0.00 sec)

    可以看到只读节点上执行的查询持有了对应的锁，且长时间没有释放。其中，由于开启了并行查询，所以多个parallel_worker线程同时持有了MDL锁。至此，您可以结合实际业务需求，等待只读节点上的事务提交或者使用KILL processlist_id命令中止事务后，重新执行alter table t1 add column d varchar(10),algorithm = inplace。

联系我们

若您对DDL操作有任何疑问，可通过钉钉搜索群号入群咨询。您可以直接@群内专家，并附上您要咨询的问题；同时群内也有PolarDB MySQL版小助手24*7小时在线回答您的问题。钉钉群号：15375044501。

上一篇：EXPLAIN DDL下一篇：执行DDL操作提示“获取不到MDL锁”