MySQL · 捉蟲動态 · 唯一鍵限制失效

唯一鍵是資料庫設計中常用的索引類型，主要用于限制資料，不允許出現重複的鍵值記錄。可以想象，如果唯一鍵限制失效了，将可能産生可怕的邏輯錯誤。本文主要讨論下最近mysql爆出來的兩個唯一鍵限制失效導緻二級索引corruption的問題。

影響版本：mysql 5.6.21之前，5.6.12之後的版本

上述修複帶來了嚴重的退化，在rc隔離級别下，使用delete + 并發insert沖突鍵值的場景，将可能觸發唯一鍵失效，我們簡單描述下沖突産生的過程：

開啟一個session，執行flush tables tbname for export，這會使purge操作停下來；

删除某條記錄，其二級索引為uk1, 執行的是标記删除，由于purge被我們人為的停止，是以這條記錄不會立刻被清理掉；

插入記錄，包含唯一索引記錄uk1，由于step 2的記錄還在（沒被purge）,是以需要檢查唯一性，在函數<code>row_ins_scan_sec_index_for_duplicate</code>中，根據隔離級别在記錄上加s not gap 鎖，唯一性檢查後送出mtr釋放block鎖；

和step 3 類似，另外一個session也插入uk1， 同樣加上s not gap鎖，因為s鎖是相容的，是以可以成功加上鎖，送出mtr釋放block鎖；

兩個session現在可以進行插入，因為受block x鎖限制，插入過程是順序的。但兩次插入都能成功，原因是在做插入鎖檢查時，會檢查相鄰記錄是否存在與(lock_x

lock_gap

lock_insert_intention)相沖突的鎖，而gap 鎖和not gap的s鎖是不沖突的(參考函數<code>lock_rec_has_to_wait</code>)， 是以兩次插入都能順利進行下去。

直接把針對 bug#68021 的更新檔給revert了。也就是說，在檢查duplicate key時總是加gap類型的s鎖（lock_ordinary），這樣上述過程的加鎖類型可以歸納為：

如上描述，這裡會有一定的幾率發生死鎖，并且死鎖資訊通常讓人無法捉摸，如果你發現兩條插入相同唯一鍵的sql出現在死鎖資訊裡，那有很大的可能是這個問題導緻的。

影響版本：mysql 5.1 ~ mysql 5.7全系列版本，上遊已确認，尚未fix。

這個問題是最近percona的開發人員alexey發現的，觸發條件是一次delete + 并發replace into操作，delete和replace操作相同的唯一鍵值。

和insert操作不同，通過replace into、load datafile replace、insert…on duplicate執行的sql，在檢查唯一建限制時，總是給沖突的記錄加lock_ordinary類型的x鎖 (而非上例的s鎖)。

問題産生的場景如下：

和上例一樣，先讓purge線程暫時停止下來；

删除包含uk1的記錄，由于purge已經停止了，記錄會留在實體檔案中不會被及時清理掉；

執行replace into，插入一條包含uk1的記錄，由于存在标記删除但尚未清理的沖突鍵值，且目前操作為replace into，是以給記錄加lock_ordinary類型的x鎖；完成沖突檢測後，送出mtr釋放block鎖；

開啟另外一個session執行replace into，同樣插入沖突鍵值uk1，由于step 3 已經加了x鎖，是以這裡再加x鎖産生鎖等待，進入等待隊列。這時候我們檢視innodb_locks表，會發現已經存在兩個鎖對象了

開啟purge線程，purge操作會清理掉之前标記删除的實體記錄，然而在step3 和step4上已經在這條記錄上加了記錄鎖，記錄被清掉了，對應的鎖記錄也需要做處理，innodb會嘗試将鎖繼承給下一條記錄，我們來看看鎖繼承的邏輯，調用函數<code>lock_rec_inherit_to_gap</code>：

當滿足如下條件時，不會做鎖繼承：

鎖類型為插入意向鎖

<code>srv_locks_unsafe_for_binlog</code>打開且鎖類型為x鎖

鎖對應事務的隔離級别小于等于rc且鎖類型為x鎖

由于目前的隔離級别為rc，并且replace into操作加的是x鎖，是以鎖沒有被相鄰記錄繼承，我們從innodb_locks系統表中也可以發現這一點：

喚醒第二個replace 操作（正在等待x鎖），執行插入操作成功；

喚醒第一個replace 操作，由于已經完成duplicate key檢測，插入成功。

從上述邏輯可以看出，當purge線程被激活後，記錄和記錄鎖對象都被移除了，purge操作悄悄的破壞了innodb的加鎖協定。

我們來看看另外一個在repeatable read 隔離級别下，唯一鍵“失效”的問題，考慮如下執行序列。

b列是唯一鍵，session1成功插入一條剛被删除的相同鍵值，并且能查詢出來兩條相同鍵值的記錄。看起來似乎是唯一鍵限制被破壞了，這實際上和innodb的内部實作有關。

在上述序列中，session 2執行删除操作，将唯一鍵進行标記删除，由于session1 已經開啟了一個活躍的視圖，根據repeatable-read的可見性原則，session 2所做的資料變更對session 1而言是不可見的，purge線程也無法去實體清理該記錄。隻要session 1不送出事務，總應該能看到被标記删除的記錄(1,2)。

當session 1插入相同唯一鍵值記錄(2,2)時，會檢查到檔案中存在沖突的唯一建，但修改該唯一鍵的事務已經送出，是以session 1認為插入記錄(2,2)是合法的，完成插入後，唯一索引頁上就存在兩條實體記錄，并且對session 1都是可見的。

這個問題是不是bug很難界定，畢竟他沒有違反rr級别下可見性原則，唯一索引資料本身也是完好的，據我所知，postgresql也遵循相同的邏輯。