解決mysql數據庫鎖問題,mysql 鎖庫

本文目錄一覽：

• 1、MySQL數據庫表鎖定的幾種方法實現
• 2、MySQL數據庫表被鎖、解鎖，刪除事務
• 3、解決一次mysql死鎖問題

MySQL數據庫表鎖定的幾種方法實現

如果兩個程序都向表中寫數據顯然會造成很大的麻煩，甚至會有意外情況發生。如果表正由一個程序寫入，同時進行讀取的另一個程序也會產生混亂的結果。

鎖定表的方法

防止客戶機的請求互相干擾或者服務器與維護程序相互干擾的方法主要有多種。如果你關閉數據庫，就可以保證服務器

和myisamchk和isamchk之間沒有交互作用。但是停止服務器的運行並不是一個好注意，因為這樣做會使得沒有故障的數據庫和表也不可用。本節主

要討論的過程，是避免服務器和myisamchk或isamchk之間的交互作用。實現這種功能的方法是對錶進行鎖定。

服務器由兩種表的鎖定方法：

1.內部鎖定

內部鎖定可以避免客戶機的請求相互干擾——例如，避免客戶機的SELECT查詢被另一個客戶機的UPDATE查詢所干擾。也可以利用內部鎖定機制防止服務器在利用myisamchk或isamchk檢查或修復表時對錶的訪問。

語法：鎖定表：LOCK TABLES

tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解鎖表：UNLOCKTABLESLOCKTABLES為當前線程鎖定表。UNLOCK TABLES釋放被當前線程持有的任何鎖。當線程發出另外一個LOCK

TABLES時，或當服務器的連接被關閉時，當前線程鎖定的所有表自動被解鎖。

如果一個線程獲得在一個表上的一個READ鎖，該線程(和所有其他線程)只能從表中讀。如果一個線程獲得一個表上的一個WRITE鎖，那麼只有持鎖的線程READ或WRITE表，其他線程被阻止。

每個線程等待(沒有超時)直到它獲得它請求的所有鎖。

WRITE鎖通常比READ鎖有更高的優先級，以確保更改儘快被處理。這意味着，如果一個線程獲得READ鎖，並且然後另外一個線程請求一個WRITE鎖,

隨後的READ鎖請求將等待直到WRITE線程得到了鎖並且釋放了它。

顯然對於檢查，你只需要獲得讀鎖。再者鍾情跨下，只能讀取表，但不能修改它，因此他也允許其它客戶機讀取表。對於修復，你必須獲得些所以防止任何客戶機在你對錶進行操作時修改它。

2.外部鎖定

服務器還可以使用外部鎖定(文件級鎖)來防止其它程序在服務器使用表時修改文件。通常，在表的檢查操作中服務器

將外部鎖定與myisamchk或isamchk作合使用。但是，外部鎖定在某些系統中是禁用的，因為他不能可靠的進行工作。對運行myisamchk或

isamchk所選擇的過程取決於服務器是否能使用外部鎖定。如果不使用，則必修使用內部鎖定協議。

如果服務器用–skip-locking選項運行，則外部鎖定禁用。該選項在某些系統中是缺省的，如Linux。可以通過運行mysqladmin

variables命令確定服務器是否能夠使用外部鎖定。檢查skip_locking變量的值並按以下方法進行：

◆

如果skip_locking為off，則外部鎖定有效您可以繼續並運行人和一個實用程序來檢查表。服務器和實用程序將合作對錶進行訪問。但是，運行任何

一個實用程序之前，應該使用mysqladmin flush-tables。為了修復表，應該使用表的修復鎖定協議。

◆

如果skip_locaking為on，則禁用外部鎖定，所以在myisamchk或isamchk檢查修復表示服務器並不知道，最好關閉服務器。如果堅

持是服務器保持開啟狀態，月確保在您使用此表示沒有客戶機來訪問它。

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死鎖問題

多線程開啟事務處理。每個事務有多個update操作和一個insert操作（都在同一張表）。

默認隔離級別：Repeatable Read

只有hotel_id=2和hotel_id=11111的數據

邏輯刪除原有數據

插入新的數據

根據現有數據情況，update的時候沒有數據被更新

報了非常多一樣的錯

發現居然有死鎖。

根據常識考慮，我每個線程（事務）更新的數據都不衝突，為什麼會產生死鎖？

帶着這個問題，打印mysql最近一次的死鎖信息

show engine innodb status

顯示如下

發現事務1在等待一個鎖

事務2也在等待一個鎖

而且事物2持有了事物1需要的鎖

關於鎖的描述，出現了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂說明了什麼？說明我關於mysql的鎖相關的知識儲備還不夠。那就開始調查mysql的鎖相關知識。

通過搜索引擎，

鎖的持有兼容程度如下表

那麼再回到死鎖日誌，可以知道 :

事務1正在獲取插入意向鎖

事務2正在獲取插入意向鎖，持有排他gap鎖

再看我們上面的鎖兼容表格，可以知道， gap lock和insert intention lock是不兼容的

那麼就可以推斷出： 事務1持有gap lock，等待事務2的insert intention lock釋放；事務2持有gap lock，等待事務1的insert intention lock釋放，從而導致死鎖。

那麼新的問題就來了，事務1的intention lock 為什麼會和事務2的gap lock 有交集，或者說，事務1要插入的數據的位置為什麼會被事務2給鎖住？

讓我回顧一下gap lock的定義：

間隙鎖，鎖定一個範圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況

那為什麼是gap lock，gap lock到底是基於什麼邏輯鎖的記錄？發現自己相關的知識儲備還不夠。那就開始調查。

調查後發現，噹噹前索引是一個 普通索引 的時候，會加一個gap lock來防止幻讀， 此gap lock 會鎖住一個左開右閉的區間。 假設索引為xx_idx（xx_id），數據分布為1,4,6,8,12，當更新xx_id=9的時候，這個時候gap lock的鎖定記錄區間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數據，當有其他事務要插入xxid in (9,10,11,12)的數據時，就會處於等待獲取鎖的狀態。

ps：當前索引不是普通索引，而且是唯一索引等其他情況，請參考下面資料

MySQL 加鎖處理分析

回到我自己的案例中，重新屢一下事務1的執行過程：

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關係 這段sql會獲取一個gap lock，範圍(2,11111]

這段sql會獲取一個insert intention lock （waiting）

再看事務2的執行過程

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關係 這段sql也會獲取一個gap lock，範圍也是(2,11111]（根據前面的知識，gap lock之間會互相兼容，可以一起持有鎖的）

這段sql也會獲取一個insert intention lock （waiting）

看到這裡，基本也就破案了。因為普通索引的關係，事務1和事務2的gap lock的覆蓋範圍太廣，導致其他事務無法插入數據。

重新梳理一下：

所以從結果來看，一堆事務被回滾，只有10007數據被更新成功

gap lock 導致了並發處理的死鎖

在mysql默認的事務隔離級別（repeatable read）下，無法避免這種情況。只能把並發處理改成同步處理。或者從業務層面做處理。

共享鎖、排他鎖、意向共享、意向排他

record lock、gap lock、next key lock、insert intention lock

show engine innodb status