當(dāng)你開始執(zhí)行一個 ALTER ，而你遇到了可怕的“元數(shù)據(jù)鎖定等待”，我敢肯定你一定遇見過。我最近遇到了一個案例，其中被更改的表要執(zhí)行一個很小范圍的更新（100行）。ALTER 在負載測試期間一直等待了幾個小時。在停止負載測試后，ALTER 按預(yù)期在不到一秒的時間內(nèi)就完成了。那么這里發(fā)生了什么？
在沈陽等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè) 網(wǎng)站設(shè)計制作按需網(wǎng)站開發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站建設(shè),全網(wǎng)營銷推廣,成都外貿(mào)網(wǎng)站建設(shè),沈陽網(wǎng)站建設(shè)費用合理。
檢查外鍵
每當(dāng)有奇數(shù)次鎖定時，我的第一直覺就是檢查外鍵。當(dāng)然這張表有一些外鍵引用了一個更繁忙的表。但是這種行為似乎仍然很奇怪。對表運行 ALTER 時，會針對子表請求一個 SHARED_UPGRADEABLE 元數(shù)據(jù)鎖。還有針對父級的 SHARED_READ_ONLY 元數(shù)據(jù)鎖。
我們來看看如何根據(jù)文檔獲取元數(shù)據(jù)鎖定[1]：
如果給定鎖定有多個服務(wù)器，則首先滿足最高優(yōu)先級鎖定請求，并且與 max_write_lock_count系統(tǒng)變量有關(guān)。寫鎖定請求的優(yōu)先級高于讀取鎖定請求。
[1]：
請務(wù)必注意鎖定順序是序列化的：語句逐個獲取元數(shù)據(jù)鎖，而不是同時獲取，并在此過程中執(zhí)行死鎖檢測。
通常在考慮隊列時考慮先進先出。如果我發(fā)出以下三個語句（按此順序），它們將按以下順序完成：
1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent
但是當(dāng)子 ALTER 語句請求對父進行讀取鎖定時，盡管排序，但兩個插入將在 ALTER 之前完成。以下是可以演示此示例的示例場景：
數(shù)據(jù)初始化：
CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`val` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent` (`parent_id`),
CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;
INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");
Session 1:
start transaction;update parent set val = "four-new" where id = 4;
Session 2:
alter table child add index `idx_new` (val);
Session 3:
start transaction;update parent set val = "three-new" where id = 3;
此時，會話 1 具有打開的事務(wù)，并且處于休眠狀態(tài)，并在父級上授予寫入元數(shù)據(jù)鎖定。 會話 2 具有在子級上授予的可升級（寫入）鎖定，并且正在等待父級的讀取鎖定。最后會話 3 具有針對父級的授權(quán)寫入鎖定：
mysql select * from performance_schema.metadata_locks;+-------------+-------------+-------------------+---------------+-------------+| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE ? ? ? ? | LOCK_DURATION | LOCK_STATUS |+-------------+-------------+-------------------+---------------+-------------+| TABLE ? ? ? | child ? ? ? | SHARED_UPGRADABLE | TRANSACTION ? | GRANTED ? ? | - ALTER (S2)| TABLE ? ? ? | parent ? ? ?| SHARED_WRITE ? ? ?| TRANSACTION ? | GRANTED ? ? | - UPDATE (S1)| TABLE ? ? ? | parent ? ? ?| SHARED_WRITE ? ? ?| TRANSACTION ? | GRANTED ? ? | - UPDATE (S3)| TABLE ? ? ? | parent ? ? ?| SHARED_READ_ONLY ?| STATEMENT ? ? | PENDING ? ? | - ALTER (S2)+-------------+-------------+-------------------+---------------+-------------+
請注意，具有掛起鎖定狀態(tài)的唯一會話是會話 2（ALTER）。會話 1 和會話 3 （分別在 ALTER 之前和之后發(fā)布）都被授予了寫鎖。排序失敗的地方是在會話 1 上發(fā)生提交的時候。在考慮有序隊列時，人們會期望會話 2 獲得鎖定，事情就會繼續(xù)進行。但是，由于元數(shù)據(jù)鎖定系統(tǒng)的優(yōu)先級性質(zhì)，會話 3 具有鎖定，會話 2 仍然等待。
如果另一個寫入會話進入并啟動新事務(wù)并獲取針對父表的寫鎖定，則即使會話 3 完成，ALTER 仍將被阻止。
只要我保持一個對父表打開元數(shù)據(jù)鎖定的活動事務(wù)，子表上的 ALTER 將永遠不會完成。更糟糕的是，由于子表上的寫鎖定成功（但是完整語句正在等待獲取父讀鎖定），所以針對子表的所有傳入讀取請求都將被阻止！
另外，請考慮一下您通常如何對無法完成的語句進行故障排除。您查看已經(jīng)打開較長時間的事務(wù)（在進程列表和 InnoDB 狀態(tài)中）。但由于阻塞線程現(xiàn)在比 ALTER 線程更年輕，因此您將看到的最舊的事務(wù)/線程是 ALTER 。
這正是這種情況下發(fā)生的情況。在準備發(fā)布時，我們的客戶端正在運行 ALTER 語句并結(jié)合負載測試（一種非常好的做法?。┮源_保順利發(fā)布。問題是負載測試保持對父表打開一個活動的寫事務(wù)。這并不是說它只是一直在寫，而是有多個線程，一個總是活躍的。 這阻止了 ALTER 完成并阻止對相對靜態(tài)的子表的隨后的讀請求。
幸運的是，這個問題有一個解決方案（除了從設(shè)計模式中驅(qū)逐外鍵）。變量?max_write_lock_count[2]?可用于允許在寫入鎖定之后在讀取鎖定之前授予讀取鎖定連續(xù)寫鎖。默認情況下，此變量設(shè)置為 18446744073709551615，如果你對該表發(fā)出 10,000 次寫入/秒，那么你的讀將被鎖定 5800 萬年……
第一步，查看行鎖使用情況，命令：
show statue like 'innodb_row_lock%';
如下圖所示：
第二步，創(chuàng)建數(shù)據(jù)庫表monitor_amount，如下圖所示：
第三步，查看innodb的狀態(tài)，命令：
show innodb status \G;
如下圖所示：
第四步，向數(shù)據(jù)庫表monitor_amount插入四條記錄，如下圖所示：
第五步，再次查看innodb狀態(tài)，如下圖所示：
第六步，可以利用刪除表命令來停止查看，如下圖所示：
1.查看表被鎖狀態(tài)
2.查看造成死鎖的sql語句
3.查詢進程
4.解鎖(刪除進程)
5.查看正在鎖的事物? (8.0以下版本)
6.查看等待鎖的事物?(8.0以下版本)
一：檢查是否鎖表, 查詢進程并殺死進程
1) 查詢是否鎖表
show open tables where in_use 0;
2) 查詢進程（如果您有SUPER權(quán)限，您可以看到所有線程。否則，您只能看到您自己的線程）
show processlist;
二：查看在鎖事務(wù),殺死事務(wù)對應(yīng)的線程ID
1) 查看正在鎖的事務(wù)
select * from information_schema.INNODB_LOCKS;
2) 殺死進程id（就是[select * from information_schema.INNODB_LOCKS; ]命令的trx_mysql_thread_id列）
kill 線程ID
3) 查看等待鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
其它：
1) 查看服務(wù)器狀態(tài)
show status like '%lock%';
2) 查看超時時間：
show variables like '%timeout%';
以下五種方法可以快速定位全局鎖的位置，僅供參考。
方法1：利用 metadata_locks 視圖
此方法僅適用于 MySQL 5.7 以上版本，該版本 performance_schema 新增了 metadata_locks，如果上鎖前啟用了元數(shù)據(jù)鎖的探針（默認是未啟用的），可以比較容易的定位全局鎖會話。
方法2：利用 events_statements_history 視圖此方法適用于 MySQL 5.6 以上版本，啟用 performance_schema.eventsstatements_history（5.6 默認未啟用，5.7 默認啟用），該表會 SQL 歷史記錄執(zhí)行，如果請求太多，會自動清理早期的信息，有可能將上鎖會話的信息清理掉。
方法3：利用 gdb 工具如果上述兩種都用不了或者沒來得及啟用，可以嘗試第三種方法。利用 gdb 找到所有線程信息，查看每個線程中持有全局鎖對象，輸出對應(yīng)的會話 ID，為了便于快速定位，我寫成了腳本形式。也可以使用 gdb 交互模式，但 attach mysql 進程后 mysql 會完全 hang 住，讀請求也會受到影響，不建議使用交互模式。
方法4：show processlist
如果備份程序使用的特定用戶執(zhí)行備份，如果是 root 用戶備份，那 time 值越大的是持鎖會話的概率越大，如果業(yè)務(wù)也用 root 訪問，重點是 state 和 info 為空的，這里有個小技巧可以快速篩選，篩選后嘗試 kill 對應(yīng) ID，再觀察是否還有 wait global read lock 狀態(tài)的會話。
方法5：重啟試試！
在程序員的職業(yè)生涯中，總會遇到數(shù)據(jù)庫表被鎖的情況，前些天就又撞見一次。由于業(yè)務(wù)突發(fā)需求，各個部門都在批量操作、導(dǎo)出數(shù)據(jù)，而數(shù)據(jù)庫又未做讀寫分離，結(jié)果就是：數(shù)據(jù)庫的某張表被鎖了！
用戶反饋系統(tǒng)部分功能無法使用，緊急排查，定位是數(shù)據(jù)庫表被鎖，然后進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。
用戶反饋某功能頁面報502錯誤，于是第一時間看服務(wù)是否正常，數(shù)據(jù)庫是否正常。在控制臺看到數(shù)據(jù)庫CPU飆升，堆積大量未提交事務(wù)，部分事務(wù)已經(jīng)阻塞了很長時間，基本定位是數(shù)據(jù)庫層出現(xiàn)問題了。
查看阻塞事務(wù)列表，發(fā)現(xiàn)其中有鎖表現(xiàn)象，本想利用控制臺直接結(jié)束掉阻塞的事務(wù)，但控制臺賬號權(quán)限有限，于是通過客戶端登錄對應(yīng)賬號將鎖表事務(wù)kill掉，才避免了情況惡化。
下面就聊聊，如果當(dāng)突然面對類似的情況，我們該如何緊急響應(yīng)？
想象一個場景，當(dāng)然也是軟件工程師職業(yè)生涯中會遇到的一種場景：原本運行正常的程序，某一天突然數(shù)據(jù)庫的表被鎖了，業(yè)務(wù)無法正常運轉(zhuǎn)，那么我們該如何快速定位是哪個事務(wù)鎖了表，如何結(jié)束對應(yīng)的事物？
首先最簡單粗暴的方式就是：重啟MySQL。對的，網(wǎng)管解決問題的神器——“重啟”。至于后果如何，你能不能跑了，要你自己三思而后行了！
重啟是可以解決表被鎖的問題的，但針對線上業(yè)務(wù)很顯然不太具有可行性。
下面來看看不用跑路的解決方案：
遇到數(shù)據(jù)庫阻塞問題，首先要查詢一下表是否在使用。
如果查詢結(jié)果為空，那么說明表沒在使用，說明不是鎖表的問題。
如果查詢結(jié)果不為空，比如出現(xiàn)如下結(jié)果：
則說明表（test）正在被使用，此時需要進一步排查。
查看數(shù)據(jù)庫當(dāng)前的進程，看看是否有慢SQL或被阻塞的線程。
執(zhí)行命令：
該命令只顯示當(dāng)前用戶正在運行的線程，當(dāng)然，如果是root用戶是能看到所有的。
在上述實踐中，阿里云控制臺之所以能夠查看到所有的線程，猜測應(yīng)該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的數(shù)據(jù)庫賬號，無法操作另外一個用戶的線程。
如果情況緊急，此步驟可以跳過，主要用來查看核對：
如果情況緊急，此步驟可以跳過，主要用來查看核對：
看事務(wù)表INNODB_TRX中是否有正在鎖定的事務(wù)線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務(wù)一直沒有commit或者rollback，而是卡住了，需要手動kill掉。
搜索的結(jié)果中，如果在事務(wù)表發(fā)現(xiàn)了很多任務(wù)，最好都kill掉。
執(zhí)行kill命令：
對應(yīng)的線程都執(zhí)行完kill命令之后，后續(xù)事務(wù)便可正常處理。
針對緊急情況，通常也會直接操作第一、第二、第六步。
這里再補充一些MySQL鎖相關(guān)的知識點：數(shù)據(jù)庫鎖設(shè)計的初衷是處理并發(fā)問題，作為多用戶共享的資源，當(dāng)出現(xiàn)并發(fā)訪問的時候，數(shù)據(jù)庫需要合理地控制資源的訪問規(guī)則，而鎖就是用來實現(xiàn)這些訪問規(guī)則的重要數(shù)據(jù)結(jié)構(gòu)。
根據(jù)加鎖的范圍，MySQL里面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（metadata lock，MDL)。
表鎖是在Server層實現(xiàn)的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現(xiàn)，而對于InnoDB來說，一般會采用行級鎖。畢竟鎖住整張表影響范圍太大了。
另外一個表級鎖是MDL（metadata lock），用于并發(fā)情況下維護數(shù)據(jù)的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。
常見的一種鎖表場景就是有事務(wù)操作處于：Waiting for table metadata lock狀態(tài)。
MySQL在進行alter table等DDL操作時，有時會出現(xiàn)Waiting for table metadata lock的等待場景。
一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態(tài)，后續(xù)對該表的任何操作（包括讀）都無法進行，因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現(xiàn)了鎖等待隊列，就會造成災(zāi)難性的后果。
通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨占鎖，會進行等待。
通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務(wù)，可以在information_schema.innodb_trx中查看到。在事務(wù)沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨占鎖。
處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然后kill掉，讓其回滾。
通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務(wù)。很可能是因為在一個顯式的事務(wù)中，對表進行了一個失敗的操作（比如查詢了一個不存在的字段），這時事務(wù)沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。
處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。
總之，alter table的語句是很危險的（核心是未提交事務(wù)或者長事務(wù)導(dǎo)致的），在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務(wù)、也沒有顯式事務(wù)中的報錯語句。
如果有alter table的維護任務(wù)，在無人監(jiān)管的時候運行，最好通過lock_wait_timeout設(shè)置好超時時間，避免長時間的metedata鎖等待。
關(guān)于MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發(fā)生，當(dāng)然這需要一定經(jīng)驗的支撐。但更重要的是，如果發(fā)現(xiàn)鎖表我們要能夠快速的響應(yīng)，快速的解決問題，避免影響正常業(yè)務(wù)，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。
重慶分公司
重慶分公司
