mysql 快照读 幻读_幻读“异常”引出的快照读创建点问题

原标题：幻读“异常”引出的快照读创建点问题

导读

重温快照度、当前读。

本文节选自叶金荣有赞专栏《乱弹MySQL》。

关于InnoDB在事务中何时创建read view，我在 InnoDB MVCC何时创建read view 这篇文章里已经说过， 在RC(read committed)隔离级别下是每次SELECT都会构造最新的read view，而在RR(repeatable read)隔离级别下是在事务中发起第一个SELECT时构造read view。

接下来我们来看一个幻读的案例。

先看运行环境：

[root@yejr.me]>s

...

Server version: 5.6.21-log MySQL Community Server (GPL)

...

[root@yejr.me]>select * from information_schema.GLOBAL_VARIABLES where

VARIABLE_NAME = 'innodb_locks_unsafe_for_binlog';

+--------------------------------+----------------+

| VARIABLE_NAME | VARIABLE_VALUE |

+--------------------------------+----------------+

| INNODB_LOCKS_UNSAFE_FOR_BINLOG | ON |

+--------------------------------+----------------+

[root@yejr.me]>select @@global.tx_isolation;

+-----------------------+

| @@global.tx_isolation |

+-----------------------+

| REPEATABLE-READ |

+-----------------------+

也就是在RR隔离级别下，又设置了 INNODB_LOCKS_UNSAFE_FOR_BINLOG=1，其效果相当于禁用GAP LOCK。换言之，也相当于是把隔离级别降到了RC，这时候会产生 幻读。

P.S，关于 INNODB_LOCKS_UNSAFE_FOR_BINLOG=1有几点提醒：

已经使用RR级别时，最好就不要再将该选项设置为1了，否则有可能会造成主从数据不一致(这时应该同时设置 binlog_format=ROW)

该选项设置为1时， 唯一键和外键约束检测依然会使用next-key lock

该选项 只能在启动时设置，且不能在运行过程中动态修改，而事务隔离级别则可以在运行过程中动态修改。从这方面出发，其实有需要的话，最好是自行调整隔离级别就好，而不是设置本选项

该选项从8.0版本开始已经被删除

接来看下这种情况下的幻读案例场景。

关于测试表的背景信息：

[root@yejr.me]> show create table t1G

CREATE TABLE `t1` (

`c1` int(10) unsigned NOT NULL DEFAULT 0,

`c2` int(10) unsigned NOT NULL DEFAULT 0,

PRIMARY KEY (`c1`),

KEY `c2` (`c2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8; 场景1

session1

session2

begin;

begin;

select * from t1 where c2 = 5;

...

| 5 | 5 |

select * from t1 where c2 = 5;

...

| 5 | 5 |

insert into t1 select 7,5;

commit;

select * from t1 where c2 = 5 for update;

...

| 5 | 5 |

| 7 | 5 |

select * from t1 where c2 = 5;

#去掉for update后，只能看到一条记录

...

| 5 | 5 |场景2

session1

session2

begin;

begin;

select * from t1 where c2 = 5;

...

| 5 | 5 |

select * from t1 where c2 = 5 for update;

...

| 5 | 5 |

insert into t1 select 7,5;

commit;

#不会被阻塞

select * from t1 where c2 = 5;

...

| 5 | 5 |

| 7 | 5 |

#看到两条记录，疑似发生了幻读？场景3

session1

session2

begin;

start transaction with consistent snapshot;

select * from t1 where c2 = 5;

...

| 5 | 5 |

insert into t1 select 7,5;

commit;

select * from t1 where c2 = 5 for update;

...

| 5 | 5 |

| 7 | 5 |

select * from t1 where c2 = 5;

#去掉for update后，只能看到一条记录

...

| 5 | 5 |

咦，这个案例看起来和场景1有点像？

注意到上面3个案例之间的区别了吗，我们来罗列一下：

场景1，session2的第一个select是在session1执行commit之后发起的，此时构建read view

场景2，session2的第一次读是select ... for update形式的，此时似乎没有创建read view

场景3，session2发起事务时直接加了 with consistent snapshot，也就是要求创建一致性快照读

看起来场景1和场景3，都是在事务提交前创建了read view

而场景2里，session2是在第二次的select才创建快照，而不是在第一次select ... for update时就创建read viw

上面其实已经提到了几个关键信息：

在RR隔离级别下，遇到第一个普通SELECT才会开始创建read view。而如果SELECT ... FOR UPDATE/FOR SHARE这样的，叫做当前读或加锁读，这种 是不会创建read view的

即便是 innodb_locks_unsafe_for_binlog=1的时候，在RR隔离级别下，事务中第一个普通SELECT创建的快照在整个事务过程中依然有效，这个选项的作用只是禁用了gap lock， 并不会影响RR级别的其他行为

发起事务时，如果加上 with consistent snapshot会立即创建read view，和事务启动后立刻发起普通SELECT相同效果

在RC隔离级别下，还是每次普通SELECT都会重新创建read view

好了，就酱。

Enjoy MySQL :)

InnoDB MVCC何时创建read view

System Variables#innodb_locks_unsafe_for_binlog，http://t.cn/AiT9ea9s

15.7.2.3 Consistent Nonlocking Reads，http://t.cn/AiTSEbPM

15.7.2.4 Locking Reads，http://t.cn/AiT9eHBn 最后，欢迎扫码订阅《乱弹MySQL》专栏，快人一步获取我最新的MySQL技术分享

责任编辑：