解決mysql數據庫表鎖死,mysql數據庫表鎖住了怎麼辦

本文目錄一覽：

• 1、MySQL數據庫如何鎖定和解鎖數據庫表
• 2、MySQL數據庫表被鎖、解鎖，刪除事務
• 3、MYSQL鎖死怎麼辦？
• 4、mysql鎖表問題分析以及解決方法-簡書
• 5、MySQL數據庫中查詢表是否被鎖以及解鎖
• 6、mysql 死鎖:如何解決mysql死鎖

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鎖死怎麼辦？

通過代碼解鎖。

代碼如下  

1set global max_connections=4000;

增加允許的最大連接數，先讓前台網站可以正常工作。

回過頭google ：mysql unauthenticated user

果然，遇到此類問題的人很多，問題在於mysql的反向ip地址解析，配置參數里加上skip-name-resolve就可以。

補充

一、查看進程運行情況（會話1）

代碼如下  

1mysql select id,user,host,db,command,time,state from processlist a;+—-+——+—————–+——————–+———+——+———–+| id | user | host | db | command | time | state|+—-+——+—————–+——————–+———+——+———–+| 40 | root | localhost:14046 | information_schema | Query | 0 | executing|| 39 | root | localhost:13992 | chf | Sleep | 251 ||| 38 | root | localhost:13991 | chf | Sleep | 251 ||+—-+——+—————–+——————–+———+——+———–+3 rows in set (0.00 sec)

二、構造表被鎖現象

1）鎖住表（會話1）

代碼如下  

1mysqlLOCK TABLES chf.disc02 READ;或者–LOCK TABLES chf.disc02 WRITE;

2）執行dml操作（會話2）

代碼如下  

1mysqldelete from chf.disc02 limit 1;–會話處於卡死狀態

3）查詢進程運行情況（會話1）

代碼如下  

1mysql select id,user,host,db,command,time,state from processlist a;+—-+——+—————–+——————–+———+——+———–+| id | user | host | db | command | time | state|+—-+——+—————–+——————–+———+——+———–+| 41 | root | localhost:14358 | chf | Query | 5 | Locked|| 40 | root | localhost:14046 | information_schema | Query | 0 | executing|| 39 | root | localhost:13992 | chf | Sleep | 343 ||| 38 | root | localhost:13991 | chf | Sleep | 343 ||+—-+——+—————–+——————–+———+——+———–+

4 rows in set (0.01 sec)

說明：發現進程id為41的進程狀態為Locked

三、解鎖操作

1）刪掉被鎖進程（會話1）

代碼如下  

1mysql kill 41;

出現現象（會話2）

ERROR 2013 (HY000): Lost connection to MySQL server during query

2）查看進程（會話1）

代碼如下  

1mysql select id,user,host,db,command,time,state from processlist a;+—-+——+—————–+——————–+———+——+———–+| id | user | host | db | command | time | state|+—-+——+—————–+——————–+———+——+———–+| 40 | root | localhost:14046 | information_schema | Query | 0 | executing|| 39 | root | localhost:13992 | chf | Sleep | 298 ||| 38 | root | localhost:13991 | chf | Sleep | 298 ||+—-+——+—————–+——————–+———+——+———–+3 rows in set (0.01 sec)

四、批量解鎖

代碼如下  

1mysql select concat(‘kill ‘,id,’;’) kill_process from processlist a where a.state=’Locked’;+————–+| kill_process |+————–+| kill 43; || kill 42; |+————–+2 rows in set (0.01 sec)

Note：

1）可以使用show processlist查看當前用戶連接

如果是root帳號，你能看到所有用戶的當前連接。如果是其它普通帳號，只能看到自己佔用的連接。show processlist;只列出前100條，如果想全列出請使用show full processlist;

2）在構造鎖的會話中，使用unlock tables;也可以解鎖

總結一下原因，大概如下：

因為mysql默認會根據客戶端的ip地址反向解析，用於用戶登錄授權之用。不過正常情況下，很少會有人這樣用。ip地址反向解析是很慢的，尤其是高負荷的mysql，每秒種幾百次甚至更高的請求，這個請求壓到本地的dns服務器上，dns服務器說不定會懷疑你在惡意請求，然後不理你了，然後這些登錄請求就掛在那裡，後面的連接還持續，然後越積越多，然後就達到mysql的最大連接數據限制了，然後新的連接就直接被拒，得到連接數過多的消息。

因為mysql配置文件使用的之前的配置文件，當時跟web同服務器，所以不存在這個問題。

這也正好解釋了為什麼phpMyAdmin里看mysqld狀態時，有很多失敗的連接，它們應該就是因反解析失敗而被拒的。

參考資料

MySQL解鎖.壹聚教程[引用時間2018-1-21]

mysql鎖表問題分析以及解決方法-簡書

項目坑有千千萬，我們靜下心來還是可以找到解決辦法的

最近接了一個由供應商留下來的項目，正是周末休息時間突然一個電話說功能用不了，翻看日誌發現是業務功能的表被鎖了，我就奇了怪了，天天沒事，突然周末來使兒。一番了解才發現那個鎖表情況是天天都有的，但是一直沒找到原因，所以DBA運維同事天天充當定時刪除機器，每天早上清除鎖表進程，丟失的數據也手動補錄，厲害了，這就是供應商做的項目嗎？爛到這個層度（吐槽一番）

回到正題，我們來聊一聊我的解決步驟

MySQL數據庫中查詢表是否被鎖以及解鎖

1.查看錶被鎖狀態

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

3.查詢進程

4.解鎖(刪除進程)

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

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

mysql 死鎖:如何解決mysql死鎖

可直接在mysql命令行執行：show engine innodb status\G;查看造成死鎖的sql語句，分析索引情況，然後優化sql然後show processlist;另外可以打開慢查詢日誌，linux下打開需在my.cnf的[mysqld]裡面加上以下內容：