嚇尿，給小表加個字段，把數據庫搞掛了
一天下午，在給線上一個小表加個字段，發現老是加不上去，一直卡死。運維同學突然跑過來跟我說，線上數據庫這半個小時一直在重啟，問我是否有做什麼操作。我當時虎軀一震，總共100多行的小表加個字段都加出問題了？我立馬停止嘗試加字段，果然數據庫恢復正常了。後面查到原因，也順利加上字段，現在來複盤總結一下。
先講下原因，表數據量雖然小，卻是一個熱點表，訪問頻率特別高，而且該表的訪問是在一個大事務中。加字段的時候一直在等待獲取MDL寫鎖。這個等待也影響了後續表訪問對MDL讀鎖的獲取，導致後面的查詢也都被堵塞了。更慘的是，客戶端有重試機制，查詢堵塞超過超時時間會再起一個session進行請求，導致數據庫的線程池很快就爆滿了，直接掛掉。
什麼是MDL鎖
MDL鎖屬於表級別的元數據鎖。表級別鎖分為數據鎖和元數據鎖，通常我們說的加鎖一般指的是加的數據鎖。跟數據鎖一樣，元數據鎖也分讀鎖和讀寫鎖。
MDL不需要顯示使用，在進行表操作時會自動加上。當對錶進行增刪改查時，會自動加上MDL讀鎖；當要對錶進行加減字段的結構修改時，會自動加上MDL寫鎖。
-
讀鎖不互斥，意味着可以多個線程同時對一張表進行增刪改查的操作。
-
寫鎖獨佔，進行結構修改前，要先等待其他所有的MDL鎖釋放了才能獲取到MDL寫鎖。獲取到寫鎖後，在寫鎖釋放前，其他線程無法獲取到MDL讀鎖和寫鎖。也就是說，修改一個表的結構過程中，會阻塞其他線程對錶的操作。
MDL鎖的必要性
MDL鎖的存在，其實是為了保證數據的一致性。想像一下，假如沒有MDL鎖，一個查詢在遍歷表數據的過程中，另外一個線程執行了ALTER TABLE t DELETE COLUMN 'col_1'把col_1這一列刪掉了，那查詢結果就亂了，結果中是否應該有這一列數據？
事故復現
介紹完MDL鎖，我們再來複現下事故。我們通過下面的操作序列來模擬線上情況。
時刻1，事務1對錶t_mdl_test進行查詢，注意此時事務1並未提交，所以獲取的MDL讀鎖也不會釋放。時刻2另外一個線程想要添加字段c, 由於事務1正持着MDL讀鎖，所以事務2會陷入阻塞，等待事務1釋放讀鎖後獲取MDL寫鎖。
申請 MDL 鎖的操作會形成一個隊列，隊列中寫鎖獲取優先級高於讀鎖。
所以事務2不僅阻塞了加字段的操作，也會阻塞後續對該表的所有操作。比如後面的事務3和事務4查詢由於獲取不到MDL讀鎖都被阻塞了。
這時，如果客戶端有重試機制，查詢超時後會重新進行請求，容易把數據庫的連接池給擠爆了。
表t_mdl_test建表：
CREATE TABLE `t_mdl_test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `a` varchar(64) NOT NULL, `b` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8;讀者可關注公眾號【會玩code】在獲取的寫庫
huiwan_write_x中自行實驗。
解決辦法
了解了原因，事情就比較好處理了，數據庫奔潰原因是由於加字段等待時間太長導致影響後續請求，但mysql又無法在 alter table 語句裏面設定等待時間.
所以當時做法是繼續嘗試加字段語句，語句卡住30秒就手動cancel掉。避免對後續請求的影響。重試了幾次發現一直沒能加上。。。，最後是通過查看接口調用監控，在請求頻率較低的時間點給加上了。
反思
- 避免寫大事務，如果不是查詢所在的事務太大，也不會導致後面語句獲取不到MDL寫鎖。
- 事務中，盡量減少加鎖時間。還是這次這個例子，從t_mdl_test中獲取的數據在事務最後一步更新其他表的時候才會用到，所以可以把t_mdl_test的查詢放在事務的尾部。減少t_mdl_test加鎖時間。
- 對錶結構修改的語句注意執行時間，長時間卡住需要注意先取消掉，避免影響其他線程對錶的增刪改查操作。
留個小問題
在查閱資料的時候，發現另外一個情況。
這種情況事務2會阻塞嗎？大家可以在自己的huiwan_write_x庫中自行實驗。原因我會在公眾號文章下留言公布，歡迎大家參與討論～。
寫在最後
喜歡本文的朋友，歡迎關注公眾號「會玩code」，專註大白話分享實用技術
公眾號福利
回復【mysql】獲取免費測試數據庫！！
回復【pdf】獲取持續更新海量學習資料！！
