View the DDL statement execution status and MDL status - PolarDB

PolarDB supports Polar Performance Schema, which monitors the execution status of DDL statements and the MDL status in the database. Polar Performance Schema is a lightweight monitoring feature. Compared with the MySQL Performance Schema feature, it uses less memory and imposes lower performance overhead. This topic describes how to use Polar Performance Schema to view the execution status of DDL statements and the MDL status.

Prerequisites

The PolarDB for MySQL cluster version must be:

5.7 with revision 5.7.1.0.35 or later.
8.0.1 with revision 8.0.1.1.21 or later.
8.0.2 with revision 8.0.2.2.8 or later.

For information about how to view the cluster version, see Checking the engine version.

Precautions

DDL statements are always executed on the primary node of the cluster. Therefore, you must query the execution status of DDL statements on the primary node.

If you connect to the cluster by using the primary endpoint, you can directly query the execution status of DDL statements on the primary node. For information about how to view the primary endpoint, see View endpoints and ports.
If you connect to the cluster by using the cluster endpoint, you can use a hint in the SQL statement to specify that the execution status of DDL statements is queried on the primary node. For more information, see Read/write splitting.

After the Performance Schema feature is enabled, the Polar Performance Schema feature is automatically disabled.

Procedure

Note

The method described in this topic applies only to tables that use the InnoDB storage engine.

Enable Polar Performance Schema.

You must set the loose_polar_performance_schema parameter to ON in the console. This parameter takes effect after the cluster is restarted. For more information, see Set cluster and node parameters.

The following table describes the parameters related to Polar Performance Schema.

Parameter	Description
loose_polar_performance_schema	Specifies whether to enable Polar Performance Schema. Valid values: ON: enables Polar Performance Schema. OFF: disables Polar Performance Schema.
loose_polar_performance_schema_enable_row_locks	Specifies whether to enable row lock monitoring information when Polar Performance Schema is enabled. OFF (default): disables row lock monitoring information. ON: enables row lock monitoring information. Note This parameter takes effect only in PolarDB for MySQL 8.0.1 and 8.0.2.
performance_schema_max_thread_instances	The maximum number of threads monitored by Polar Performance Schema. Valid values: -1 to 65536. A value of -1 indicates auto-tuning. Note This parameter has been tuned. We recommend that you do not modify it.
performance_schema_max_metadata_locks	The maximum number of MDLs monitored by Polar Performance Schema. Valid values: -1 to 1048576. A value of -1 indicates auto-tuning. Note This parameter has been tuned. We recommend that you do not modify it.

After the cluster is started, you can run the following statement to verify that Polar Performance Schema is enabled.

SHOW VARIABLES LIKE 'polar_performance_schema';

Sample output:

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

View the DDL statement execution status and MDL status.

During DDL statement execution, you can run the following statement to query the performance_schema.events_stages_current table to obtain the execution status of the current DDL statement:

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

Sample output:

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

By using the performance_schema.threads and information_schema.PROCESSLIST tables, you can run the following statement to view the SQL statement corresponding to the current event.

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;

Sample output:

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

You can run the following statement to query the performance_schema.metadata_locks table to obtain the MDL usage in the current cluster:

SELECT * FROM performance_schema.metadata_locks;

Sample output:

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

You can use the OWNER_THREAD_ID field to run the following statement to view the thread information of the MDL holder in the performance_schema.threads table.

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

Best practices

Waiting for table metadata lock

In practice, a common cause of DDL blocking is the inability to obtain an MDL, which results in the Waiting for table metadata lock state. By using the metadata_lock table, you can quickly identify the cause of DDL blocking.

Example:

Run the show processlist command to check the status after executing alter table t1 add column d varchar(10),algorithm = inplace. Sample output:

/*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)

You can see that the DDL is in the Waiting for table metadata lock state.

In this case, you can query the performance_schema.metadata_locks table to view the MDL status of the DDL operation. Sample output:

/*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)

You can see that thread 3810574 holds a test/t1 table SHARED_READ lock, which prevents thread 3810086 from acquiring the EXCLUSIVE lock. You can use the performance_schema.threads table to obtain detailed information about the corresponding threads. Sample output:

/*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)

You can see that thread 3810086 is the blocked DDL thread, and thread 3810574 is a slow query. Thread 3810574 holds the lock, which prevents the alter table t1 add column d varchar(10),algorithm = inplace statement from acquiring the MDL. Therefore, the statement is blocked. In this case, you can wait for the transaction to commit based on your business requirements, or use the KILL processlist_id command to terminate the transaction, and then re-execute the alter table t1 add column d varchar(10),algorithm = inplace statement.

Wait for syncing with replicas

PolarDB uses a cluster architecture. Therefore, when a DDL operation is executed on the primary node, it must wait for all read-only nodes to release the corresponding MDL. If you observe the DDL operation in the Wait for syncing with replicas state when using show processlist, it indicates that a read-only node holds the MDL on the corresponding table. In this case, you can refer to this practice to quickly identify the thread information of the lock holder on the read-only node.

Example:

On the primary node of the cluster, run the show processlist command to check the status after executing alter table t1 add column d varchar(10),algorithm = inplace. Sample output:

/*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)

You can see that the DDL is in the Wait for syncing with replicas state.

In this case, you can use a hint to query the performance_schema.metadata_locks table on a specified read-only node to view the MDL status. Sample output:

/*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)

You can see that threads 3513381, 3519277, 3519279, 3519278, and 3519276 on the read-only node hold SHARED_READ locks on the test/t1 table. You can use the performance_schema.threads table to obtain detailed information about the corresponding threads. Sample output:

/*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)

You can see that a query executed on the read-only node holds the corresponding locks and has not released them for an extended period. Because parallel query is enabled, multiple parallel_worker threads hold MDLs simultaneously. In this case, you can wait for the transaction on the read-only node to commit based on your business requirements, or use the KILL processlist_id command to terminate the transaction, and then re-execute the alter table t1 add column d varchar(10),algorithm = inplace statement.

Contact us

If you have any questions about DDL operations, Contact us.