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

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

環(huán)境

數(shù)據(jù)庫(kù)版本：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àn)兆網(wǎng)卡

現(xiàn)象

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

# 使用show processlist語(yǔ)句查看會(huì)話狀態(tài)信息，發(fā)現(xiàn)DDL語(yǔ)句的state列值為Waiting for table metadata lock，表示在等待MDL元數(shù)據(jù)鎖。根據(jù)MySQL 5.7及其之后的版本中的online ddl特性，該語(yǔ)句應(yīng)該立即執(zhí)行完成（它只會(huì)修改元數(shù)，因?yàn)檫@里只是修改了字段長(zhǎng)度，并沒(méi)有修改字段的其他屬性），因此，故障現(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)象的位置不要搞錯(cuò)，在什么位置發(fā)現(xiàn)故障問(wèn)題，首先就在什么位置進(jìn)行故障確認(rèn)。舉個(gè)反例，曾經(jīng)我碰到一個(gè)同學(xué)，在一個(gè)“應(yīng)用-->LVS-->分庫(kù)中間件-->讀寫分離中間件-->數(shù)據(jù)庫(kù)主從集群”的架構(gòu)層級(jí)中，應(yīng)用側(cè)反饋數(shù)據(jù)庫(kù)連接很慢，這位同學(xué)直接登錄到數(shù)據(jù)庫(kù)中去確認(rèn)現(xiàn)象了，顯然，確認(rèn)故障現(xiàn)象時(shí)搞錯(cuò)了位置，在這個(gè)反例中確認(rèn)故障現(xiàn)象的位置應(yīng)該以應(yīng)用側(cè)反饋故障現(xiàn)象的位置為準(zhǔn)（首先使用LVS的VIP嘗試連接），逐層往下確認(rèn)

分析

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

排查

有了思路，接下來(lái)就按照上文中提到的思路逐個(gè)進(jìn)行排查
首先，我們查看主機(jī)負(fù)載信息，通過(guò)下圖我們可以看到，主機(jī)基本處于空載狀態(tài)，毫無(wú)壓力
創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括筠連網(wǎng)站建設(shè)、筠連網(wǎng)站制作、筠連網(wǎng)頁(yè)制作以及筠連網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，筠連網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到筠連省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

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

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

# 反復(fù)多執(zhí)行幾次show processlist語(yǔ)句
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)

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

# 發(fā)現(xiàn)并沒(méi)有事務(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視圖查看表級(jí)別的鎖等待
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)
# 通過(guò)performance_schema.threads表查看是哪個(gè)線程（thread_id是數(shù)據(jù)庫(kù)內(nèi)部的線程ID，我們需要看到與之對(duì)應(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)
# 通過(guò)show processlist再次查看一下id號(hào)，額。。發(fā)現(xiàn)id列為28的居然就是執(zhí)行DDL語(yǔ)句被hang住那個(gè)會(huì)話，好吧，白忙活了
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)

通過(guò)show engine innodb status查看下鎖與事務(wù)信息

# 仍然沒(méi)有發(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)沒(méi)招了，也許我們還可以用mysqladmin debug命令試試看

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

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

# 喔。。發(fā)現(xiàn)居然MDL鎖信息的采集器開關(guān)并沒(méi)有打開，難怪metadata_locks表中無(wú)法記錄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)，啟用之后，需要新的請(qǐng)求進(jìn)來(lái)才能夠收集對(duì)應(yīng)的事件信息，因此，如果要采集MDL鎖的信息，我們得讓故障現(xiàn)象重新復(fù)現(xiàn)一遍。讀到這里，也許你會(huì)說(shuō)，干嘛不早說(shuō)？好吧，SORRY，我是故意的...
現(xiàn)在，我們終止掉DDL語(yǔ)句，使用文末的步驟重新操作一遍
然后，我們重新查詢information_schema下的事務(wù)和鎖信息

# 查看information_schema.innodb_trx表，仍然沒(méi)有事務(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鎖信息，咦，有信息了！不過(guò)，有點(diǎn)亂?。ㄎ覀冞@里只查詢sbtest庫(kù)下的sbtest1表就可以了，因?yàn)槲覀兊牟僮饕仓簧婕暗竭@張表）
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時(shí)產(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語(yǔ)句被阻塞的那個(gè)會(huì)話id。SHARED_UPGRADABLE類型的MDL鎖是一把共享升級(jí)鎖，一般在執(zhí)行online DDL語(yǔ)句時(shí)會(huì)產(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語(yǔ)句被阻塞的那個(gè)會(huì)話id。EXCLUSIVE類型的MDL鎖是一把排它X鎖，用于阻止其他線程讀寫元數(shù)據(jù)信息，一般在執(zhí)行DDL時(shí)產(chǎn)生
| TABLE | sbtest | sbtest1 | 140049018564112 | EXCLUSIVE | TRANSACTION | PENDING | | 70 | 6 |
+-------------+---------------+-------------+-----+---------+---------------+-------------+--------+-----------------+----------------+
3 rows in set (0.00 sec)
# 通過(guò)上述信息我們可以得知，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語(yǔ)句就是被process id為27的線程阻塞的，但，還不是很直觀，查看起來(lái)比較繁瑣，我們還是直接使用sys.schema_table_lock_waits視圖查看表級(jí)別的鎖等待試試看吧，可以發(fā)現(xiàn)，該視圖打印的信息看起來(lái)就很清晰了（可以清晰看到誰(shuí)在等待，誰(shuí)持有鎖），不過(guò)，怎么有兩行？以哪行為準(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)

通過(guò)上述一翻折騰，我們終于找出了誰(shuí)持有了MDL鎖了，不過(guò)，遺憾的是，我們無(wú)法知道持有MDL鎖的線程執(zhí)行了什么操作，所以我們不能草率地直接將其殺掉，作為DBA側(cè)的人員來(lái)說(shuō)，或許我們可以推測(cè)一下，與應(yīng)用的INSERT報(bào)錯(cuò)操作或許有關(guān)系，但這里找不出任何直接的關(guān)聯(lián)證據(jù)，怎么辦呢？請(qǐng)繼續(xù)往下看
PS：
- 在MySQL 5.7版本中，mdl的instruments采集器是默認(rèn)關(guān)閉的，如果事先沒(méi)有啟用該采集器，則將會(huì)排查該問(wèn)題帶來(lái)巨大的麻煩，所以，建議大家在MySQL 5.7中啟用該采集器
  * 啟用這個(gè)采集器并不會(huì)有多大的性能損失，但是帶來(lái)的便利大家都看到了
  * 更多的時(shí)候，我們也許會(huì)碰到語(yǔ)句正在正常執(zhí)行，但是執(zhí)行時(shí)間卻非常長(zhǎng)，無(wú)法看到MySQL內(nèi)部此時(shí)正在執(zhí)行具體的什么操作，此時(shí)，甚至建議大家將所有的等待事件都默認(rèn)啟用，在很多時(shí)候可以方便地查看每個(gè)會(huì)話正在執(zhí)行什么具體的操作。不過(guò)，請(qǐng)注意，performance_schema在MySQL 5.7版本中默認(rèn)啟用，與關(guān)閉performance_schema相比，啟用之后有1%~5%的性能損耗；與關(guān)閉performance_schema相比，啟用所有的等待事件之后有1%~15%的性能損耗。具體損耗需要看具體的語(yǔ)句類型
- 在MySQL 8.0版本中，mdl的instruments采集器是默認(rèn)啟用的

解決

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

預(yù)防

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

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è)對(duì)請(qǐng)求失敗的事務(wù)，需要有相應(yīng)的重試、回滾機(jī)制（以便在發(fā)生異常時(shí)及時(shí)釋放事務(wù)相關(guān)的資源），以及記錄每一筆請(qǐng)求的日志記錄

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

1）在同一個(gè)數(shù)據(jù)庫(kù)中，開啟三個(gè)會(huì)話
2）在會(huì)話1中，開啟一個(gè)顯式事務(wù)，并使用INSERT語(yǔ)句插入一行超過(guò)字段定義長(zhǎng)度的數(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）在會(huì)話2中，將報(bào)錯(cuò)字段pad的長(zhǎng)度加長(zhǎng)到70個(gè)字符（模擬DBA側(cè)使用DDL語(yǔ)句修改列長(zhǎng)度定義）

# 查看表結(jié)構(gòu)中的字段定義長(zhǎng)度，可以發(fā)現(xiàn)，報(bào)錯(cuò)的pad列定義長(zhǎng)度為varchar類型的60個(gè)字符長(zhǎng)度
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字段的定義長(zhǎng)度為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語(yǔ)句中，pad列給出數(shù)據(jù)字符串的字符長(zhǎng)度與字節(jié)長(zhǎng)度
## INSERT語(yǔ)句中給出的pad列值字符長(zhǎng)度為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語(yǔ)句中給出的pad列值字節(jié)長(zhǎng)度為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語(yǔ)句修改pad列定義長(zhǎng)度為70
root@localhost : sbtest 04:12:47> alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '';   
## 發(fā)生阻塞

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

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

| 作者簡(jiǎn)介

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

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

分享名稱：MySQL執(zhí)行DDL語(yǔ)句hang住了怎么辦?
轉(zhuǎn)載來(lái)源：http://muchs.cn/article44/ghsjee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、App設(shè)計(jì)、自適應(yīng)網(wǎng)站、網(wǎng)站設(shè)計(jì)公司、微信公眾號(hào)、品牌網(wǎng)站建設(shè)

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

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