在程序員的職業(yè)生涯中，總會(huì)遇到數(shù)據(jù)庫(kù)表被鎖的情況，前些天就又撞見一次。由于業(yè)務(wù)突發(fā)需求，各個(gè)部門都在批量操作、導(dǎo)出數(shù)據(jù)，而數(shù)據(jù)庫(kù)又未做讀寫分離，結(jié)果就是：數(shù)據(jù)庫(kù)的某張表被鎖了！
網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序定制開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了肅寧免費(fèi)建站歡迎大家使用！
用戶反饋系統(tǒng)部分功能無(wú)法使用，緊急排查，定位是數(shù)據(jù)庫(kù)表被鎖，然后進(jìn)行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過(guò)程，建議點(diǎn)贊收藏，以備不時(shí)之需。
用戶反饋某功能頁(yè)面報(bào)502錯(cuò)誤，于是第一時(shí)間看服務(wù)是否正常，數(shù)據(jù)庫(kù)是否正常。在控制臺(tái)看到數(shù)據(jù)庫(kù)CPU飆升，堆積大量未提交事務(wù)，部分事務(wù)已經(jīng)阻塞了很長(zhǎng)時(shí)間，基本定位是數(shù)據(jù)庫(kù)層出現(xiàn)問(wèn)題了。
查看阻塞事務(wù)列表，發(fā)現(xiàn)其中有鎖表現(xiàn)象，本想利用控制臺(tái)直接結(jié)束掉阻塞的事務(wù)，但控制臺(tái)賬號(hào)權(quán)限有限，于是通過(guò)客戶端登錄對(duì)應(yīng)賬號(hào)將鎖表事務(wù)kill掉，才避免了情況惡化。
下面就聊聊，如果當(dāng)突然面對(duì)類似的情況，我們?cè)撊绾尉o急響應(yīng)？
想象一個(gè)場(chǎng)景，當(dāng)然也是軟件工程師職業(yè)生涯中會(huì)遇到的一種場(chǎng)景：原本運(yùn)行正常的程序，某一天突然數(shù)據(jù)庫(kù)的表被鎖了，業(yè)務(wù)無(wú)法正常運(yùn)轉(zhuǎn)，那么我們?cè)撊绾慰焖俣ㄎ皇悄膫€(gè)事務(wù)鎖了表，如何結(jié)束對(duì)應(yīng)的事物？
首先最簡(jiǎn)單粗暴的方式就是：重啟MySQL。對(duì)的，網(wǎng)管解決問(wèn)題的神器——“重啟”。至于后果如何，你能不能跑了，要你自己三思而后行了！
重啟是可以解決表被鎖的問(wèn)題的，但針對(duì)線上業(yè)務(wù)很顯然不太具有可行性。
下面來(lái)看看不用跑路的解決方案：
遇到數(shù)據(jù)庫(kù)阻塞問(wèn)題，首先要查詢一下表是否在使用。
如果查詢結(jié)果為空，那么說(shuō)明表沒(méi)在使用，說(shuō)明不是鎖表的問(wèn)題。
如果查詢結(jié)果不為空，比如出現(xiàn)如下結(jié)果：
則說(shuō)明表（test）正在被使用，此時(shí)需要進(jìn)一步排查。
查看數(shù)據(jù)庫(kù)當(dāng)前的進(jìn)程，看看是否有慢SQL或被阻塞的線程。
執(zhí)行命令：
該命令只顯示當(dāng)前用戶正在運(yùn)行的線程，當(dāng)然，如果是root用戶是能看到所有的。
在上述實(shí)踐中，阿里云控制臺(tái)之所以能夠查看到所有的線程，猜測(cè)應(yīng)該使用的就是root用戶，而筆者去kill的時(shí)候，無(wú)法kill掉，是因?yàn)榈卿浀挠脩舴莚oot的數(shù)據(jù)庫(kù)賬號(hào)，無(wú)法操作另外一個(gè)用戶的線程。
如果情況緊急，此步驟可以跳過(guò)，主要用來(lái)查看核對(duì)：
如果情況緊急，此步驟可以跳過(guò)，主要用來(lái)查看核對(duì)：
看事務(wù)表INNODB_TRX中是否有正在鎖定的事務(wù)線程，看看ID是否在show processlist的sleep線程中。如果在，說(shuō)明這個(gè)sleep的線程事務(wù)一直沒(méi)有commit或者rollback，而是卡住了，需要手動(dòng)kill掉。
搜索的結(jié)果中，如果在事務(wù)表發(fā)現(xiàn)了很多任務(wù)，最好都kill掉。
執(zhí)行kill命令：
對(duì)應(yīng)的線程都執(zhí)行完kill命令之后，后續(xù)事務(wù)便可正常處理。
針對(duì)緊急情況，通常也會(huì)直接操作第一、第二、第六步。
這里再補(bǔ)充一些MySQL鎖相關(guān)的知識(shí)點(diǎn)：數(shù)據(jù)庫(kù)鎖設(shè)計(jì)的初衷是處理并發(fā)問(wèn)題，作為多用戶共享的資源，當(dāng)出現(xiàn)并發(fā)訪問(wèn)的時(shí)候，數(shù)據(jù)庫(kù)需要合理地控制資源的訪問(wèn)規(guī)則，而鎖就是用來(lái)實(shí)現(xiàn)這些訪問(wèn)規(guī)則的重要數(shù)據(jù)結(jié)構(gòu)。
根據(jù)加鎖的范圍，MySQL里面的鎖大致可以分成全局鎖、表級(jí)鎖和行鎖三類。MySQL中表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（metadata lock，MDL)。
表鎖是在Server層實(shí)現(xiàn)的，ALTER TABLE之類的語(yǔ)句會(huì)使用表鎖，忽略存儲(chǔ)引擎的鎖機(jī)制。表鎖通過(guò)lock tables… read/write來(lái)實(shí)現(xiàn)，而對(duì)于InnoDB來(lái)說(shuō)，一般會(huì)采用行級(jí)鎖。畢竟鎖住整張表影響范圍太大了。
另外一個(gè)表級(jí)鎖是MDL（metadata lock），用于并發(fā)情況下維護(hù)數(shù)據(jù)的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問(wèn)一張表時(shí)會(huì)被自動(dòng)加上。
常見的一種鎖表場(chǎng)景就是有事務(wù)操作處于：Waiting for table metadata lock狀態(tài)。
MySQL在進(jìn)行alter table等DDL操作時(shí)，有時(shí)會(huì)出現(xiàn)Waiting for table metadata lock的等待場(chǎng)景。
一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態(tài)，后續(xù)對(duì)該表的任何操作（包括讀）都無(wú)法進(jìn)行，因?yàn)樗鼈円矔?huì)在Opening tables的階段進(jìn)入到Waiting for table metadata lock的鎖等待隊(duì)列。如果核心表出現(xiàn)了鎖等待隊(duì)列，就會(huì)造成災(zāi)難性的后果。
通過(guò)show processlist可以看到表上有正在進(jìn)行的操作（包括讀），此時(shí)alter table語(yǔ)句無(wú)法獲取到metadata 獨(dú)占鎖，會(huì)進(jìn)行等待。
通過(guò)show processlist看不到表上有任何操作，但實(shí)際上存在有未提交的事務(wù)，可以在information_schema.innodb_trx中查看到。在事務(wù)沒(méi)有完成之前，表上的鎖不會(huì)釋放，alter table同樣獲取不到metadata的獨(dú)占鎖。
處理方法：通過(guò) select * from information_schema.innodb_trxG, 找到未提交事物的sid，然后kill掉，讓其回滾。
通過(guò)show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒(méi)有任何進(jìn)行中的事務(wù)。很可能是因?yàn)樵谝粋€(gè)顯式的事務(wù)中，對(duì)表進(jìn)行了一個(gè)失敗的操作（比如查詢了一個(gè)不存在的字段），這時(shí)事務(wù)沒(méi)有開始，但是失敗語(yǔ)句獲取到的鎖依然有效，沒(méi)有釋放。從performance_schema.events_statements_current表中可以查到失敗的語(yǔ)句。
處理方法：通過(guò)performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。
總之，alter table的語(yǔ)句是很危險(xiǎn)的（核心是未提交事務(wù)或者長(zhǎng)事務(wù)導(dǎo)致的），在操作之前要確認(rèn)對(duì)要操作的表沒(méi)有任何進(jìn)行中的操作、沒(méi)有未提交事務(wù)、也沒(méi)有顯式事務(wù)中的報(bào)錯(cuò)語(yǔ)句。
如果有alter table的維護(hù)任務(wù)，在無(wú)人監(jiān)管的時(shí)候運(yùn)行，最好通過(guò)lock_wait_timeout設(shè)置好超時(shí)時(shí)間，避免長(zhǎng)時(shí)間的metedata鎖等待。
關(guān)于MySQL的鎖表其實(shí)還有很多其他場(chǎng)景，我們?cè)趯?shí)踐的過(guò)程中盡量避免鎖表情況的發(fā)生，當(dāng)然這需要一定經(jīng)驗(yàn)的支撐。但更重要的是，如果發(fā)現(xiàn)鎖表我們要能夠快速的響應(yīng)，快速的解決問(wèn)題，避免影響正常業(yè)務(wù)，避免情況進(jìn)一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無(wú)患，避免突然狀況下抓瞎。
多線程開啟事務(wù)處理。每個(gè)事務(wù)有多個(gè)update操作和一個(gè)insert操作（都在同一張表）。
默認(rèn)隔離級(jí)別：Repeatable Read
只有hotel_id=2和hotel_id=11111的數(shù)據(jù)
邏輯刪除原有數(shù)據(jù)
插入新的數(shù)據(jù)
根據(jù)現(xiàn)有數(shù)據(jù)情況，update的時(shí)候沒(méi)有數(shù)據(jù)被更新
報(bào)了非常多一樣的錯(cuò)
發(fā)現(xiàn)居然有死鎖。
根據(jù)常識(shí)考慮，我每個(gè)線程（事務(wù)）更新的數(shù)據(jù)都不沖突，為什么會(huì)產(chǎn)生死鎖？
帶著這個(gè)問(wèn)題，打印mysql最近一次的死鎖信息
show engine innodb status
顯示如下
發(fā)現(xiàn)事務(wù)1在等待一個(gè)鎖
事務(wù)2也在等待一個(gè)鎖
而且事物2持有了事物1需要的鎖
關(guān)于鎖的描述，出現(xiàn)了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂說(shuō)明了什么？說(shuō)明我關(guān)于mysql的鎖相關(guān)的知識(shí)儲(chǔ)備還不夠。那就開始調(diào)查mysql的鎖相關(guān)知識(shí)。
通過(guò)搜索引擎，
鎖的持有兼容程度如下表
那么再回到死鎖日志，可以知道 :
事務(wù)1正在獲取插入意向鎖
事務(wù)2正在獲取插入意向鎖，持有排他gap鎖
再看我們上面的鎖兼容表格，可以知道， gap lock和insert intention lock是不兼容的
那么就可以推斷出： 事務(wù)1持有g(shù)ap lock，等待事務(wù)2的insert intention lock釋放；事務(wù)2持有g(shù)ap lock，等待事務(wù)1的insert intention lock釋放，從而導(dǎo)致死鎖。
那么新的問(wèn)題就來(lái)了，事務(wù)1的intention lock 為什么會(huì)和事務(wù)2的gap lock 有交集，或者說(shuō)，事務(wù)1要插入的數(shù)據(jù)的位置為什么會(huì)被事務(wù)2給鎖??？
讓我回顧一下gap lock的定義：
間隙鎖，鎖定一個(gè)范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況
那為什么是gap lock，gap lock到底是基于什么邏輯鎖的記錄？發(fā)現(xiàn)自己相關(guān)的知識(shí)儲(chǔ)備還不夠。那就開始調(diào)查。
調(diào)查后發(fā)現(xiàn)，當(dāng)當(dāng)前索引是一個(gè) 普通索引 的時(shí)候，會(huì)加一個(gè)gap lock來(lái)防止幻讀， 此gap lock 會(huì)鎖住一個(gè)左開右閉的區(qū)間。 假設(shè)索引為xx_idx（xx_id），數(shù)據(jù)分布為1,4,6,8,12，當(dāng)更新xx_id=9的時(shí)候，這個(gè)時(shí)候gap lock的鎖定記錄區(qū)間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數(shù)據(jù)，當(dāng)有其他事務(wù)要插入xxid in (9,10,11,12)的數(shù)據(jù)時(shí)，就會(huì)處于等待獲取鎖的狀態(tài)。
ps：當(dāng)前索引不是普通索引，而且是唯一索引等其他情況，請(qǐng)參考下面資料
MySQL 加鎖處理分析
回到我自己的案例中，重新屢一下事務(wù)1的執(zhí)行過(guò)程：
因?yàn)槠胀ㄋ饕?/p>
KEY hotel_date_idx ( hotel_id , rate_date )
的關(guān)系 這段sql會(huì)獲取一個(gè)gap lock，范圍(2,11111]
這段sql會(huì)獲取一個(gè)insert intention lock （waiting）
再看事務(wù)2的執(zhí)行過(guò)程
因?yàn)槠胀ㄋ饕?/p>
KEY hotel_date_idx ( hotel_id , rate_date )
的關(guān)系 這段sql也會(huì)獲取一個(gè)gap lock，范圍也是(2,11111]（根據(jù)前面的知識(shí)，gap lock之間會(huì)互相兼容，可以一起持有鎖的）
這段sql也會(huì)獲取一個(gè)insert intention lock （waiting）
看到這里，基本也就破案了。因?yàn)槠胀ㄋ饕年P(guān)系，事務(wù)1和事務(wù)2的gap lock的覆蓋范圍太廣，導(dǎo)致其他事務(wù)無(wú)法插入數(shù)據(jù)。
重新梳理一下：
所以從結(jié)果來(lái)看，一堆事務(wù)被回滾，只有10007數(shù)據(jù)被更新成功
gap lock 導(dǎo)致了并發(fā)處理的死鎖
在mysql默認(rèn)的事務(wù)隔離級(jí)別（repeatable read）下，無(wú)法避免這種情況。只能把并發(fā)處理改成同步處理?；蛘邚臉I(yè)務(wù)層面做處理。
共享鎖、排他鎖、意向共享、意向排他
record lock、gap lock、next key lock、insert intention lock
show engine innodb status
問(wèn)題：Lock wait timeout exceeded; try restarting transaction
MySQL版本：5.6.44
官方文檔
意思是：InnoDB在鎖等待超時(shí)過(guò)期時(shí)報(bào)告此錯(cuò)誤。等待時(shí)間過(guò)長(zhǎng)的語(yǔ)句被回滾（而不是整個(gè)事務(wù)）。如果SQL語(yǔ)句需要等待其他事務(wù)完成的時(shí)間更長(zhǎng)，則可以增加 innodb_lock_wait_timeout 配置選項(xiàng)的值；如果太多長(zhǎng)時(shí)間運(yùn)行的事務(wù)導(dǎo)致鎖定問(wèn)題并降低繁忙系統(tǒng)上的并發(fā)性，則可以減少該選項(xiàng)的值。
鎖等待超時(shí)，可能是出現(xiàn)了死鎖，也可能有事務(wù)長(zhǎng)時(shí)間未提交
庫(kù)：information_schema
表：
查看各表信息
innodb_trx 表
innodb_locks 表
innodb_lock_waits 表
processlist 表
模擬出現(xiàn)死鎖
準(zhǔn)備一張只有主鍵的表：t_test (id)
Navicat 新建查詢1
Navicat 新建查詢2
檢查是否鎖表
查詢當(dāng)前正在執(zhí)行的事務(wù)
查詢當(dāng)前出現(xiàn)的鎖
查詢鎖等待對(duì)應(yīng)的關(guān)系
查詢等待鎖的事務(wù)所執(zhí)行的SQL
最后，事務(wù)2 等待鎖超時(shí)報(bào)錯(cuò)： Lock wait timeout exceeded; try restarting transaction;
通過(guò)事務(wù)線程ID查找進(jìn)程信息
win10 查看端口信息
重慶分公司
重慶分公司
