記一次排查線上MySQL死鎖過程,不能只會curd,還要知道加鎖原理

一燈架構發表於2022-06-29

昨晚我正在床上睡得著著的,突然來了一條簡訊。

啥,線上MySQL死鎖了,我趕緊登入線上系統,檢視業務日誌。

能清楚看到是這條insert語句發生了死鎖。

MySQL如果檢測到兩個事務發生了死鎖,會回滾其中一個事務,讓另一個事務執行成功。很明顯,我們這條insert語句被回滾了。

insert into user (id, name, age) values (6, '張三', 6);

但是我們怎麼排查這個問題呢?

到底跟哪條SQL產生了死鎖?

好在MySQL記錄了最近一次的死鎖日誌,可以用命令列工具檢視:

show engine innodb status;

在死鎖日誌中,可以清楚地看到這兩條insert語句產生了死鎖,最終事務2被會回滾,事務1執行成功。

# 事務1
insert into user (id,name,age) values (5,'張三',5);
# 事務2
insert into user (id,name,age) values (6,'李四',6);

這兩條insert語句,怎麼看也不像能產生死鎖,我們來還原一下事發過程。

先看一下對應的Java程式碼:

@Override
@Transactional(rollbackFor = Exception.class)
public void insertUser(User user) {
    User userResult = userMapper.selectByIdForUpdate(user.getId());
    // 如果userId不存在,就插入資料,否則更新
    if (userResult == null) {
        userMapper.insert(user);
    } else {
        userMapper.update(user);
    }
}

業務邏輯程式碼很簡單,如果userId不存在,就插入資料,否則更新user物件資料。

從死鎖日誌中,我們看到有兩條insert語句,很明顯userId=5和userId=6的資料都不存在。

所以對應的SQL執行過程,可能就是這樣的:

先用for update加上排他鎖,防止其他事務修改當前資料,然後再insert資料,最後發生了死鎖,事務2被回滾。

兩個事務分別在兩個主鍵ID上面加鎖,為什麼會產生死鎖呢?

如果看過上篇文章,就會明白。

當id=5存在這條資料時,MySQL就會加Record Locks(記錄鎖),意思就是隻在id=5這一條記錄上加鎖。

當id=5這條記錄不存在時,就會鎖定一個範圍。

假設表中的記錄是這樣的:

id name age
1 王二 1
10 一燈 10
select * from user where id=5 for update;

這條select語句鎖定範圍就是 (1, 10]

最後兩個事務的執行過程就變成了:

通過這個示例看到,兩個事務都可以先後鎖定 (1, 10]這個範圍,說明MySQL預設加的臨鍵鎖的範圍是可以交叉的。

那怎麼解決這個死鎖問題呢?

我能想到的解決辦法就是,把這兩個語句select和insert,合併成一條語句:

insert into user (id,name,age) values (5,'張三',5)
	on duplicate key update name='張三',age=5;

大家有什麼好辦法嗎?

這個死鎖情況,還是挺常見的,趕緊回去翻一下專案程式碼有沒有這樣的問題。

文章持續更新,可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。

相關文章