快捷導(dǎo)航

淺談Mysql insert on duplicate key 死鎖問題定位與解決

更新時間：2022年05月05日 15:28:10 作者：呼呼虎

本文介紹了在并發(fā)場景下的 insert on duplicate key update sql 出現(xiàn)的死鎖，經(jīng)過分析發(fā)現(xiàn)這種sql確實比較容易造成死鎖，這篇文章就從分析死鎖展開，到最終如何解決這樣的問題分享相應(yīng)的思路，感興趣的可以了解一下

前言

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

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

死鎖定位

我們目前生產(chǎn)環(huán)境使用Mysql版本為5.7，默認事務(wù)隔離級別為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)我們看到死鎖后，在對應(yīng)數(shù)據(jù)庫中進行分析，”show engine innodb status“，就發(fā)現(xiàn)這樣的報錯信息"lock_mode X locks gap before rec insert intention waiting"。意思就是在等待gap lock（間隙鎖）。

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

insert on duplicate key的鎖

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

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

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

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

兼容性	插入意向鎖	行鎖	gap lock
插入意向鎖	兼容	互斥	互斥
行鎖	兼容	互斥	兼容
gap lock	兼容	兼容	兼容

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

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

id	name	phone	timestamp
1	jack	15500000000	1970.1.1
2	tom	15600000000	1970.1.1
3	hurry	15700000000	1970.1.1

階段	tx1	tx2	tx3
1	insert into user_info (name, phone, update_time) values (test1,15700000001,1970.1.1) on duplicate key update update_time=now();
1	持有（15700000001，正無窮）的插入意向鎖以及gap lock
2		insert into user_info (name, phone, update_time) values (test2,15700000002,1970.1.1) on duplicate key update update_time=now();
2		申請（15700000002，正無窮）的插入意向鎖失敗，申請gap lock成功，等待中
3			insert into user_info (name, phone, update_time) values (test3,15700000004,1970.1.1) on duplicate key update update_time=now();
3			申請（15700000003，正無窮）的插入意向鎖失敗，申請gap lock成功，等待中
4	commit 提交事務(wù)，釋放鎖
5		申請插入意向鎖成功	申請插入意向鎖成功
6		死鎖	死鎖

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

問題解決

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

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

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

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

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

您可能感興趣的文章: