文档

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

更新时间:

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

前提条件

集群版本需为PolarDB MySQL版8.0.1版本且Revision version为8.0.1.1.21及以上,您可以通过查询版本号确认集群版本。

注意事项

由于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

    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.threadsinformation_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则为一个慢查询。线程3810086持有了锁,导致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)

    可以看到只读节点上的35133813519277351927935192783519276线程持有了表test/t1SHARED_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