查詢mysql數據庫表被鎖（數據庫表被鎖住）

1、MySQL數據庫中查詢表是否被鎖以及解鎖
2、mysql表被鎖了怎麼解鎖
3、如何查看MySQL數據庫的死鎖信息
4、MySQL數據庫表被鎖、解鎖，刪除事務
5、怎麼查看mysql表是否被鎖定

1.查看錶被鎖狀態

2.查看造成死鎖的sql語句

3.查詢進程

4.解鎖(刪除進程)

5.查看正在鎖的事物 (8.0以下版本)

6.查看等待鎖的事物 (8.0以下版本)

重啟mysql服務

執行show processlist，找到state，State狀態為Locked即被其他查詢鎖住。KILL 10866。

查看MySQL數據庫的死鎖日誌

1. 使用終端或命令提示符登錄到MySQL，輸入命令：mysql -h xxxx.xxx.xxx -P 3306 -u username -p 解釋：xxxx.xxx.xxx是數據庫IP地址，username是數據庫用戶名，輸入命令後，會讓你輸入username對應的密碼，就可以登錄了

2. 如何查看MySQL數據庫的死鎖信息在MySQL客戶端下輸入命令： show engine innodb status \G;

3. 如何定位MySQL數據庫的死鎖信息在打印出來的信息中找到“LATEST DETECTED DEADLOCK”一節內容，看圖中紅線

4. 如何分析日誌，定位死鎖原因看3裡面的圖，紫色劃線部分分析：事務1，等待 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`，這個位置的X鎖事務2，持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`這個地方的S鎖事務2，等待這個地方的X鎖理論上這個事務2是可以提交的不會，死鎖，但是這個事務日誌只打印最後一部分死鎖，信息，這裡面隱含的條件是，事務1也持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`這個地方的S鎖，這樣，事務2不能加X鎖，同時事務1也不能加X鎖，產生死鎖。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

執行命令：

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

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

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

看事務表INNODB_TRX中是否有正在鎖定的事務線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務一直沒有commit或者rollback，而是卡住了，需要手動kill掉。

搜索的結果中，如果在事務表發現了很多任務，最好都kill掉。

執行kill命令：

對應的線程都執行完kill命令之後，後續事務便可正常處理。

針對緊急情況，通常也會直接操作第一、第二、第六步。

這裡再補充一些MySQL鎖相關的知識點：數據庫鎖設計的初衷是處理並發問題，作為多用戶共享的資源，當出現並發訪問的時候，數據庫需要合理地控制資源的訪問規則，而鎖就是用來實現這些訪問規則的重要數據結構。

根據加鎖的範圍，MySQL裡面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（metadata lock，MDL)。

表鎖是在Server層實現的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現，而對於InnoDB來說，一般會採用行級鎖。畢竟鎖住整張表影響範圍太大了。

另外一個表級鎖是MDL（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的鎖等待隊列。如果核心表出現了鎖等待隊列，就會造成災難性的後果。

通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨佔鎖，會進行等待。

通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務，可以在information_schema.innodb_trx中查看到。在事務沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨佔鎖。

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

當你開始執行一個 ALTER ，而你遇到了可怕的“元數據鎖定等待”，我敢肯定你一定遇見過。我最近遇到了一個案例，其中被更改的表要執行一個很小範圍的更新（100行）。ALTER 在負載測試期間一直等待了幾個小時。在停止負載測試後，ALTER 按預期在不到一秒的時間內就完成了。那麼這裡發生了什麼？

檢查外鍵

每當有奇數次鎖定時，我的第一直覺就是檢查外鍵。當然這張表有一些外鍵引用了一個更繁忙的表。但是這種行為似乎仍然很奇怪。對錶運行 ALTER 時，會針對子表請求一個 SHARED_UPGRADEABLE 元數據鎖。還有針對父級的 SHARED_READ_ONLY 元數據鎖。

我們來看看如何根據文檔獲取元數據鎖定[1]：

如果給定鎖定有多個服務器，則首先滿足最高優先級鎖定請求，並且與 max_write_lock_count系統變量有關。寫鎖定請求的優先級高於讀取鎖定請求。

[1]：

請務必注意鎖定順序是序列化的：語句逐個獲取元數據鎖，而不是同時獲取，並在此過程中執行死鎖檢測。

通常在考慮隊列時考慮先進先出。如果我發出以下三個語句（按此順序），它們將按以下順序完成：

1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent

但是當子 ALTER 語句請求對父進行讀取鎖定時，儘管排序，但兩個插入將在 ALTER 之前完成。以下是可以演示此示例的示例場景：

數據初始化：

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 具有打開的事務，並且處於休眠狀態，並在父級上授予寫入元數據鎖定。會話 2 具有在子級上授予的可升級（寫入）鎖定，並且正在等待父級的讀取鎖定。最後會話 3 具有針對父級的授權寫入鎖定：

請注意，具有掛起鎖定狀態的唯一會話是會話 2（ALTER）。會話 1 和會話 3 （分別在 ALTER 之前和之後發布）都被授予了寫鎖。排序失敗的地方是在會話 1 上發生提交的時候。在考慮有序隊列時，人們會期望會話 2 獲得鎖定，事情就會繼續進行。但是，由於元數據鎖定系統的優先級性質，會話 3 具有鎖定，會話 2 仍然等待。

如果另一個寫入會話進入並啟動新事務並獲取針對父表的寫鎖定，則即使會話 3 完成，ALTER 仍將被阻止。

只要我保持一個對父表打開元數據鎖定的活動事務，子表上的 ALTER 將永遠不會完成。更糟糕的是，由於子表上的寫鎖定成功（但是完整語句正在等待獲取父讀鎖定），所以針對子表的所有傳入讀取請求都將被阻止！

另外，請考慮一下您通常如何對無法完成的語句進行故障排除。您查看已經打開較長時間的事務（在進程列表和 InnoDB 狀態中）。但由於阻塞線程現在比 ALTER 線程更年輕，因此您將看到的最舊的事務/線程是 ALTER 。

這正是這種情況下發生的情況。在準備發布時，我們的客戶端正在運行 ALTER 語句並結合負載測試（一種非常好的做法！）以確保順利發布。問題是負載測試保持對父表打開一個活動的寫事務。這並不是說它只是一直在寫，而是有多個線程，一個總是活躍的。這阻止了 ALTER 完成並阻止對相對靜態的子表的隨後的讀請求。

幸運的是，這個問題有一個解決方案（除了從設計模式中驅逐外鍵）。變量 max_write_lock_count[2] 可用於允許在寫入鎖定之後在讀取鎖定之前授予讀取鎖定連續寫鎖。默認情況下，此變量設置為 18446744073709551615，如果你對該表發出 10,000 次寫入/秒，那麼你的讀將被鎖定 5800 萬年……

原創文章，作者：UT7Q3，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/127241.html

查詢mysql數據庫表被鎖（數據庫表被鎖住）

相關推薦

發表回復