何谓 脏读、幻读、不可重复读-20201020
case1 脏读#即 看到别的事务中未提交的数据。哈哈
查看当前的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
脏读的隔离级别必须是 读未提交，因此修改当前的隔离级别
mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
session1 修改数据 不提交，确认一下会话2 是否可以看到会话1的修改呢？
|
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tab01 set n1='AAA' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | AAA | 22:12:01 |
+------+------+----------+
|
session2 确认一下数据
|
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | AAA | 22:12:01 |
+------+------+----------+
|
哈哈，会话2居然可以看到会话1未提交的数据，这就是所谓的脏读
case2 不可重复读
会话1确认数据
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 22:12:01 |
+------+------+----------+
1 row in set (0.00 sec)
会话2修改数据未提交
mysql> update tab01 set n1='AAA' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
会话1再次确认数据
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | AAA | 22:12:01 |
+------+------+----------+
奇了个怪，数据无故变更。哈哈 ，不可重复读
case3 幻读
会话1确认当前数据
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | AAA | 22:12:01 |
+------+------+----------+
1 row in set (0.00 sec)
会话2插入一条数据
mysql> insert into tab01 values(2,'bbb',now());
Query OK, 1 row affected (0.02 sec)
会话1再次确认数据
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | AAA | 22:12:01 |
| 2 | bbb | 23:08:50 |
+------+------+----------+
奇了个怪，无故变成2条数据，哈哈 此为幻觉 即 幻读
case4 可重复读
修改当前的隔离级别未 可重复读
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
会话1开始一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
会话1确认当前数据
mysql> select * from tab01 where id=1;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 22:12:01 |
+------+------+----------+
1 row in set (0.00 sec)
会话2开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
会话2修改数据
mysql> update tab01 set n1='AAA' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.27 sec)
会话2确认数据已变更
mysql> select * from tab01 where id=1;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | AAA | 22:12:01 |
+------+------+----------+
会话1再次确认数据
mysql> select * from tab01 where id=1;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 22:12:01 |
+------+------+----------+
哈哈 还是原来的aaa。奇了个怪 会话2已修改数据并且以commit，但是数据怎么没变更，哈哈 可重复读取 即 可重复读哦
明白了不？
脏读 根源 update
幻读 根源 insert
创建时间：2021-12-22 10:40
넶浏览量：0
