MySQL執(zhí)行DDL語句hang住了怎么辦?-創(chuàng)新互聯(lián)

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

環(huán)境

數(shù)據(jù)庫版本：MySQL 5.7.27
數(shù)據(jù)信息
- sysbench模擬2張1000W的表
操作系統(tǒng)版本：CentOS Linux release 7.3.1611 (Core)
服務(wù)器信息
- CPU：Intel(R) Xeon(R) CPU E5-4627 v2 @ 3.30GHz * 2
- 內(nèi)存：248G
- 磁盤：1.6T LSI Flash卡
- 網(wǎng)卡：萬兆網(wǎng)卡

現(xiàn)象

當(dāng)我們發(fā)現(xiàn)一個故障問題時，首先需要做的事情，就是先確認(rèn)現(xiàn)象，也就是先要自己親眼瞅見故障長什么樣子，而不是直接上去就是一通胡亂排查。尤其是故障是別人反饋過來的時候，一定要首先確認(rèn)故障現(xiàn)象表現(xiàn)及其真實性（別人反饋過來的問題，很多時候根本就不是問題，而是他自己姿勢不對）。
在本文中，提到的故障現(xiàn)象是DBA側(cè)自己執(zhí)行DDL語句修改字段長度時自己發(fā)現(xiàn)的（當(dāng)然，這里是模擬的DBA側(cè)操作），那么，根據(jù)上述準(zhǔn)則，我們先開啟另外一個會話，查看執(zhí)行DDL語句 hang住具體是什么情況？

# 使用show processlist語句查看會話狀態(tài)信息，發(fā)現(xiàn)DDL語句的state列值為Waiting for table metadata lock，表示在等待MDL元數(shù)據(jù)鎖。根據(jù)MySQL 5.7及其之后的版本中的online ddl特性，該語句應(yīng)該立即執(zhí)行完成（它只會修改元數(shù)，因為這里只是修改了字段長度，并沒有修改字段的其他屬性），因此，故障現(xiàn)象確認(rèn)
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)

友情提示：確認(rèn)故障現(xiàn)象的位置不要搞錯，在什么位置發(fā)現(xiàn)故障問題，首先就在什么位置進(jìn)行故障確認(rèn)。舉個反例，曾經(jīng)我碰到一個同學(xué)，在一個“應(yīng)用-->LVS-->分庫中間件-->讀寫分離中間件-->數(shù)據(jù)庫主從集群”的架構(gòu)層級中，應(yīng)用側(cè)反饋數(shù)據(jù)庫連接很慢，這位同學(xué)直接登錄到數(shù)據(jù)庫中去確認(rèn)現(xiàn)象了，顯然，確認(rèn)故障現(xiàn)象時搞錯了位置，在這個反例中確認(rèn)故障現(xiàn)象的位置應(yīng)該以應(yīng)用側(cè)反饋故障現(xiàn)象的位置為準(zhǔn)（首先使用LVS的VIP嘗試連接），逐層往下確認(rèn)

分析

確認(rèn)了故障現(xiàn)象，DDL語句hang住的原因是因為在等待MDL元數(shù)據(jù)鎖，但不知道MDL元數(shù)據(jù)鎖被誰持有了。接下來，就要圍繞這個現(xiàn)象，推測可能導(dǎo)致該問題的一些原因了，哪些原因可能導(dǎo)致該問題呢？我們可以按照下面的思路進(jìn)行逐個排除
- 服務(wù)器的主機負(fù)載過高（CPU、內(nèi)存、磁盤吞吐與IOPS、網(wǎng)卡帶寬），有沒有別的程序擠占了數(shù)據(jù)庫進(jìn)程的資源
  * 你也許會說，這里的現(xiàn)象很明顯是因為在等MDL鎖，跟服務(wù)器負(fù)載無關(guān)，肯定是有人加了鎖沒釋放。但我想提醒的是，該現(xiàn)象真的是一個單純的問題嗎？會不會是一個鏈?zhǔn)椒磻?yīng)導(dǎo)致的？等待MDL鎖雖然是DDL語句被阻塞的原因，但也許它同時也是服務(wù)器高負(fù)載的現(xiàn)象與結(jié)果
- 數(shù)據(jù)庫進(jìn)程的負(fù)載過高
  * 數(shù)據(jù)庫中的活躍會話數(shù)量及其狀態(tài)
  * 數(shù)據(jù)庫的QPS/TPS
- 存在其他會話正在執(zhí)行DML語句，或執(zhí)行了某些DML語句之后事務(wù)未及時提交、或者其他某個會話也同時在執(zhí)行某個DDL語句修改sbtest1表的表結(jié)構(gòu)信息

排查

有了思路，接下來就按照上文中提到的思路逐個進(jìn)行排查
首先，我們查看主機負(fù)載信息，通過下圖我們可以看到，主機基本處于空載狀態(tài)，毫無壓力
目前創(chuàng)新互聯(lián)公司已為上千余家的企業(yè)提供了網(wǎng)站建設(shè)、域名、虛擬主機、網(wǎng)站托管、企業(yè)網(wǎng)站設(shè)計、資溪網(wǎng)站維護(hù)等服務(wù)，公司將堅持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

MySQL 執(zhí)行DDL語句 hang住了怎么辦?

然后，我們查看數(shù)據(jù)庫的活躍會話數(shù)量及其狀態(tài)，我們可以看到數(shù)據(jù)庫中并沒有大量會話，也不存在正在執(zhí)行的DML語句在操作表sbtest1，也不存在同時有其他會話同時使用DDL在操作相同的表，但這里無法確認(rèn)是否存在未提交的事務(wù)

# 反復(fù)多執(zhí)行幾次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)

通過上述步驟，已確認(rèn)在負(fù)載層面并沒有問題，此時，我們需要重點確認(rèn)是否存在某個會話執(zhí)行了某些DML語句之后事務(wù)未及時提交，如何確認(rèn)這些信息呢？我們可以通過performance_schema和information_schema中的鎖和事務(wù)相關(guān)的表進(jìn)行查詢確認(rèn)
先查看information_schema中記錄的事務(wù)信息

# 發(fā)現(xiàn)并沒有事務(wù)存在...
admin@localhost : sbtest 05:49:17> select * from information_schema.innodb_trx\G
Empty set (0.00 sec)
# 也可以順便使用sys.innodb_lock_waits視圖確認(rèn)是否存在一些事務(wù)鎖等待
admin@localhost : performance_schema 06:27:35> select * from sys.innodb_lock_waits\G
Empty set, 3 warnings (0.00 sec)  # 查詢結(jié)果為空
查看performance_schema下的MDL元數(shù)據(jù)鎖記錄信息 
# 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)  # 查詢結(jié)果為空

查看performance_schema下的handle持有信息

# 發(fā)現(xiàn)表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是數(shù)據(jù)庫內(nèi)部的線程ID，我們需要看到與之對應(yīng)的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 |  # 發(fā)現(xiàn)processlist id為28
| 71 | 29 | FOREGROUND |
| 72 | 30 | FOREGROUND |
+-----------+----------------+------------+
5 rows in set (0.00 sec)
# 通過show processlist再次查看一下id號，額。。發(fā)現(xiàn)id列為28的居然就是執(zhí)行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查看下鎖與事務(wù)信息

# 仍然沒有發(fā)現(xiàn)有效的鎖信息
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)
......

排查到這里，視乎已經(jīng)沒招了，也許我們還可以用mysqladmin debug命令試試看

# 執(zhí)行debug命令，執(zhí)行該命令之后，一些鎖信息可能會被debug出來打印到錯誤日志中
[root@physical-machine ~]# mysqladmin debug
# 很遺憾，在錯誤日志中仍然沒有發(fā)現(xiàn)有效的鎖信息
[root@physical-machine ~]# vim /data/mysqldata1/log/error.log

還有最后一招，可以使用pstack和gdb命令查看，但，生產(chǎn)系統(tǒng)不建議隨隨便便使用這類命令，這類命令會讓整個實例級別發(fā)生一段時間的阻塞。且上述故障問題并不能說明整個數(shù)據(jù)庫實例級別存在問題，也許只是發(fā)生MDL鎖等待的兩個會話之間的問題。so...咋辦呢？要認(rèn)慫嗎？
等等，我們好像忽略了點什么，既然DDL語句在等待MDL元數(shù)據(jù)鎖，為啥在performance_schema.metadata_locks表中沒有記錄？查看一下MDL事件采集器試試看？

# 喔。。發(fā)現(xiàn)居然MDL鎖信息的采集器開關(guān)并沒有打開，難怪metadata_locks表中無法記錄MDL元數(shù)據(jù)鎖信息
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)
# 現(xiàn)在，我們啟用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)

關(guān)于instruments采集器，如果事先是處于關(guān)閉狀態(tài)，啟用之后，需要新的請求進(jìn)來才能夠收集對應(yīng)的事件信息，因此，如果要采集MDL鎖的信息，我們得讓故障現(xiàn)象重新復(fù)現(xiàn)一遍。讀到這里，也許你會說，干嘛不早說？好吧，SORRY，我是故意的...
現(xiàn)在，我們終止掉DDL語句，使用文末的步驟重新操作一遍
然后，我們重新查詢information_schema下的事務(wù)和鎖信息

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

查看performance_schema下的MDL元數(shù)據(jù)鎖記錄信息

# 查看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狀態(tài)，為thread_id=69的線程所持有。SHARED_WRITE類型的MDL鎖是一把意向排他IX鎖，通常在執(zhí)行MDL或select ... for update時產(chǎn)生
| TABLE | sbtest | sbtest1 | 140048817276288 | SHARED_WRITE | TRANSACTION | GRANTED | | 69 | 11 |
# 從這行信息我們可以看到，表sbtest1上有一把SHARED_UPGRADABLE的鎖處于GRANTED狀態(tài)，為thread_id=70的線程持有，從上文中的信息我們可以知道，thread_id=70的process id為28，也就是執(zhí)行DDL語句被阻塞的那個會話id。SHARED_UPGRADABLE類型的MDL鎖是一把共享升級鎖，一般在執(zhí)行online DDL語句時會產(chǎn)生。它的作用是在執(zhí)行online ddl期間允許相同表的DML但防止DDL
| TABLE | sbtest | sbtest1 | 140049018604784 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 70 | 6 |
# 從這行信息我們可以看到，表sbtest1上有一把EXCLUSIVE處于PENDING狀態(tài)，為thread_id=70的線程在等待，從上文中的信息我們可以知道，thread_id=70的process id為28，也就是執(zhí)行DDL語句被阻塞的那個會話id。EXCLUSIVE類型的MDL鎖是一把排它X鎖，用于阻止其他線程讀寫元數(shù)據(jù)信息，一般在執(zhí)行DDL時產(chǎn)生
| 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 |  # 發(fā)現(xiàn)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視圖查看表級別的鎖等待試試看吧，可以發(fā)現(xiàn)，該視圖打印的信息看起來就很清晰了（可以清晰看到誰在等待，誰持有鎖），不過，怎么有兩行？以哪行為準(zhǔn)呢？
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鎖的線程執(zhí)行了什么操作，所以我們不能草率地直接將其殺掉，作為DBA側(cè)的人員來說，或許我們可以推測一下，與應(yīng)用的INSERT報錯操作或許有關(guān)系，但這里找不出任何直接的關(guān)聯(lián)證據(jù)，怎么辦呢？請繼續(xù)往下看
PS：
- 在MySQL 5.7版本中，mdl的instruments采集器是默認(rèn)關(guān)閉的，如果事先沒有啟用該采集器，則將會排查該問題帶來巨大的麻煩，所以，建議大家在MySQL 5.7中啟用該采集器
  * 啟用這個采集器并不會有多大的性能損失，但是帶來的便利大家都看到了
  * 更多的時候，我們也許會碰到語句正在正常執(zhí)行，但是執(zhí)行時間卻非常長，無法看到MySQL內(nèi)部此時正在執(zhí)行具體的什么操作，此時，甚至建議大家將所有的等待事件都默認(rèn)啟用，在很多時候可以方便地查看每個會話正在執(zhí)行什么具體的操作。不過，請注意，performance_schema在MySQL 5.7版本中默認(rèn)啟用，與關(guān)閉performance_schema相比，啟用之后有1%~5%的性能損耗；與關(guān)閉performance_schema相比，啟用所有的等待事件之后有1%~15%的性能損耗。具體損耗需要看具體的語句類型
- 在MySQL 8.0版本中，mdl的instruments采集器是默認(rèn)啟用的

解決

在上文中我們找到了問題的原因，大致的解決方法也找到了，但陷入了兩難的境地，這個時候，我們?nèi)绾尉駬窠鉀Q這個問題的方法呢？這里我們羅列了如下幾種解決方式供參考：
- 方式一：殺死阻塞DDL語句的會話，但通常這不是明智之舉，因為我們無法找出持有MDL鎖的會話執(zhí)行了什么操作，也無法判斷什么原因?qū)е聸]有釋放MDL鎖。且應(yīng)用側(cè)不知情的情況下操作，是存在一定風(fēng)險的
- 方式二：終止DDL語句，選擇業(yè)務(wù)低峰期或其他時間段執(zhí)行，顯然，這種被動干等、且可能需要反復(fù)嘗試，還不一定能解決問題的做法不是最高效的
- 方式三：應(yīng)用開發(fā)人員溝通，確認(rèn)清楚INSERT語句是否必須要立即插入超長數(shù)據(jù)（另外，別忘記請應(yīng)用開發(fā)人員協(xié)同排查process id為27的線程做了什么操作導(dǎo)致MDL鎖未釋放）
  * 如果不是，則建議應(yīng)用開發(fā)人員自行調(diào)整數(shù)據(jù)長度以匹配表結(jié)構(gòu)定義長度，這樣DDL語句暫時也不需要執(zhí)行了，DBA側(cè)直接撤銷DDL操作即可。后續(xù)如果有需求，則選擇在一個業(yè)務(wù)低峰期或者一個維護(hù)窗口執(zhí)行即可
  * 如果該INSERT語句必須要立即插入超長數(shù)據(jù)，則建議并指導(dǎo)應(yīng)用開發(fā)人員處理可能存在的事務(wù)回滾相關(guān)事宜（這個時候事務(wù)并未提交，理論上回滾該事務(wù)在數(shù)據(jù)庫層面不存在數(shù)據(jù)丟失的風(fēng)險），然后，DBA側(cè)再執(zhí)行DDL語句修改字段定義長度
PS：也許有的同學(xué)會對方式二提出質(zhì)疑，在生產(chǎn)環(huán)境中，執(zhí)行DDL語句，DBA側(cè)可以任性做嘗試嗎？當(dāng)然不能，我們在上文中提到過，這里我們模擬的DDL操作只會修改元數(shù)據(jù)，為什么只會修改元數(shù)據(jù)呢，因為我們是將varchar類型列從60個字符定義長度修改為70個字符定義長度，而這里數(shù)據(jù)庫環(huán)境中的字符集為utf8。至于為什么滿足這些條件之后，在MySQL 5.7及其之后的版本中的online ddl只需要修改元數(shù)據(jù)就能夠完成操作，本文不做贅述，有興趣的同學(xué)請自行研究MySQL 5.7或MySQL 8.0的online ddl特性。

預(yù)防

如果MDL鎖等待現(xiàn)象持續(xù)時間太長沒有及時發(fā)現(xiàn)，在高并發(fā)業(yè)務(wù)場景下是比較危險的，一旦后續(xù)持續(xù)不斷有該DDL涉及的表相關(guān)的DML請求進(jìn)來，則可能造成大量的鎖等待，甚至迅速將數(shù)據(jù)庫的連接數(shù)打滿。要預(yù)防該情況的發(fā)生，需要應(yīng)用側(cè)和DBA側(cè)都各自做好預(yù)防策略
DBA側(cè)可通過在執(zhí)行DDL語句的會話中，會話級別設(shè)置lock_wait_timeout系統(tǒng)變量為一個較小的值，在超過該時間值之后，仍然無法獲得所需的鎖時，自動放棄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

應(yīng)用側(cè)對請求失敗的事務(wù)，需要有相應(yīng)的重試、回滾機制（以便在發(fā)生異常時及時釋放事務(wù)相關(guān)的資源），以及記錄每一筆請求的日志記錄

附錄-復(fù)現(xiàn)方法

1）在同一個數(shù)據(jù)庫中，開啟三個會話
2）在會話1中，開啟一個顯式事務(wù)，并使用INSERT語句插入一行超過字段定義長度的數(shù)據(jù)行（模擬應(yīng)用側(cè)插入數(shù)據(jù)）

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側(cè)使用DDL語句修改列長度定義）

# 查看表結(jié)構(gòu)中的字段定義長度，可以發(fā)現(xiàn)，報錯的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列給出數(shù)據(jù)字符串的字符長度與字節(jié)長度
## 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列值字節(jié)長度為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 '';   
## 發(fā)生阻塞

4）在會話3中，查看數(shù)據(jù)庫中的會話狀態(tài)信息（模擬DBA側(cè)排查故障問題）

# 可以看到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）通過上述模擬步驟，我們可以很容易地得出結(jié)論，導(dǎo)致DDL語句發(fā)生MDL鎖等待的原因，就是因為執(zhí)行INSERT語句的顯式事務(wù)在報錯之后，處于一個既未提交，也未回滾的狀態(tài)，這個時候，需要應(yīng)用自行處理這種情況，但如果在真實環(huán)境中，作為DBA側(cè)的人員在排查這類問題時，就顯得比較尷尬了。當(dāng)然，如果使用的是自動提交的事務(wù)，則不存在這個問題，當(dāng)執(zhí)行INSERT語句失敗時，事務(wù)會自動回滾。
- 注意：在MySQL 5.7中，超過字段定義長度的語句發(fā)生報錯，是因為sql_mode參數(shù)默認(rèn)值設(shè)置了嚴(yán)格模式，在MySQL 5.6及其之前的版本中，sql_mode參數(shù)的默認(rèn)值是未設(shè)置嚴(yán)格模式的，這種情況下，超過字段定義長度的INSERT操作仍然能執(zhí)行成功，但成功插入數(shù)據(jù)庫中的數(shù)據(jù)是被截斷之后的數(shù)據(jù)（超長部分被丟棄了）
PS：MDL元數(shù)據(jù)鎖的類型有很多種，根據(jù)官方手冊中對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鎖的具體含義和發(fā)生的場景，關(guān)于MDL鎖更詳細(xì)的信息可參考如下這兩個鏈接
- https://blog.csdn.net/wanbf123/article/details/80697787
- https://blog.csdn.net/finalkof1983/article/details/88063328

| 作者簡介

羅小波·沃趣科技高級數(shù)據(jù)庫技術(shù)專家

IT從業(yè)多年，主要負(fù)責(zé)MySQL 產(chǎn)品的數(shù)據(jù)庫支撐與售后二線支撐。曾參與版本發(fā)布系統(tǒng)、輕量級監(jiān)控系統(tǒng)、運維管理平臺、數(shù)據(jù)庫管理平臺的設(shè)計與編寫，熟悉MySQL體系結(jié)構(gòu)，Innodb存儲引擎，喜好專研開源技術(shù)，多次在公開場合做過線下線上數(shù)據(jù)庫專題分享，發(fā)表過多篇數(shù)據(jù)庫相關(guān)的研究文章。

網(wǎng)頁名稱：MySQL執(zhí)行DDL語句hang住了怎么辦?-創(chuàng)新互聯(lián)
URL標(biāo)題：http://muchs.cn/article32/ddcosc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開發(fā)、自適應(yīng)網(wǎng)站、網(wǎng)站建設(shè)、網(wǎng)站設(shè)計、網(wǎng)站制作、移動網(wǎng)站建設(shè)

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

猜你還喜歡下面的內(nèi)容