事务的4种隔离级别演示

1.read uncommitted：读未提交

set global transaction isolation level read uncommitted;

（每次事务1修改事务的时候，事务2需要关掉从新打开，才能生效事务1开启的事务）

事务一:

复制代码
mysql> use db2
Database changed
mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |     500 |
|  2 | lisi     |    1500 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET balance = balance - 500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET balance = balance + 500 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
复制代码

事务2：

复制代码
mysql> use db2
Database changed
mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |     500 |
|  2 | lisi     |    1500 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)
复制代码

查看把钱给你了然后事务1回滚了事务二在进行查看就和以前前一样了

 

2.read committed：读已提交（Oracle）

事务1：

复制代码
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = 1000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET balance = balance - 500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET balance = balance + 500 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.03 sec)
复制代码

事务二：

复制代码
mysql> use db2;
Database changed
mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |     500 |
|  2 | lisi     |    1500 |
+----+----------+---------+
2 rows in set (0.00 sec)
复制代码

会出现不可重复读现象和幻读

3. repeatable read：可重复读（MySQL默认）

事务1：

复制代码
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = 1000;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET balance = balance - 500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET balance = balance + 500 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.04 sec)
复制代码

事务2：

复制代码
mysql> use db2
Database changed
mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |     500 |
|  2 | lisi     |    1500 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
|  2 | lisi     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |     500 |
|  2 | lisi     |    1500 |
+----+----------+---------+
2 rows in set (0.00 sec)
复制代码

事务1提交后事务二不提交的情况下进行查询读取的是上面一样的数据（可重复读取）

事务2提交后在进行查询才是事务1提交过来的数据

4. serializable ： 串行化

事务1：

复制代码
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET balance = balance - 500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET balance = balance + 500 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.06 sec)
复制代码

事务2：

复制代码
mysql> use db2;
Database changed
mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |     500 |
|  2 | lisi     |    1500 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | NAME     | balance |
+----+----------+---------+
|  1 | zhangsan |       0 |
|  2 | lisi     |    2000 |
+----+----------+---------+
2 rows in set (25.42 sec)
复制代码

当事务1提交之后事务二才会显示出来

如果事务1不提交事务2进行查询不会查询出东西一直在哪里等待事务1的提交

（事务1提交后事务2马上会执行出来）

事务2光标一直在等待 

posted @   魔光领域  阅读(40)  评论(0)  编辑  收藏  举报
相关博文：
· 事务的四大特征和事务隔离级别介绍
· 事务_基本演示和事务_默认自动提交&手动提交
· 事务隔离级别演示
· 事务-隔离级别介绍、隔离级别演示
· 事务_事务隔离级别演示1和事务_事务隔离级别演示2
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示