mysql-事务-校验不可重复读和幻象读
问题描述
为了验证mysql在默念的隔离级别是不是解决了不可重复读和幻象读的问题
预备
- 不可重复读: 事务A读到了事务B已经提交的修改数据
- 幻象读: 事务A读到了事务B已经提交的新增数据
校验
查看mysql数据库默认隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set
验证是否解决不可重复读
- 事务A查询用户金额,此时开启事务B
- 事务B修改用户的金额,并提交事务
- 查看事务A中是否读取到事务B提交的修改
事务A | 事务B
mysql> start transaction; | mysql> start transaction;
Query OK, 0 rows affected | Query OK, 0 rows affected
|
mysql> select * from t_account; | mysql> select * from t_account;
+----+---------+--------+ | +----+---------+--------+
| id | user_id | amount | | | id | user_id | amount |
+----+---------+--------+ | +----+---------+--------+
| 1 | 1 | 23 | | | 1 | 1 | 23 |
+----+---------+--------+ | +----+---------+--------+
1 row in set | 1 row in set
|
| mysql> update t_account set amount=10 where user_id=1;
| Query OK, 1 row affected
| Rows matched: 1 Changed: 1 Warnings: 0
| mysql> select * from t_account;
| +----+---------+--------+
| | id | user_id | amount |
| +----+---------+--------+
| | 1 | 1 | 10 |
| +----+---------+--------+
| 1 row in set
| # 事务B提交修改
| mysql> commit;
| Query OK, 0 rows affected
mysql> select * from t_account; |
+----+---------+--------+ |
| id | user_id | amount | |
+----+---------+--------+ |
| 1 | 1 | 23 | |
+----+---------+--------+ |
1 row in set |
# 事务A也提交
mysql> commit;
Query OK, 0 rows affected
mysql> select * from t_account;
+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
| 1 | 1 | 10 |
+----+---------+--------+
1 row in set
可以发现,直至事务B提交了事务,在事务A中,也没有读取到事务B修改的值。只有事务A也提交事务,才能查询到修改的值,但这已经没有事务了
验证是否解决幻象读
- 事务A查询用户金额,此时开启事务B
- 事务B新增另一个用户的帐户,并提交事务
- 查看事务A中是否读取到事务B提交的新增用户的帐户的数据
事务A | 事务B
mysql> start transaction; | mysql> start transaction;
Query OK, 0 rows affected | Query OK, 0 rows affected
|
mysql> select * from t_account; | mysql> select * from t_account;
+----+---------+--------+ | +----+---------+--------+
| id | user_id | amount | | | id | user_id | amount |
+----+---------+--------+ | +----+---------+--------+
| 1 | 1 | 10 | | | 1 | 1 | 10 |
+----+---------+--------+ | +----+---------+--------+
1 row in set | 1 row in set
|
| mysql> INSERT INTO `dbl`.`t_account` (`user_id`, `amount`)
| VALUES ('2', '8');
|
| Query OK, 1 row affected
| mysql> select * from t_account;
| +----+---------+--------+
| | id | user_id | amount |
| +----+---------+--------+
| | 1 | 1 | 10 |
| | 3 | 2 | 8 |
| +----+---------+--------+
| 2 rows in set
|
| mysql> commit;
| Query OK, 0 rows affected
mysql> select * from t_account; |
+----+---------+--------+ |
| id | user_id | amount | |
+----+---------+--------+ |
| 1 | 1 | 10 | |
+----+---------+--------+ |
1 row in set |
可以发现,直至事务B提交了事务,在事务A中,也没有读取到事务B新增的帐户数据
结论
由上述验证,发现,mysql默认的隔离级别(repeatable read)确实解决了不可重复读和幻象读的问题