mysql存儲過程出現(xiàn)鎖表鎖行的情況怎么解決

行鎖的等待

專注于為中小企業(yè)提供成都做網站、網站設計服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)雙河免費做網站提供優(yōu)質的服務。我們立足成都，凝聚了一批互聯(lián)網行業(yè)人才，有力地推動了千余家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網站建設實現(xiàn)規(guī)模擴充和轉變。

在介紹如何解決行鎖等待問題前，先簡單介紹下這類問題產生的原因。產生原因簡述：當多個事務同時去操作（增刪改）某一行數據的時候，MySQL 為了維護 ACID 特性，就會用鎖的形式來防止多個事務同時操作某一行數據，避免數據不一致。只有分配到行鎖的事務才有權力操作該數據行，直到該事務結束，才釋放行鎖，而其他沒有分配到行鎖的事務就會產生行鎖等待。如果等待時間超過了配置值（也就是 innodb_lock_wait_timeout 參數的值，個人習慣配置成 5s，MySQL 官方默認為 50s），則會拋出行鎖等待超時錯誤。

如上圖所示，事務 A 與事務 B 同時會去 Insert 一條主鍵值為 1 的數據，由于事務 A 首先獲取了主鍵值為 1 的行鎖，導致事務 B 因無法獲取行鎖而產生等待，等到事務 A 提交后，事務 B 才獲取該行鎖，完成提交。這里強調的是行鎖的概念，雖然事務 B 重復插入了主鍵，但是在獲取行鎖之前，事務一直是處于行鎖等待的狀態(tài)，只有獲取行鎖后，才會報主鍵沖突的錯誤。當然這種 Insert 行鎖沖突的問題比較少見，只有在大量并發(fā)插入場景下才會出現(xiàn)，項目上真正常見的是 updatedelete 之間行鎖等待，這里只是用于示例，原理都是相同的。

三、產生的原因根據我之前接觸到的此類問題，大致可以分為以下幾種原因

mysql數據庫鎖表如何解鎖

當前運行的所有事務

select * from information_schema.innodb_trx

當前出現(xiàn)的鎖

select * from information_schema.innodb_locks

鎖等待的對應關系

select * from information_schema.innodb_lock_waits

通過找到線程id號，進行kill

通過 select * from information_schema.innodb_trx 查詢 trx_mysql_thread_id然后執(zhí)行 kill 線程ID

MySQL數據庫表被鎖、解鎖，刪除事務

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

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

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

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

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

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

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

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

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

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

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

如果查詢結果不為空，比如出現(xiàn)如下結果：

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

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

執(zhí)行命令：

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

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

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

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

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

搜索的結果中，如果在事務表發(fā)現(xiàn)了很多任務，最好都kill掉。

執(zhí)行kill命令：

對應的線程都執(zhí)行完kill命令之后，后續(xù)事務便可正常處理。

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

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

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

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

另外一個表級鎖是MDL（metadata lock），用于并發(fā)情況下維護數據的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。

常見的一種鎖表場景就是有事務操作處于：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)了鎖等待隊列，就會造成災難性的后果。

通過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的維護任務，在無人監(jiān)管的時候運行，最好通過lock_wait_timeout設置好超時時間，避免長時間的metedata鎖等待。

關于MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發(fā)生，當然這需要一定經驗的支撐。但更重要的是，如果發(fā)現(xiàn)鎖表我們要能夠快速的響應，快速的解決問題，避免影響正常業(yè)務，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。