[MySQL Bug] bug#65111碎碎念-CSDN博客

changelog里的描述：

InnoDB: In a transaction using the REPEATABLE READ isolation level, an UPDATE or DELETE statement for an InnoDB table could sometimes overlook rows recently committed by other transactions. As explained in Section 14.3.9.2, “Consistent Nonlocking Reads”, DML statements within a REPEATABLE READ transaction apply to rows committed by other transactions, even if a query could not see those rows. (Bug #14007649, Bug #65111)

http://bugs.mysql.com/bug.php?id=65111

从bug#65111的描述来看，根据文档 http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html中的描述：

有两个以以START TRANSACTION WITH CONSISTENT SNAPSHOT开始的session1 和session2，session1 先于session2。如果session 2插入了rec1，并提交了事务，session 1虽然看不到新插入的数据rec1，但可以对其进行Update，并随后这些记录对sesion1变的可见。

bug#65111提供的test case显然不符合上述文档中的定义。

DROP TABLE IF EXISTS `update_bug`;
CREATE TABLE `update_bug` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `fk_id` int(11) DEFAULT NULL,
  `start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end_time` timestamp NULL DEFAULT NULL,
  KEY `ub_ix1` (`fk_id`,`end_time`),
  KEY `ub_ix2` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `update_bug` (fk_id, start_time, end_time) VALUES (1, '1999-12-31 23:59:59', '1999-12-31 23:59:59'), (1, '1999-12-31 23:59:59', NULL);

#Session 1
START TRANSACTION WITH CONSISTENT SNAPSHOT;

#Session 2
START TRANSACTION WITH CONSISTENT SNAPSHOT;
UPDATE update_bug SET end_time = '2000-01-01 00:00:02' WHERE fk_id = 1 AND end_time IS NULL;
INSERT INTO update_bug (fk_id, start_time, end_time) VALUES (1, '2000-01-01 00:00:02', NULL);
COMMIT;

#Session 1
UPDATE update_bug SET end_time = '2000-01-01 00:00:01' WHERE fk_id = 1 AND end_time IS NULL;
INSERT INTO update_bug (fk_id, start_time, end_time) VALUES (1, '2000-01-01 00:00:01', NULL);
COMMIT;

当把表update_bug上的索引移除时，则一切符合文档里的定义。

从官方的patch来看，问题应该出在QUICK_RANGE_SELECT::init_ror_merged_scan

backtrace如下

mysql_update (sql_update.cc:603)

|–>QUICK_ROR_INTERSECT_SELECT::reset(opt_range.cc:1485)

|–>QUICK_ROR_INTERSECT_SELECT::init_ror_merged_scan (opt_range.cc:1451, 1457)

|–>QUICK_RANGE_SELECT::init_ror_merged_scan

|–>handler::clone

由于这里session1的update的where条件为fk_id=1 and end_time IS NULL，因此可以使用索引ub_ix1来检索数据。

设置断点

b QUICK_RANGE_SELECT::init_ror_merged_scan

b row_search_for_mysql

b handler::clone

当做ROR Merge Scan时，需要拷贝一个新的handler，使用handler的clone成员函数，但在innodb里clone并没有做实现。file->prebuilt->select_lock_type并没有赋值给新的handler。

挂在table上的handler(LOCK_X)

(gdb) p ((ha_innobase *)(head->file))->prebuilt->select_lock_type

$16 = 3

clone的新handler(LOCK_NONE)

(gdb) p ((ha_innobase *)(file))->prebuilt->select_lock_type

$17 = 5

随后在检索数据时，使用了不同的select_lock_type

rr_quick(records.cc:344)

|–>QUICK_ROR_INTERSECT_SELECT::get_next(opt_range.cc:8436)

|–>QUICK_RANGE_SELECT::get_next(opt_range.cc:8711)

|–>handler::read_multi_range_first(sql/handler.cc:4378)

|–>handler::read_range_first(sql/handler.cc:4504)

|–>ha_innobase::index_read(ha_innodb.cc:6440)

|–>row_search_for_mysql

(gdb) p prebuilt->select_lock_type

$4 = 3

|–> QUICK_ROR_INTERSECT_SELECT::get_next(opt_range.cc:8459)

……

row_search_for_mysql

(gdb) p prebuilt->select_lock_type

$5 = 5

当select_lock_type=5时。执行的是一致性读，因此无法看到session2插入的记录。

在statement模式下，这可能导致主备数据不一致。

官方的patch是增加了ha_innobase::clone函数，并将之前的select_lock_type赋值给新handler。

patch地址见：

http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/3820

不过比较蛋疼的是，这个Bug引起了一个非常严重的bug（准确的讲，应该是激活了相应的code path）：

http://bugs.mysql.com/bug.php?id=65745

一条简单的update语句陷入无限循环，导致磁盘满，这个bug直接导致MySQL5.5.25版本被废弃。

已经有人分析了bug#65745，感兴趣的详见 http://hedengcheng.com/?p=278

这里做一些简述：

bug#65745提供的test case，打上上一个patch后就可以重现

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id1 int NOT NULL,
  id2 int NOT NULL,
  a int,
  b int,
  PRIMARY KEY (id1,id2),
  KEY (id1, a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` VALUES (1,1,NULL,1);
INSERT INTO `t1` VALUES (2,2,1,NULL);
INSERT INTO `t1` VALUES (2,3,2,NULL);
INSERT INTO `t1` VALUES (2,4,3,NULL);
INSERT INTO `t1` VALUES (2,5,4,NULL);
INSERT INTO `t1` VALUES (2,6,NULL,2);

bug的原因在于错误的判断是否修改了用来scan的索引记录(used_key_is_modified)

官方Patch：

http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/3875