关于mysql的gap lock解决幻读问题的一些小实验

测试条件:

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版权协议,转载请附上原文出处链接和本声明。