淺談Mysql insert on duplicate key 死鎖問(wèn)題定位與解決
前言
最近在監(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)，是否互斥或兼容。
| 兼容性 | 插入意向鎖 | 行鎖 | gap lock |
|---|---|---|---|
| 插入意向鎖 | 兼容 | 互斥 | 互斥 |
| 行鎖 | 兼容 | 互斥 | 兼容 |
| gap lock | 兼容 | 兼容 | 兼容 |
因此可以看到，在持有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ù)
| 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，正無(wú)窮）的插入意向鎖以及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 | 申請(qǐng)（15700000002，正無(wú)窮）的插入意向鎖失敗，申請(qǐng)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 | 申請(qǐng)（15700000003，正無(wú)窮）的插入意向鎖失敗，申請(qǐng)gap lock成功，等待中 | ||
| 4 | commit 提交事務(wù)，釋放鎖 | ||
| 5 | 申請(qǐng)插入意向鎖成功 | 申請(qǐng)插入意向鎖成功 | |
| 6 | 死鎖 | 死鎖 |
因此形成死鎖，其中一個(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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
淺談mysql雙層not exists查詢(xún)執(zhí)行流程
本文主要介紹了淺談mysql雙層not?exists查詢(xún)執(zhí)行流程，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06
使用MySQL 數(shù)據(jù)庫(kù)的站點(diǎn),當(dāng)訪問(wèn)連接數(shù)過(guò)多時(shí),就會(huì)出現(xiàn) "Too many connections" 的錯(cuò)誤,所以我們需要設(shè)置MySQL查看和修改最大連接數(shù),具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10
Myisamchk是MyISAM表維護(hù)的一個(gè)非常實(shí)用的工具?？梢允褂胢yisamchk實(shí)用程序來(lái)獲得有關(guān)數(shù)據(jù)庫(kù)表的信息或檢查、修復(fù)、優(yōu)化他們。myisamchk適用MyISAM表(對(duì)應(yīng).MYI和.MYD文件的表)。2008-10-10
Centos 7下使用RPM包安裝MySQL 5.7.9教程
這篇文章主要為大家詳細(xì)介紹了Centos 7下使用RPM包安裝MySQL 5.7.9的教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-05-05
mysql中json基礎(chǔ)查詢(xún)?cè)斀?附圖文)
MySQL提供了一些函數(shù)來(lái)對(duì)JSON數(shù)據(jù)進(jìn)行操作,下面這篇文章主要給大家介紹了關(guān)于mysql中json基礎(chǔ)查詢(xún)的相關(guān)資料,文中通過(guò)圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-10-10
datediff是SQL SERVER里面的用法，ORACLE沒(méi)有，主要作用是返回兩個(gè)日期之間的時(shí)間間隔，本文通過(guò)實(shí)例代碼給大家詳細(xì)講解，對(duì)datediff用法感興趣的朋友跟隨小編一起看看吧2022-11-11
MYSQL設(shè)置字段自動(dòng)獲取當(dāng)前時(shí)間的sql語(yǔ)句
整理數(shù)據(jù)庫(kù)數(shù)據(jù)，看到好多表都有加create_time和 update_time字段，來(lái)記錄數(shù)據(jù)插入的時(shí)間和更新時(shí)間，但是時(shí)間插入是通過(guò)代碼來(lái)維護(hù)的,這篇文章主要介紹了MYSQL設(shè)置字段自動(dòng)獲取當(dāng)前時(shí)間,需要的朋友可以參考下2023-07-07
這篇文章主要介紹了Mysql之組合索引方法詳解，文中通過(guò)示例代碼和查詢(xún)結(jié)果展示介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-08-08
在同一臺(tái)機(jī)器上運(yùn)行多個(gè) MySQL 服務(wù)
在同一臺(tái)機(jī)器上運(yùn)行多個(gè) MySQL 服務(wù)...2006-11-11
MySQL使用select語(yǔ)句查詢(xún)指定表中指定列(字段)的數(shù)據(jù)
本文介紹MySQL數(shù)據(jù)庫(kù)中執(zhí)行select查詢(xún)語(yǔ)句，查詢(xún)指定列的數(shù)據(jù)，即指定字段的數(shù)據(jù),需要的朋友可以參考下2016-11-11