嚇尿，給小表加個欄位，把資料庫搞掛了

會玩code發表於2021-05-28
資料庫

一天下午，在給線上一個小表加個欄位，發現老是加不上去，一直卡死。運維同學突然跑過來跟我說，線上資料庫這半個小時一直在重啟，問我是否有做什麼操作。我當時虎軀一震，總共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掉。避免對後續請求的影響。重試了幾次發現一直沒能加上。。。，最後是通過檢視介面呼叫監控，在請求頻率較低的時間點給加上了。

反思

  1. 避免寫大事務，如果不是查詢所在的事務太大，也不會導致後面語句獲取不到MDL寫鎖。
  2. 事務中，儘量減少加鎖時間。還是這次這個例子，從t_mdl_test中獲取的資料在事務最後一步更新其他表的時候才會用到，所以可以把t_mdl_test的查詢放在事務的尾部。減少t_mdl_test加鎖時間。
  3. 對錶結構修改的語句注意執行時間，長時間卡住需要注意先取消掉，避免影響其他執行緒對錶的增刪改查操作。

留個小問題

在查閱資料的時候，發現另外一個情況。

這種情況事務2會阻塞嗎？大家可以在自己的huiwan_write_x庫中自行實驗。原因我會在公眾號文章下留言公佈，歡迎大家參與討論～。

寫在最後

喜歡本文的朋友，歡迎關注公眾號「會玩code」，專注大白話分享實用技術

公眾號福利

回覆【mysql】獲取免費測試資料庫！！

回覆【pdf】獲取持續更新海量學習資料！！

相關文章