問題發(fā)生場(chǎng)景

記錄一下最近項(xiàng)目中遇到的一個(gè)問題，前幾天在部署項(xiàng)目后，在線上運(yùn)行過程中，突然報(bào)了入下這樣的錯(cuò)誤，從報(bào)錯(cuò)信息中我們可以看到，是mysql在執(zhí)行update操作的時(shí)候報(bào)了一個(gè)死鎖的問題，今天解決了，特此記錄一下。

Mysql鎖類型分析

MySQL有三種鎖的級(jí)別：頁級(jí)、表級(jí)、行級(jí)，這個(gè)地方我遇到的問題是來自于行級(jí)鎖，所以重點(diǎn)說一下。

行級(jí)鎖在使用的時(shí)候并不是直接鎖掉這行記錄，而是鎖索引

• 如果一條sql用到了主鍵索引(mysql主鍵自帶索引)，mysql會(huì)鎖住主鍵索引;
• 如果一條sql操作了非主鍵索引，mysql會(huì)先鎖住非主鍵索引，再鎖定主鍵索引.

死鎖原理

mysql的兩種鎖排它鎖(X鎖)和共享鎖(S鎖)(mysql還有其他鎖，需要了解可以自己去查，這個(gè)地方列舉兩個(gè))：

• X鎖，是事務(wù)T對(duì)數(shù)據(jù)A加上X鎖時(shí)，只允許事務(wù)T讀取和修改數(shù)據(jù)A，別的事務(wù)就沒辦法讀取和修改，所以也叫排它鎖，是互斥的
• S鎖，是事務(wù)T對(duì)數(shù)據(jù)A加上S鎖時(shí)，其他事務(wù)只能再對(duì)數(shù)據(jù)A加S鎖，而不能加X鎖，直到T釋放A上的S鎖，別的事務(wù)也用加S鎖，所以也叫共享鎖，是不互斥的

一般造成死鎖的原因是因?yàn)閮蓚€(gè)事物添加鎖的時(shí)候沒能及時(shí)的解鎖釋放資源，等到第二個(gè)事務(wù)要添加鎖的時(shí)候發(fā)現(xiàn)已經(jīng)被鎖，從而造成環(huán)路等待，構(gòu)成死鎖條件。

問題排查過程

通過上面log日志中的報(bào)錯(cuò)信息，能很快確認(rèn)報(bào)錯(cuò)的準(zhǔn)確位置

這個(gè)地方是執(zhí)行了一個(gè)update的操作，我找到了了這個(gè)表，看了一下這個(gè)表的索引是有三個(gè)，兩個(gè)非主鍵索引，一個(gè)主鍵索引.

果然，在圖中這兩條索引在那個(gè)update語句中都有進(jìn)行的操作，具體如下：

然后我又找了一下有可能會(huì)跟這條語句發(fā)生沖突的地方，果然在這個(gè)報(bào)錯(cuò)信息的上面，執(zhí)行了一條這樣的sql，這兩條sql執(zhí)行的間隔僅僅不超過1毫秒

根據(jù)上面所說的，如果一條sql用到了主鍵索引(mysql主鍵自帶索引)，mysql會(huì)鎖住主鍵索引;

如果一條sql操作了非主鍵索引，mysql會(huì)先鎖住非主鍵索引，再鎖定主鍵索引.

因此sql(2)在使用的時(shí)候用到了schedu_id這個(gè)非主鍵索引，還需要鎖定主鍵索引，然而此時(shí)sql(1)開始執(zhí)行

然后鎖定了主鍵索引，但是在set操作中還用到了schedu_id這個(gè)非主鍵索引，但是這個(gè)索引在sql(1)執(zhí)行的時(shí)候還在處于被鎖的狀態(tài)，因此兩條sql就出現(xiàn)了對(duì)索引資源的競(jìng)爭(zhēng)，造成了死鎖.

問題原因

我的事務(wù)1中update wc_examine會(huì)多很多個(gè)update，這里有N行記錄被鎖定，事務(wù)的更新大量數(shù)據(jù)時(shí)間比較長，更新會(huì)加x鎖，而此時(shí)事務(wù)2是UPDATE wc_examine ，在update之前先執(zhí)行了select 操作，添加了S鎖，然后想要update的時(shí)候添加X鎖。

事務(wù)1的X鎖正準(zhǔn)備加上還是還沒加上，實(shí)際是存在X鎖，但是事務(wù)2加了s鎖，事務(wù)1會(huì)等待事務(wù)2的s鎖 事務(wù)2的完整事務(wù)加了s鎖立即就要加x鎖，但是事務(wù)1的x鎖沒有釋放。造成了環(huán)路等待。

解決方法

這個(gè)地方，代碼的問題需要根據(jù)情況自己去修改，可以試著把索引去掉(有風(fēng)險(xiǎn))，或者在進(jìn)行update的時(shí)候盡量避開非主鍵索引，我這里記錄一下被鎖后應(yīng)該怎么去解決的方法，首先先用sql查詢一下mysql的事務(wù)處理表

select * from information_schema.INNODB_TRX  

正常情況下的狀態(tài)都是RUNNING，但是在被鎖之后就會(huì)變成LOCK WAIT ，一旦出現(xiàn)這種情況，就得殺死這個(gè)進(jìn)程，如果進(jìn)程殺不死就只能重啟Mysql服務(wù)了

殺死進(jìn)程

kill 進(jìn)程ID

然后系統(tǒng)就能繼續(xù)運(yùn)行了

經(jīng)驗(yàn)教訓(xùn)

無論前臺(tái)后臺(tái)的程序，都不應(yīng)該存在僅根據(jù)非主鍵的幾個(gè)字段一查就要update/delete的場(chǎng)景。

即使有，也應(yīng)該改為先把要更新的記錄查出來然后逐條按主鍵id更新。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論