前言

MySQL 的鎖機(jī)制相信大家在學(xué)習(xí) MySQL 的時(shí)候都有簡(jiǎn)單的了解過(guò)，那既然有鎖就必定繞不開(kāi)死鎖這個(gè)問(wèn)題。其實(shí) MySQL 在大部分場(chǎng)景下是不會(huì)存在死鎖問(wèn)題的（比如并發(fā)量不高，SQL 寫(xiě)得不至于太拉胯的情況），但是在高并發(fā)的業(yè)務(wù)場(chǎng)景下，一不注意就會(huì)產(chǎn)生死鎖，而這個(gè)死鎖分析起來(lái)也比較麻煩。

前段時(shí)間在公司實(shí)習(xí)的時(shí)候就遇到了一個(gè)比較奇怪的死鎖，之前一直沒(méi)來(lái)得及好好整理，最近有空復(fù)現(xiàn)了一下，算是積累一點(diǎn)經(jīng)驗(yàn)。

業(yè)務(wù)場(chǎng)景

簡(jiǎn)單說(shuō)一下業(yè)務(wù)背景，公司做的是電商直播，我負(fù)責(zé)的是主播端相關(guān)的業(yè)務(wù)。而這個(gè)死鎖就出現(xiàn)在主播后臺(tái)對(duì)商品信息進(jìn)行更新的時(shí)候。

我們的一個(gè)商品會(huì)有兩個(gè)關(guān)聯(lián)的 ID，通過(guò)其中任何一個(gè) ID 都無(wú)法確定唯一一件商品（也就是說(shuō)這個(gè) ID 和商品是一對(duì)多的關(guān)系），只能同時(shí)查詢兩個(gè) ID，才能確定一件商品。所以在更新商品信息的時(shí)候，需要在 where 條件中同時(shí)指定兩個(gè) ID，下面是死鎖 SQL 的結(jié)構(gòu)（已脫敏）：

UPDATE test_table SET `name`="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

這個(gè) SQL 非常簡(jiǎn)單，根據(jù)兩個(gè)等值條件，對(duì)一個(gè)字段進(jìn)行更新。

不知道你看到這個(gè) SQL 會(huì)不會(huì)懵逼，按常理來(lái)說(shuō)，應(yīng)該是一個(gè)事務(wù)里有多條 SQL 才會(huì)有可能出現(xiàn)死鎖，這一條 SQL 怎么可能出現(xiàn)死鎖呢？

是的，我當(dāng)時(shí)也有這樣的疑惑，甚至懷疑是不是報(bào)警系統(tǒng)瞎報(bào)（最后證明不是…），當(dāng)時(shí)是真的摸不著頭腦。并且因?yàn)閿?shù)據(jù)庫(kù)權(quán)限的原因，想看死鎖日志都看不到，又是臨近下班的時(shí)候，找 DBA 能麻煩死，所以就直接搜索引擎走起了……（關(guān)鍵詞：update 死鎖 單條 sql），最后查出來(lái)是由于 MySQL 的索引合并優(yōu)化導(dǎo)致的，即 Index Merge，下面會(huì)進(jìn)行詳細(xì)講解并復(fù)現(xiàn)一下死鎖場(chǎng)景。

索引合并

Index Merge 是 MySQL 在 5.0 的時(shí)候引入的一項(xiàng)優(yōu)化功能，主要是用于優(yōu)化一條 SQL 使用多個(gè)索引的情況。

我們來(lái)看剛剛的 SQL，假設(shè) class_id 和 teacher_id 分別是兩個(gè)普通索引：

UPDATE test_table SET `name`="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

如果沒(méi)有 Index Merge 優(yōu)化的時(shí)候，MySQL 查詢數(shù)據(jù)的步驟如下：

• 根據(jù) class_id 或 teacher_id （具體使用哪個(gè)索引由優(yōu)化器根據(jù)實(shí)際數(shù)據(jù)情況自行判斷，這里假設(shè)使用 class_id的索引）在二級(jí)索引上查詢到對(duì)應(yīng)數(shù)據(jù)的主鍵 ID
• 根據(jù)查詢到的主鍵 ID 進(jìn)行回標(biāo)查詢（即查詢聚簇索引），得到相應(yīng)的數(shù)據(jù)行
• 從數(shù)據(jù)行中獲取 teacher_id ，判斷其是否等于 8，滿足條件則返回

從這個(gè)過(guò)程中，不難看出，MySQL 只使用到了一個(gè)索引，至于為什么不使用多個(gè)索引，簡(jiǎn)單來(lái)說(shuō)就是因?yàn)槎鄠€(gè)索引在多棵樹(shù)上，強(qiáng)行使用反而降低性能。

再來(lái)看看引入了 Index Merge 優(yōu)化后，MySQL 查詢數(shù)據(jù)的步驟如下：

• 根據(jù) class_id 查詢到相應(yīng)的主鍵，再根據(jù)主鍵回表查詢到對(duì)應(yīng)的數(shù)據(jù)行（記為結(jié)果集 A）
• 根據(jù) teacher_id 查詢到相應(yīng)的主鍵，再根據(jù)主鍵回表查詢到對(duì)應(yīng)的數(shù)據(jù)行（記為結(jié)果集 B）
• 將結(jié)果集 A 和結(jié)果集 B 執(zhí)行交集操作，獲得最終滿足條件的結(jié)果集

這里可以看出，有了 Index Merge 之后，MySQL 將一條 SQL 語(yǔ)句拆分成了兩個(gè)查詢步驟，分別使用兩個(gè)索引，再用交集操作優(yōu)化性能。

死鎖分析

分析完了 Index Merge 的步驟，我們?cè)倩剡^(guò)頭想一下為什么會(huì)出現(xiàn)死鎖呢？

還記得上面說(shuō)的 Index Merge 將一條 SQL 查詢拆分成了兩個(gè)步驟嗎，問(wèn)題就出現(xiàn)在這里。我們知道 UPDATE 語(yǔ)句是會(huì)加上一個(gè)行級(jí)排他鎖的，在分析加鎖步驟之前，我們假設(shè)有如下一個(gè)數(shù)據(jù)表：

上表數(shù)據(jù)滿足我們文章開(kāi)頭說(shuō)的特點(diǎn)，根據(jù) class_id 和 teacher_id 單個(gè)字段均無(wú)法唯一確定一條數(shù)據(jù)，只能聯(lián)合兩個(gè)字段，才能確定一條數(shù)據(jù)，并且設(shè)定 class_id 和 teacher_id 分別為兩個(gè)普通索引。

假設(shè)有如下兩條 SQL 語(yǔ)句并發(fā)執(zhí)行，它們的參數(shù)完全不同，直覺(jué)告訴我們應(yīng)該不會(huì)出現(xiàn)死鎖，但直覺(jué)往往是錯(cuò)誤的：

// 線程 A 執(zhí)行
UPDATE test_table SET `name`="zhangsan" WHERE class_id = 2 AND teacher_id = 1;

// 線程 B 執(zhí)行
UPDATE test_table SET `name`="zhangsan" WHERE class_id = 1 AND teacher_id = 2;

那么在 Index Merge 的優(yōu)化下，并發(fā)執(zhí)行如上 SQL 的時(shí)候，MySQL 的加鎖步驟如下：

最終，兩個(gè)事務(wù)互相等待，形成死鎖

解決方案

因?yàn)檫@個(gè)死鎖本質(zhì)上還是由于 Index Merge 這個(gè)優(yōu)化導(dǎo)致的，所以要解決這個(gè)場(chǎng)景的死鎖問(wèn)題，本質(zhì)上只要讓 MySQL 不走 Index Merge 優(yōu)化即可。

方案一

手動(dòng)將一條 SQL 拆分成多條 SQL，在邏輯層做交集操作，阻止 MySQL 的憨憨優(yōu)化行為，比如這里我們可以先根據(jù) class_id 查詢到相應(yīng)主鍵，再根據(jù) teacher_id 查詢相應(yīng)主鍵，最后根據(jù)交集后的主鍵查詢數(shù)據(jù)。

方案二

建立聯(lián)合索引，比如這里可以將 class_id 和 teacher_id 建立一個(gè)聯(lián)合索引，MySQL 就不會(huì)走 Index Merge 了

方案三

強(qiáng)制走單個(gè)索引，在表名后添加 for index(class_id) 可以指定該語(yǔ)句僅走 class_id 索引

方案四

關(guān)閉 Index Merge 優(yōu)化：

• 永久關(guān)閉：SET [GLOBAL|SESSION] optimizer_switch='index_merge=off';
• 臨時(shí)關(guān)閉：UPDATE /*+ NO_INDEX_MERGE(test_table) */ test_table SET name="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

場(chǎng)景復(fù)現(xiàn)

數(shù)據(jù)準(zhǔn)備

為了方便測(cè)試，這里提供一個(gè) SQL 腳本，將其用 Navicat 導(dǎo)入后即可得到需要的測(cè)試數(shù)據(jù)：

下載地址：https://cdn.juzibiji.top/file/index_merge_student.sql

導(dǎo)入之后，我們會(huì)得到如下格式的 10000 條測(cè)試數(shù)據(jù)：

測(cè)試代碼

由于篇幅限制，這里僅給出代碼 Gist 鏈接：https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60

上述代碼主要是開(kāi)啟 100 個(gè)線程執(zhí)行我們的數(shù)據(jù)修改 SQL 語(yǔ)句，來(lái)模擬線上并發(fā)情況，在運(yùn)行幾秒鐘后，我們會(huì)得到下面這樣一個(gè)報(bào)錯(cuò)：

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

這代表已經(jīng)產(chǎn)生了死鎖異常

死鎖分析

上面我們用代碼已經(jīng)構(gòu)造出了一個(gè)死鎖，接下來(lái)我們進(jìn)入 MySQL 看看死鎖日志，在 MySQL 中執(zhí)行如下命令即可查看死鎖日志：

SHOW ENGINE INNODB STATUS;

在日志中，我們找到 LATEST DETECTED DEADLOCK 這一行，這里開(kāi)始便是我們上次產(chǎn)生的死鎖，接下來(lái)我們開(kāi)始分析。

通過(guò)第 29 行可以看到，事務(wù) 1 執(zhí)行的 SQL 的條件是 class_id = 6 和 teacher_id = 16 ，它目前持有了一個(gè)行鎖，第 34~39 行是該行數(shù)據(jù)，34 行是主鍵的十六進(jìn)制表示，我們轉(zhuǎn)換為 10 進(jìn)制即為 1616。同樣的，看 45 行，其等待拿鎖的是主鍵 id 1517 的數(shù)據(jù)。

接下來(lái)用同樣的方法分析事務(wù) 2，可知事務(wù) 2 持有了 3 把鎖，分別是主鍵 id 為1317、1417、1517 的數(shù)據(jù)行，等待的是 1616 。

看到這里我們就已經(jīng)發(fā)現(xiàn)了，事務(wù) 1 持有 1616 等待 1517，事務(wù) 2 持有1517 等待 1616，所以形成了一個(gè)死鎖。此時(shí) MySQL 的處理方法是回滾持有鎖最少的事務(wù)，并且 JDBC 會(huì)拋出我們前面的 MySQLTransactionRollbackException 回滾異常。

總結(jié)

這個(gè)死鎖在排查的時(shí)候其實(shí)非常不好排查，如果你不知道 MySQL 的 Index Merge，那么在排查的時(shí)候其實(shí)是毫無(wú)頭緒的，因?yàn)槌尸F(xiàn)在你面前的就只有一條非常簡(jiǎn)單的 SQL，就算看死鎖日志，也是一樣的不明所以。

所以處理這類(lèi)問(wèn)題，更多的還是考驗(yàn)?zāi)愕闹R(shí)儲(chǔ)備量和經(jīng)驗(yàn)，只要遇到過(guò)一次，后面在寫(xiě) SQL 的時(shí)候多加注意就好了！

到此這篇關(guān)于MySQL線上死鎖分析實(shí)戰(zhàn)的文章就介紹到這了,更多相關(guān)MySQL線上死鎖分析內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論