背景

在程序員的職業生涯中，總會遇到數據庫表被鎖的情況，前些天就又撞見一次。由于業務突發需求，各個部門都在批量操作、導出數據，而數據庫又未做讀寫分離，結果就是：數據庫的某張表被鎖了!

用戶反饋系統部分功能無法使用，緊急排查，定位是數據庫表被鎖，然后進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。

故障追蹤

用戶反饋某功能頁面報502錯誤，于是第一時間看服務是否正常，數據庫是否正常。在控制臺看到數據庫CPU飆升，堆積大量未提交事務，部分事務已經阻塞了很長時間，基本定位是數據庫層出現問題了。

查看阻塞事務列表，發現其中有鎖表現象，本想利用控制臺直接結束掉阻塞的事務，但控制臺賬號權限有限，于是通過客戶端登錄對應賬號將鎖表事務kill掉，才避免了情況惡化。

下面就聊聊，如果當突然面對類似的情況，我們該如何緊急響應?

解決方案

想象一個場景，當然也是軟件工程師職業生涯中會遇到的一種場景：原本運行正常的程序，某一天突然數據庫的表被鎖了，業務無法正常運轉，那么我們該如何快速定位是哪個事務鎖了表，如何結束對應的事物?

首先最簡單粗暴的方式就是：重啟MySQL。對的，網管解決問題的神器——“重啟”。至于后果如何，你能不能跑了，要你自己三思而后行了!

重啟是可以解決表被鎖的問題的，但針對線上業務很顯然不太具有可行性。

下面來看看不用跑路的解決方案：

第一步：查看表使用

遇到數據庫阻塞問題，首先要查詢一下表是否在使用。

show open tables where in_use > 0 ;

如果查詢結果為空，那么說明表沒在使用，說明不是鎖表的問題。

ysql>  show open tables where in_use > 0 ;
Empty set (0.00 sec)

如果查詢結果不為空，比如出現如下結果：

mysql>  show open tables where in_use > 0 ;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t     |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

則說明表(test)正在被使用，此時需要進一步排查。

第二步：查看進程

查看數據庫當前的進程，看看是否有慢SQL或被阻塞的線程。

執行命令：

show processlist;

該命令只顯示當前用戶正在運行的線程，當然，如果是root用戶是能看到所有的。

在上述實踐中，阿里云控制臺之所以能夠查看到所有的線程，猜測應該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的數據庫賬號，無法操作另外一個用戶的線程。

第三步：查看當前運行的所有事務

如果情況緊急，此步驟可以跳過，主要用來查看核對：

SELECT * FROM information_schema.INNODB_TRX;

第四步：查看當前出現的鎖

如果情況緊急，此步驟可以跳過，主要用來查看核對：

SELECT * FROM information_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 * frominformation_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的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發生，當然這需要一定經驗的支撐。但更重要的是，如果發現鎖表我們要能夠快速的響應，快速的解決問題，避免影響正常業務，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。