建站
咨詢
售后
建站咨詢
新聞中心
mysql存儲(chǔ)過(guò)程出現(xiàn)鎖表鎖行的情況怎么解決
行鎖的等待
在做網(wǎng)站、成都網(wǎng)站建設(shè)中從網(wǎng)站色彩、結(jié)構(gòu)布局、欄目設(shè)置、關(guān)鍵詞群組等細(xì)微處著手，突出企業(yè)的產(chǎn)品/服務(wù)/品牌，幫助企業(yè)鎖定精準(zhǔn)用戶，提高在線咨詢和轉(zhuǎn)化，使成都網(wǎng)站營(yíng)銷成為有效果、有回報(bào)的無(wú)錫營(yíng)銷推廣。創(chuàng)新互聯(lián)專業(yè)成都網(wǎng)站建設(shè)十年了，客戶滿意度97.8%，歡迎成都創(chuàng)新互聯(lián)客戶聯(lián)系。
在介紹如何解決行鎖等待問(wèn)題前，先簡(jiǎn)單介紹下這類問(wèn)題產(chǎn)生的原因。產(chǎn)生原因簡(jiǎn)述：當(dāng)多個(gè)事務(wù)同時(shí)去操作（增刪改）某一行數(shù)據(jù)的時(shí)候，MySQL 為了維護(hù) ACID 特性，就會(huì)用鎖的形式來(lái)防止多個(gè)事務(wù)同時(shí)操作某一行數(shù)據(jù)，避免數(shù)據(jù)不一致。只有分配到行鎖的事務(wù)才有權(quán)力操作該數(shù)據(jù)行，直到該事務(wù)結(jié)束，才釋放行鎖，而其他沒(méi)有分配到行鎖的事務(wù)就會(huì)產(chǎn)生行鎖等待。如果等待時(shí)間超過(guò)了配置值（也就是 innodb_lock_wait_timeout 參數(shù)的值，個(gè)人習(xí)慣配置成 5s，MySQL 官方默認(rèn)為 50s），則會(huì)拋出行鎖等待超時(shí)錯(cuò)誤。
如上圖所示，事務(wù) A 與事務(wù) B 同時(shí)會(huì)去 Insert 一條主鍵值為 1 的數(shù)據(jù)，由于事務(wù) A 首先獲取了主鍵值為 1 的行鎖，導(dǎo)致事務(wù) B 因無(wú)法獲取行鎖而產(chǎn)生等待，等到事務(wù) A 提交后，事務(wù) B 才獲取該行鎖，完成提交。這里強(qiáng)調(diào)的是行鎖的概念，雖然事務(wù) B 重復(fù)插入了主鍵，但是在獲取行鎖之前，事務(wù)一直是處于行鎖等待的狀態(tài)，只有獲取行鎖后，才會(huì)報(bào)主鍵沖突的錯(cuò)誤。當(dāng)然這種 Insert 行鎖沖突的問(wèn)題比較少見(jiàn)，只有在大量并發(fā)插入場(chǎng)景下才會(huì)出現(xiàn)，項(xiàng)目上真正常見(jiàn)的是 updatedelete 之間行鎖等待，這里只是用于示例，原理都是相同的。
三、產(chǎn)生的原因根據(jù)我之前接觸到的此類問(wèn)題，大致可以分為以下幾種原因
MySQL數(shù)據(jù)庫(kù)表被鎖、解鎖，刪除事務(wù)
在程序員的職業(yè)生涯中，總會(huì)遇到數(shù)據(jù)庫(kù)表被鎖的情況，前些天就又撞見(jiàn)一次。由于業(yè)務(wù)突發(fā)需求，各個(gè)部門都在批量操作、導(dǎo)出數(shù)據(jù)，而數(shù)據(jù)庫(kù)又未做讀寫(xiě)分離，結(jié)果就是：數(shù)據(jù)庫(kù)的某張表被鎖了！
用戶反饋系統(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ù)的一致性，保證讀寫(xiě)的正確性，不需要顯式的使用，在訪問(wèn)一張表時(shí)會(huì)被自動(dòng)加上。
常見(jiàn)的一種鎖表場(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)有開(kā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ú)患，避免突然狀況下抓瞎。
MySQL從入門到精通(九) MySQL鎖，各種鎖
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制，在數(shù)據(jù)庫(kù)中，除傳統(tǒng)的計(jì)算資源（CPU、RAM、I/O）爭(zhēng)用外，數(shù)據(jù)也是一種供許多用戶共享的資源，如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性，有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素，從這個(gè)角度來(lái)說(shuō)，鎖對(duì)數(shù)據(jù)庫(kù)而言是尤其重要，也更加復(fù)雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數(shù)據(jù)庫(kù)中的所有表。2、表級(jí)鎖，每次操作鎖住整張表。3、行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)。
全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖，加鎖后整個(gè)實(shí)例就處于只讀狀態(tài)，后續(xù)的DML的寫(xiě)語(yǔ)句，DDL語(yǔ)句，已經(jīng)更新操作的事務(wù)提交語(yǔ)句都將阻塞。其典型的使用場(chǎng)景就是做全庫(kù)的邏輯備份，對(duì)所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。但是對(duì)數(shù)據(jù)庫(kù)加全局鎖是有弊端的，如在主庫(kù)上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)會(huì)受影響，第二如果是在從庫(kù)上備份，那么在備份期間從庫(kù)不能執(zhí)行主庫(kù)同步過(guò)來(lái)的二進(jìn)制日志，會(huì)導(dǎo)致主從延遲。
解決辦法是在innodb引擎中，備份時(shí)加上--single-transaction參數(shù)來(lái)完成不加鎖的一致性數(shù)據(jù)備份。
添加全局鎖: flush tables with read lock; 解鎖 unlock tables。
表級(jí)鎖,每次操作會(huì)鎖住整張表.鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)讀最低,應(yīng)用在myisam、innodb、BOB等存儲(chǔ)引擎中。表級(jí)鎖分為: 表鎖、元數(shù)據(jù)鎖(meta data lock, MDL)和意向鎖。
表鎖又分為: 表共享讀鎖 read lock、表獨(dú)占寫(xiě)鎖write lock
語(yǔ)法: 1、加鎖 lock tables 表名 ... read/write
2、釋放鎖 unlock tables 或者關(guān)閉客戶端連接
注意: 讀鎖不會(huì)阻塞其它客戶端的讀，但是會(huì)阻塞其它客戶端的寫(xiě)，寫(xiě)鎖既會(huì)阻塞其它客戶端的讀，又會(huì)阻塞其它客戶端的寫(xiě)。大家可以拿一張表來(lái)測(cè)試看看。
元數(shù)據(jù)鎖,在加鎖過(guò)程中是系統(tǒng)自動(dòng)控制的，無(wú)需顯示使用，在訪問(wèn)一張表的時(shí)候會(huì)自動(dòng)加上，MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)的時(shí)候，不可以對(duì)元數(shù)據(jù)進(jìn)行寫(xiě)入操作。為了避免DML和DDL沖突，保證讀寫(xiě)的正確性。
在MySQL5.5中引入了MDL，當(dāng)對(duì)一張表進(jìn)行增刪改查的時(shí)候，加MDL讀鎖(共享)；當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行變更操作時(shí)，加MDL寫(xiě)鎖(排他).
查看元數(shù)據(jù)鎖:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;
意向鎖，為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來(lái)減少表鎖的檢查。意向鎖分為,意向共享鎖is由語(yǔ)句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;
行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應(yīng)用在innodb存儲(chǔ)引擎中。
innodb的數(shù)據(jù)是基于索引組織的，行鎖是通過(guò)對(duì)索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，而不是對(duì)記錄加的鎖，對(duì)于行級(jí)鎖，主要分為以下三類:
1、行鎖或者叫record lock記錄鎖，鎖定單個(gè)行記錄的鎖，防止其他事物對(duì)次行進(jìn)行update和delete操作，在RC,RR隔離級(jí)別下都支持。
2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個(gè)間隙進(jìn)行insert操作，產(chǎn)生幻讀，在RR隔離級(jí)別下都支持。
3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時(shí)鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap,在RR隔離級(jí)別下支持。
innodb實(shí)現(xiàn)了以下兩種類型的行鎖
1、共享鎖 S: 允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
2、排他鎖 X: 允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。
insert 語(yǔ)句 排他鎖 自動(dòng)添加的
update語(yǔ)句 排他鎖 自動(dòng)添加
delete 語(yǔ)句 排他鎖 自動(dòng)添加
select 正常查詢語(yǔ)句 不加鎖 。。。
select 。。。lock in share mode 共享鎖 需要手動(dòng)在select 之后加lock in share mode
select 。。。for update 排他鎖 需要手動(dòng)在select之后添加for update
默認(rèn)情況下，innodb在repeatable read事務(wù)隔離級(jí)別運(yùn)行，innodb使用next-key鎖進(jìn)行搜索和索引掃描，以防止幻讀。
間隙鎖唯一目的是防止其它事務(wù)插入間隙，間隙鎖可以共存，一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用的間隙鎖。
分享文章：mysql怎么釋放行鎖 mysql的行鎖
分享網(wǎng)址：http://ef60e0e.cn/article/doppgci.html
