54、【数据库技术】【MySQL】事务

1、概述

• 事务（Transaction），在数据库领域中可以理解成对数据操作的一个执行单元。

• 事务是一个整体，由一条或者多条 SQL 语句组成，这些 SQL 语句要么都执行成功，要么都执行失败，只要有一条 SQL 出现异常，整个操作就会回滚，导致整个事务执行失败。

• 事务所涉及的 SQL 语句都是 DML 即数据操纵语句，像纯 DQL 这样的不涉及事务。

• DML 涉及到数据的修改（增、删、改），而在计算机领域中，无可避免地会遇到并发、网络传输等来自各个方面的各种问题，如果处理不当会导致数据库中的数据出现偏差，带来很多麻烦。用事务的形式组织这些 DML，如果事务中的所有语句都准确无误执行，提交事务即最终确认对数据的修改；而一旦其中任何一个语句出现问题，不提交事务并且需要恢复原状。DQL 是查询，仅进行查询操作不涉及数据修改，所以没有必要以事务的形式组织纯 DQL。

• 明确一点，只是说在仅进行查询操作的情况下，不用涉及事务。而不是说事务中不能有查询操作！事务中可以有查询操作，但是需涉及到相应的 DML 操作！

• 回滚：即在事务运行的过程中发生了某种故障，事务不能继续执行，将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

2、事务的典型案例

• 讲数据库的事务，最经典的案例就是银行账户转账。

• 有两个银行账户分别是 A 和 B，其中 A 账户中有1000元，B 账户中有200元，A 账户需向 B 账户转账300元。

• 假设银行的数据库用如下表存储账户的金额信息：

CREATE TABLE tb (
id VARCHAR(10) PRIMARY KEY,
salary INT NOT NULL);

INSERT INTO tb (id,salary)
VALUES ('A',1000),('B',200);

• 进行转账，对应数据库应执行的 SQL 语句：

-- 语句1
UPDATE tb
SET salary=salary-300
WHERE id='A';

-- 语句2
UPDATE tb
SET salary=salary+300
WHERE id='B';

针对上述的语句1和语句2，如果两个都正确执行了，没有问题；但是如果语句1或语句2中的任何一个语句在执行过程中出现差错，比如说，成功执行语句1后，因为网络连接出现问题，语句2没有成功执行，最终，账户 B 没有收到转账且账户 A 中还少了300元。这样的话，直接导致现实中的经济损失。所以，需要以数据库的事务来组织上面的两个 SQL 语句，一旦出现问题，终止事务，及时回滚，恢复原状。

3、MySQL 中事务自动提交

• MySQL 默认将一条 DML 语句就作为一个事务看待，并且自动提交即自动确认生效。

• MySQL 中查看配置自动提交的变量：

SHOW VARIABLES LIKE 'autocommit';

MySQL-默认事务自动提交-ON 表示自动提交是开启的

• 将默认自动提交设置为手动提交：

SET @@autocommit=OFF;

一旦经过了上面的设置后，如果希望执行的 DML 语句生效就必须使用COMMIT来手动提交事务。

4、MySQL 中事务手动提交

• 在没有将autocommit设置为OFF之前，也可以手动提交事务，前提是需要显式开启事务。

• 开启事务之后，其后面执行的所有 DML 语句都不会立即生效，直到提交事务；如果出现问题，回滚事务，恢复到开启事务前的状态。也就是说，事务的结局有两种，要么被提交，要么被撤销（回滚）即COMMIT与ROLLBACK二选一。

MySQL-事务的结局

-- 显式开启一个事务
-- 这个语句显式地标记一个事务的起始点
START TRANSACTION;
-- 或者直接使用
BEGIN;

-- DML
······

-- 表示提交事务，即提交事务的所有操作使前面所有的 DML 生效，具体地说，就是将事务中所有对数据库的更新都写到物理的磁盘上中，事务正常结束。
COMMIT;

-- 表示撤销（回滚）事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始之前的状态。
ROLLBACK；

• 举例：转账

CREATE TABLE my_tb1 (
id CHAR(1) PRIMARY KEY,
salary INT NOT NULL DEFAULT 0
);

INSERT INTO my_tb1 (id,salary)
VALUES ('A',1000),('B',300);

-- 事务成功，提交

START TRANSACTION;

UPDATE my_tb1
SET salary=salary-200
WHERE id='A';

UPDATE my_tb1
SET salary=salary+200
WHERE id='B';

COMMIT;

-- 事务失败，回滚

START TRANSACTION;

UPDATE my_tb1
SET salary=salary-200
WHERE id='A';

UPDATE my_tb1
SET salary=salary+200
WHERE id='B';

ROLLBACK;

5、事务的特性

MySQL-事务的特性

6、MySQL 事务隔离级别

• 根据事务的隔离性，各个事务之间是相互独立的，但是现实中往往会因为并发访问等因素导致“隔离”出现问题，这个时候可以通过设置隔离级别来解决这些问题。

• 关于并发访问等带来的问题，并不是仅在数据库层面就能解决的；同样的道理，在数据库层面，也并不是只有设置隔离级别这一种方式解决问题。

• 并发访问带来的问题：

1、脏读：读取到涉及未提交事务的数据。读取到的数据称为“脏数据”。
比如：账户 A 向 账户 B 转账500元，账户 B 读取到自己的账户余额中有500元，当 B 想用这些钱去购物结账的时候，出现问题，导致“转账”事务回滚，此时 B 再次查看账户的时候，这500元没有了。

2、不可重复读：在一个事务中（大前提，在一个事务中），对同一个数据，多次读取的结果不一致。

3、幻读：在一个事务中，虽然是基于查询到的结果设计并执行 DML 但是出现问题，也就是查询出的结果无法支撑后续的操作。
比如：希望通过一个事务向表中插入一条数据，查询到表中没有与之重复的数据，但是执行插入操作的时候，却发现该数据已经存在。

• MySQL 中事务隔离级别（由低到高）：

最低级别：读未提交（read uncommitted）
解释：进行查询操作，未提交事务引发的数据修改的结果也能被查询到。
能规避的问题：无法规避任何一种问题

读已提交（read committed）
解释：进行查询操作，未提交事务引发的数据修改的结果不能被查询到，只能查询到已提交事务引发的数据修改的结果。
能规避的问题：脏读
备注：Oracle 数据库、Microsoft SQL Server 数据库默认使用的隔离级别。

可重复读（repeatable read）
解释：在“读已提交”的基础上，
能规避的问题：脏读、不可重复读
备注：MySQL 数据库默认使用的隔离级别

最高级别：串行化（serializable）
解释：
能规避的问题：脏读、不可重复读、幻读

MySQL-事务隔离级别

• 事务隔离级别越高，能够规避的问题越多，数据的安全性越强但是读写效率会随之降低。需结合实际情况选择合适的隔离级别。另外，由并发带来的问题，不仅仅需要在数据库层面解决，在数据库层面，也不只有隔离级别这一种解决方案。

6.1、MySQL 查看与修改事务隔离级别

• 查看隔离级别

MySQL 中有两类事务隔离级别，一类是全局事务隔离级别，一类是当前会话事务隔离级别。
全局事务隔离级别：对所有会话都生效；
当前会话事务隔离级别：只对当前会话有效；

会话：与 MySQL 服务建立一个连接称为一个会话。

-- 查看全局事务隔离级别
SELECT  @@global.transaction_isolation;

-- 查看当前会话事务隔离级别
SELECT @@transaction_isolation;
SELECT @@session.transaction_isolation;

-- 查看全局事务隔离级别
-- MySQL 8.0 版本无法使用，提示没有这个值
SELECT @@global.tx_isolation;

-- 查看当前会话事务隔离级别
-- MySQL 8.0 版本无法使用，提示没有这个值
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;

MySQL-默认的事务隔离级别

• 修改事务隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

6.2、“脏读”演示

• 为了演示“脏读”现象，先将 MySQL 全局的事务隔离级别调整至READ UNCOMMITTED。

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

• 仍以转账为例。新（修改完隔离级别后必须重新建立会话才有效）打开两个命令行窗口即两个会话，来模拟一种并发场景，一个称为Session 1，另一个称为Session 2。

1、在Session 2中开启一个事务，执行“转账”操作，暂不提交：

MySQL-脏读演示1

2、此时，在Session 1中执行查询操作，查询结果如下：

MySQL-脏读演示2

可以看到，未提交的事务引起的数据变化也会被查询到。如果Session 2最终提交了事务，也没什么影响，但是如果Session 2因某种原因必须回滚事务并且Session 1也没有重新查询确认，此时Session 1出现的问题就是“脏读”，即读到了“脏数据”。

6.3、“不可重复读”演示

• 为了演示“不可重复读”现象，先将 MySQL 全局的事务隔离级别调整至READ UNCOMMITTED或READ COMMITTED。

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

• 仍以转账为例。新打开两个命令行窗口即两个会话，来模拟一种并发场景，一个称为Session 1，另一个称为Session 2。

1、在Session 2中开启事务，先执行一次查询：

MySQL-不可重复读演示1

2、在Session 1中开启事务，执行“转账”，并提交：

MySQL-不可重复读演示2

3、在Session 2中已开启的事务中再次执行查询：

MySQL-不可重复读演示3

在同一个事务中，读取到的数据不一样，这就是“不可重复读”，可能会感到奇怪，查询到不一样的不是很正常的吗，在下面会按照上面的步骤演示“可重复读”。

• 演示“可重复读”，首先将事务隔离等级调整至REPEATABLE READ或SERIALIZABLE，规避“不可重复读”。下面与“不可重复读”步骤一样：

MySQL-可重复读演示1 MySQL-可重复读演示2 MySQL-可重复读演示3 MySQL-可重复读演示4

可重复读：在一个事务中（大前提，在一个事务中），对同一个数据，多次读取的结果一致。

6.4、“幻读”演示

• 演示“幻读”，首先将事务隔离等级调整至READ UNCOMMITTED或READ COMMITTED或REPEATABLE READ。

• 以添加账户为例。新打开两个命令行窗口即两个会话，来模拟一种并发场景，一个称为Session 1，另一个称为Session 2。

1、在Session 1和Session 2中均开启事务。
2、Session 1准备向账户中添加一个id（主键）为 C 的账户，所以，先查询一下是否存在id为 C 的账户。

MySQL-幻读演示1

3、Session 2在Session 1通过查询确定后要插入数据之前，插入了一个id为 C 的数据，并提交事务。

MySQL-幻读演示2

4、等Session 1在插入数据时，发现出现错误，无法将数据插入。

MySQL-幻读演示3

避免幻读，需要将隔离级别提升到最高的SERIALIZABLE。一旦提示到这个级别，意味着，在一个事务中执行 DML 但未COMMIT或未ROLLBACK的时候，其他事务进行的所有操作（包括查询）都将被“阻塞”，直到那个事务执行了COMMIT或ROLLBACK。
SERIALIZABLE级别基本上是不去使用的，因为效率太低。还是那句话，因并发导致的问题，不应只在数据库层面解决，即使在数据库层面，也不止设置隔离级别这一种方案。