程序員的成長之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享?

閱讀本文大概需要 5 分鐘。

來自：blog.csdn.net/daidaineteasy/article/details/109266083

背景

生產(chǎn)環(huán)境出現(xiàn)死鎖流水，通過查看死鎖日志，看到造成死鎖的是兩條一樣的update語句（只有where條件中的值不同），如下：

UPDATE?test_table?SET?`status`?=?1?WHERE?`trans_id`?=?'xxx1'?AND?`status`?=?0;
UPDATE?test_table?SET?`status`?=?1?WHERE?`trans_id`?=?'xxx2'?AND?`status`?=?0;

一開始比較費(fèi)解，通過大量查詢跟學(xué)習(xí)后，分析出了死鎖形成的具體原理，特分享給大家，希望能幫助到遇到同樣問題的朋友。

因?yàn)镸ySQL知識點(diǎn)較多，這里對很多名詞不進(jìn)行過多介紹，有興趣的朋友，可以后續(xù)進(jìn)行專項(xiàng)深入學(xué)習(xí)。

死鎖日志

***?(1)?TRANSACTION:
TRANSACTION?791913819,?ACTIVE?0?sec?starting?index?read,?thread?declared?inside?InnoDB?4999
mysql?tables?in?use?3,?locked?3
LOCK?WAIT?4?lock?struct(s),?heap?size?1184,?3?row?lock(s)
MySQL?thread?id?462005230,?OS?thread?handle?0x7f55d5da3700,?query?id?2621313306?x.x.x.x?test_user?Searching?rows?for?update
UPDATE?test_table?SET?`status`?=?1?WHERE?`trans_id`?=?'xxx1'?AND?`status`?=?0;
***?(1)?WAITING?FOR?THIS?LOCK?TO?BE?GRANTED:
RECORD?LOCKS?space?id?110?page?no?39167?n?bits?1056?index?`idx_status`?of?table?`test`.`test_table`?trx?id?791913819?lock_mode?X?waiting
Record?lock,?heap?no?495?PHYSICAL?RECORD:?n_fields?2;?compact?format;?info?bits?0

***?(2)?TRANSACTION:
TRANSACTION?791913818,?ACTIVE?0?sec?starting?index?read,?thread?declared?inside?InnoDB?4999
mysql?tables?in?use?3,?locked?3
5?lock?struct(s),?heap?size?1184,?4?row?lock(s)
MySQL?thread?id?462005231,?OS?thread?handle?0x7f55cee63700,?query?id?2621313305?x.x.x.x?test_user?Searching?rows?for?update
UPDATE?test_table?SET?`status`?=?1?WHERE?`trans_id`?=?'xxx2'?AND?`status`?=?0;
***?(2)?HOLDS?THE?LOCK(S):
RECORD?LOCKS?space?id?110?page?no?39167?n?bits?1056?index?`idx_status`?of?table?`test`.`test_table`?trx?id?791913818?lock_mode?X
Record?lock,?heap?no?495?PHYSICAL?RECORD:?n_fields?2;?compact?format;?info?bits?0

***?(2)?WAITING?FOR?THIS?LOCK?TO?BE?GRANTED:
RECORD?LOCKS?space?id?110?page?no?41569?n?bits?88?index?`PRIMARY`?of?table?`test`.`test_table`?trx?id?791913818?lock_mode?X?locks?rec?but?not?gap?waiting
Record?lock,?heap?no?14?PHYSICAL?RECORD:?n_fields?30;?compact?format;?info?bits?0

***?WE?ROLL?BACK?TRANSACTION?(1)

簡要分析下上邊的死鎖日志：

1、第一塊內(nèi)容（第1行到第9行）中，第6行為事務(wù)(1)執(zhí)行的SQL語句，第7和第8行意思為事務(wù)(1)在等待 idx_status 索引上的X鎖；

2、第二塊內(nèi)容（第11行到第19行）中，第16行為事務(wù)(2)執(zhí)行的SQL語句，第17和第18行意思為事務(wù)(2)持有 idx_status 索引上的X鎖；

3、第三塊內(nèi)容（第21行到第23行）的意思為，事務(wù)(2)在等待 PRIMARY 索引上的X鎖。（but not gap指不是間隙鎖）

4、最后一句的意思即為，MySQL將事務(wù)(1)進(jìn)行了回滾操作。

表結(jié)構(gòu)

CREATE?TABLE?`test_table`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`trans_id`?varchar(21)?NOT?NULL,
??`status`?int(11)?NOT?NULL,
??PRIMARY?KEY?(`id`),
??UNIQUE?KEY?`uniq_trans_id`?(`trans_id`)?USING?BTREE,
??KEY?`idx_status`?(`status`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=1?DEFAULT?CHARSET=utf8

通過表結(jié)構(gòu)可以看出，trans_id 列上有一個(gè)唯一索引 uniq_trans_id ，status 列上有一個(gè)普通索引 idx_status ，id列為主鍵索引 PRIMARY 。

InnoDB引擎中有兩種索引：

• 聚簇索引：?將數(shù)據(jù)存儲與索引放到了一塊，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)。
• 輔助索引：?輔助索引葉子節(jié)點(diǎn)存儲的是主鍵值，也就是聚簇索引的鍵值。

主鍵索引 PRIMARY 就是聚簇索引，葉子節(jié)點(diǎn)中會保存行數(shù)據(jù)。uniq_trans_id 索引和 idx_status 索引為輔助索引，葉子節(jié)點(diǎn)中保存的是主鍵值，也就是id列值。

當(dāng)我們通過輔助索引查找行數(shù)據(jù)時(shí)，先通過輔助索引找到主鍵id，再通過主鍵索引進(jìn)行二次查找（也叫回表），最終找到行數(shù)據(jù)。

執(zhí)行計(jì)劃

通過看執(zhí)行計(jì)劃，可以發(fā)現(xiàn)，update語句用到了索引合并，也就是這條語句既用到了?uniq_trans_id?索引，又用到了?idx_status?索引，Using intersect(uniq_trans_id,idx_status)的意思是通過兩個(gè)索引獲取交集。

為什么會用 index_merge（索引合并）

MySQL5.0之前，一個(gè)表一次只能使用一個(gè)索引，無法同時(shí)使用多個(gè)索引分別進(jìn)行條件掃描。但是從5.1開始，引入了?index merge?優(yōu)化技術(shù)，對同一個(gè)表可以使用多個(gè)索引分別進(jìn)行條件掃描。

如執(zhí)行計(jì)劃中的語句：

UPDATE?test_table?SET?`status`?=?1?WHERE?`trans_id`?=?'38'?AND?`status`?=?0?;

MySQL會根據(jù)?trans_id = ‘38’這個(gè)條件，利用?uniq_trans_id?索引找到葉子節(jié)點(diǎn)中保存的id值；同時(shí)會根據(jù)?status = 0這個(gè)條件，利用?idx_status?索引找到葉子節(jié)點(diǎn)中保存的id值；然后將找到的兩組id值取交集，最終通過交集后的id回表，也就是通過 PRIMARY 索引找到葉子節(jié)點(diǎn)中保存的行數(shù)據(jù)。

這里可能很多人會有疑問了，uniq_trans_id?已經(jīng)是一個(gè)唯一索引了，通過這個(gè)索引最終只能找到最多一條數(shù)據(jù)，那MySQL優(yōu)化器為啥還要用兩個(gè)索引取交集，再回表進(jìn)行查詢呢，這樣不是多了一次?idx_status?索引查找的過程么。我們來分析一下這兩種情況執(zhí)行過程。

第一種 只用uniq_trans_id索引 ：

• 根據(jù)?trans_id = ‘38’查詢條件，利用uniq_trans_id?索引找到葉子節(jié)點(diǎn)中保存的id值；
• 通過找到的id值，利用PRIMARY索引找到葉子節(jié)點(diǎn)中保存的行數(shù)據(jù)；
• 再通過?status = 0?條件對找到的行數(shù)據(jù)進(jìn)行過濾。

第二種 用到索引合并?Using intersect(uniq_trans_id,idx_status)：

• 根據(jù)?trans_id = ‘38’?查詢條件，利用?uniq_trans_id?索引找到葉子節(jié)點(diǎn)中保存的id值；
• 根據(jù)?status = 0?查詢條件，利用?idx_status?索引找到葉子節(jié)點(diǎn)中保存的id值；
• 將1/2中找到的id值取交集，然后利用PRIMARY索引找到葉子節(jié)點(diǎn)中保存的行數(shù)據(jù)

上邊兩種情況，主要區(qū)別在于，第一種是先通過一個(gè)索引把數(shù)據(jù)找到后，再用其它查詢條件進(jìn)行過濾；第二種是先通過兩個(gè)索引查出的id值取交集，如果取交集后還存在id值，則再去回表將數(shù)據(jù)取出來。

當(dāng)優(yōu)化器認(rèn)為第二種情況執(zhí)行成本比第一種要小時(shí)，就會出現(xiàn)索引合并。（生產(chǎn)環(huán)境流水表中?status = 0?的數(shù)據(jù)非常少，這也是優(yōu)化器考慮用第二種情況的原因之一）。

為什么用了?index_merge?就死鎖了

上面簡要畫了一下兩個(gè)update事務(wù)加鎖的過程，從圖中可以看到，在idx_status?索引和 PRIMARY （聚簇索引） 上都存在重合交叉的部分，這樣就為死鎖造成了條件。

如，當(dāng)遇到以下時(shí)序時(shí)，就會出現(xiàn)死鎖：

事務(wù)1等待事務(wù)2釋放鎖，事務(wù)2等待事務(wù)1釋放鎖，這樣就造成了死鎖。

MySQL檢測到死鎖后，會自動回滾代價(jià)更低的那個(gè)事務(wù)，如上邊的時(shí)序圖中，事務(wù)1持有的鎖比事務(wù)2少，則MySQL就將事務(wù)1進(jìn)行了回滾。

解決方案

一、從代碼層面

• where 查詢條件中，只傳?trans_id?，將數(shù)據(jù)查詢出來后，在代碼層面判斷 status 狀態(tài)是否為0；
• 使用?force index(uniq_trans_id)?強(qiáng)制查詢語句使用?uniq_trans_id?索引；
• where 查詢條件后邊直接用 id 字段，通過主鍵去更新。

二、從MySQL層面

• 刪除?idx_status?索引或者建一個(gè)包含這倆列的聯(lián)合索引；
• 將MySQL優(yōu)化器的index merge優(yōu)化關(guān)閉。

推薦閱讀：

原作者出局，F(xiàn)aker.js已被社區(qū)控制

ElasticSearch進(jìn)階：一文全覽各種ES查詢在Java中的實(shí)現(xiàn)

互聯(lián)網(wǎng)初中高級大廠面試題(9個(gè)G)

內(nèi)容包含Java基礎(chǔ)、JavaWeb、MySQL性能優(yōu)化、JVM、鎖、百萬并發(fā)、消息隊(duì)列、高性能緩存、反射、Spring全家桶原理、微服務(wù)、Zookeeper、數(shù)據(jù)結(jié)構(gòu)、限流熔斷降級......等技術(shù)棧！

?戳閱讀原文領(lǐng)取！? ? ? ? ? ? ? ??? ??? ? ? ? ? ? ? ? ? ?朕已閱?