mysql 幻读

烧脑

1
2
3
4
5
create table `t_bitfly` (
`id` bigint(20) not null default '0',
`value` varchar(32) default null,
primary key (`id`)
);
Session A Session B
start transaction; start transaction;
select * from t_bitfly;
empty set
insert into t_bitfly values (1, ‘a’);
select * from t_bitfly;
empty set
commit;
select * from t_bitfly;
empty set
insert into t_bitfly values (1, ‘a’);
error 1062 (23000): duplicate entry ‘1’ for key 1

以为表里没有数据,其实数据已经存在了,傻乎乎的提交后,才发现数据冲突了

Session A Session B
start transaction; start transaction;
select * from t_bitfly;
1 a
insert into t_bitfly values (2, ‘b’);
select * from t_bitfly;
1 a
commit;
select * from t_bitfly;
1 a
update t_bitfly set value=‘z’;
Rows matched: 2 Changed: 2 Warnings: 0
(怎么多出来一行)

第一次读取出一行,做了一次更新后,另一个事务里提交的数据就出现了。也可以看做是一种幻读?