腳本之家服務器常用軟件

快捷導航

MySQL線上死鎖分析實戰(zhàn)

更新時間：2021年02月25日 15:28:21 作者：是小桔啦

這篇文章主要介紹了MySQL線上死鎖分析實戰(zhàn)，文章內容分析的很清楚，有對于這方面不懂的同學可以研究下

前言

MySQL 的鎖機制相信大家在學習 MySQL 的時候都有簡單的了解過，那既然有鎖就必定繞不開死鎖這個問題。其實 MySQL 在大部分場景下是不會存在死鎖問題的（比如并發(fā)量不高，SQL 寫得不至于太拉胯的情況），但是在高并發(fā)的業(yè)務場景下，一不注意就會產生死鎖，而這個死鎖分析起來也比較麻煩。

前段時間在公司實習的時候就遇到了一個比較奇怪的死鎖，之前一直沒來得及好好整理，最近有空復現(xiàn)了一下，算是積累一點經驗。

業(yè)務場景

簡單說一下業(yè)務背景，公司做的是電商直播，我負責的是主播端相關的業(yè)務。而這個死鎖就出現(xiàn)在主播后臺對商品信息進行更新的時候。

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

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

這個 SQL 非常簡單，根據兩個等值條件，對一個字段進行更新。

不知道你看到這個 SQL 會不會懵逼，按常理來說，應該是一個事務里有多條 SQL 才會有可能出現(xiàn)死鎖，這一條 SQL 怎么可能出現(xiàn)死鎖呢？

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

索引合并

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

我們來看剛剛的 SQL，假設 class_id 和 teacher_id 分別是兩個普通索引：

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

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

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

從這個過程中，不難看出，MySQL 只使用到了一個索引，至于為什么不使用多個索引，簡單來說就是因為多個索引在多棵樹上，強行使用反而降低性能。

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

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

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

死鎖分析

分析完了 Index Merge 的步驟，我們再回過頭想一下為什么會出現(xiàn)死鎖呢？

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

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

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

// 線程 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 的時候，MySQL 的加鎖步驟如下：

最終，兩個事務互相等待，形成死鎖

解決方案

因為這個死鎖本質上還是由于 Index Merge 這個優(yōu)化導致的，所以要解決這個場景的死鎖問題，本質上只要讓 MySQL 不走 Index Merge 優(yōu)化即可。

方案一

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

方案二

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

方案三

強制走單個索引，在表名后添加 for index(class_id) 可以指定該語句僅走 class_id 索引

方案四

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

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

場景復現(xiàn)

數(shù)據準備

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

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

導入之后，我們會得到如下格式的 10000 條測試數(shù)據：

測試代碼

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

上述代碼主要是開啟 100 個線程執(zhí)行我們的數(shù)據修改 SQL 語句，來模擬線上并發(fā)情況，在運行幾秒鐘后，我們會得到下面這樣一個報錯：

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

這代表已經產生了死鎖異常

死鎖分析

上面我們用代碼已經構造出了一個死鎖，接下來我們進入 MySQL 看看死鎖日志，在 MySQL 中執(zhí)行如下命令即可查看死鎖日志：

SHOW ENGINE INNODB STATUS;

在日志中，我們找到 LATEST DETECTED DEADLOCK 這一行，這里開始便是我們上次產生的死鎖，接下來我們開始分析。

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

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

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