一個最不可思議的MySQL死結分析

標籤：www.   核心   get   也會   執行計畫   而且   comm   isa   主鍵   

一個最不可思議的MySQL死結分析

1. 死結問題背景

 

做MySQL代碼的深入分析也有些年頭了，再加上自己10年左右的資料庫核心研發經驗，自認為對於MySQL/InnoDB的加鎖實現了如指掌，正因如此，前段時間，還專門寫了一篇洋洋洒洒的文章，專門分析MySQL的加鎖實現細節：《MySQL加鎖處理分析》。

 

但是，昨天”潤潔”同學在《MySQL加鎖處理分析》這篇博文下諮詢的一個MySQL的死結情境，還是徹底把我給難住了。此死結，完全違背了本人原有的鎖知識體系，讓我百思不得其解。本著機器不會騙人，既然報出死結，那麼就一定存在死結的原則，我又重新深入分析了InnoDB對應的源碼實現，進行多次實驗，配合恰到好處的靈光一現，還真讓我分析出了這個死結產生的原因。這篇博文的餘下部分的內容安排，首先是給出”潤潔”同學描述的死結情境，然後再給出我的剖析。對個人來說，這是一篇十分有必要的總結，對此博文的讀者來說，希望以後碰到類似的死結問題時，能夠明確死結的原因所在。

 

 

 

1. 一個不可思議的死結

 

“潤潔”同學，給出的死結情境如下：

 

表結構：

 

CREATE TABLE dltask (

id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘auto id’,

a varchar(30) NOT NULL COMMENT ‘uniq.a’,

b varchar(30) NOT NULL COMMENT ‘uniq.b’,

c varchar(30) NOT NULL COMMENT ‘uniq.c’,

x varchar(30) NOT NULL COMMENT ‘data’,

PRIMARY KEY (id),

UNIQUE KEY uniq_a_b_c (a, b, c)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’deadlock test’;

 

a，b，c三列，組合成一個唯一索引，主鍵索引為id列。

 

交易隔離等級：

 

RR (Repeatable Read)

 

每個事務只有一條SQL：

 

delete from dltask where a=? and b=? and c=?;

 

SQL的執行計畫：

 

 

死結日誌：

 

 

1. 初步分析

 

並發事務，每個事務只有一條SQL語句：給定唯一的二級索引索引值，刪除一條記錄。每個事務，最多隻會刪除一條記錄，為什麼會產生死結？這絕對是不可能的。但是，事實上，卻真的是發生了死結。產生死結的兩個事務，刪除的是同一條記錄，這應該是死結發生的一個潛在原因，但是，即使是刪除同一條記錄，從原理上來說，也不應該產生死結。因此，經過初步分析，這個死結是不可能產生的。這個結論，遠遠不夠！

 

1. 如何閱讀死結日誌

 

在詳細給出此死結產生的原因之前，讓我們先來看看，如何閱讀MySQL給出的死結日誌。

 

以上列印出來的死結日誌，由InnoDB引擎中的lock0lock.c::lock_deadlock_recursive()函數產生。死結中的事務資訊，通過調用函數lock_deadlock_trx_print()處理；而每個事務持有、等待的鎖資訊，由lock_deadlock_lock_print()函數產生。

 

例如，以上的死結，有兩個事務。事務1，當前正在操作一張表(mysql tables in use 1)，持有兩把鎖(2 lock structs，一個表級意圖鎖定，一個行鎖(1 row lock))，這個事務，當前正在處理的語句是一條delete語句。同時，這唯一的一個行鎖，處於等待狀態(WAITING FOR THIS LOCK TO BE GRANTED)。

 

事務1等待中的行鎖，加鎖的對象是唯一索引uniq_a_b_c上頁面號為12713頁面上的一行(註：具體是哪一行，無法看到。但是能夠看到的是，這個行鎖，一共有96個bits可以用來鎖96個行記錄，n bits 96：lock_rec_print()方法)。同時，等待的行鎖模式為next key鎖(lock_mode X)。(註：關於InnoDB的鎖模式，可參考我早期的一篇PPT：《InnoDB 事務/鎖/多版本 實現分析》。簡單來說，next key鎖有兩層含義，一是對目前記錄加X鎖，防止記錄被並發修改，同時鎖住記錄之前的GAP，防止有新的記錄插入到此記錄之前。)

 

同理，可以分析事務2。事務2上有兩個行鎖，兩個行鎖對應的也都是唯一索引uniq_a_b_c上頁面號為12713頁面上的某一條記錄。一把行鎖處於持有狀態，鎖模式為X lock with no gap(註：記錄鎖，只鎖記錄，但是不鎖記錄前的GAP，no gap lock)。一把行鎖處於等待狀態，鎖模式為next key鎖(註：與事務1等待的鎖模式一致。同時，需要注意的一點是，事務2的兩個鎖模式，並不是一致的，不完全相容。持有的鎖模式為X lock with no gap，等待的鎖模式為next key lock X。因此，並不能因為持有了X lock with no gap，就可以說next key lock X就一定能夠加上。)。

 

分析這個死結日誌，就能發現一個死結。事務1的next key lock X正在等待事務2持有的X lock with no gap(行鎖X衝突)，同時，事務2的next key lock X，卻又在等待事務1正在等待中的next key鎖(註：這裡，事務2等待事務1的原因，在於公平競爭，杜絕事務1發生饑餓現象。)，形成迴圈等待，死結產生。

 

死結產生後，根據兩個事務的權重，事務1的權重更小，被選為死結的犧牲者，復原。

 

根據對於死結日誌的分析，確認死結確實存在。而且，產生死結的兩個事務，確實都是在運行同樣的基於唯一索引的等值刪除操作。既然死結確實存在，那麼接下來，就是抓出這個死結產生原因。

 

1. 死結原因深入剖析

 

1. Delete操作的加鎖邏輯

 

在《MySQL加鎖處理分析》一文中，我詳細分析了各種SQL語句對應的加鎖邏輯。例如：Delete語句，內部就包含一個當前讀(加鎖讀)，然後通過當前讀返回的記錄，調用Delete操作進行刪除。在此文的 組合六：id唯一索引+RR 中，可以看到，RR隔離等級下，針對於滿足條件的查詢記錄，會對記錄加上排它鎖(X鎖)，但是並不會鎖住記錄之前的GAP(no gap lock)。對應到此文上面的死結例子，事務2所持有的鎖，是一把記錄上的排它鎖，但是沒有鎖住記錄前的GAP(lock_mode X locks rec but not gap)，與我之前的加鎖分析一致。

 

其實，在《MySQL加鎖處理分析》一文中的 組合七：id非唯一索引+RR 部分的最後，我還提出了一個問題：如果組合五、組合六下，針對SQL：select * from t1 where id = 10 for update; 第一次查詢，沒有找到滿足查詢條件的記錄，那麼GAP鎖是否還能夠省略？針對此問題，參與的朋友在做過實驗之後，給出的正確答案是：此時GAP鎖不能省略，會在第一個不滿足查詢條件的記錄上加GAP鎖，防止新的滿足條件的記錄插入。

 

其實，以上兩個加鎖策略，都是正確的。以上兩個策略，分別對應的是：1）唯一索引上滿足查詢條件的記錄存在並且有效；2）唯一索引上滿足查詢條件的記錄不存在。但是，除了這兩個之外，其實還有第三種：3）唯一索引上滿足查詢條件的記錄存在但是無效。眾所周知，InnoDB上刪除一條記錄，並不是真正意義上的物理刪除，而是將記錄標識為刪除狀態。(註：這些標識為刪除狀態的記錄，後續會由背景Purge操作進行回收，物理刪除。但是，刪除狀態的記錄會在索引中存放一段時間。) 在RR隔離等級下，唯一索引上滿足查詢條件，但是卻是刪除記錄，如何加鎖？InnoDB在此處的處理策略與前兩種策略均不相同，或者說是前兩種策略的組合：對於滿足條件的刪除記錄，InnoDB會在記錄上加next key lock X(對記錄本身加X鎖，同時鎖住記錄前的GAP，防止新的滿足條件的記錄插入。) Unique查詢，三種情況，對應三種加鎖策略，總結如下：

 

• 找到滿足條件的記錄，並且記錄有效，則對記錄加X鎖，No Gap鎖(lock_mode X locks rec but not gap)；

   

• 找到滿足條件的記錄，但是記錄無效(標識為刪除的記錄)，則對記錄加next key鎖(同時鎖住記錄本身，以及記錄之前的Gap：lock_mode X);

• 未找到滿足條件的記錄，則對第一個不滿足條件的記錄加Gap鎖，保證沒有滿足條件的記錄插入(locks gap before rec)；

 

此處，我們看到了next key鎖，是否很眼熟？對了，前面死結中事務1，事務2處於等待狀態的鎖，均為next key鎖。明白了這三個加鎖策略，其實構造一定的並發情境，死結的原因已經呼之欲出。但是，還有一個前提策略需要介紹，那就是InnoDB內部採用的死結預防策略。

 

1. 死結預防策略

 

InnoDB引擎內部(或者說是所有的資料庫內部)，有多種鎖類型：事務鎖(行鎖、表鎖)，Mutex(保護內部的共用變數操作)、RWLock(又稱之為Latch，保護內部的頁面讀取與修改)。

 

InnoDB每個頁面為16K，讀取一個頁面時，需要對頁面加S鎖，更新一個頁面時，需要對頁面加上X鎖。任何情況下，操作一個頁面，都會對頁面加鎖，頁面鎖加上之後，頁面記憶體儲的索引記錄才不會被並發修改。

 

因此，為了修改一條記錄，InnoDB內部如何處理：

 

1. 根據給定的查詢條件，找到對應的記錄所在頁面；

    

2. 對頁面加上X鎖(RWLock)，然後在頁面內尋找滿足條件的記錄；

    

3. 在持有頁面鎖的情況下，對滿足條件的記錄加事務鎖(行鎖：根據記錄是否滿足查詢條件，記錄是否已經被刪除，分別對應於上面提到的3種加鎖策略之一)；

    

4. 死結預防策略：相對於事務鎖，頁面鎖是一個短期持有的鎖，而事務鎖(行鎖、表鎖)是長期持有的鎖。因此，為了防止頁面鎖與事務鎖之間產生死結。InnoDB做了死結預防的策略：持有事務鎖(行鎖、表鎖)，可以等待擷取頁面鎖；但反之，持有頁面鎖，不能等待持有事務鎖。

    

5. 根據死結預防策略，在持有頁面鎖，加行鎖的時候，如果行鎖需要等待。則釋放頁面鎖，然後等待行鎖。此時，行鎖擷取沒有任何鎖保護，因此加上行鎖之後，記錄可能已經被並發修改。因此，此時要重新加回頁面鎖，重新判斷記錄的狀態，重新在頁面鎖的保護下，對記錄加鎖。如果此時記錄未被並發修改，那麼第二次加鎖能夠很快完成，因為已經持有了相同模式的鎖。但是，如果記錄已經被並發修改，那麼，就有可能導致本文前面提到的死結問題。

1. 以上的InnoDB死結預防處理邏輯，對應的函數，是row0sel.c::row_search_for_mysql()。感興趣的朋友，可以跟蹤調試下這個函數的處理流程，很複雜，但是集中了InnoDB的精髓。

 

1. 剖析死結的成因

 

做了這麼多鋪墊，有了Delete操作的3種加鎖邏輯、InnoDB的死結預防策略等準備知識之後，再回過頭來分析本文最初提到的死結問題，就會手到拈來，事半而功倍。

 

首先，假設dltask中只有一條記錄：(1, ‘a’, ‘b’, ‘c’, ‘data’)。三個並發事務，同時執行以下的這條SQL：

 

delete from dltask where a=’a’ and b=’b’ and c=’c’;

 

並且產生了以下的並發執行邏輯，就會產生死結：

 

 

上面分析的這個並發流程，完整展現了死結日誌中的死結產生的原因。其實，根據事務1步驟6，與事務0步驟3/4之間的順序不同，死結日誌中還有可能產生另外一種情況，那就是事務1等待的鎖模式為記錄上的X鎖 + No Gap鎖(lock_mode X locks rec but not gap waiting)。這第二種情況，也是”潤潔”同學給出的死結用例中，使用MySQL 5.6.15版本測試出來的死結產生的原因。

 

1. 總結

 

行文至此，MySQL基於唯一索引的單條記錄的刪除操作並發，也會產生死結的原因，已經分析完畢。其實，分析此死結的痛點，在於理解MySQL/InnoDB的行鎖模式，針對不同情況下的加鎖模式的區別，以及InnoDB處理頁面鎖與事務鎖的死結預防策略。明白了這些，死結的分析就會顯得清晰明了。

 

最後，總結下此類死結，產生的幾個前提：

 

• Delete操作，針對的是唯一索引上的等值查詢的刪除；(範圍下的刪除，也會產生死結，但是死結的情境，跟本文分析的情境，有所不同)

   

• 至少有3個(或以上)的並發刪除操作；

• 並發刪除操作，有可能刪除到同一條記錄，並且保證刪除的記錄一定存在；

• 事務的隔離等級設定為Repeatable Read，同時未設定innodb_locks_unsafe_for_binlog參數(此參數預設為FALSE)；(Read Committed隔離等級，由於不會加Gap鎖，不會有next key，因此也不會產生死結)

• 使用的是InnoDB儲存引擎；(廢話！MyISAM引擎根本就沒有行鎖)

一個最不可思議的MySQL死結分析