(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掉要好多了…