测试条件:
mysql版本:5.5.40
默认的REPEATABLE READ
//在5.5版本中,为tx_isolation
mysql> show variables like 'tx_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)
一个事务在CMD中执行,一个在Navicat中执行
建表如下
create talbe lockDemo(
id int not null,
fullname varchar(100) not null,
height int,
primary key(id)
)engine=innodb charset=utf8;
MVCC下的普通select的幻读问题
//事务1 //事务二
start transaction read write; start transaction read write;
mysql> select * from lockDemo;
+----+----------+--------+
| id | fullName | height |
+----+----------+--------+
| 1 | JackMa | 168 |
| 3 | PonyMa | 178 |
+----+----------+--------+
2 rows in set (0.00 sec)
insert into lockDemo values(5,"TonyMa",189);
commit;
mysql> select * from lockDemo;
+----+----------+--------+
| id | fullName | height |
+----+----------+--------+
| 1 | JackMa | 168 |
| 3 | PonyMa | 178 |
+----+----------+--------+
2 rows in set (0.00 sec)
//通过MVCC的方式解决了幻读的问题
//锁定读(注意要能使用索引,执行计划为全表扫描的话,就会把整个表锁起来,无法模拟出gap锁)
mysql> select * from lockDemo where id>3 for update;
+----+----------+--------+
| id | fullName | height |
+----+----------+--------+
| 5 | TonyMa | 189 |
+----+----------+--------+
1 row in set (0.00 sec)
//这个时候是能读取到新插入的记录的,那这个时候再插 入呢?就插入不了啦
insert into lockDemo values(7,"JonyMa",190);
//运行之后就会一直阻塞,然后等待锁定读释放锁之后
范围当前读下的幻读问题
//事务1 //事务二
start transaction start transaction
update lockDemo set height=180 where id>3;
mysql> insert into lockDemo values(7,"JonyMa",190);
//此时事务1获取了gap_lock,事务二会一直阻塞,然后 一定时间后直接超时报错了
select * form lockDemo where id>3;
mysql> select * from lockDemo where id>3;
+----+----------+--------+
| id | fullName | height |
+----+----------+--------+
| 5 | TonyMa | 180 |
+----+----------+--------+
1 row in set (0.00 sec)
关闭gap Lock来看看范围当前读下的幻读问题
修改配置文件之后重启mysql
[mysqld]
innodb_locks_unsafe_for_binlog=1
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | ON |
+--------------------------------+-------+
1 row in set (0.00 sec)
这个时候间隙锁已经关闭了,我们再重复一下之前的操作
//事务1 //事务2
strat transaction start transaction
update lockDemo set height=170 where id>3;
mysql> insert into lockDemo values(7,"JonyMa",189);
Query OK, 1 row affected (0.00 sec)
//居然没有阻塞,插入成功了!!!哦耶!!赶紧提交
commit;
update lockDemo set height=175 where id>3;
commit;
select * from lockDemo where id>3;
mysql> select * from lockDemo where id>3;
+----+----------+--------+
| id | fullName | height |
+----+----------+--------+
| 5 | TonyMa | 175 |
| 7 | JonyMa | 175 |
+----+----------+--------+
2 rows in set (0.00 sec)
问题就很明显了,在第二次的update定位要修改的记录时,它定位到了其他事务提交的记录,也就出现了幻影现象,那么关闭之后的快照读有没有受到影响呢?当然没有,快照读依靠readView和版本链实现
//事务1 //事务2
start transaction start transaction
mysql> select * from lockDemo where id>1;
+----+----------+--------+
| id | fullName | height |
+----+----------+--------+
| 3 | PonyMa | 178 |
| 5 | TonyMa | 175 |
| 7 | JonyMa | 175 |
+----+----------+--------+
3 rows in set (0.00 sec)
//新增记录并且提交
insert into lockDemo values(9,"FannyMa",199);
commit;
mysql> select * from lockDemo where id >1;
+----+----------+--------+
| id | fullName | height |
+----+----------+--------+
| 3 | PonyMa | 178 |
| 5 | TonyMa | 175 |
| 7 | JonyMa | 175 |
+----+----------+--------+
3 rows in set (0.00 sec)
可见,快照读的幻读问题依然可以通过MVCC的方式来解决,实验结束
版权声明:本文为weixin_44587956原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接和本声明。