以下文章來自微信官方賬號——節目新視界,兩位師兄撰寫。
在程序員的職業生涯中,總有數據庫表被鎖的情況,前幾天又發生了一次。由于突發業務需求,各部門都在批量操作導出數據,數據庫讀寫不分離。結果,數據庫中的一個表被鎖定!
反饋系統部分功能無法使用,鎖定數據庫表進行緊急排查定位,然后進行緊急處理。這篇文章告訴你類似突發事件的故障排除和解決過程。建議喜歡的收藏未雨綢繆。
故障查尋
用戶在某個功能頁面報錯502,于是先看服務是否正常,數據庫是否正常。在控制臺中,我看到數據庫的CPU猛增,堆積了大量未提交的事務,有些事務已經被阻塞很久了?;径ㄎ皇菙祿鞂佑袉栴}。
看了一下被屏蔽的交易列表,發現里面有鎖。我本想用控制臺直接結束被阻塞的事務,但是控制臺賬號權限有限,所以我通過客戶端登錄相應的賬號來殺死被鎖定的事務,防止了情況惡化。
大家說說吧。如果突然面臨類似情況,應該如何緊急應對?
解決辦法
想象一個場景,當然是軟件工程師職業生涯中會遇到的場景:一個本來正常運行的程序,有一天突然數據庫的表被鎖了,業務無法正常運行。那么如何快速定位哪個事務鎖了表,如何結束相應的事情呢?
首先,最簡單粗暴的方法就是:重啟MySQL。沒錯,網管解決問題的神器——“重啟”。至于后果,能不能跑,三思而后行!
重啟可以解決表鎖定的問題,但對于在線業務顯然不可行。
讓我們來看看不跑步的解決方案:
第一步:查看表使用
當遇到數據庫阻塞的問題時,應該首先檢查表是否在使用中。
showopentableswherein _ use
如果查詢結果為空,說明表沒有被使用,說明不是鎖表的問題。
mysqlshowopentableswherein _ use0
清空設置(0.00秒)
例如,如果查詢結果不為空,將出現以下結果:
mysqlshowopentableswherein _ use
- - - -
|數據庫|表|使用中|名稱鎖定|
- - - -
|測試|t|1|0|
- - - -
1流動插圖(0.00秒)
那么測試臺正在使用中,此時需要進一步調查。
第二步:查看進程
檢查數據庫的當前進程,查看是否有緩慢的SQL或阻塞的線程。
執行命令:
showprocesslist
這個命令只顯示當前用戶正在運行的線程。當然,如果是root的話,可以全部看到。
在上面的實踐中,阿里云控制臺之所以可以查看所有線程,是因為應該是root,但是我去殺的時候,因為登錄的用戶不是root的數據庫賬號,不能操作另一個用戶的線程,所以殺不了。
第三步:查看當前運行的所有事務
在緊急情況下,可以跳過這一步,主要用于檢查:
SELECT*FROMinformation_schema。INNODB
第四步:查看當前出現的鎖
在緊急情況下,可以跳過這一步,主要用于檢查:
SELECT*FROMinformation_schema
.INNODB_LOCKs;第五步:查詢鎖等待的對應關系
SELECT * FROM information_schema.INNODB_LOCK_waits;
看事務表INNODB_TRX中是否有正在鎖定的事務線程,看看ID是否在show processlist的sleep線程中。如果在,說明這個sleep的線程事務一直沒有commit或者rollback,而是卡住了,需要手動kill掉。
搜索的結果中,如果在事務表發現了很多任務,最好都kill掉。
第六步:kill掉事務
執行kill命令:
kill 1011;
對應的線程都執行完kill命令之后,后續事務便可正常處理。
針對緊急情況,通常也會直接操作第一、第二、第六步。
MySQL的鎖
這里再補充一些MySQL鎖相關的知識點:數據庫鎖設計的初衷是處理并發問題,作為多用戶共享的資源,當出現并發訪問的時候,數據庫需要合理地控制資源的訪問規則,而鎖就是用來實現這些訪問規則的重要數據結構。
根據加鎖的范圍,MySQL里面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種:一種是表鎖,一種是元數據鎖(metadata lock,MDL)。
表鎖是在Server層實現的,ALTER TABLE之類的語句會使用表鎖,忽略存儲引擎的鎖機制。表鎖通過lock tables... read/write來實現,而對于InnoDB來說,一般會采用行級鎖。畢竟鎖住整張表影響范圍太大了。
另外一個表級鎖是MDL(metadata lock),用于并發情況下維護數據的一致性,保證讀寫的正確性,不需要顯式的使用,在訪問一張表時會被自動加上。
MySQL鎖表場景
常見的一種鎖表場景就是有事務操作處于:Waiting for table metadata lock狀態。
Waiting for table metadata lock
MySQL在進行alter table等DDL操作時,有時會出現Waiting for table metadata lock的等待場景。
一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態,后續對該表的任何操作(包括讀)都無法進行,因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現了鎖等待隊列,就會造成災難性的后果。
場景一:長事務運行,阻塞DDL,繼而阻塞所有同表的后續操作。
通過show processlist可以看到表上有正在進行的操作(包括讀),此時alter table語句無法獲取到metadata 獨占鎖,會進行等待。
場景二:為提交事務,阻塞DDL,繼而阻塞所有同表的后續操作。
通過show processlist看不到表上有任何操作,但實際上存在有未提交的事務,可以在information_schema.innodb_trx中查看到。在事務沒有完成之前,表上的鎖不會釋放,alter table同樣獲取不到metadata的獨占鎖。
處理方法:通過 select * from information_schema.innodb_trx\G, 找到未提交事物的sid,然后kill掉,讓其回滾。
場景三:顯式事務失敗操作獲得鎖,未釋放
通過show processlist看不到表上有任何操作,在information_schema.innodb_trx中也沒有任何進行中的事務。很可能是因為在一個顯式的事務中,對表進行了一個失敗的操作(比如查詢了一個不存在的字段),這時事務沒有開始,但是失敗語句獲取到的鎖依然有效,沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。
處理方法:通過performance_schema.events_statements_current找到其sid,kill 掉該session,也可以kill掉DDL所在的session。
總之,alter table的語句是很危險的(核心是未提交事務或者長事務導致的),在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯語句。
如果有alter table的維護任務,在無人監管的時候運行,最好通過lock_wait_timeout設置好超時時間,避免長時間的metedata鎖等待。
小結
關于MySQL的鎖表其實還有很多其他場景,我們在實踐的過程中盡量避免鎖表情況的發生,當然這需要一定經驗的支撐。但更重要的是,如果發現鎖表我們要能夠快速的響應,快速的解決問題,避免影響正常業務,避免情況進一步惡化。所以,本文中的解決思路大家一定要收藏或記憶一下,做到有備無患,避免突然狀況下抓瞎。
完