mysql資料庫被鎖了怎麼解（mysql鎖庫和解鎖）

本文目錄一覽：

• 1、MySQL資料庫中查詢表是否被鎖以及解鎖
• 2、MySQL資料庫如何鎖定和解鎖資料庫表
• 3、MySQL資料庫表被鎖、解鎖，刪除事務
• 4、如何利用MySQL資料庫鎖定和解鎖資料庫表

MySQL資料庫中查詢表是否被鎖以及解鎖

1.查看錶被鎖狀態

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

3.查詢進程

4.解鎖(刪除進程)

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

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

MySQL資料庫如何鎖定和解鎖資料庫表

第一步，創建資料庫表writer和查看錶結構，利用SQL語句：

create table writer(

wid int(10),

wno int(10),

wname varchar(20),

wsex varchar(2),

wage int(2)

第二步，向資料庫表writer插入五條數據，插入後查看錶里數據

第三步，利用鎖定語句鎖定資料庫表writer，利用SQL語句：

lock table writer read;

讓資料庫表只讀不能進行寫

第四步，為了驗證鎖定效果，可以查看資料庫表數據，利用SQL語句：

select * from writer;

第五步，利用update語句對id=5進行更新，SQL語句為：

update writer set wname = ‘胡思思’ where id = 5;

第六步，利用unlock進行解鎖，SQL語句為：

unlock tables;

MySQL資料庫表被鎖、解鎖，刪除事務

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

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

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

如何利用MySQL資料庫鎖定和解鎖資料庫表

第一步，創建資料庫表writer和查看錶結構，利用SQL語句：

create table writer(

wid int(10),

wno int(10),

wname varchar(20),

wsex varchar(2),

wage int(2)

);

desc writer;

如下圖所示：

第二步，向資料庫表writer插入五條數據，插入後查看錶里數據，如下圖所示：

第三步，利用鎖定語句鎖定資料庫表writer，利用SQL語句：

lock table writer read;

讓資料庫表只讀不能進行寫

如下圖所示：

第四步，為了驗證鎖定效果，可以查看資料庫表數據，利用SQL語句：

select * from writer;

如下圖所示：

第五步，利用update語句對id=5進行更新，SQL語句為：

update writer set wname = ‘胡思思’ where id = 5;

如下圖所示：

6

第六步，利用unlock進行解鎖，SQL語句為：

unlock tables;

如下圖所示：