MySQL?中定位?DDL?被阻塞的問題及解決方案

更新時間：2022年01月11日 14:17:45 作者：iVictor

DDL 被阻塞了，如何找到阻塞它的 SQL?下面，就這個問題，給一個清晰明了、拿來即用的解決方案，本文通過一個簡單的demo給大家介紹的非常詳細(xì)，感興趣的朋友跟隨小編一起看看吧

DDL 被阻塞了，如何找到阻塞它的 SQL?

經(jīng)常碰到開發(fā)、測試童鞋會問，線下開發(fā)、測試環(huán)境，執(zhí)行了一個DDL，發(fā)現(xiàn)很久都沒有執(zhí)行完，是不是被阻塞了？要怎么解決？

包括在群里，也經(jīng)常會碰到類似問題：DDL 被阻塞了，如何找到阻塞它的 SQL ？

實際上，如何解決 DDL 被阻塞的問題，是 MySQL 中一個共性且高頻的問題。

下面，就這個問題，給一個清晰明了、拿來即用的解決方案：

怎么判斷一個DDL是不是被阻塞了？當(dāng)DDL被阻塞時，怎么找出阻塞它的會話？

怎么判斷一個 DDL是不是被阻塞了?

首先，看一個簡單的Demo

session1> create table sbtest.t1(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.02 sec)
session1> insert into sbtest.t1 values(1,'a');
Query OK, 1 row affected (0.01 sec)
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> select * from sbtest.t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)
session2> alter table sbtest.t1 add c1 datetime;
阻塞中。。。
session3> show processlist;
+----+-----------------+-----------+------+---------+-------+---------------------------------+---------------------------------------+
| Id | User            | Host      | db   | Command | Time  | State                           | Info                                  |
+----+-----------------+-----------+------+---------+-------+---------------------------------+---------------------------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 47628 | Waiting on empty queue          | NULL                                  |
| 24 | root            | localhost | NULL | Sleep   |    11 |                                 | NULL                                  |
| 25 | root            | localhost | NULL | Query   |     5 | Waiting for table metadata lock | alter table sbtest.t1 add c1 datetime |
| 26 | root            | localhost | NULL | Query   |     0 | init                            | show processlist                      |
+----+-----------------+-----------+------+---------+-------+---------------------------------+---------------------------------------+
4 rows in set (0.00 sec)

判斷一個 DDL 是不是被阻塞了，很簡單，就是執(zhí)行 show processlist ，查看 DDL 操作對應(yīng)的狀態(tài)。

如果顯示的是 Waiting for table metadata lock ，則意味著這個 DDL 被阻塞了。

DDL 一旦被阻塞了，后續(xù)針對該表的所有操作都會被阻塞，都會顯示 Waiting for table metadata lock 。這也是 DDL 讓人聞之色變的原因。

碰到了類似場景，要么 Kill DDL 操作，要么 Kill 阻塞 DDL 的會話。

Kill DDL 操作是一個治標(biāo)不治本的方法，畢竟 DDL 操作總要執(zhí)行。

除此之外，對于 DDL 操作，需要獲取元數(shù)據(jù)庫鎖的階段有兩個：DDL 開始之初和 DDL 結(jié)束之前。如果是后者，就意味著之前的操作都要回滾，成本相對較高。

所以，碰到類似場景，我們一般都會 Kill 阻塞 DDL 的會話。

那么，怎么知道是哪些會話阻塞了 DDL 呢？

下面我們看看具體的定位方法。

定位方法

方法一：sys.schema_table_lock_waits

sys.schema_table_lock_waits 是MySQL 5.7引入的，用來定位 DDL 被阻塞的問題。

針對上面這個Demo。

我們看看sys.schema_table_lock_waits的輸出。

mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: sbtest
                 object_name: t1
           waiting_thread_id: 62
                 waiting_pid: 25
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table sbtest.t1 add c1 datetime
          waiting_query_secs: 17
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 61
                blocking_pid: 24
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 24
sql_kill_blocking_connection: KILL 24
*************************** 2. row ***************************
               object_schema: sbtest
                 object_name: t1
           waiting_thread_id: 62
                 waiting_pid: 25
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table sbtest.t1 add c1 datetime
          waiting_query_secs: 17
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 62
                blocking_pid: 25
            blocking_account: root@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 25
sql_kill_blocking_connection: KILL 25
2 rows in set (0.00 sec)

只有一個 alter 操作，卻產(chǎn)生了兩條記錄，而且兩條記錄的 Kill 對象還不一樣，其中一條 Kill 的對象還是 alter 操作本身。

如果對表結(jié)構(gòu)不熟悉或不仔細(xì)看記錄內(nèi)容的話，難免會 Kill 錯對象。

不僅如此，在 DDL 操作被阻塞后，如果后續(xù)有 N 個查詢被 DDL 操作堵塞，還會產(chǎn)生 N*2 條記錄。

在定位問題時，這 N*2 條記錄完全是個噪音。

這個時候，就需要我們對上述記錄進(jìn)行過濾了。

過濾的關(guān)鍵是 blocking_lock_type 不等于 SHARED_UPGRADABLE。

SHARED_UPGRADABLE 是一個可升級的共享元數(shù)據(jù)鎖，加鎖期間，允許并發(fā)查詢和更新，常用在 DDL 操作的第一階段。

所以，阻塞DDL的不會是SHARED_UPGRADABLE。

故而，針對上面這個 case，我們可以通過下面這個查詢來精確地定位出需要 Kill 的會話。

SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
 AND waiting_query = 'alter table sbtest.t1 add c1 datetime';

方法二：Kill DDL 之前的會話

sys.schema_table_lock_waits 是 MySQL 5.7 才引入的。

但在實際生產(chǎn)環(huán)境，MySQL 5.6還是占有相當(dāng)多的份額。

如何解決MySQL 5.6的這個痛點呢？

細(xì)究下來，導(dǎo)致 DDL 被阻塞的操作，無非兩類：

表上有慢查詢未結(jié)束。

表上有事務(wù)未提交。

其中，第一類比較好定位，通過 show processlist 就能發(fā)現(xiàn)。

而第二類僅憑 show processlist 很難定位，因為未提交事務(wù)的連接在 show processlist 中的狀態(tài)同空閑連接一樣，都是 Sleep 。

所以，網(wǎng)上有 Kill 空閑連接的說法，其實也不無道理，但這樣做就太簡單粗暴了，難免會誤殺。

其實，既然是事務(wù)，在 information_schema.innodb_trx中肯定會有記錄，如 session1 中的事務(wù)，在表中的記錄如下，

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421568246406360
                 trx_state: RUNNING
               trx_started: 2022-01-02 08:53:50
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 24
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

其中 trx_mysql_thread_id 是線程 id ，結(jié)合 information_schema.processlist ，可進(jìn)一步縮小范圍。

所以，我們可以通過下面這個 SQL ，定位出執(zhí)行時間早于 DDL 的事務(wù)。

SELECT concat('kill ', i.trx_mysql_thread_id, ';')
FROM information_schema.innodb_trx i, (
    SELECT MAX(time) AS max_time
    FROM information_schema.processlist
    WHERE state = 'Waiting for table metadata lock'
      AND (info LIKE 'alter%'
      OR info LIKE 'create%'
      OR info LIKE 'drop%'
      OR info LIKE 'truncate%'
      OR info LIKE 'rename%'
  )) p
WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;

可喜的是，當(dāng)前正在執(zhí)行的查詢也會顯示在information_schema.innodb_trx中。

所以，上面這個 SQL 同樣也適用于慢查詢未結(jié)束的場景。

MySQL 5.7中使用sys.schema_table_lock_waits的注意事項

sys.schema_table_lock_waits 視圖依賴了一張 MDL 相關(guān)的表-performance_schema.metadata_locks。

該表是 MySQL 5.7 引入的，會顯示 MDL 的相關(guān)信息，包括作用對象、鎖的類型及鎖的狀態(tài)等。

但在 MySQL 5.7 中，該表默認(rèn)為空，因為與之相關(guān)的 instrument 默認(rèn)沒有開啟。MySQL 8.0 才默認(rèn)開啟。

mysql> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | NO      | NO    |
+----------------------------+---------+-------+
1 row in set (0.00 sec)

所以，在 MySQL 5.7 中，如果我們要使用 sys.schema_table_lock_waits ，必須首先開啟 MDL 相關(guān)的 instrument。

開啟方式很簡單，直接修改 performance_schema.setup_instruments 表即可。

具體SQL如下。

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

但這種方式是臨時生效，實例重啟后，又會恢復(fù)為默認(rèn)值。

建議同步修改配置文件。

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

總結(jié)

1. 執(zhí)行 show processlist ，如果 DDL 的狀態(tài)是 Waiting for table metadata lock ，則意味著這個 DDL 被阻塞了。

2. 定位導(dǎo)致 DDL 被阻塞的會話，常用的方法有兩種：

2.1 sys.schema_table_lock_waits

SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
  AND (waiting_query LIKE 'alter%'
  OR waiting_query LIKE 'create%'
  OR waiting_query LIKE 'drop%'
  OR waiting_query LIKE 'truncate%'
  OR waiting_query LIKE 'rename%');

這種方法適用于 MySQL 5.7 和 8.0。

注意，MySQL 5.7 中，MDL 相關(guān)的 instrument 默認(rèn)沒有打開。

2.2 Kill DDL 之前的會話

SELECT concat('kill ', i.trx_mysql_thread_id, ';')
FROM information_schema.innodb_trx i, (
    SELECT MAX(time) AS max_time
    FROM information_schema.processlist
    WHERE state = 'Waiting for table metadata lock'
      AND (info LIKE 'alter%'
      OR info LIKE 'create%'
      OR info LIKE 'drop%'
      OR info LIKE 'truncate%'
      OR info LIKE 'rename%'
  )) p
WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;

如果 MySQL 5.7 中 MDL 相關(guān)的 instrument 沒有打開或在 MySQL 5.6 中，可使用該方法。

到此這篇關(guān)于MySQL 中如何定位 DDL 被阻塞的問題的文章就介紹到這了,更多相關(guān)MySQL定位 DDL 被阻塞內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: