在上篇文章《MySQL表結(jié)構(gòu)變更，不可不知的Metadata Lock》中，我們介紹了MDL引入的背景，及基本概念，從“道”的層面知道了什么是MDL。下面就從“術(shù)”的層面看看如何定位MDL的相關(guān)問題。
在MySQL 5.7中，針對MDL，引入了一張新表performance_schema.metadata_locks，該表可對外展示MDL的相關(guān)信息，包括其作用對象，類型及持有等待情況。
開啟MDL的instrument
但是相關(guān)instrument并沒有開啟（MySQL 8.0是默認開啟的），其可通過如下兩種方式開啟，
臨時生效
修改performance_schema.setup_instrume nts表，但實例重啟后，又會恢復(fù)為默認值。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';
永久生效
在配置文件中設(shè)置
[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
測試場景
下面結(jié)合一個簡單的Demo，來看看在MySQL 5.7中如何定位DDL操作的阻塞問題。
session1> begin;Query OK, 0 rows affected (0.00 sec)session1> delete from slowtech.t1 where id=2;Query OK, 1 row affected (0.00 sec)session1> select * from slowtech.t1;+------+------+| id | name |+------+------+| 1 | a |+------+------+1 row in set (0.00 sec)session1> update slowtech.t1 set name='c' where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0session2> alter table slowtech.t1 add c1 int; ##被阻塞session3> show processlist;+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+| 2 | root | localhost | NULL | Sleep | 51 | | NULL || 3 | root | localhost | NULL | Query | 0 | starting | show processlist || 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+3 rows in set (0.00 sec)session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+| object_type | object_schema | object_name | lock_type | lock_duration | lock_status | owner_thread_id |+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+| TABLE | slowtech | t1 | SHARED_WRITE | TRANSACTION | GRANTED | 27 || GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 29 || SCHEMA | slowtech | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 29 || TABLE | slowtech | t1 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 29 || TABLE | slowtech | t1 | EXCLUSIVE | TRANSACTION | PENDING | 29 || TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION | GRANTED | 28 |+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+6 rows in set (0.00 sec)
這里，重點關(guān)注lock_status，"PENDING"代表線程在等待MDL，而"GRANTED"則代表線程持有MDL。
如何找出引起阻塞的會話
結(jié)合owner_thread_id，可以可到，是29號線程在等待27號線程的MDL，此時，可kill掉52號線程。
但需要注意的是，owner_thread_id給出的只是線程ID，并不是show processlist中的ID。如果要查找線程對應(yīng)的processlist id，需查詢performance_schema.threads表。
session3> select * from performance_schema.threads where thread_id in (27,29)/G*************************** 1. row *************************** THREAD_ID: 27 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 2 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULLPROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 214 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 9800*************************** 2. row *************************** THREAD_ID: 29 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 4 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULLPROCESSLIST_COMMAND: Query PROCESSLIST_TIME: 172 PROCESSLIST_STATE: Waiting for table metadata lock PROCESSLIST_INFO: alter table slowtech.t1 add c1 int PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 99072 rows in set (0.00 sec)
將這兩張表結(jié)合，借鑒sys.innodb_lock _waits的輸出，實際上我們也可以直觀地呈現(xiàn)MDL的等待關(guān)系。
SELECT a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, "Metadata Lock" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connectionFROM performance_schema.metadata_locks aJOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMAAND a.OBJECT_NAME = b.OBJECT_NAMEAND a.lock_status = 'PENDING'AND b.lock_status = 'GRANTED'AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_IDAND a.lock_type = 'EXCLUSIVE'JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_IDJOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID/G*************************** 1. row *************************** locked_schema: slowtech locked_table: t1 locked_type: Metadata Lock waiting_processlist_id: 4 waiting_age: 259 waiting_query: alter table slowtech.t1 add c1 int waiting_state: Waiting for table metadata lock blocking_processlist_id: 2 blocking_age: 301 blocking_query: NULLsql_kill_blocking_connection: KILL 21 row in set (0.00 sec)輸出一目了然，DDL操作如果要獲得MDL，執(zhí)行kill 2即可。
官方的sys.schematablelock_waits
實際上，MySQL 5.7在sys庫中也集成了類似功能，同樣的場景，其輸出如下，
mysql> select * from sys.schema_table_lock_waits/G*************************** 1. row *************************** object_schema: slowtech object_name: t1 waiting_thread_id: 29 waiting_pid: 4 waiting_account: root@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table slowtech.t1 add c1 int waiting_query_secs: 446 waiting_query_rows_affected: 0 waiting_query_rows_examined: 0 blocking_thread_id: 27 blocking_pid: 2 blocking_account: root@localhost blocking_lock_type: SHARED_READ blocking_lock_duration: TRANSACTION sql_kill_blocking_query: KILL QUERY 2sql_kill_blocking_connection: KILL 2*************************** 2. row *************************** object_schema: slowtech object_name: t1 waiting_thread_id: 29 waiting_pid: 4 waiting_account: root@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table slowtech.t1 add c1 int waiting_query_secs: 446 waiting_query_rows_affected: 0 waiting_query_rows_examined: 0 blocking_thread_id: 29 blocking_pid: 4 blocking_account: root@localhost blocking_lock_type: SHARED_UPGRADABLE blocking_lock_duration: TRANSACTION sql_kill_blocking_query: KILL QUERY 4sql_kill_blocking_connection: KILL 42 rows in set (0.00 sec)
具體分析下官方的輸出，
只有一個alter table操作，卻產(chǎn)生了兩條記錄，而且兩條記錄的kill對象竟然還不一樣，對表結(jié)構(gòu)不熟悉及不仔細看記錄內(nèi)容的話，難免會kill錯對象。
不僅如此，如果有N個查詢被DDL操作堵塞，則會產(chǎn)生N*2條記錄。在阻塞操作較多的情況下，這N*2條記錄完全是個噪音。
而之前的SQL，無論有多少操作被阻塞，一個alter table操作，就只會輸出一條記錄。
如何查看阻塞會話已經(jīng)執(zhí)行過的操作
但上面這個SQL也有遺憾，其blocking_query為NULL，而在會話1中，其明明已經(jīng)執(zhí)行了三個SQL。
這個與performance_schema.threads（類似于show processlist）有關(guān)，其只會輸出當前正在運行的SQL，對于已經(jīng)執(zhí)行過的，實際上是沒辦法看到。
但在線上，kill是一個需要謹慎的操作，畢竟你很難知道kill的是不是業(yè)務(wù)關(guān)鍵操作？又或者，是個批量update操作？那么，有沒有辦法抓到該事務(wù)之前的操作呢？
答案，有。
即Performance Schema中記錄Statement Event（操作事件）的表，具體包括
events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。
常用的是前面三個。
三者的表結(jié)構(gòu)完全一致，其中，events_statements_history又包含了events_statements_current的操作，所以我們這里會使用events_statements_history。
終極SQL如下，
SELECT locked_schema, locked_table, locked_type, waiting_processlist_id, waiting_age, waiting_query, waiting_state, blocking_processlist_id, blocking_age, substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query, sql_kill_blocking_connectionFROM ( SELECT b.OWNER_THREAD_ID AS granted_thread_id, a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, "Metadata Lock" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID AND a.lock_type = 'EXCLUSIVE' JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID ) t1, ( SELECT thread_id, group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text FROM performance_schema.events_statements_history GROUP BY thread_id ) t2WHERE t1.granted_thread_id = t2.thread_id /G*************************** 1. row *************************** locked_schema: slowtech locked_table: t1 locked_type: Metadata Lock waiting_processlist_id: 4 waiting_age: 294 waiting_query: alter table slowtech.t1 add c1 int waiting_state: Waiting for table metadata lock blocking_processlist_id: 2 blocking_age: 336 blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1sql_kill_blocking_connection: KILL 21 row in set, 1 warning (0.00 sec)從上面的輸出可以看到，blocking_query中包含了會話1中當前事務(wù)的所有操作，按執(zhí)行的先后順序輸出。
需要注意的是，默認情況下，events_statements_history只會保留每個線程最近的10個操作，如果事務(wù)中進行的操作較多，實際上也是沒辦法抓全的。
總結(jié)
以上所述是小編給大家介紹的解決MySQL 5.7中如何定位DDL被阻塞的問題，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對VeVb武林網(wǎng)網(wǎng)站的支持！
新聞熱點
疑難解答
圖片精選
