MySQL中定位DDL被阻塞的問題處理過程
在生產環(huán)境中，執(zhí)行了一個DDL，發(fā)現(xiàn)很久都沒有執(zhí)行完，是不是被阻塞了？要怎么解決？
實際上，如何解決DDL阻塞的問題，是MySQL中一個共性且高頻的問題。
下面，就這個問題，給一個清晰明了、拿來即用的解決方案：
- 怎么判斷一個DDL是不是被阻塞了？
- 當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操作對應的狀態(tài)。
如果顯示的是"Waiting for table metadata lock"，則意味著這個DDL被阻塞了。
DDL一旦被阻塞了，后續(xù)針對該表的所有操作都會被阻塞，都會顯示"Waiting for table metadata lock"。這也是DDL讓人聞之色變的原因。
碰到類似場景，要么 kill DDL 操作，要么 kill 阻塞 DDL 的會話。
kill DDL 操作是一個治標不治本的方法，畢竟 DDL 操作總要執(zhí)行。
除此之外，對于 DDL 操作，需要關注元數(shù)據庫鎖的階段有兩個：DDL 開始之初和 DDL 結束之前。如果是后者，在此時 kill DDL 就意味著之前的操作都要回滾，成本相對較高。
所以碰到類似場景，我們一般都會直接 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 操作，卻產生了兩條記錄，而且兩條記錄的 kill 對象還不一樣，其中一條 kill 的對象還是 alter 操作本身。
如果對表結構不熟悉或者不仔細看記錄內容的話，難免會 kill 錯對象。
不僅如此，在 DDL 操作被阻塞后，如果后續(xù)有 N 個查詢被 DDL 操作阻塞，還會產生 N2 條記錄。
在定位問題時，這 N2 條記錄完全是個噪音。
這個時候，就需要我們對上述記錄進行過濾了。
過濾的關鍵是 blocking_lock_type 不等于 SHARED_UPGRADANLE。
SHARED_UPGRADABLE 是一個可升級的共享元數(shù)據鎖，加鎖期間，允許并發(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才引入的。但在實際生產環(huán)境中，MySQL 5.6還是占有相當多的份額。
如何解決MySQL 5.6的這個痛點呢？
細究下來，導致 DDL 被阻塞的操作，無非兩類：
1.表上有慢查詢未結束。
2.表上有事務未提交。
- 其中，第一類比較好定位，通過 “show processlist” 就能發(fā)現(xiàn)。
- 而第二類僅憑 “show processlist” 很難定位，因為未提交事務的連接在 “show processlist” 中的狀態(tài)同空閑連接是一樣的，都是 Sleep。
- 所以，網上有 kill 空閑會話連接的說法，其實也不無道理，但這樣做就太簡單粗暴了，難免會誤殺。
- 其實，既然是事務， 在 “information_schema.innodb_trx” 中肯定會有記錄，如 session1 中的事務，在表中的記錄如下：
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，結合 “information_schema.processlist”，可進一步縮小范圍。
所以，我們可以通過下面這個SQL，定位出執(zhí)行時間早于 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;
可喜的是，當前正在執(zhí)行的查詢也會顯示在 “information_schema.innodb_trx” 中。
所以，上面這個SQL同樣也適用于慢查詢未結束的場景。
MySQL 5.7中使用 “sys.schema_table_lock_waits” 的注意事項
“sys.schema_table_lock_waits” 試圖依賴一張 MDL 相關的表：“performance_schema.metadata_locks”。
該表是 MySQL 5.7 引入的，會顯示 MDL 的相關信息，包括作用對象、鎖的類型及鎖的狀態(tài)等。
但在 MySQL 5.7 中，該表默認為空，因為與之相關的 instrument 默認沒有開啟。MySQL 8.0 才默認開啟。
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 相關的 instrument。
開啟方式很簡單，直接修改 “performance_schema.setup_instruments” 表即可。
具體 SQL 如下：
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
但這種方式是臨時生效，實例重啟后，又會恢復為默認值。
建議同步修改配置文件：
[mysqld] performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
總結
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
mysql error 1130 hy000:Host''localhost''解決方案
本文將詳細提供mysql error 1130 hy000:Host'localhost'解決方案,需要的朋友可以參考下2012-11-11
這篇文章主要介紹了SQL如何按照年月來查詢數(shù)據問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要為大家詳細介紹了Linux環(huán)境下mysql5.6.24二進制自動安裝腳本，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-03-03
本文主要介紹了MySQL GTID集合運算函數(shù)總結,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-04-04
phpmyadmin報錯：#2003 無法登錄 MySQL服務器的解決方法
通過phpmyadmin連接mysql數(shù)據庫時提示：“2003 無法登錄 MySQL服務器”。。。很明顯這是沒有啟動mysql服務，右擊我的電腦-管理-找到服務，找到mysql啟動一下2012-04-04
delimiter是mysql分隔符，在mysql客戶端中分隔符默認是分號（；）。如果一次輸入的語句較多，并且語句中間有分號，這時需要新指定一個特殊的分隔符。這篇文章給大家介紹了Mysql中的delimiter的作用，感興趣的朋友一起看看吧2018-09-09
MySQL流程控制IF()、IFNULL()、NULLIF()、ISNULL()函數(shù)的使用
這篇文章介紹了MySQL流程控制IF()、IFNULL()、NULLIF()、ISNULL()函數(shù)的使用方法，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-12-12
這篇文章主要介紹了解決mysql連接超時和mysql連接錯誤的問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-07-07
MySQL的當前讀和快照讀是數(shù)據庫并發(fā)控制的核心機制,理解它們的區(qū)別和實現(xiàn)原理對于設計高性能、高并發(fā)的數(shù)據庫應用至關重要,這篇文章主要介紹了mysql中快照讀和當前讀操作方法的相關資料,需要的朋友可以參考下2026-04-04
當涉及到大量數(shù)據時,對于?ORDER?BY?操作,可以考慮為相應的列添加索引,如果不使用索引,mysql會使用filesort來進行排序,這篇文章主要介紹了mysql?order?by?排序原理,需要的朋友可以參考下2024-02-02