前言

最近在監(jiān)測(cè)線上日志時(shí)發(fā)現(xiàn)我們一個(gè)Mysql業(yè)務(wù)db時(shí)常出現(xiàn) dead lock，頻次不高但卻一直出現(xiàn)，定位后發(fā)現(xiàn)是在并發(fā)場(chǎng)景下的 insert on duplicate key update sql 出現(xiàn)的死鎖。經(jīng)過(guò)分析發(fā)現(xiàn)這種sql確實(shí)比較容易造成死鎖，不太適用于我們目前的業(yè)務(wù)場(chǎng)景，于是更換后解決問(wèn)題。

這篇文章就從分析死鎖展開(kāi)，到最終如何解決這樣的問(wèn)題 分享相應(yīng)的思路。

死鎖定位

我們目前生產(chǎn)環(huán)境使用Mysql版本為5.7，默認(rèn)事務(wù)隔離級(jí)別為RR，以下為我們的大致table結(jié)構(gòu)（字段已經(jīng)完全脫敏，使用非業(yè)務(wù)字段）。

CREATE TABLE IF NOT EXISTS `user_info` (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        name VARCHAR(20) NOT NULL,
        phone BIGINT(20) UNSIGNED NOT NULL,
        update_time timestamp  NOT NULL,
        UNIQUE KEY phone (phone)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

造成死鎖的sql如下：

insert into user_info (name, phone, update_time) values (X,Y,Z) on duplicate key update update_time=Z;

當(dāng)我們看到死鎖后，在對(duì)應(yīng)數(shù)據(jù)庫(kù)中進(jìn)行分析，”show engine innodb status“，就發(fā)現(xiàn)這樣的報(bào)錯(cuò)信息"lock_mode X locks gap before rec insert intention waiting"。意思就是在等待gap lock（間隙鎖）。

于是我們開(kāi)始分析on duplicate key這個(gè)關(guān)鍵字的sql所可能引入的鎖，以及對(duì)應(yīng)我們業(yè)務(wù)場(chǎng)景中可能觸發(fā)死鎖的問(wèn)題。

insert on duplicate key的鎖

首先insert on duplicate key 這條sql的語(yǔ)義是：如果insert中的對(duì)應(yīng)鍵值在數(shù)據(jù)庫(kù)中沒(méi)有找到對(duì)應(yīng)的唯一索引記錄，即進(jìn)行插入；如果對(duì)表中唯一索引記錄沖突，便進(jìn)行更新，能夠很輕松的達(dá)到一種效果： 有則直接更新，無(wú)則插入。而我們業(yè)務(wù)中的sql是自增主鍵id，這樣一來(lái)沖突的只有可能是 phone這個(gè)唯一索引了。

首先，在RR的事務(wù)隔離級(jí)別下，insert on duplicate key這個(gè)sql與普通insert只插入意向鎖和記錄鎖不同，insert on duplicate key sql如果沒(méi)有找到對(duì)應(yīng)的會(huì)在唯一鍵上插入gap lock和插入意向鎖（如果有對(duì)應(yīng)記錄則會(huì)獲取next key lock，next key lock 比gap lock多了一個(gè)邊緣的記錄鎖）。Mysql sql lock。

gap lock即間隙鎖，假設(shè)目前表中唯一鍵的數(shù)據(jù)有以下幾個(gè)，1，5，10。那么insert的key如果是4,在1-5之間，則獲取的gap lock的區(qū)間就是（1，5）；如果插入的數(shù)據(jù)是15，則在10-正無(wú)窮之間，因此gap lock的區(qū)間就是（10，正無(wú)窮），這個(gè)gap lock。

插入意向鎖也是類(lèi)似于gap lock的一種，生效的范圍也一致，只是對(duì)應(yīng)鎖上相同范圍或者有交集的。橫軸為已持有，縱軸為后續(xù)申請(qǐng)，是否互斥或兼容。

因此可以看到，在持有g(shù)ap lock時(shí)，在插入的時(shí)候如果申請(qǐng)插入意向鎖，便會(huì)需要等待，而insert on duplicate key的sql在執(zhí)行時(shí)一般就是gap lock和插入意向鎖。那么造成死鎖的問(wèn)題就定位到了，肯定是同一時(shí)間多個(gè)insert事務(wù)到來(lái)，并且所插入的記錄對(duì)應(yīng)的唯一鍵范圍基本一致，所擁有的gap lock和插入意向鎖的范圍有交集，便可以出現(xiàn)共同持有鎖反而造成死鎖的問(wèn)題。

那我們大致還原一下對(duì)應(yīng)場(chǎng)景，以下是目前數(shù)據(jù)庫(kù)中的數(shù)據(jù)

因此形成死鎖，其中一個(gè)事務(wù)回滾。

問(wèn)題解決

可以看到，在我們的業(yè)務(wù)場(chǎng)景中，并沒(méi)有特別復(fù)雜的sql，但是仍然會(huì)導(dǎo)致死鎖，主要是插入數(shù)據(jù)的有序性以及高并發(fā)性，因此我們的解決思路也相對(duì)簡(jiǎn)單。

針對(duì)我們業(yè)務(wù)的幾個(gè)思路：

• 取消使用insert on duplicate key sql，換用普通insert sql，然后捕獲對(duì)應(yīng)dupicate 異常，進(jìn)行異常重試和插入；
• 業(yè)務(wù)上進(jìn)行接口限流，并且入?yún)?shù)據(jù)的insert on duplicate key 數(shù)據(jù)list大小在事務(wù)中進(jìn)行控制，分批執(zhí)行，可以減少死鎖的情況。

insert on duplicate key 雖然很方便一條sql完成幾條sql的事情，保證原子性，但是還是不適用于較高并發(fā)的場(chǎng)景，使用時(shí)需要多權(quán)衡。

到此這篇關(guān)于淺談Mysql insert on duplicate key 死鎖問(wèn)題定位與解決的文章就介紹到這了,更多相關(guān)Mysql insert on duplicate key 死鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論