MySQL 執行DDL語句 hang住了怎麼辦?

• MySQL 執行DDL語句 hang住了怎麼辦? 不要慌，先點支菸，聽我娓娓道來！
• 前兩天，早上7點多的時候，抓起手機忽然看到了圈內的一則DDL語句 hang住的案例，閱讀到文末，發現文中留有一些疑問（當然，文章是7月份的，現在或許作者已經解決了這些疑問），於是一咕嚕從床上爬起來，按照文中的復現方法操作了一遍（復現方法見文末），然後，按照自己的思路解決了該疑問，現在將整個過程整理出來分享給大家。

環境

• 資料庫版本：MySQL 5.7.27
• 資料資訊
  • sysbench模擬2張1000W的表
• 作業系統版本：CentOS Linux release 7.3.1611 (Core)
• 伺服器資訊
  • CPU：Intel(R) Xeon(R) CPU E5-4627 v2 @ 3.30GHz * 2
  • 記憶體：248G
  • 磁碟：1.6T LSI Flash卡
  • 網路卡：萬兆網路卡

現象

• 當我們發現一個故障問題時，首先需要做的事情，就是先確認現象，也就是先要自己親眼瞅見故障長什麼樣子，而不是直接上去就是一通胡亂排查。尤其是故障是別人反饋過來的時候，一定要首先確認故障現象表現及其真實性（別人反饋過來的問題，很多時候根本就不是問題，而是他自己姿勢不對）。
• 在本文中，提到的故障現象是DBA側自己執行DDL語句修改欄位長度時自己發現的（當然，這裡是模擬的DBA側操作），那麼，根據上述準則，我們先開啟另外一個會話，檢視執行DDL語句 hang住具體是什麼情況？

# 使用show processlist語句檢視會話狀態資訊，發現DDL語句的state列值為Waiting for table metadata lock，表示在等待MDL後設資料鎖。根據MySQL 5.7及其之後的版本中的online ddl特性，該語句應該立即執行完成（它只會修改元數，因為這裡只是修改了欄位長度，並沒有修改欄位的其他屬性），因此，故障現象確認
admin@localhost : (none) 11:48:22> show processlist;
+----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------+
| 27 | admin | localhost | sbtest | Sleep | 123 | | NULL |
| 28 | admin | localhost | sbtest | Query | 102 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |
| 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |
| 30 | admin | localhost | NULL | Sleep | 93 | | NULL |
+----+-------+-----------+--------+---------+------+---------------------------------+-------------------------------+
4 rows in set (0.00 sec)

• 友情提示：確認故障現象的位置不要搞錯，在什麼位置發現故障問題，首先就在什麼位置進行故障確認。舉個反例，曾經我碰到一個同學，在一個“應用-->LVS-->分庫中介軟體-->讀寫分離中介軟體-->資料庫主從叢集”的架構層級中，應用側反饋資料庫連線很慢，這位同學直接登入到資料庫中去確認現象了，顯然，確認故障現象時搞錯了位置，在這個反例中確認故障現象的位置應該以應用側反饋故障現象的位置為準（首先使用LVS的VIP嘗試連線），逐層往下確認

分析

• 確認了故障現象，DDL語句hang住的原因是因為在等待MDL後設資料鎖，但不知道MDL後設資料鎖被誰持有了。接下來，就要圍繞這個現象，推測可能導致該問題的一些原因了，哪些原因可能導致該問題呢？我們可以按照下面的思路進行逐個排除
  • 伺服器的主機負載過高（CPU、記憶體、磁碟吞吐與IOPS、網路卡頻寬），有沒有別的程式擠佔了資料庫程式的資源
    * 你也許會說，這裡的現象很明顯是因為在等MDL鎖，跟伺服器負載無關，肯定是有人加了鎖沒釋放。但我想提醒的是，該現象真的是一個單純的問題嗎？會不會是一個鏈式反應導致的？等待MDL鎖雖然是DDL語句被阻塞的原因，但也許它同時也是伺服器高負載的現象與結果
  • 資料庫程式的負載過高
    * 資料庫中的活躍會話數量及其狀態
    * 資料庫的QPS/TPS
  • 存在其他會話正在執行DML語句，或執行了某些DML語句之後事務未及時提交、或者其他某個會話也同時在執行某個DDL語句修改sbtest1表的表結構資訊

排查

• 有了思路，接下來就按照上文中提到的思路逐個進行排查

• 首先，我們檢視主機負載資訊，通過下圖我們可以看到，主機基本處於空載狀態，毫無壓力

• 然後，我們檢視資料庫的活躍會話數量及其狀態，我們可以看到資料庫中並沒有大量會話，也不存在正在執行的DML語句在操作表sbtest1，也不存在同時有其他會話同時使用DDL在操作相同的表，但這裡無法確認是否存在未提交的事務

# 反覆多執行幾次show processlist語句
admin@localhost : (none) 11:49:10> show processlist;
+----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+--------+---------+------+---------------------------------+------------------------------------+
| 27 | admin | localhost | sbtest | Sleep | 149 | | NULL |
| 28 | admin | localhost | sbtest | Query | 128 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |
| 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |
| 30 | admin | localhost | NULL | Sleep | 119 | | NULL |
+----+-------+-----------+--------+---------+------+---------------------------------+---------------------------------------+
4 rows in set (0.00 sec)

• 通過上述步驟，已確認在負載層面並沒有問題，此時，我們需要重點確認是否存在某個會話執行了某些DML語句之後事務未及時提交，如何確認這些資訊呢？我們可以通過performance_schema和information_schema中的鎖和事務相關的表進行查詢確認

• 先檢視information_schema中記錄的事務資訊

# 發現並沒有事務存在...
admin@localhost : sbtest 05:49:17> select * from information_schema.innodb_trx\G
Empty set (0.00 sec)
# 也可以順便使用sys.innodb_lock_waits檢視確認是否存在一些事務鎖等待
admin@localhost : performance_schema 06:27:35> select * from sys.innodb_lock_waits\G
Empty set, 3 warnings (0.00 sec)  # 查詢結果為空
檢視performance_schema下的MDL後設資料鎖記錄資訊 
# WTF..居然為空
admin@localhost : sbtest 06:00:21> select * from performance_schema.metadata_locks;
Empty set (0.00 sec)
# 也可以順便使用sys.schema_table_lock_waits檢視檢視錶級別的鎖等待
admin@localhost : performance_schema 06:28:12> select * from sys.schema_table_lock_waits\G
Empty set (0.00 sec)  # 查詢結果為空

• 檢視performance_schema下的handle持有資訊

# 發現表sbtest1的handle被thread_id=70的執行緒持有
admin@localhost : (none) 11:49:36> select * from performance_schema.table_handles where OWNER_THREAD_ID!=0;
+-------------+---------------+-------------+--------+-----------------+----------------+---------------+---------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
+-------------+---------------+-------------+---------+-----------------+----------------+---------------+---------------+
| TABLE | sbtest | sbtest1 | 140049018564288 | 70 | 6 | NULL | NULL |
+-------------+---------------+-------------+----------+-----------------+----------------+---------------+---------------+
1 row in set (0.00 sec)
# 通過performance_schema.threads表檢視是哪個執行緒（thread_id是資料庫內部的執行緒ID，我們需要看到與之對應的processlist id）
admin@localhost : (none) 11:50:03> select thread_id,processlist_id,type from performance_schema.threads where processlist_id is not null and type='FOREGROUND';
+-----------+----------------+------------+
| thread_id | processlist_id | type |
+-----------+----------------+------------+
| 43 | 1 | FOREGROUND |
| 69 | 27 | FOREGROUND |
| 70 | 28 | FOREGROUND |  # 發現processlist id為28
| 71 | 29 | FOREGROUND |
| 72 | 30 | FOREGROUND |
+-----------+----------------+------------+
5 rows in set (0.00 sec)
# 通過show processlist再次檢視一下id號，額。。發現id列為28的居然就是執行DDL語句被hang住那個會話，好吧，白忙活了
admin@localhost : (none) 11:50:26> show processlist;
+----+-------+-----------+--------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+--------+---------+------+---------------------------------+--------------------------------------+
| 27 | admin | localhost | sbtest | Sleep | 228 | | NULL |
| 28 | admin | localhost | sbtest | Query | 207 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |
| 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |
| 30 | admin | localhost | NULL | Sleep | 198 | | NULL |
+----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+
4 rows in set (0.00 sec)

• 通過show engine innodb status檢視下鎖與事務資訊

# 仍然沒有發現有效的鎖資訊
admin@localhost : performance_schema 06:14:13> show engine innodb status;
......
------------
TRANSACTIONS
------------
Trx id counter 11559
Purge done for trx's n:o < 11557 undo n:o < 0 state: running but idle
History list length 60
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421628104988048, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421628104987136, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421628104985312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421628104984400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421628104986224, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
......

• 排查到這裡，視乎已經沒招了，也許我們還可以用mysqladmin debug命令試試看

# 執行debug命令，執行該命令之後，一些鎖資訊可能會被debug出來列印到錯誤日誌中
[root@physical-machine ~]# mysqladmin debug
# 很遺憾，在錯誤日誌中仍然沒有發現有效的鎖資訊
[root@physical-machine ~]# vim /data/mysqldata1/log/error.log

• 還有最後一招，可以使用pstack和gdb命令檢視，但，生產系統不建議隨隨便便使用這類命令，這類命令會讓整個例項級別發生一段時間的阻塞。且上述故障問題並不能說明整個資料庫例項級別存在問題，也許只是發生MDL鎖等待的兩個會話之間的問題。so...咋辦呢？要認慫嗎？

• 等等，我們好像忽略了點什麼，既然DDL語句在等待MDL後設資料鎖，為啥在performance_schema.metadata_locks表中沒有記錄？檢視一下MDL事件採集器試試看？

# 喔。。發現居然MDL鎖資訊的採集器開關並沒有開啟，難怪metadata_locks表中無法記錄MDL後設資料鎖資訊
admin@localhost : performance_schema 06:30:16> select * from performance_schema.setup_instruments where name like '%/mdl';
+----------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | NO | NO |
+----------------------------+---------+-------+
1 row in set (0.00 sec)
# 現在，我們啟用mdl的採集器
admin@localhost : sbtest 07:18:52> call sys.ps_setup_enable_instrument('sql/mdl');
+-----------------------+
| summary |
+-----------------------+
| Enabled 6 instruments |
+-----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

• 關於instruments採集器，如果事先是處於關閉狀態，啟用之後，需要新的請求進來才能夠收集對應的事件資訊，因此，如果要採集MDL鎖的資訊，我們得讓故障現象重新復現一遍。讀到這裡，也許你會說，幹嘛不早說？好吧，SORRY，我是故意的...
• 現在，我們終止掉DDL語句，使用文末的步驟重新操作一遍

• 然後，我們重新查詢information_schema下的事務和鎖資訊

# 檢視information_schema.innodb_trx表，仍然沒有事務資訊
admin@localhost : sbtest 07:17:03> select * from information_schema.innodb_trx\G
Empty set (0.00 sec)
# 檢視事務鎖等待資訊，仍然為空
admin@localhost : sbtest 07:17:30> select * from sys.innodb_lock_waits\G
Empty set, 3 warnings (0.01 sec)

• 檢視performance_schema下的MDL後設資料鎖記錄資訊

# 檢視metadata_locks表中的MDL鎖資訊，咦，有資訊了！不過，有點亂啊（我們這裡只查詢sbtest庫下的sbtest1表就可以了，因為我們的操作也只涉及到這張表）
admin@localhost : (none) 11:52:46> select * from performance_schema.metadata_locks where OBJECT_SCHEMA='sbtest' and OBJECT_NAME='sbtest1';
+-------------+---------------+-------------+-----+--------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-----+--------+---------------+-------------+--------+-----------------+----------------+
# 從這行資訊我們可以看到，表sbtest1上有一把SHARED_WRITE鎖處於GRANTED狀態，為thread_id=69的執行緒所持有。SHARED_WRITE型別的MDL鎖是一把意向排他IX鎖，通常在執行MDL或select ... for update時產生
| TABLE | sbtest | sbtest1 | 140048817276288 | SHARED_WRITE | TRANSACTION | GRANTED | | 69 | 11 |
# 從這行資訊我們可以看到，表sbtest1上有一把SHARED_UPGRADABLE的鎖處於GRANTED狀態，為thread_id=70的執行緒持有，從上文中的資訊我們可以知道，thread_id=70的process id為28，也就是執行DDL語句被阻塞的那個會話id。SHARED_UPGRADABLE型別的MDL鎖是一把共享升級鎖，一般在執行online DDL語句時會產生。它的作用是在執行online ddl期間允許相同表的DML但防止DDL
| TABLE | sbtest | sbtest1 | 140049018604784 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 70 | 6 |
# 從這行資訊我們可以看到，表sbtest1上有一把EXCLUSIVE處於PENDING狀態，為thread_id=70的執行緒在等待，從上文中的資訊我們可以知道，thread_id=70的process id為28，也就是執行DDL語句被阻塞的那個會話id。EXCLUSIVE型別的MDL鎖是一把排它X鎖，用於阻止其他執行緒讀寫後設資料資訊，一般在執行DDL時產生
| TABLE | sbtest | sbtest1 | 140049018564112 | EXCLUSIVE | TRANSACTION | PENDING | | 70 | 6 |
+-------------+---------------+-------------+-----+---------+---------------+-------------+--------+-----------------+----------------+
3 rows in set (0.00 sec)
# 通過上述資訊我們可以得知，thread_id=70的執行緒需要獲取的EXCLUSIVE鎖與thread_id=69的執行緒所持有的SHARED_WRITE鎖衝突了，但thread_id=69執行緒的process id是多少呢？檢視一下performance_schema.threads表
admin@localhost : (none) 11:53:47> select thread_id,processlist_id,type from performance_schema.threads where processlist_id is not null and type='FOREGROUND';
+-----------+----------------+------------+
| thread_id | processlist_id | type |
+-----------+----------------+------------+
| 43 | 1 | FOREGROUND |
| 69 | 27 | FOREGROUND |  # 發現thread_id=69的執行緒process id為27
| 70 | 28 | FOREGROUND |
| 71 | 29 | FOREGROUND |
| 74 | 32 | FOREGROUND |
+-----------+----------------+------------+
5 rows in set (0.01 sec)
# 到這裡，我們知道了DDL語句就是被process id為27的執行緒阻塞的，但，還不是很直觀，檢視起來比較繁瑣，我們還是直接使用sys.schema_table_lock_waits檢視檢視錶級別的鎖等待試試看吧，可以發現，該檢視列印的資訊看起來就很清晰了（可以清晰看到誰在等待，誰持有鎖），不過，怎麼有兩行？以哪行為準呢？
admin@localhost : (none) 11:59:04> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: sbtest
                 object_name: sbtest1
           waiting_thread_id: 70
                 waiting_pid: 28
             waiting_account: admin@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table sbtest1 modify col ... E utf8_bin NOT NULL DEFAULT ''
          waiting_query_secs: 744
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 69
                blocking_pid: 27
            blocking_account: admin@localhost
          blocking_lock_type: SHARED_WRITE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 27 # 這一行表鎖等待資訊提示kill 掉process id為27的執行緒
sql_kill_blocking_connection: KILL 27
*************************** 2. row ***************************
               object_schema: sbtest
                 object_name: sbtest1
           waiting_thread_id: 70
                 waiting_pid: 28
             waiting_account: admin@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table sbtest1 modify col ... E utf8_bin NOT NULL DEFAULT ''
          waiting_query_secs: 744
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 70
                blocking_pid: 28
            blocking_account: admin@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 28  # 這一行表鎖等待資訊提示kill 掉process id為28的執行緒
sql_kill_blocking_connection: KILL 28
2 rows in set (0.01 sec)

• 通過上述一翻折騰，我們終於找出了誰持有了MDL鎖了，不過，遺憾的是，我們無法知道持有MDL鎖的執行緒執行了什麼操作，所以我們不能草率地直接將其殺掉，作為DBA側的人員來說，或許我們可以推測一下，與應用的INSERT報錯操作或許有關係，但這裡找不出任何直接的關聯證據，怎麼辦呢？請繼續往下看
• PS：
  
  • 在MySQL 5.7版本中，mdl的instruments採集器是預設關閉的，如果事先沒有啟用該採集器，則將會排查該問題帶來巨大的麻煩，所以，建議大家在MySQL 5.7中啟用該採集器
    * 啟用這個採集器並不會有多大的效能損失，但是帶來的便利大家都看到了
    * 更多的時候，我們也許會碰到語句正在正常執行，但是執行時間卻非常長，無法看到MySQL內部此時正在執行具體的什麼操作，此時，甚至建議大家將所有的等待事件都預設啟用，在很多時候可以方便地檢視每個會話正在執行什麼具體的操作。不過，請注意，performance_schema在MySQL 5.7版本中預設啟用，與關閉performance_schema相比，啟用之後有1%~5%的效能損耗；與關閉performance_schema相比，啟用所有的等待事件之後有1%~15%的效能損耗。具體損耗需要看具體的語句型別
  • 在MySQL 8.0版本中，mdl的instruments採集器是預設啟用的

解決

• 在上文中我們找到了問題的原因，大致的解決方法也找到了，但陷入了兩難的境地，這個時候，我們如何抉擇解決這個問題的方法呢？這裡我們羅列了如下幾種解決方式供參考：
  
  • 方式一：殺死阻塞DDL語句的會話，但通常這不是明智之舉，因為我們無法找出持有MDL鎖的會話執行了什麼操作，也無法判斷什麼原因導致沒有釋放MDL鎖。且應用側不知情的情況下操作，是存在一定風險的
  • 方式二：終止DDL語句，選擇業務低峰期或其他時間段執行，顯然，這種被動乾等、且可能需要反覆嘗試，還不一定能解決問題的做法不是最高效的
  • 方式三：應用開發人員溝通，確認清楚INSERT語句是否必須要立即插入超長資料（另外，別忘記請應用開發人員協同排查process id為27的執行緒做了什麼操作導致MDL鎖未釋放）
    * 如果不是，則建議應用開發人員自行調整資料長度以匹配表結構定義長度，這樣DDL語句暫時也不需要執行了，DBA側直接撤銷DDL操作即可。後續如果有需求，則選擇在一個業務低峰期或者一個維護視窗執行即可
    * 如果該INSERT語句必須要立即插入超長資料，則建議並指導應用開發人員處理可能存在的事務回滾相關事宜（這個時候事務並未提交，理論上回滾該事務在資料庫層面不存在資料丟失的風險），然後，DBA側再執行DDL語句修改欄位定義長度
• PS：也許有的同學會對方式二提出質疑，在生產環境中，執行DDL語句，DBA側可以任性做嘗試嗎？當然不能，我們在上文中提到過，這裡我們模擬的DDL操作只會修改後設資料，為什麼只會修改後設資料呢，因為我們是將varchar型別列從60個字元定義長度修改為70個字元定義長度，而這裡資料庫環境中的字符集為utf8。至於為什麼滿足這些條件之後，在MySQL 5.7及其之後的版本中的online ddl只需要修改後設資料就能夠完成操作，本文不做贅述，有興趣的同學請自行研究MySQL 5.7或MySQL 8.0的online ddl特性。

預防

• 如果MDL鎖等待現象持續時間太長沒有及時發現，在高併發業務場景下是比較危險的，一旦後續持續不斷有該DDL涉及的表相關的DML請求進來，則可能造成大量的鎖等待，甚至迅速將資料庫的連線數打滿。要預防該情況的發生，需要應用側和DBA側都各自做好預防策略

• DBA側可通過在執行DDL語句的會話中，會話級別設定lock_wait_timeout系統變數為一個較小的值，在超過該時間值之後，仍然無法獲得所需的鎖時，自動放棄DDL操作（請自行評估需求）

root@localhost : sbtest 04:37:43> set lock_wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)
root@localhost : sbtest 04:37:47> alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

• 應用側對請求失敗的事務，需要有相應的重試、回滾機制（以便在發生異常時及時釋放事務相關的資源），以及記錄每一筆請求的日誌記錄

附錄-復現方法

• 1）在同一個資料庫中，開啟三個會話

• 2）在會話1中，開啟一個顯式事務，並使用INSERT語句插入一行超過欄位定義長度的資料行（模擬應用側插入資料）

admin@localhost : sbtest:13: > begin;
Query OK, 0 rows affected (0.00 sec)
admin@localhost : sbtest:17: > insert into sbtest1 values(2,2,'40393031789-25132409365-58213491013-66541287984-65586459874-05762316127-59922091522-12151119251-49498591378-18011532520','test-29736863337-73672352543-26439979097-89323822066-87557735686');
ERROR 1406 (22001): Data too long for column 'pad' at row 1

• 3）在會話2中，將報錯欄位pad的長度加長到70個字元（模擬DBA側使用DDL語句修改列長度定義）

# 檢視錶結構中的欄位定義長度，可以發現，報錯的pad列定義長度為varchar型別的60個字元長度
root@localhost : sbtest 04:12:03> show create table sbtest1;
+---------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',
  `pad` varchar(60) COLLATE utf8_bin NOT NULL DEFAULT '',  # pad欄位的定義長度為60
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+---------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
# 檢視INSERT語句中，pad列給出資料字串的字元長度與位元組長度
## INSERT語句中給出的pad列值字元長度為64
root@localhost : sbtest 11:01:33> select char_length('test-29736863337-73672352543-26439979097-89323822066-87557735686');
+---------------------------------------------------------------------------------+
| char_length('test-29736863337-73672352543-26439979097-89323822066-87557735686') |
+---------------------------------------------------------------------------------+
| 64 |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
## INSERT語句中給出的pad列值位元組長度為64
root@localhost : sbtest 11:02:19> select length('test-29736863337-73672352543-26439979097-89323822066-87557735686');
+----------------------------------------------------------------------------+
| length('test-29736863337-73672352543-26439979097-89323822066-87557735686') |
+----------------------------------------------------------------------------+
| 64 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 使用alter語句修改pad列定義長度為70
root@localhost : sbtest 04:12:47> alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '';   
## 發生阻塞

• 4）在會話3中，檢視資料庫中的會話狀態資訊（模擬DBA側排查故障問題）

# 可以看到info列的alter語句的state列值為Waiting for table metadata lock，表示在等待MDL鎖
admin@localhost : (none) 11:50:55> show processlist;
+----+-------+-----------+--------+---------+------+---------------------------------+--------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+--------+---------+------+---------------------------------+--------------------------------+
| 27 | admin | localhost | sbtest | Sleep | 919 | | NULL |
| 28 | admin | localhost | sbtest | Query | 898 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |
| 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |
| 32 | admin | localhost | NULL | Sleep | 154 | | NULL |
+----+-------+-----------+--------+---------+------+---------------------------------+------------------------------------+
4 rows in set (0.00 sec)

• 5）通過上述模擬步驟，我們可以很容易地得出結論，導致DDL語句發生MDL鎖等待的原因，就是因為執行INSERT語句的顯式事務在報錯之後，處於一個既未提交，也未回滾的狀態，這個時候，需要應用自行處理這種情況，但如果在真實環境中，作為DBA側的人員在排查這類問題時，就顯得比較尷尬了。當然，如果使用的是自動提交的事務，則不存在這個問題，當執行INSERT語句失敗時，事務會自動回滾。
  
  • 注意：在MySQL 5.7中，超過欄位定義長度的語句發生報錯，是因為sql_mode引數預設值設定了嚴格模式，在MySQL 5.6及其之前的版本中，sql_mode引數的預設值是未設定嚴格模式的，這種情況下，超過欄位定義長度的INSERT操作仍然能執行成功，但成功插入資料庫中的資料是被截斷之後的資料（超長部分被丟棄了）
• PS：MDL後設資料鎖的型別有很多種，根據官方手冊中對performance_schema.metadata_locks表的LOCK_TYPE欄位的描述可得知，一共有9種（INTENTION_EXCLUSIVE、SHARED、SHARED_HIGH_PRIO、SHARED_READ、SHARED_WRITE、SHARED_UPGRADABLE、SHARED_NO_WRITE、SHARED_NO_READ_WRITE、EXCLUSIVE），但，官方手冊中並未找到每一種MDL鎖的具體含義和發生的場景，關於MDL鎖更詳細的資訊可參考如下這兩個連結
  
  • https://blog.csdn.net/wanbf123/article/details/80697787
  • https://blog.csdn.net/finalkof1983/article/details/88063328

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2658975/，如需轉載，請註明出處，否則將追究法律責任。