背景

最近遇到一個問題，需要在user用戶表千萬級別數(shù)據(jù)中添加兩個字段，發(fā)現(xiàn)老是加不上去，一直卡死。

表數(shù)據(jù)量不僅大，而且是一個熱點表，訪問頻率特別高，而且該表的訪問是在一個大事務(wù)中。加字段的時候一直在等待獲取MDL寫鎖。

這個等待也影響了后續(xù)表訪問對MDL讀鎖的獲取，導(dǎo)致后面的查詢也都被堵塞了。

更慘的是，客戶端有重試機制，查詢堵塞超過超時時間會再起一個session進(jìn)行請求，導(dǎo)致數(shù)據(jù)庫的線程池很快就爆滿了，直接掛掉。

MDL鎖

MDL鎖介紹

MDL鎖屬于表級別的元數(shù)據(jù)鎖。表級別鎖分為數(shù)據(jù)鎖和元數(shù)據(jù)鎖，通常我們說的加鎖一般指的是加的數(shù)據(jù)鎖。跟數(shù)據(jù)鎖一樣，元數(shù)據(jù)鎖也分讀鎖和寫鎖。

MDL不需要顯示使用，在進(jìn)行表操作時會自動加上。當(dāng)對表數(shù)據(jù)進(jìn)行增刪改查（ insert、delete、select、update等）時，會自動加上MDL讀鎖；當(dāng)要對表進(jìn)行加減字段的結(jié)構(gòu)修改時，會自動加上MDL寫鎖。

• 讀鎖不互斥：意味著可以多個線程同時對一張表進(jìn)行增刪改查（CRUD）的操作。
• 寫鎖獨占：進(jìn)行結(jié)構(gòu)修改前，要先等待其他所有的MDL鎖釋放了才能獲取到MDL寫鎖。獲取到寫鎖后，在寫鎖釋放前，其他線程無法獲取到MDL讀鎖和寫鎖。也就是說，修改一個表的結(jié)構(gòu)過程中，會阻塞其他線程對表的操作

MDL鎖是MySQL自動隱式加鎖，無需我們手動操作。在我們執(zhí)行DML語句的時候，MySQL自動添加MDL讀鎖。在我們執(zhí)行DDL語句的時候，MySQL自動添加MDL寫鎖。讀鎖與讀鎖之間不互斥，讀鎖與寫鎖、寫鎖與寫鎖之間互斥。注意：MDL鎖是表鎖，會對整張表加鎖

• DML（Data Manipulation Language）數(shù)據(jù)操縱語言：適用范圍：對表數(shù)據(jù)進(jìn)行操作，比如 insert、delete、select、update等。
• DDL（Data Definition Language）數(shù)據(jù)定義語言：適用范圍：對表結(jié)構(gòu)進(jìn)行操作，比如create、drop、alter、rename、truncate等。

MDL鎖的必要性

MDL鎖 的存在，其實是為了保證 數(shù)據(jù)的一致性 。

想象一下，假如沒有 MDL鎖 ，一個查詢在遍歷表數(shù)據(jù)的過程中，另外一個線程執(zhí)行了ALTER TABLE t DELETE COLUMN 'col_1'把col_1這一列刪掉了，那查詢結(jié)果就亂了，結(jié)果中是否應(yīng)該有這一列數(shù)據(jù)？

所以為了保證并發(fā)操作下數(shù)據(jù)的一致性。

如果一個事務(wù)正在執(zhí)行中，另一個在這時修改了表結(jié)構(gòu)，不但可能導(dǎo)致當(dāng)前事務(wù)出現(xiàn)不可重復(fù)讀的問題，還有可能連事務(wù)都無法提交

事故復(fù)現(xiàn)

介紹完MDL鎖，我們再來復(fù)現(xiàn)下事故。我們通過下面的操作序列來模擬線上情況。

時刻1，事務(wù)1對表user進(jìn)行查詢，注意此時事務(wù)1并未提交，所以獲取的MDL讀鎖也不會釋放。

時刻2另外一個線程想要添加字段, 由于 事務(wù)1正持著MDL讀鎖，所以事務(wù)2會陷入阻塞，等待事務(wù)1釋放讀鎖后獲取MDL寫鎖。

申請 MDL 鎖的操作會形成一個隊列，隊列中寫鎖獲取優(yōu)先級高于讀鎖。

所以事務(wù)2不僅阻塞了加字段的操作，也會阻塞后續(xù)對該表的所有操作。比如后面的事務(wù)3查詢由于獲取不到MDL讀鎖都被阻塞了。

這時，如果客戶端有重試機制，查詢超時后會重新進(jìn)行請求，容易把數(shù)據(jù)庫的連接池給擠爆了。

Mysql服務(wù)宕機的原因

為什么會出現(xiàn)這種情況呢？

原因是在執(zhí)行查詢語句的時候，MySQL自動加了MDL鎖（metadata lock，即元數(shù)據(jù)鎖）。

不行的話，我們可以再執(zhí)行一下show processlist命令，查看有哪些正在執(zhí)行的進(jìn)程：

可以清楚的看到Session2和Session3的語句正在等待MDL鎖，Waiting for table metadata lock。

解決方案

方案一

了解了原因，事情就比較好處理了，數(shù)據(jù)庫奔潰原因是由于加字段等待時間太長導(dǎo)致影響后續(xù)請求，但mysql又無法在 alter table 語句里面設(shè)定等待時間。

所以當(dāng)時做法是繼續(xù)嘗試加字段語句，語句卡住30秒就手動cancel掉。避免對后續(xù)請求的影響。重試了幾次發(fā)現(xiàn)一直沒能加上。最后是通過查看接口調(diào)用監(jiān)控，在請求頻率較低的時間點給加上了。

方案二

從MySQL5.6版本開始增加了Online DDL，作用就是在執(zhí)行DDL的時候，允許并發(fā)執(zhí)行DML。簡單翻譯就是修改表結(jié)構(gòu)的時候，也能同時支持并發(fā)執(zhí)行增刪查改操作。

從MySQL8.0版本開始又優(yōu)化了Online DDL，支持快速添加列，可以實現(xiàn)給大表秒級加字段。具體用法就是在DDL語句后面增加兩個參數(shù)ALGORITHM和LOCK。

比如下面這樣：

ALTER TABLE user ADD age int NOT NULL DEFAULT '0' COMMENT '年齡', ALGORITHM=Inplace, LOCK=NONE;

ALGORITHM可以指定使用哪種算法執(zhí)行DDL，可選項有：

• Copy：拷貝方式，MySQL5.6 之前 DDL 的執(zhí)行方式，過程就是先創(chuàng)建新表，修改新表結(jié)構(gòu)，把舊表數(shù)據(jù)復(fù)制到新表，刪除舊表，重命名新表。執(zhí)行過程非常耗時，產(chǎn)生大量的磁盤IO和占用CPU，還有使Buffer poll失效，而且需要鎖住舊表，性能較差，現(xiàn)在基本很少使用。
• Inplace：原地修改，MySQL5.6開始引入的，優(yōu)點是不會在Server層發(fā)生表數(shù)據(jù)拷貝，過程中允許并發(fā)執(zhí)行DML操作。過程就是先添加MDL寫鎖，執(zhí)行初始化操作，然后降級為MDL讀鎖，執(zhí)行DDL操作（比較耗時，允許并發(fā)執(zhí)行DML操作），升級為MDL寫鎖，完成DDL操作。
• Instant：快速修改，MySQL8.0開始引入的，可以實現(xiàn)快速給大表添加字段。

性能依次是，Instant > Inplace > Copy。

LOCK可以指定執(zhí)行過程中，是否加鎖，可選項有：

• NONE不加鎖，允許DML操作。
• SHARED加讀鎖，允許讀操作，禁止DML操作。
• DEFAULT默認(rèn)鎖模式，在滿足DDL操作前提下，默認(rèn)鎖模式會允許盡可能多的讀操作和DML操作。
• EXCLUSIVE加寫鎖，禁止讀操作和DML操作。

其他

這里我們重點關(guān)注INNODB_TRX, INNODB_LOCKS, 以及INNODB_LOCK_WAITS三張表，表如其名，這三張表記錄了正在運行的事務(wù)（包括事務(wù)占用or釋放鎖的信息）狀態(tài)信息

select * FROM information_schema.INNODB_TRX;
select * FROM information_schema.INNODB_LOCKS;
select * FROM information_schema.INNODB_LOCK_WAITS;

desc information_schema.INNODB_TRX;
desc information_schema.INNODB_LOCKS;
desc information_schema.INNODB_LOCK_WAITS

show engine innodb status;
show processlist;

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論