問題出現(xiàn)
公司的數(shù)據(jù)庫(kù)經(jīng)常出現(xiàn)死鎖， 造成服務(wù)不穩(wěn)定，搭載數(shù)據(jù)庫(kù)的機(jī)器還經(jīng)常OOM， 到時(shí)候只能重啟數(shù)據(jù)庫(kù)
查詢show engine innodb status\G查看死鎖信息
可以看到是兩條update語(yǔ)句產(chǎn)生了死鎖， 其蹊蹺之處在于其中第一條只操作了一張表， 另外一條操作了兩張表， 乍一看看不出原因， 可以知道的結(jié)論是第一條語(yǔ)句鎖住了8行， 第二條語(yǔ)句鎖住了851243， 但是他們都在等待對(duì)方鎖住的記錄。 查看了一下調(diào)用， 其中第二條語(yǔ)句是一個(gè)定時(shí)任務(wù)， 每隔五分鐘會(huì)執(zhí)行一次， 第一條語(yǔ)句是隨用戶點(diǎn)擊而執(zhí)行的， 現(xiàn)在的死鎖頻率為每天一次， 看上去死鎖發(fā)生率已經(jīng)很高了。那我們首先可以來分析一下SQL語(yǔ)句。
SQL語(yǔ)句分析
首先GlobalDiscountCode這張表數(shù)據(jù)庫(kù)的隔離級(jí)別：REPEATABLE-READ， 索引有
看一下這條語(yǔ)句的查詢結(jié)果
可以看到有13條， 這條UPDATE語(yǔ)句在主鍵索引上本應(yīng)該有13條X鎖的， 所以可以肯定的是至少有一條主鍵索引被第二個(gè)SQL語(yǔ)句給鎖住了
explain一下這條語(yǔ)句看一看
可以發(fā)現(xiàn)它走的索引是userId_code這條復(fù)合索引。 那么加鎖順序也是按照這條復(fù)合索引來的。所以我們可以得知在userId_code這個(gè)索引上加的是gap鎖+X鎖， 在主鍵索引上加的是X鎖， 通過開始的死鎖信息可以看出， 發(fā)生死鎖的地方是X鎖， 所以是在主鍵索引處。
那我們分析一下第二個(gè)SQL語(yǔ)句。 這條語(yǔ)句看起來比較復(fù)雜， 會(huì)inner join兩個(gè)表的update
我們通過改寫成select語(yǔ)句來explain一下， 看出對(duì)GlobalDiscountCode這張表實(shí)際上是走的遍歷了全表， 所以理論上來說它要將這張表逐行鎖住， 同時(shí)也要鎖住GlobaOrder這張表中選中的記錄， 加鎖順序按照GlobalDiscountCode的主鍵順序來的
死鎖分析
死鎖的成因都是因?yàn)榧渔i順序的問題， 比較常見的是顯示死鎖， 也就是兩個(gè)事物對(duì)兩把鎖上鎖順序不同產(chǎn)生的， 不常見的是兩條語(yǔ)句因?yàn)椴捎玫乃饕煌?導(dǎo)致的加鎖順序不同， 按照何登成的博客上畫的可以看出原因
顯然我們這里的死鎖成因肯定也是因?yàn)橛盟饕龑?dǎo)致的加鎖順序不一致， 那么看一看兩條語(yǔ)句的加鎖順序吧， 我們參照之前第一條語(yǔ)句的select結(jié)果可以知道， 它走的是userId_code這條復(fù)合索引， 然后用這條復(fù)合索引的時(shí)候id是亂序的， 所以對(duì)主鍵索引的加鎖也是不按照id順序來的， 但是第二條語(yǔ)句對(duì)主鍵索引的加鎖順序是按照id順序的， 所以這兩天語(yǔ)句就會(huì)產(chǎn)生死鎖。
死鎖解決
mysql版本為5.5.11時(shí)候， 默認(rèn)會(huì)使用復(fù)合索引， 而不走單獨(dú)的索引， 如果是走userId這個(gè)索引的話， 對(duì)主鍵索引的加鎖就也順序了， 那么我們需要顯示使用userId這個(gè)索引就可以解決問題了， 所以更改第一條SQL語(yǔ)句為
update GlobalDiscountCode FORCE INDEX(userId) set readed=1 where userId=? 這樣就不會(huì)產(chǎn)生死鎖了。
找到原因是因?yàn)閿?shù)據(jù)庫(kù)選擇了復(fù)合索引才導(dǎo)致的死鎖， 看來復(fù)合索引不能亂加。 會(huì)導(dǎo)致加鎖順序亂掉的。
后記
最開始運(yùn)維和DBA都在糾結(jié)于修改第二條SQL語(yǔ)句， 認(rèn)為那條語(yǔ)句寫的不夠好， 執(zhí)行了太長(zhǎng)時(shí)間， 導(dǎo)致的死鎖， 說那條語(yǔ)句里用了效率不高的in， 所以應(yīng)該把in去掉， 換成六條單獨(dú)的語(yǔ)句， 但是實(shí)際上試了下并沒有提高效率， 執(zhí)行差不多還是一秒鐘。 同時(shí)第二條SQL語(yǔ)句執(zhí)行頻率很低， 五分鐘執(zhí)行一次， 但是已經(jīng)造成每天都會(huì)有死鎖了， 說明死鎖出現(xiàn)的幾率已經(jīng)很高了， 即便通過提高效率縮短執(zhí)行時(shí)間， 提高了一兩倍也沒什么意義。
DBA還想辦法縮小鎖范圍。 但是這個(gè)語(yǔ)句實(shí)際上是沒法拆分的， 如果拆分成兩條去寫， 也就是先select出來再update， 但是這樣就喪失了隔離性， 所以也是不可取的。當(dāng)然如果按照業(yè)務(wù)來講還是有縮小的可能性， 比如說我們可以按照日期排序， 只update最近一周的訂單， 這樣就會(huì)把鎖范圍縮小很多， 出現(xiàn)死鎖的可能性也基本沒有了， 因?yàn)槲覀儼l(fā)優(yōu)惠券的頻次一般會(huì)大于一周， 這也是解決死鎖的一種思路。
考慮到肯定是這兩條語(yǔ)句之間的交叉死鎖， 而且第二條語(yǔ)句其實(shí)相當(dāng)于表鎖， 但是是逐行執(zhí)行的這一特點(diǎn)（InnoDB不會(huì)鎖升級(jí)）于是我打算暴力一些， 直接把GlobalDiscountCode這個(gè)表表鎖，寫法如下
這么執(zhí)行， 效率也是提升了的， 而且理論上也不會(huì)產(chǎn)生死鎖了， 不過顯示鎖表?yè)?dān)心會(huì)出問題， 沒敢上線。
