• 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

| 作者簡介

羅小波·沃趣科技高級數據庫技術專家

IT從業多年，主要負責MySQL 產品的數據庫支撐與售后二線支撐。曾參與版本發布系統、輕量級監控系統、運維管理平臺、數據庫管理平臺的設計與編寫，熟悉MySQL體系結構，Innodb存儲引擎，喜好專研開源技術，多次在公開場合做過線下線上數據庫專題分享，發表過多篇數據庫相關的研究文章。

網頁名稱：MySQL執行DDL語句hang住了怎么辦?-創新互聯
URL標題：http://www.yijiale78.com/article32/ddcosc.html

成都網站建設公司_創新互聯，為您提供軟件開發、自適應網站、網站建設、網站設計、網站制作、移動網站建設

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯