Waiting for table metadata lock 這個mdl鎖，我們最常見，這篇先拿它開刀。
1 長查詢/mysqldump 阻塞DDL
session1 執行大的查詢時候(select * from sbtest10 order by k desc;).
session2送出了一個對session中一張表的alter操作(alter table sbtest10 add column dd1111d int not null;).這時候session2會一直被阻塞,直到session1的查詢完成。
session2未完成的這個時間段内, 執行show processlist 就會顯示 Waiting for table metadata lock
session3 這時候又發起了一個非常簡單的查詢(select * from sbtest10 limit 3;)，可以看到也是被阻塞的狀态的。
> show full processlist;
+--------+---------+------------+--------------------+---------+------+---------------------------------+------------------------------------------------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent |
+--------+---------+------------+--------------------+---------+------+---------------------------------+------------------------------------------------------+-----------+
| 115950 | root | localhost | performance_schema | Query | 0 | starting | show full processlist | 0 |
| 117002 | root | localhost | test | Query | 2 | Waiting for table metadata lock | alter table sbtest10 add column dd1111d int not null | 0 |
| 117027 | root | localhost | test | Query | 2 | executing | select * from sbtest10 order by k desc | 0 |
| 115950 | root | localhost | test | Query | 6 | Waiting for table metadata lock | select * from sbtest10 limit 3 | 0 |
+--------+---------+------------+--------------------+---------+------+---------------------------------+------------------------------------------------------+-----------+
會話1先執行select , 會話2後執行alter。
在會話1執行完畢前，會話2拿不到MDL鎖，從表格上面來看，主要阻塞在rename階段。會話1在執行完畢後，會話2拿到MDL鎖，變為rename table狀态，這個操作持續時間非常短,會話1再次執行查詢，當會話2執行完後，此時會話1正常執行。這說明對于MDL鎖而言，select會阻塞alter，而alter不會阻塞select。在rename的瞬間，alter是會阻塞select的.
當執行select語句時，隻要select語句在擷取MDL_SHARED_READ鎖之前，alter沒有執行到rename階段，那麼select擷取MDL_SHARED_READ鎖成功，後續有alter執行到rename階段，請求MDL_EXCLUSIVE鎖時，就會被阻塞。rename階段會持有MDL_EXCLUSIVE鎖，但由于這個過程時間非常短(大頭都在copy資料階段)，并且是alter的最後一個階段，是以基本感覺不到alter會阻塞select語句。由于MDL鎖在事務送出後才釋放，若線上存在大查詢，或者存在未送出的事務，則會出現ddl卡住的現象。這裡要注意的是，ddl卡住後，若再有select查詢或DML進來，都會被堵住，就會出現thread running飙高的情況。
#### 注意： 對于MDL鎖而言，select會阻塞alter，而alter加鎖後DDL運作起來後會立馬釋放掉鎖(這個加/釋放鎖的時間開銷非常短)，這時候是不會阻塞select。
2 未送出的事務阻塞 DDL
此時執行 show processlist; 如下：+-----+------+-----------+---------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+---------+---------+------+---------------------------------+------------------------------------+
| 143 | root | localhost | testdb | Query | 223 | Waiting for table metadata lock | alter table t add column c3 int | ---> session2
| 145 | root | localhost | testdb | Query | 4 | Waiting for table metadata lock | select count(*) from t | ---> session3
+-----+------+-----------+---------+---------+------+---------------------------------+------------------------------------+
session1 對表t進行update操作，存在未送出的事務，故一直持有 MDL_SHARED_WRITE鎖，由于沒有執行COMMIT，會一直持有。
session2 DDL 操作會請求 TABLE-TRANSACTION-EXCLUSIVE 鎖，該鎖與session1 的MDL_SHARED_WRITE 鎖互斥，故 session2 的DDL 等待；session3的查詢操作會請求TABLE- TRANSACTION- MDL_SHARED_READ鎖，雖然MDL_SHARED_READ與活躍鎖MDL_SHARED_WRITE不沖突，但是與session2的等待鎖EXCLUSIVE沖突，是以也會等待。
解決該中場景的問題比較麻煩，但從show processlist 不能檢查出哪個會話持有鎖 。可以從兩個方面進行調查
a 查詢 select * from information_schema.innodb_trx\G
b 檢查 show engine innodb status \G 檢視裡面transaction的ACTIVE的時間長度
active N sec 說明事務持續了N秒，一般而言超過10秒的事務都是有問題的。找到了活動的事務之後，要和開發溝通看看能否直接kill這個會話？
3 第1種情況的特例，存在一個查詢失敗的語句，比如查詢不存在的列，語句失敗傳回，但是事務沒有送出，此時alter仍然會被堵住。
通過show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。這很可能是因為在一個顯式的事務中，對TableA進行了一個失敗的操作(比如查詢了一個不存在的字段)，這時事務沒有開始，但是失敗語句擷取到的鎖依然有效。從select * from performance_schema.events_statements_current\G 表中可以查到失敗的語句。
官方手冊上對此的說明如下：
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
也就是說除了文法錯誤，其他錯誤語句擷取到的鎖在這個事務送出或復原之前，仍然不會釋放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解釋這一行為的原因很難了解，因為錯誤的語句根本不會被記錄到二進制日志。
檢視鎖情況：
普通版(不推薦使用)：
> select * from sys.schema_table_lock_waits\G
***************************[ 1. row ]***************************
object_schema | sbtest
object_name | sbtest3
waiting_thread_id | 746
waiting_pid | 244
waiting_account | [email protected]
waiting_lock_type | EXCLUSIVE
waiting_lock_duration | TRANSACTION
waiting_query | alter table sbtest3 add column ccc22 int
waiting_query_secs | 505
waiting_query_rows_affected | 0
waiting_query_rows_examined | 0
blocking_thread_id | 746
blocking_pid | 244
blocking_account | [email protected]
blocking_lock_type | SHARED_UPGRADABLE
blocking_lock_duration | TRANSACTION
sql_kill_blocking_query | KILL QUERY 244
sql_kill_blocking_connection | KILL 244
***************************[ 2. row ]***************************
object_schema | sbtest
object_name | sbtest3
waiting_thread_id | 746
waiting_pid | 244
waiting_account | [email protected]
waiting_lock_type | EXCLUSIVE
waiting_lock_duration | TRANSACTION
waiting_query | alter table sbtest3 add column ccc22 int
waiting_query_secs | 505
waiting_query_rows_affected | 0
waiting_query_rows_examined | 0
blocking_thread_id | 100
blocking_pid | 18
blocking_account | [email protected]
blocking_lock_type | SHARED_READ
blocking_lock_duration | TRANSACTION
sql_kill_blocking_query | KILL QUERY 18
sql_kill_blocking_connection | KILL 18
可以看到，我們實際上隻有一個alter table操作，這裡 sys庫查出的卻是兩條記錄，而且兩條記錄的kill對象竟然還不一樣，對表結構不熟悉及不仔細看記錄内容的話，難免會kill錯對象。
不僅如此，如果有N個查詢被DDL操作堵塞，則會産生N*2條記錄。在阻塞操作較多的情況下，這N*2條記錄完全是個噪音。
加強版(步驟稍繁瑣些，但是也可以用)：
> 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 |
+---------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| GLOBAL | | | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 746 |
| BACKUP LOCK | | | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 746 |
| SCHEMA | sbtest | | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 746 |
| TABLE | sbtest | sbtest3 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 746 |
| BACKUP TABLES | | | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 746 |
| TABLESPACE | | sbtest/sbtest3 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 746 |
| TABLE | sbtest | #sql-41f69_f4 | EXCLUSIVE | STATEMENT | GRANTED | 746 |
| TABLE | sbtest | sbtest3 | EXCLUSIVE | TRANSACTION | PENDING | 746 |
| TABLE | sbtest | sbtest3 | SHARED_READ | TRANSACTION | GRANTED | 100 |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION | GRANTED | 810 |
+---------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
這裡，重點關注 lock_status , "PENDING" 代表線程在等待MDL , 而"GRANTED"則代表線程持有MDL。> select * from performance_schema.threads where thread_id IN (746,100 )\G
***************************[ 1. row ]***************************
THREAD_ID | 100
NAME | thread/sql/one_connection
TYPE | FOREGROUND
PROCESSLIST_ID | 18
PROCESSLIST_USER | root
PROCESSLIST_HOST | localhost
PROCESSLIST_DB | sbtest
PROCESSLIST_COMMAND | Sleep
PROCESSLIST_TIME | 610
PROCESSLIST_STATE |
PROCESSLIST_INFO | show full processlist -- 這是前一個異常的連接配接，最後執行的sql。一般我們把這個連接配接幹掉，就可以解決掉mdl阻塞的問題。
PARENT_THREAD_ID |
ROLE |
INSTRUMENTED | YES
HISTORY | YES
CONNECTION_TYPE | Socket
THREAD_OS_ID | 270396
RESOURCE_GROUP | USR_default
***************************[ 2. row ]***************************
THREAD_ID | 746
NAME | thread/sql/one_connection
TYPE | FOREGROUND
PROCESSLIST_ID | 244
PROCESSLIST_USER | root
PROCESSLIST_HOST | localhost
PROCESSLIST_DB | sbtest
PROCESSLIST_COMMAND | Query
PROCESSLIST_TIME | 502
PROCESSLIST_STATE | Waiting for table metadata lock
PROCESSLIST_INFO | alter table sbtest3 add column ccc22 int
PARENT_THREAD_ID |
ROLE |
INSTRUMENTED | YES
HISTORY | YES
CONNECTION_TYPE | Socket
THREAD_OS_ID | 271454
RESOURCE_GROUP | USR_default
終極版(一步到位，直接找到要殺掉的連接配接id) ：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_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 \G
*************************** 1. row ***************************
locked_schema: sbtest
locked_table: sbtest3
locked_type: Metadata Lock
waiting_processlist_id: 244
waiting_age: 2474
waiting_query: alter table sbtest3 add column ccc22 int
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 18
blocking_age: 2582
blocking_query: show full processlist
sql_kill_blocking_connection: KILL 18 --- 我們殺掉這個連接配接，就可以解決這裡的MDL鎖問題了
1 row in set (0.00 sec)
暴擊版(直接把造成mdl的會話的執行過的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_connection
FROM
(
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
) t2
WHERE
t1.granted_thread_id = t2.thread_id \G
結果如下：
*************************** 1. row ***************************
locked_schema: sbtest
locked_table: sbtest3
locked_type: Metadata Lock
waiting_processlist_id: 244
waiting_age: 2979
waiting_query: alter table sbtest3 add column ccc22 int
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 18
blocking_age: 3087
blocking_query: select k,d from sbtest3;show full processlist;show full processlist;show full processlist;show databases;show tables;show slave status;select user,host,authentication_string,password_expired,password_last_changed from mysql.user -- 這裡我們可以看到連接配接執行過哪些sql了
sql_kill_blocking_connection: KILL 18
1 row in set (0.00 sec)