一次MySQL死鎖的排查記錄

前幾天線上收到一條告警郵件，生產環境MySQL操作發生了死鎖，郵件告警的提煉出來的SQL大致如下。

update pe_order_product_info_test
        set  end_time = '2021-04-30 23:59:59'
        where order_no = '111111111'
        and product_id = 123456
        and status in (1,2);
update pe_order_product_info_test
        set  end_time = '2021-04-30 23:59:59'
        where order_no = '222222222'
        and product_id = 123456
        and status in (1,2);

是一條Update語句，定位了它的調用情況，發現Update的調用方只有一處，並且在Cat中看到一個小時的調用次數只有700多次，這個調用量基本與並發Update引起死鎖無關了。

當時猜測了幾種情況，這裡Update進行操作時有其他業務方調用Select相關的介面，但是排查了那個時間點發生死鎖應用的調用鏈，發現好像並沒有其他會影響到Update的調用。

為了更進一步了解當時的情況，就聯繫了DBA老師，要了當時死鎖發生時的日誌，準備拿到日誌之後大幹一場，好好分析一下問題，結果...

DBA老師看了死鎖日誌直接點出了問題要害——index_merge索引合併。

1. 什麼是索引合併

這是MySQL在5.1引入的優化技術，再此之前，一個表僅僅只能使用一個索引，但索引合併的引入，可以對同一張表使用多個索引分別進行條件掃描。

如果要拿索引合併index_merge與只使用一個索引做比較，那麼拿上面那個update語句來做演示。

update pe_order_product_info_test
        set end_time = '2021-04-30 23:59:59'
        where order_no = '111111111'
        and product_id = 123456
        and status in (1,2);

只是用一個索引時，MySQL會選擇一個最優的索引來使用，比如使用index_order_no，拿它來找出所有order_no為111111111的索引記錄，從該索引上找到它的PRIMARY索引的id，然後回表找到對應的行數據，最後在記憶體中根據剩下的product_id和status條件來進行過濾。

但如果MySQL優化器覺得你如果只是用一個索引，拿出大量記錄，然後再在記憶體中使用product_id和status過濾（並且符合該條件的記錄值很少），這個第二步效率可能不高時，他就會使用索引合併進行優化。

如果使用索引合併去判斷where條件時，那麼它就會先通過index_order_no索引去找到PRIMARY索引的id，再通過index_product_id索引去找到PRIMARY索引的id，最後將兩個id集合求交集，再回表找到行數據。(索引合併使用索引的順序是不確定的)

2. 場景復現

在MySQL的Bug回饋文檔中也有記錄一個Bug #77209的記錄，標註了索引合併引發死鎖的情況。但是我按照它給出的repeat並不能重現索引合併的場景，在它的實例中早了600萬隨機數，我猜測可能是MySQL調高了索引合併的條件，將數據量增加到了1000萬。

先來帶大家復現一下當時的情況。

環境：MySQL 5.6.24

  1. 創建一張測試表

    CREATE TABLE `a` (
  `ID` int  AUTO_INCREMENT PRIMARY KEY,
  `NAME` varchar(21),
  `STATUS` int,
  KEY `NAME` (`NAME`),
  KEY `STATUS` (`STATUS`)
) engine = innodb;

  2. 導入數據，為了方便導入一些隨機數據，需要先開啟一個兼容性配置。

    set global show_compatibility_56=on;

    開始導入隨機數據。

    set @N=0;
insert into a(ID,NAME,STATUS)
select
	@N:=@N+1,
	@N%1600000, 
	floor(rand()*4)
 from information_schema.global_variables a, information_schema.global_variables b, information_schema.global_variables c 
LIMIT 10000000;

  3. 測試

    update a set status=5 where rand() < 0.005 limit 1;
explain UPDATE a SET STATUS = 2 WHERE NAME =  '1000000' AND STATUS = 5;

3. 為什麼發生了死鎖

直接上一副圖，以及兩個update事務的加鎖流程。

可以看到在訂單與產品這個模型中，Update事務一和Update事物二在product_id索引和primary索引上都存在交叉重合，這就導致了死鎖的發生。

步數 事務一 事務二
1 鎖住index_order_no索引樹上order_no為2222的索引項
2 鎖住index_order_no索引樹上order_no為3333的索引項
3 回表鎖住 PRIMARY 索引中 id 為 11 的索引項
4 回表鎖住 PRIMARY 索引中 id 為 12 的索引項
5 鎖住index_product_id索引樹上product_id為2000的四個索引項
6 嘗試去鎖住index_product_id索引樹上product_id為2000的四個索引項，但是已經被事務一鎖住，等待事務一釋放在index_product_id上的鎖
7 試圖回表鎖住 PRIMARY 索引中 id 為10，11，12，13的索引項，發現id為12的索引項在第4步已經被事務二鎖住，等待事務二釋放在

這就是本次死鎖發生的原因所在了，解決方案有很多種，可以根據具體場景選擇。

  1. 刪除某一個索引，這當然不是一個好辦法
  2. 關閉index_merge優化
  3. 為查詢條件增加聯合索引，在本例中是product_id和order_no。

4. 最後

當然最後這些都是我個人的分析，DBA老師給的建議是直接上聯合索引，網上關於索引合併的資料實在太少了，除了官方文檔簡單扯了扯，剩下的都是轉載來轉載去的部落格，內容都一模一樣，DBA老師也不寫部落格，所以我就只能按我上述這個思路理解了，如果網友有什麼問題歡迎指出~

Tags:

VirMach 便宜 VPS