【MySQL高阶】26.事务(1)

1. 事务

1.1 什么是事务

事务是把一组SQL语句打包成为一个整体，在这组SQL的执行过程中，要么全部成功，要么全部失败，这组SQL语句可以是一条也可以是多条。

再来看一下转账的例子，如图：

在这个例子中，涉及了两条更新语句：

# ================账户表==================== CREATE TABLE `account` ( `id` bigint PRIMARY KEY AUTO_INCREMENT, `name` varchar(255) NOT NULL, # 姓名 `balance` decimal(10, 2) NOT NULL # 余额 ); INSERT INTO account(`name`, balance) VALUES('张三', 1000); INSERT INTO account(`name`, balance) VALUES('李四', 1000); # ================更新操作=================== # 张三余额减少100 UPDATE account set balance = balance - 100 where name = '张三'; # 李四余额增加100 UPDATE account set balance = balance + 100 where name = '李四';

如果转账成功，应该有以下结果：

1. 张三的账户余额减少100，变成900，李四的账户余额增加了100，变成1100，不能出现张三的余额减少而李四的余额没有增加的情况；
2. 张三和李四在发生转账前后的总额不变，也就是说转账前张三和李四的余额总数为1000+1000=2000，转账后他们的余额总数为900+1100=2000；
3. 转账后的余额结果应当保存到存储介质中，以便以后读取；
4. 还有一点需要要注意，在转账的处理过程中张三和李四的余额不能因其他的转账事件而受到干扰；

以上这四点在事务的整个执行过程中必须要得到保证，这也就是事务的ACID特性，即：

1. Atomicity(原子性)：一个事务中的所有操作，要么全部成功，要么全部失败，不会出现只执行了一半的情况，如果事务在执行过程中发生错误，会回滚（ Rollback ）到事务开始前的状态，就像这个事务从来没有执行过一样；
2. Consistency(一致性)：在事务开始之前和事务结束以后，数据库的完整性不会被破坏。这表示写入的数据必须完全符合所有的预设规则，包括数据的精度、关联性以及关于事务执行过程中服务器崩溃后如何恢复；
3. Isolation(隔离性)：数据库允许多个并发事务同时对数据进行读写和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务可以指定不同的隔离级别，以权衡在不同的应用场景下数据库性能和安全，后面的小节会详细介绍；
4. Durability(持久性)：事务处理结束后，对数据的修改将永久的写入存储介质，即便系统故障也不会丢失。

1.2 为什么要使用事务

事务具备的ACID特性，也是我们使用事务的原因，在我们日常的业务场景中有大量的需求要用事务来保证。支持事务的数据库能够简化我们的编程模型, 不需要我们去考虑各种各样的潜在错误和并发问题，在使用事务过程中，要么提交，要么回滚，不用去考虑网络异常，服务器宕机等其他因素，因此我们经常接触的事务本质上是数据库对ACID模型的一个实现，是为应用层服务的。

1.3 怎么使用事务

要使用事务那么数据库就要支持事务，在MySQL中支持事务的存储引擎是InnoDB，可以通过show engines;语句查看：

• 通过以下语句可以完成对事务的控制：

  • START TRANSACTION或BEGIN开始一个新的事务；

  • COMMIT提交当前事务，并对更改持久化保存；

  • ROLLBACK回滚当前事务，取消其更改；

  • SET autocommit禁用或启用当前会话的默认自动提交模式，autocommit是一个系统变量可以通过选项指定也可以通过命令行设置--autocommit[={OFF|ON}]

• 演示开启一个事务，执行修改后并回滚

# 开启事务 mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 在修改之前查看表中的数据 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 1000.00 | | 2 | 李四 | 1000.00 | +----+--------+---------+ 2 rows in set (0.00 sec) # 张三余额减少100 mysql> UPDATE account set balance = balance - 100 where name = '张三'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 李四余额增加100 mysql> UPDATE account set balance = balance + 100 where name = '李四'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 在修改之后，提交之前查看表中的数据，余额已经被修改 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 900.00 | | 2 | 李四 | 1100.00 | +----+--------+---------+ 2 rows in set (0.00 sec) # 回滚事务 mysql> rollback; Query OK, 0 rows affected (0.00 sec) # 再查询发现修改没有生效 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 1000.00 | | 2 | 李四 | 1000.00 | +----+--------+---------+ 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 | 张三 | 1000.00 | | 2 | 李四 | 1000.00 | +----+--------+---------+ 2 rows in set (0.00 sec) # 张三余额减少100 mysql> UPDATE account set balance = balance - 100 where name = '张三'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 李四余额增加100 mysql> UPDATE account set balance = balance + 100 where name = '李四'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 在修改之后，提交之前查看表中的数据，余额已经被修改 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 900.00 | | 2 | 李四 | 1100.00 | +----+--------+---------+ 2 rows in set (0.00 sec) # 提交事务 mysql> commit; Query OK, 0 rows affected (0.01 sec) # 再查询发现数据已被修改，说明数据已经持久化到磁盘 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 900.00 | | 2 | 李四 | 1100.00 | +----+--------+---------+ 2 rows in set (0.00 sec)

• 默认情况下MySQL启用事务自动提交，也就是说每个语句都是一个事务，就像被START TRANSACTION和COMMIT包裹一样，不能使用ROLLBACK来撤销执行结果；但是如果在语句执行期间发生错误，则自动回滚；
• 自动提交：
  • 执行即生效，无法回滚（ROLLBACK）。
  • 每一条语句就是一个完整的事务。
• 手动提交：
  • 修改只是暂时的，对当前会话可见（在隔离级别允许的情况下），对其他会话不可见。
  • 必须显式执行COMMIT才能永久保存数据。
  • 如果发现操作有误，可以执行ROLLBACK撤销自上次提交以来的所有修改。
  • 注意：当你关闭数据库连接时，未提交的事务通常会自动回滚！
• 通过SET autocommit设置自动与手动提交

# 查看当前的事务提交模式 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | # ON表示自动提交模式 +---------------+-------+ 1 row in set, 1 warning (0.02 sec) # 设置为手动提交(禁用自动提交) mysql> SET AUTOCOMMIT=0; # 方式一 mysql> SET AUTOCOMMIT=OFF; # 方式二 Query OK, 0 rows affected (0.00 sec) # 再次查看事务提交模式 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | # OFF表示关闭自动提交，此时转为手动提交 +---------------+-------+ 1 row in set, 1 warning (0.00 sec)

• 手动提交模式下，提交或回滚事务时直接使用commit或rollback

# 查看事务提交模式，确定自动提交已关闭 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | # OFF表示关闭自动提交，此时转为手动提交 +---------------+-------+ 1 row in set, 1 warning (0.00 sec) # 查询表中现在的数据 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 900.00 | | 2 | 李四 | 1100.00 | +----+--------+---------+ 2 rows in set (0.00 sec) # 张三余额减少100 mysql> UPDATE account set balance = balance - 100 where name = '张三'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 在修改之后查看表中的数据，余额已经被修改 mysql> select * from account; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | 张三 | 800.00 | # 比原来的减少了100 | 2 | 李四 | 1100.00 | +----+------+---------+ 2 rows in set (0.00 sec) # 回滚事务 mysql> rollback; Query OK, 0 rows affected (0.00 sec) # 再查询是被修改之后的值，发现修改没有生效 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 900.00 | | 2 | 李四 | 1100.00 | +----+--------+---------+ 2 rows in set (0.00 sec) # 上一个事务已回滚，接下来重新执行更新操作，让张三余额减少100 mysql> UPDATE account set balance = balance - 100 where name = '张三'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 在修改之后查看表中的数据，余额已经被修改 mysql> select * from account; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | 张三 | 800.00 | # 比原来的减少了100 | 2 | 李四 | 1100.00 | +----+------+---------+ 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 | 张三 | 800.00 | | 2 | 李四 | 1100.00 | +----+------+---------+ 2 rows in set (0.00 sec)

• 通过SET autocommit设置自动与自动提交

# 查看当前的事务提交模式 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | # 手动提交模式 +---------------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> SET AUTOCOMMIT=1; # 方式一 mysql> SET AUTOCOMMIT=ON; # 方式二 Query OK, 0 rows affected (0.00 sec) # 再次查看事务提交模式 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | # ON表示自动提交模式 +---------------+-------+ 1 row in set, 1 warning (0.00 sec)

注意：

只要使用START TRANSACTION或BEGIN开启事务，必须要通过COMMIT提交才会持久化，与是否设置SET autocommit无关。

2. InnoDB 和 ACID 模型

ACID模型是一组数据库设计原则，强调业务数据的可靠性，MySQL的InnoDB存储引擎严格遵循ACID模型，不会因为软件崩溃和硬件故障等异常导致数据的不完整。在ACID的实现过程中涉及到一些系统变量和相关知识点在这里先列出来，后面我们要逐步讲解：

• Atomicity(原子性)：原子性方面主要涉及InnoDB的事务开启与提交，我们之前做过详细讲解与回顾

  • 设置autocommit[={OFF|ON}]系统变量，开启和禁用事务是否自动提交.
  • 使用START TRANSACTION或BEGIN语句开启事务；
  • 使用COMMIT语句提交事务；
  • 使用ROLLBACK语句回滚事务。

• Consistency(一致性)：一致性主要涉及InnoDB内部对于崩溃时数据保护的相关处理，相关特性包括：

  • InnoDB存储引擎的双写缓冲区doublewrite buffer；InnoDB存储引擎专题中已经介绍过

  • InnoDB存储引擎的崩溃恢复，备份与恢复专题中讲解。

• Isolation(隔离性)：隔离方面主要涉及应用于每个事务的隔离级别，相关特性包括:

  • 通过SET TRANSACTION语句设置事务的隔离级别；
  • InnoDB存储引擎的锁，锁可以在INFORMATION_SCHEMA系统库和Performance Schema系统库中的data_locks和data_lock_waits表查看，后面的小节会详细讲解；

• Durability(持久性)：持久性涉及MySQL与特定硬件配置的交互，可能性取决于CPU、网络和存储设备的性能，由于硬件环境比较复杂，所以无法提供固定的操作南，只能根据实际环境进行测试得到最佳的性能，相关特性包括:

  • InnoDB存储引擎的双写缓冲区doublewrite buffer；
  • innodb_flush_log_at_trx_commit系统变量的设置；
  • sync_binlog系统变量的设置；
  • innodb_file_per_table系统变量的设置；
  • 存储设备(如磁盘驱动器、SSD或RAID磁盘阵列)中的写缓冲区；
  • 存储设备中由电池支持的缓存。
  • 运行MySQL的操作系统，特别是对fsync()系统调用的支持；
  • 不间断电源UPS (uninterruptible power supply)，保护所有运行MySQL服务器和数据存储设备的电力供应；
  • 备份策略，例如备份的频率和类型，以及备份保留周期；
  • 分布式环境中数据中心之间的网络连接。

• 需要重点说明的是，事务最终要保证数据的可靠和一致，也就是说ACID中的Consistency(一致性)是最终的目的，那么当事务同时满足了Atomicity(原子性)，Isolation(隔离性)和Durability(持久性)时，也就实现了一致性。

3. 如何实现原子性

在一个事务的执行过程中，如果多条DML语句顺利执行，那么结果最终会写入数据库；如果在事务的执行过程中，其中一条DML语句出现异常，导致后面的语句无法继续执行或即使继续执行也会导致数据不完整、不一致，这时前面执行的语句已经对数据做了修改，如果要保证一致性，就需要对之前的修改做撤销操作，这个撤销操作称为回滚rollback，如下图所示：

• 那么回滚操作是如何实现的呢？回滚过程中依据的是什么呢？在InnoDB专题中介绍过UndoLog的作用和原理，我们大致回顾一下，在事务执行每个DML之前，把原始数据记录在一个日志里，做为回滚的依据，这个日志称为Undo Log(回滚日志或撤销日志)，在不考虑缓存和刷盘的条件下，执行过程如下所示：

• 当需要回滚操作时，MySQL根据操作类型，在Insert Undo链或Update Undo链中读取相应的日志记录，并反向执行修改，使数据还原，完成回滚。

• 通过Undo Log实现了数据的回滚操作，这时就可以保证在事务成功的时候全部的SQL语句都执行成功，在事务失败的时候全部的SQL语句都执行失败，实现在原子性。

4. 如何实现持久性

提交的事务要把数据写入(持久化到)存储介质，比如磁盘。在正常情况下大多没有问题，可是在服务器崩溃或突然断电的情况下，一个事务中的多个修改操作，只有一部分写入了数据文件，而另一部分没有写入，如果不做针对处理的话，就会造成数据的丢失，从而导致数据不完整，也就不能保证一致性。

在真正写入数据文件之前，MySQL会把事务中的所有DML操作以日志的形式记录下来，以便在服务器下次启动的时候进行恢复操作，恢复操作的过程就是把日志中没有写到数据文件的记录重新执行一遍，保证所有的需要保存的数据都持久化到存储介质中，我们把这个日志称为Redo Log(重做日志)；生成重做日志是保证数据一致性的重要环节。在持久化的处理过程中，还包括缓冲池、Doublewrite Buffer(双写缓冲区)、Binary Log(二进制日志) 等知识点。