唯一鍵是數據庫設計中常用的索引類型，主要用于約束數據，不允許出現重復的鍵值記錄。可以想象，如果唯一鍵約束失效了，將可能產生可怕的邏輯錯誤。本文主要討論下最近MySQL爆出來的兩個唯一鍵約束失效導致二級索引corruption的問題。 ## 問題一： 檢查重復鍵加鎖邏輯不當 影響版本：MySQL 5.6.21之前，5.6.12之后的版本 ### 介紹分析 在5.6.12之前的版本中，當插入一條帶唯一約束的記錄時，如果表上已經存在了這條記錄，或者有一條標記刪除的相同鍵值記錄時，就需要對這條記錄加S GAP （類型為LOCK_ORDINARY）鎖，即使你使用的是READ-COMMIT的隔離級別。因此有人report了[bug#68021](http://bugs.mysql.com/bug.php?id=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的?[補丁](https://github.com/percona/percona-server/pull/83)。 ## 問題三：事務可見性導致的唯一鍵“失效” 我們來看看另外一個在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也遵循相同的邏輯。