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儲存引擎的表。
開啟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)查看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操作有任何疑問，請聯絡我們。