建表SQL
CREATE TABLE `user` (
`id` int(11) NOT NULL COMMENT 'id',
`balance` int(255) DEFAULT NULL COMMENT '余额',
`version` int(255) DEFAULT NULL COMMENT '版本号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;
当前的MYSQL的事务隔离级别为,REPEATABLE-READ
select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
尝试触发幻读
SESSION1 |
SESSION2 |
start transaction; |
start transaction; |
select * from user; |
|
|
insert into user values(1, 0, 0); |
|
commit; |
select * from user; |
|
commit; |
|
select * from user; |
|
- session1开启事务,并且执行一次检索,记录为空
- session2开启事务,并且插入一条新记录,提交
- session1执行检索,未读取到session2插入的新记录,记录还是为空
- session1提交,再次检索可以成功读取到session2插入的新记录
说明 MYSQL 的 REPEATABLE-READ , 其实是可以解决幻读的问题
但是,不一定能彻底的解决幻读的问题
SESSION1 |
SESSION2 |
start transaction; |
start transaction; |
select * from user where version = 1; |
|
|
insert into user values(2, 0, 1); |
|
commit; |
select * from user where version = 1; |
|
update user set balance = 1 where version = 1; |
|
select * from user where version = 1; |
|
commit; |
|
- session1开启事务,并且根据条件
version = 1
执行一次检索,记录为空
- session2开启事务,并且插入一条符合session1检索条件的新记录,提交
- session1再次尝试根据条件
version = 1
进行检索,未读取到session2插入的新记录,记录还是为空
- session1尝试修改session2新插入的记录,修改成功
- session1执行修改成功后,再次根据条件
version = 1
进行检索,发现读取到了session2新插入的记录
2 个赞