MySQL·捉蟲動態·唯一鍵約束失效

唯一鍵是資料庫設計中常用的索引型別，主要用於約束資料，不允許出現重複的鍵值記錄。可以想象，如果唯一鍵約束失效了，將可能產生可怕的邏輯錯誤。本文主要討論下最近MySQL爆出來的兩個唯一鍵約束失效導致二級索引corruption的問題。

問題一： 檢查重複鍵加鎖邏輯不當

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

介紹分析

在5.6.12之前的版本中，當插入一條帶唯一約束的記錄時，如果表上已經存在了這條記錄，或者有一條標記刪除的相同鍵值記錄時，就需要對這條記錄加S GAP （型別為LOCK_ORDINARY）鎖，即使你使用的是READ-COMMIT的隔離級別。因此有人report了bug#68021 ，認為在RC級別下，檢查duplicate key時無需加GAP鎖，在MySQL 5.6.12版本里針對RR級別依然加LOCK_ORDINARY型別的S鎖，而針對RC級別加LOCK_REC_NOT_GAP型別的S鎖。

上述修復帶來了嚴重的退化，在RC隔離級別下，使用DELETE + 併發INSERT衝突鍵值的場景，將可能觸發唯一鍵失效，我們簡單描述下衝突產生的過程：

1. 開啟一個session，執行flush tables tbname for export，這會使purge操作停下來；
2. 刪除某條記錄，其二級索引為uk1, 執行的是標記刪除，由於purge被我們人為的停止，因此這條記錄不會立刻被清理掉；
3. 插入記錄，包含唯一索引記錄uk1，由於step 2的記錄還在（沒被purge）,因此需要檢查唯一性，在函式row_ins_scan_sec_index_for_duplicate中，根據隔離級別在記錄上加S NOT GAP 鎖，唯一性檢查後提交mtr釋放block鎖；
4. 和step 3 類似，另外一個session也插入uk1， 同樣加上S NOT GAP鎖，因為S鎖是相容的，因此可以成功加上鎖，提交mtr釋放block鎖；

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

   LOCK_GAP

   LOCK_INSERT_INTENTION)相沖突的鎖，而GAP 鎖和NOT GAP的S鎖是不衝突的(參考函式lock_rec_has_to_wait)， 因此兩次插入都能順利進行下去。

修復

直接把針對 bug#68021 的補丁給revert了。也就是說，在檢查duplicate key時總是加GAP型別的S鎖（LOCK_ORDINARY），這樣上述過程的加鎖型別可以歸納為：

SESSION1 持有 LOCK_ORDINARY S LOCK
SESSION2 持有 LOCK_ORDINARY S LOCK
SESSION1 INSERT RECORD ... CONFLICT, ENQUEUE （LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION) ——> WAIT
SESSION2 INSERT RECORD ... CONFLICT, ENQUEUE LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION ——> DEAD LOCK HAPPEN

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

問題二：鎖繼承邏輯缺陷導致約束失效(bug#76927)

影響版本：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鎖)。

問題產生的場景如下：

1. 和上例一樣，先讓purge執行緒暫時停止下來；
2. 刪除包含uk1的記錄，由於purge已經停止了，記錄會留在物理檔案中不會被及時清理掉；
3. 執行REPLACE INTO，插入一條包含uk1的記錄，由於存在標記刪除但尚未清理的衝突鍵值，且當前操作為replace into，因此給記錄加LOCK_ORDINARY型別的X鎖；完成衝突檢測後，提交mtr釋放block鎖；

4. 開啟另外一個session執行REPLACE INTO，同樣插入衝突鍵值UK1，由於Step 3 已經加了X鎖，因此這裡再加X鎖產生鎖等待，進入等待佇列。這時候我們檢視innodb_locks表，會發現已經存在兩個鎖物件了

    mysql> select * from information_schema.innodb_locks;
    +------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
    | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
    +------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
    | 1300:6:4:2 | 1300 | X | RECORD | `test`.`t1` | a | 6 | 4 | 2 | 1 |
    | 1299:6:4:2 | 1299 | X | RECORD | `test`.`t1` | a | 6 | 4 | 2 | 1 |
    +------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
    2 rows in set (0.00 sec)
   

5. 開啟purge執行緒，purge操作會清理掉之前標記刪除的物理記錄，然而在step3 和step4上已經在這條記錄上加了記錄鎖，記錄被清掉了，對應的鎖記錄也需要做處理，InnoDB會嘗試將鎖繼承給下一條記錄，我們來看看鎖繼承的邏輯，呼叫函式lock_rec_inherit_to_gap：

    for (lock = lock_rec_get_first(lock_sys->rec_hash, block, heap_no);
         lock != NULL;
         lock = lock_rec_get_next(heap_no, lock)) {
   
            if (!lock_rec_get_insert_intention(lock)
                && !((srv_locks_unsafe_for_binlog
                      || lock->trx->isolation_level
                      <= TRX_ISO_READ_COMMITTED)
                     && lock_get_mode(lock) == LOCK_X)) {
   
                    lock_rec_add_to_queue(
                            LOCK_REC | LOCK_GAP | lock_get_mode(lock),
                            heir_block, heir_heap_no, lock->index,
                            lock->trx, FALSE);
            }
    }
   

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

   • 鎖型別為插入意向鎖
   • srv_locks_unsafe_for_binlog開啟且鎖型別為X鎖
   • 鎖對應事務的隔離級別小於等於RC且鎖型別為X鎖

   由於當前的隔離級別為RC，並且REPLACE INTO操作加的是X鎖，因此鎖沒有被相鄰記錄繼承，我們從INNODB_LOCKS系統表中也可以發現這一點：

    mysql> select * from information_schema.innodb_locks;
    Empty set (0.00 sec)
   

6. 喚醒第二個replace 操作（正在等待X鎖），執行插入操作成功；
7. 喚醒第一個replace 操作，由於已經完成duplicate key檢測，插入成功。

修復

從上述邏輯可以看出，當purge執行緒被啟用後，記錄和記錄鎖物件都被移除了，purge操作悄悄的破壞了InnoDB的加鎖協議。

修復的方法也比較簡單，InnoDB認為只可能加S鎖來維持一致性約束，因此當記錄被物理刪除時，只有S型別的鎖才被繼承。但對於REPLACE這樣的操作，加的是X型別的鎖，這種鎖型別必須也要考慮進去，將其繼承給下一條記錄。Alexey已經將patch push到percona server，改動也就一行，可以參考Percona的 補丁。

問題三：事務可見性導致的唯一鍵“失效”

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

建立測試表：create table t1 (a int primary key, b int unique key) engine = innodb;

session 1:
mysql> insert into t1 values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)

session 2:
mysql> delete from t1;
Query OK, 1 row affected (0.00 sec)

session 1:

mysql> insert into t1 values (2,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 2 |
+---+------+
2 rows in set (0.00 sec)

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也遵循相同的邏輯。