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