(bugfix in 5.6.21) DUPLICATES IN UNIQUE SECONDARY INDEX BECAUSE OF FIX OF BUG#68021

(bugfix in 5.6.21) duplicates in unique secondary index because of fix of bug#68021

在mysql5.6.21版本裡，fix了這樣一個有趣(坑爹)的bug,影響5.6.12之後及5.6.21之間的版本

innodb: with a transaction isolation level less than or equal to read committed, gap locks were not taken when scanning a unique secondary index to check for duplicates. as a result, duplicate check logic failed allowing duplicate key values in the unique secondary index.

參考的bug号為：

http://bugs.mysql.com/bug.php?id=68021 (由于fix bug#68021導緻的上述問題)

http://bugs.mysql.com/bug.php?id=73170 (在bug#73170中移除了對應的fix)

根據上述資訊，我有意識的在裡面加了些debug sync點，并重制了該過程

root@sb 03:45:16>select * from t1 where b >=7;

+—-+——+——+

| a | b | c |

| 7 | 8 | 9 |

| 16 | 7 | 8 |

| 26 | 7 | 8 |

3 rows in set (0.00 sec)

可以看到，這裡b為uk，卻插入了兩條相同的記錄。

root@sb 03:45:27>show create table t1;

+——-+————————————————————————————————————————————————————————————–+

| table | create table |

| t1 | create table `t1` (

`a` int(11) not null,

`b` int(11) default null,

`c` int(11) default null,

primary key (`a`),

unique key `b` (`b`)

) engine=innodb default charset=utf8 |

1 row in set (0.00 sec)

root@sb 10:34:57>check table t1;

+——-+——-+———-+——————————————+

| table | op | msg_type | msg_text |

| sb.t1 | check | warning | innodb: index “b” is marked as corrupted |

| sb.t1 | check | error | corrupt |

2 rows in set (0.15 sec)

簡單的解釋下，我使用的是read commit隔離級别。

step 1:

在某個session 執行flush tables tbname for export….這會使purge操作停下來

step 2:

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

step 3:

插入記錄，包含唯一索引記錄uk1，由于step2的記錄還在（沒被purge）,是以需要檢查唯一性，在函數row_ins_scan_sec_index_for_duplicate中，根據隔離級别在記錄上加s not gap 鎖.唯一性檢查後commit mini transaction

step 4

和step 3 類似，另外一個session也插入uk1， 同樣加上s not gap鎖，并commit mini transaction

step 5

兩個session現在可以進行插入，因為受block x鎖限制，插入過程是順序的。但兩次插入都能成功，原因是在做插入鎖檢查時，會檢查相鄰記錄是否存在lock_x | lock_gap | lock_insert_intention)相沖突的鎖， 而gap鎖和not gap的s鎖是不沖突的(參考lock_rec_has_to_wait)， 是以兩次插入都能順利進行下去。

ref: btr_cur_optimistic_insert->lock_rec_insert_check_and_lock

fix:

官方的fix是把對bug#68021的更新檔給恢複掉。也就是說，在檢查duplicate key時，總是加gap s 鎖，也就是lock_ordinary s鎖

這樣過程歸納為：

session 1 hold lock_ordinary s lock

session 2 hold lock_ordinary s lock

session 1 insert record…conflict, enqueue （lock_x | lock_gap | lock_insert_intention) ——> wait

session 2 insert record…conflict, enqueue lock_x | lock_gap | lock_insert_intention ——> dead lock happen

如上描述，這會有一定的幾率發生死鎖，并且通常會讓人摸不着頭腦。。。當然，死鎖肯定比讓二級索引corruption掉要好多了…