MySQL中的事务

最新推荐文章于 2024-03-24 21:53:13 发布

axinawang

最新推荐文章于 2024-03-24 21:53:13 发布

阅读量1.4k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/daqi1983/article/details/127645683

版权

MySQL 专栏收录该内容

17 篇文章 2 订阅

订阅专栏

事务处理

事务处理机制在应用程序开发过程中有着非常重要的作用，它可以保证在同一个事务中的操作具有同步性，从而让整个应用程序更加安全。

事务概述

现实生活中，人们经常会进行转账操作，转账可以分为转入和转出两部分，只有这两个部分都完成才认为转账成功。在数据库中，转账过程中的SQL语句，只要任意一条语句出现异常没有执行成功，就会导致两个账户的转账金额不同步，出现转账错误。MySQL中可以使用事务避免上述情况的发生。

MySQL中的事务必须满足4个特性，分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性是指一个事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功。

一致性是指事务将数据库从一个一致状态转变为下一个一致的状态。

隔离性是指当一个事务在执行时，不会受到其他事务的影响。

持久性是指事务一旦提交，对数据库中数据的修改就是永久性的。

事务的基本操作

MySQL中，用户执行的每一条SQL语句，默认都会当成单独的事务自动提交。如果想要将一组SQL语句作为一个事务，需要在执行这组SQL语句之前显式地开启事务，显式开启事务的语句如下。

START TRANSACTION;

开启事务之后，后续的每一条SQL语句将不再自动提交，用户想要提交时，需要手动提交事务。只有事务提交后，事务中的SQL语句才会生效。手动提交事务的语句具体如下。

COMMIT;

如果不想提交当前事务，还可以使用下列语句取消事务（即回滚），具体如下。

ROLLBACK;

需要注意的是，ROLLBACK语句只能针对未提交的事务执行回滚操作，已提交的事务是不能回滚的。当执行COMMIT或ROLLBACK后，当前事务就会自动结束。

例如，公司为了激励中层，为部门经理陈二和李四设立奖金，奖金总额固定为2000元，第一个月俩人奖金都为1000元，第二个月开始根据部门业绩调整俩人奖金，扣除业绩不好的经理的奖金奖励给业绩好的经理（奖金总额不变）。数据库管理员收到通知后具体操作步骤如下。

（1）数据库管理员想要先查询员工表emp中陈二和李四当前的信息，具体SQL语句如下所示。

SELECT * FROM emp WHERE ename='陈二' OR ename='李四';

+-------+-------+------+------+---------+------+--------+
| empno | ename | job  | mgr  | sal     | comm | deptno |
+-------+-------+------+------+---------+------+--------+
|  9566 | 李四  | 经理 | 9839 | 3995.00 | NULL |     20 |
|  9982 | 陈二  | 经理 | 9839 | 3450.00 | NULL |     10 |
+-------+-------+------+------+---------+------+--------+

（2）因为操作陈二和李四的数据时，需要确保操作要么都成功，要么都失败，数据库管理员需要在每次操作之前都开启事务。数据库管理员将员工表emp中陈二和李四的奖金都设置为1000，具体SQL语句如下所示。

# 开启事务
START TRANSACTION;
# 设置陈二的奖金
UPDATE emp SET comm=10000 WHERE ename='陈二';
# 设置李四的奖金
UPDATE emp SET comm=10000 WHERE ename='李四';

（3）为了保险起见，数据库管理员决定在提交修改信息之前，先查询修改后的信息，具体SQL语句及执行结果如下所示。

SELECT * FROM emp WHERE ename='陈二' OR ename='李四';

+-------+-------+------+------+---------+----------+--------+
| empno | ename | job  | mgr  | sal     | comm     | deptno |
+-------+-------+------+------+---------+----------+--------+
|  9566 | 李四  | 经理 | 9839 | 3995.00 | 10000.00 |     20 |
|  9982 | 陈二  | 经理 | 9839 | 3450.00 | 10000.00 |     10 |
+-------+-------+------+------+---------+----------+--------+

数据库管理员从查询出的员工信息看到，奖金信息修改错误，将奖金1000元设置为了10000元。

（4）数据库管理员庆幸还好没有提交事务，否则被查出来可能被算作重大工作失误。数据库管理员不想重新修改数据，决定撤销之前修改奖金的操作，并查询撤销操作后的数据，具体语句及执行结果如下所示。

回滚事务

ROLLBACK;

查看奖金

 SELECT * FROM emp WHERE ename='陈二' OR ename='李四';

+-------+-------+------+------+---------+------+--------+
| empno | ename | job  | mgr  | sal     | comm | deptno |
+-------+-------+------+------+---------+------+--------+
|  9566 | 李四  | 经理 | 9839 | 3995.00 | NULL |     20 |
|  9982 | 陈二  | 经理 | 9839 | 3450.00 | NULL |     10 |
+-------+-------+------+------+---------+------+--------+

（5）数据库管理员重新设置李四和陈二的奖金，对于此次数据修改数据库管理员觉得肯定不会出错，设置奖金后将事务进行提交，具体语句如下所示。

# 开启事务
START TRANSACTION;
# 设置陈二的奖金
UPDATE emp SET comm=1000 WHERE ename='陈二';
# 设置李四的奖金
UPDATE emp SET comm=1000 WHERE ename='李四';
# 提交事务
COMMIT;

（6）此时数据库管理员查询修改后的数据，具体语句如下所示。

mysql> SELECT * FROM emp WHERE ename='陈二' OR ename='李四';
+-------+-------+------+------+---------+---------+--------+
| empno | ename | job  | mgr  | sal     | comm    | deptno |
+-------+-------+------+------+---------+---------+--------+
|  9566 | 李四  | 经理 | 9839 | 3995.00 | 1000.00 |     20 |
|  9982 | 陈二  | 经理 | 9839 | 3450.00 | 1000.00 |     10 |
+-------+-------+------+------+---------+---------+--------+
2 rows in set (0.00 sec)

设置是否自动提交

MySQL中事务默认是自动提交，如果用户想要设置事务的自动提交方式，可以通过更改AUTOCOMMIT的值来实现。AUTOCOMMIT的值设置为1表示开启事务自动提交，设置为0表示关闭事务自动提交，如果想要查看当前会话的AUTOCOMMIT值，可以使用如下语句。

SELECT @@AUTOCOMMIT;

如果想要关闭当前会话事务的自动提交，可以使用以下语句。

set AUTOCOMMIT=0;

执行上述语句后，用户需要手动执行提交（COMMIT）操作，事务才会提交，如果直接终止MySQL会话，MySQL会自动进行回滚。

事务的保存点

在回滚事务时，事务内的所有操作将都被撤销。如果希望只撤销事务内的部分操作，则可以借助事务的保存点实现。事务中创建保存点的语法格式如下所示。

SAVEPOINT 保存点名;

在事务中设置保存点后，可以将事务回滚到指定的保存点，事务中回滚到保存点的语法格式如下所示。

ROLLBACK TO SAVEPOINT 保存点名;

如果某个保存点不再使用，可以通过如下语法格式删除指定的保存点。

RELEASE SAVEPOINT 保存点名;

需要注意的是，一个事务可以创建多个保存点。一旦提交事务，事务中的保存点都会被删除。另外，如果事务回滚到某个保存点后，该保存点之后创建的其他保存点也会被删除。

下面接着通过设置员工奖金的案例演示事务保存点的使用，具体步骤如下。

（1）到了第2个月，数据库管理员需要根据部门业绩重新调整陈二和李四的奖金，调整奖金之前，数据库管理员对员工表emp中陈二和李四当前的奖金信息进行了查询，具体SQL语句如下所示。

SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';

+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1000.00 |
| 陈二  | 1000.00 |
+-------+---------+

（2）经过初步核算，本月需要将陈二的奖金增加200，李四的奖金减少200。本次事务中可能还需要对陈二和李四的信息进行修改，数据库管理员在本次修改后创建一个保存点，具体语句如下所示。

START TRANSACTION; -- 开启事务
UPDATE emp SET comm=comm+200 WHERE ename='陈二'; -- 设置陈二的奖金
UPDATE emp SET comm=comm-200 WHERE ename='李四'; -- 设置李四的奖金
SAVEPOINT  s1; -- 创建保存点s1

（3）数据库管理员修改奖金并创建好事务保存点后，为确保修改无误，想要查询员工表emp中陈二和李四当前的奖金信息，具体SQL语句如下所示。

SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';

+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  |  800.00 |
| 陈二  | 1200.00 |
+-------+---------+

（4）数据库管理员又收到通知，陈二的奖金修改为600，李四的奖金修改为1400，具体语句如下所示。

UPDATE emp SET comm=600 WHERE ename='陈二';   -- 设置陈二的奖金
UPDATE emp SET comm=1400 WHERE ename='李四'; -- 设置李四的奖金

（5）陈二对奖金调整不满，找老板理论。不久后数据库管理员又收到通知，需要将陈二和李四的奖金恢复到第一次修改后的结果。数据库管理员对事务进行了回滚，并在回滚后再次查询了陈二和李四当前的奖金信息，具体语句如下所示。

ROLLBACK TO SAVEPOINT s1; -- 回滚到保存点s1
SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';

（6）由于之前老板没有将根据业绩调整奖金的公式说太清楚，老板决定本月暂时先取消根据业绩调整奖金，通知数据库管理员将陈二和李四的奖金恢复到上个月的状态，数据库管理员庆幸操作数据之前开启了事务，此时回滚事务就可以恢复数据到最初的状态，要不然还要挨个去查数据，具体语句如下所示。

ROLLBACK; -- 回滚事务

SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';

+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1000.00 |
| 陈二  | 1000.00 |
+-------+---------+

事务的隔离级别

MySQL支持多线程并发访问，用户可以通过不同的线程执行不同的事务。为了保证多个事务之间互不影响，就需要为事务设置适当的隔离级别。MySQL中，事务有4种隔离级别，分别为READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交）、REPEATABLE READ（可重复读）和SERIALIZABLE（串行化）。

READ UNCOMMITTED

READ UNCOMMITTED是事务隔离级别中最低的级别，在该级别下的事务可以读取到其他事务中未提交的数据，这种读取方式也被称为脏读（Dirty Read）。

例如，数据库管理员收到通知将陈二的奖金减少200，但是在修改时候由于失误减少了2000，执行了下面的UPDATE语句做了奖金的修改。

UPDATE emp SET comm=comm-2000 WHERE ename='陈二';

数据库管理员庆幸说还好没提交事务，此时老板在他的客户端中对员工信息进行查看，如果老板客户端设置的隔离级别是READ UNCOMMITTED，就会读到数据库管理员事务中未提交的数据，发现数据库管理员修改错误的信息。

为了演示上述情况，首先开启两个命令行窗口，为方便分辨分别称为客户端A和客户端B。两个客户端都登录到MySQL数据库，并将操作的数据库切换为ems。准备完成后，按如下步骤进行操作。

1.设置客户端B中事务的隔离级别 MySQL的默认隔离级别是REPEATABLE READ（可重复读），该级别可以避免脏读，为了演示脏读，需要将客户端B中事务的隔离级别设置为READ UNCOMMITTED（读未提交），具体语句如下所示。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

上述语句中，SESSION表示当前会话，TRANSACTION就表示事务，ISOLATION表示隔离，LEVEL表示级别，READ UNCOMMITTED表示当前设置的隔离级别，上述语句执行成功后，使用SELECT语句查询事务的隔离级别，结果如下。

SELECT @@session.transaction_isolation;

2.演示脏读

数据库管理员收到通知需要修改员工陈二和李四当前的奖金，修改之前数据库管理员对陈二和李四当前的奖金信息进行了查询，具体如下。

SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';

+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1000.00 |
| 陈二  | 1000.00 |
+-------+---------+

数据库管理员在客户端A中开启事务，接着对陈二和李四的奖金进行了修改，具体语句及执行结果如下。

START TRANSACTION;
UPDATE emp SET comm=comm-200 WHERE ename='陈二';
UPDATE emp SET comm=comm+200 WHERE ename='李四';

需要注意的是，此时不要提交事务，因为如果提交事务就无法演示脏读的现象。

此时，老板打电话问数据库管理员陈二和李四的奖金是否修改好了，因为数据库管理员还没提交事务，就和老板说还没有修改好，老板刚好在电脑旁边，在客户端B中查询陈二和李四的奖金信息（老板是技术人员出身，会数据库相关的技术），具体如下所示。

SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';

从查询结果可以看出，客户端B能看陈二和李四的奖金修改过后的信息，这是由于客户端B的事务隔离级别较低，因此读取了客户端A中还没有提交的内容，出现了脏读的情况。

脏读演示完毕，为了下面讲解时数据不混乱，此处先在客户端A中执行“ROLLBACK;”命令进行事务回滚，让数据恢复到最初的值。

3.设置客户端B事务的隔离级别

老板觉得需要解决脏读的现象，让数据库管理员解决一下。为了防止脏读的发生，数据库管理员在客户端B中将事务的隔离级别设置为READ COMMITTED（读已提交），该隔离级别可以避免脏读，设置的语句及执行结果如下。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4.验证是否出现脏读

修改完隔离级别后，数据库管理员为了验证是否解决了脏读现象，首先在客户端B中查询陈二和李四的奖金信息，具体如下所示。

mysql> SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';
+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1000.00 |
| 陈二  | 1000.00 |
+-------+---------+
2 rows in set (0.00 sec)

接着数据库管理员在客户端A中开启事务，并修改陈二和李四的奖金，具体语句及执行结果如下。

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

mysql> UPDATE emp SET comm=comm-200 WHERE ename='陈二';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE emp SET comm=comm+200 WHERE ename='李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

数据库管理员修改员工表的数据后，在客户端B中查询陈二和李四的奖金信息，具体如下所示。

mysql> SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';
+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1000.00 |
| 陈二  | 1000.00 |
+-------+---------+
2 rows in set (0.00 sec)

通过对比两次查询结果可以发现，本次客户端B中并没有查询到客户端A中未提交的内容，说明READ COMMITTED隔离级别可以避免脏读。值得一提的是，脏读在实际应用中会带来很多问题，为了保证数据的一致性，在实际应用中几乎不会使用隔离级别READ UNCOMMITTED。

为了保证后续演示数据不混乱，在客户端A中执行“ROLLBACK;”命令进行事务回滚，使数据恢复到最初的值。

READ COMMITTED

MySQL中READ COMMITTED级别下，事务只能读取其他事务已经提交的内容，可以避免脏读现象，但是会出现不可重复读和幻读的情况。不可重复读是指在事务内重复读取别的线程已经提交的数据，由于多次查询期间，其他事务做了更新操作，出现多次读取的结果不一致的现象。

不可重复读并不算错误，但在有些情况下却不符合实际需求。例如，银行根据用户的余额送积分，余额小于500的送100积分，余额大于500的送500积分。银行在系统中开启事务A，生成余额在500以下人员清单时，刘一余额为300；接着在事务A中查询余额在500以上的人员清单，期间刘一存入了1000元，导致刘一同时在送100积分和送500积分的人员清单中。

通过修改员工奖金的案例演示不可重复读的情况，具体步骤如下。

1.演示不可重复读

老板想要查看陈二和李四当前的奖金信息，于是在客户端B中，开启事务进行查询，具体如下所示。

mysql> START TRANSACTION; -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';
+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1000.00 |
| 陈二  | 1000.00 |
+-------+---------+
2 rows in set (0.00 sec)

此时，数据库管理员同时根据本月的标准在客户端A中使用UPDATE语句修改陈二和李四的奖金信息，具体语句及执行结果如下所示。

mysql> UPDATE emp SET comm=comm-200 WHERE ename='陈二';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE emp SET comm=comm+200 WHERE ename='李四';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在数据库管理员修改了陈二和李四的奖金后，客户端B中老板在刚才的事务中又查询了一次陈二和李四的奖金信息，具体如下所示。

mysql> SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';
+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1200.00 |
| 陈二  |  800.00 |
+-------+---------+
2 rows in set (0.00 sec)

查询后老板发现陈二和李四的奖金信息两次查询结果不一致，觉得太奇怪了，一个事务中相同的查询语句查询出的结果却不一致。

上述情况演示成功后，将客户端B中的事务提交。

2.设置客户端B中事务的隔离级别

老板不希望在一个事务中看到的查询结果不一致，为了防止不可重复读的情况出现，老板安排数据库管理员对数据库进行优化。数据库管理员接到任务后，在客户端B中将事务的隔离级别设置为REPEATABLE READ（可重复读），设置的语句及执行结果如下所示。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

3.验证是否出现不可重复读

修改完隔离级别后，数据库管理员为了验证是否已经解决了不可重复读的现象，在客户端B中开启事务，并且对陈二和李四的奖金信息进行了查询，具体如下所示。

mysql> START TRANSACTION; -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';
+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1200.00 |
| 陈二  |  800.00 |
+-------+---------+
2 rows in set (0.00 sec)

接着数据库管理员在客户端A中使用UPDATE语句修改陈二和李四的奖金信息，具体语句及执行结果如下所示。

mysql> UPDATE emp SET comm=comm-200 WHERE ename='陈二';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE emp SET comm=comm+200 WHERE ename='李四';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

数据库管理员修改员工信息后，在客户端B中对陈二和李四的奖金信息进行查询，具体如下所示。

mysql> SELECT ename,comm FROM emp WHERE ename='陈二' OR ename='李四';
+-------+---------+
| ename | comm    |
+-------+---------+
| 李四  | 1200.00 |
| 陈二  |  800.00 |
+-------+---------+
2 rows in set (0.00 sec)

数据库管理员对比客户端B两次的查询结果，发现客户端B隔离级别修改为REPEATABLE READ后，查询的结果是一致的，并没有出现不同的数据，说明事务的隔离级别为REPEATABLE READ时，可以避免不可重复读的情况。

REPEATABLE READ

REPEATABLE READ是MySQL默认的事务隔离级别，它可以避免脏读、不可重复读。但理论上，该级别会出现幻读。

幻读又被称为虚读，是指在一个事务内两次查询中数据条数不一致，幻读和不可重复读类似，同样是在两次查询过程中，不同的是，幻读是由于其他事务做了插入记录的操作，导致记录数有所增加。不过MySQL的存储引擎通过多版本并发控制机制解决了该问题，将事务的隔离级别为REPEATABLE READ时可以避免幻读。

例如，银行根据用户的余额送积分，余额小于500的送100积分，余额大于500的送500积分。银行开启事务A生成余额在500以下人员清单时，刘一和陈二还没注册；事务A接着查询余额在500以上的人员清单期间，刘一和陈二同时进行了注册并分别存入了300元和1000元，导致刘一不在送100积分和送500积分的人员清单中，而同时注册的陈二却在送500积分的人员清单中。

通过插入员工案例来演示幻读的情况，具体步骤如下。

1.设置客户端B的隔离级别

将客户端B的事务隔离级别设置为READ COMMITTED，设置的语句及执行结果如下所示。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2.演示幻读

老板想要查看下当前公司奖金大于1500的员工信息，首先在客户端B中开启事务，并且查询奖金大于1500的员工信息，具体如下所示。

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

mysql> SELECT ename,comm FROM emp WHERE comm>1500;
+-------+---------+
| ename | comm    |
+-------+---------+
| 八戒  | 2000.00 |
+-------+---------+
1 row in set (0.00 sec)

此时数据库管理员刚好在客户端A中将刚入职的员工信息插入到员工表中，具体语句及插入结果如下所示。

INSERT INTO emp VALUES(9999,'悟空','人事',9982,3000,1800,40);

在数据库管理员插入了新入职的员工信息后，客户端B中老板在刚才的事务中又查询了一次奖金大于1500的员工信息，具体如下所示。

mysql> SELECT ename,comm FROM emp WHERE comm>1500;
+-------+---------+
| ename | comm    |
+-------+---------+
| 八戒  | 2000.00 |
| 悟空  | 1800.00 |
+-------+---------+
2 rows in set (0.00 sec)

老板发现第二次查询数据时比第一次查询时多了一条记录。

3.重新设置客户端B的隔离级别

幻读的现象并不能算是一种错误，但是老板不希望在一个事务中看到的查询结果不一致。为了防止幻读的情况出现，老板安排数据库管理员对数据库进行优化。数据库管理员接到任务后，为了防止出现幻读，将客户端B中的隔离级别设置为REPEATABLE READ，设置的具体语句如下所示。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4.验证是否出现幻读

修改完隔离级别后，数据库管理员为了验证是否已经解决了不可重复读的现象，首先在客户端B中开启一个事务，并且查询奖金大于1500的员工信息，具体如下所示。

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

mysql> SELECT ename,comm FROM emp WHERE comm>1500;
+-------+---------+
| ename | comm    |
+-------+---------+
| 八戒  | 2000.00 |
| 悟空  | 1800.00 |
+-------+---------+
2 rows in set (0.00 sec)

接着数据库管理员在客户端A中执行添加操作，具体语句及插入结果如下所示。

INSERT INTO emp VALUES(9977,'唐僧','人事',9982,4000,1900,40);

数据库管理员插入员工信息后，在客户端B中再次查询奖金大于1500的员工信息，具体如下所示。

mysql> SELECT ename,comm FROM emp WHERE comm>1500;
+-------+---------+
| ename | comm    |
+-------+---------+
| 八戒  | 2000.00 |
| 悟空  | 1800.00 |
+-------+---------+
2 rows in set (0.00 sec)

数据库管理员对比客户端B中的两次查询结果，发现客户端B设置隔离级别为REPEATABLE READ后，在同一个事务中两次的查询结果是一致的，并没有读取到其他事务新插入的记录，任务完成。说明设置事务的隔离级别为REPEATABLE READ可以避免幻读。

SERIALIZABLE

SERIALIZABLE是事务的最高隔离级别，它会在每个读的数据行上加锁，从而解决脏读、幻读、重复读的问题。这个级别，可能导致大量的超时和锁竞争的现象，因此也是性能最低的一种隔离级别。

老板觉得隔离级别太低会出现之前的脏读、不可重复读和幻读，那就把隔离级别调到最高，这样上述3种现象都可以避免。数据库管理员听完为老板演示了事务的隔离级别设置SERIALIZABLE后的导致的现象，具体步骤如下。

1.设置客户端B中事务的隔离级别

数据库管理员首先将客户端B中的隔离级别设置为SERIALIZABLE:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2.演示可串行化

接着数据库管理员在客户端B中开启事务，然后使用SELECT语句查询奖金大于1500的员工信息，查询结果如下。

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

mysql> SELECT ename,comm FROM emp WHERE comm>1500;
+-------+---------+
| ename | comm    |
+-------+---------+
| 八戒  | 2000.00 |
| 唐僧  | 1900.00 |
| 悟空  | 1800.00 |
+-------+---------+
3 rows in set (0.00 sec)

接着数据库管理员在客户端A中往数据表中插入数据，插入的语句如下所示。

INSERT INTO emp VALUES(9933,'沙僧','人事',9982,2000,1600,40);

客户端A执行插入语句后的效果如下图所示。

从上图中可以看出，客户端A中执行插入语句后，不是立即执行成功，而是光标一直在闪，一直在等待。此时，提交客户端B中的事务，客户端A中的插入操作会立即执行。如果客户端B一直未提交事务，客户端A的操作会一直等待，直到超时后，客户端A中出现如下提示信息。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上述提示信息表示锁等待超时，尝试重新启动事务。默认情况下，锁等待的超时时间为50秒。

虽然事务的隔离级别设置为SERIALIZABLE可以避免脏读、不可重复读和幻读的现象，但是对使用数据库时性能太差了，一般不会在实际开发中使用。

上机实践：图书管理系统中事务的应用

实践需求1：手动开启事务，首先删除数据表book原有的全部数据，然后向数据表book插入图书信息，图书信息如下表。

name	price	upload_time	borrower_id	borrow_time	state
Java基础入门（第3版）	59.00	CURRENT_TIMESTAMP	NULL	NULL	'0'
三国演义	69.00	CURRENT_TIMESTAMP	NULL	NULL	'0'
MySQL数据库入门	40.00	CURRENT_TIMESTAMP	1	'2021-08-06 11:16:05'	'1'
JavaWeb程序开发入门	49.00	CURRENT_TIMESTAMP	NULL	NULL	'0'
西游记	59.00	CURRENT_TIMESTAMP	NULL	NULL	'0'
水浒传	66.66	CURRENT_TIMESTAMP	NULL	NULL	'0'
唐诗三百首	39.00	CURRENT_TIMESTAMP	NULL	NULL	'0'
Python数据可视化	49.80	CURRENT_TIMESTAMP	NULL	NULL	'0'