🪴 Obsidian Publish

MySQL 事务机制

13分钟阅读

Doc: 03.事务隔离：为什么你改了我还看不见？.pdf

事务的概念

事务是逻辑上的一组操作，要么都执行，要么都不执行。经典转账例子。

事务的特性

ACID 四大特性

  • 原子性（ Atomicity ） ：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  • 一致性（ Consistency ）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；也有表示为从一个合法状态转变为另一个合法状态。
  • 隔离性（ Isolation ）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；或者说，对于事务 a 来说，事务 b 要不在事务 a 前，要不在事务 a 后。
  • 持久性（ Durability ）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务的操作

手动提交事务

START TRANSACTION; // BEGIN TRANSACTION;
// ......
COMMIT;    // 提交事务
ROLLBACK;  // 回滚事务

回滚点

  • 设置回滚点 SAVEPOINT 名字
  • 回到回滚点 ROLLBACK TO 名字
  • RELEASE SAVEPOINT 明确地释放保留点。

START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个修饰符：
① READ ONLY ：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
② READ WRITE ：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
③ WITH CONSISTENT SNAPSHOT ：立刻启动一致性读。使用 START TRANSACTION 语句后，并非立刻开启事务，而是在第一条语句执行前再开启。但是可以配置该修饰符立刻开启事务，分配一个事务 ID。

自动提交事务

  • MySQL 默认为每一条 DML(增删改) 语句自动开启一个事务、自动提交事务。
  • SELECT @@autocommit; 其中， @@ 表示全局变量，1 表示开启，0 表示关闭。
  • SET @@autocommit = 0; 关闭自动提交事务，之后必须 commit; 才会提交任务并更新数据库。

隐式提交事务

  1. 当一个事务还没有 commit 或者 rollback ，又开启新事务时，则隐式提交上一个事务。
  2. 当 autocommit 为 0，手动将其更改为 1 时，隐式提交上一个事务。
  3. 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会隐式的提交上一个事务。

事务的实现原理

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性。
MySQL InnoDB 引擎使用 undo log(回滚日志) 来保证事务的原子性。
MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性。

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

一些并发概念

并发控制保证一致性。
并发进行一个调度的结果等价于一个串行调度，这种调度被称为可串行化调度。
冲突等价：如果到调度 a 可以通过一系列非冲突指令交换成 b，则称 a 和 b 调度是冲突等价的。不是所有的串行调度相互之间都冲突等价 MySQL 面试题。
冲突可串行化：若一个调度 s 与一个串行调度冲突等价，称调度 s 是冲突可串行化。

并发事务的问题

  • 脏读（Dirty read）:  事务 A 准备修改数据库数据，数据修改还没有提交时，事务 B 执行并将该数据读取出来，导致事务 B 读取的数据是“脏数据”。
  • 丢失修改（Lost to modify）:  指事务 A 修改了数据后，事务 B 也修改了数据，导致事务 A 修改的结果丢失。
  • 不可重复读（Unrepeatable read）:  指事务 A 内多次读同一数据期间，事务 B 修改该数据，最终导致事务 A 执行期间读取到的结果不同。
  • 幻读（Phantom read）:  事务 A 读取ｎ行数据过程中，事务 B 增加了一些数据，导致事务 A 读取过程中多了一些数据。
  • 写倾斜（Write Skew），即假如 x ， y 需要满足约束 x + y >= 0 ，初始时 x = -3 ， y = 5 ，事务 T1 先读 x 和 y ，然后事务 T2 读 x 和 y ，接着事务 T2 将 y 更新为 3 后提交，事务 T1 将 x 改为 -5 后提交，最终 x = -5 ， y = 3 不满足约束 x + y >= 0 。

不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

事务的隔离级别

四个隔离级别

SQL 标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
隔离级别脏读不可重复读幻读
READ-UNCOMMITTED√√√
READ-COMMITTED×√√
REPEATABLE-READ××√
SERIALIZABLE×××

MySQL 的默认隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。
我们可以通过 SELECT @@tx_isolation; 命令来查看，
MySQL 8.0 该命令改为 SELECT @@transaction_isolation;

mysql> SELECT @@transaction_isolation;
+--------------------------+
| @@tx_isolation    |
+--------------------------+
| REPEATABLE-READ  |
+--------------------------+

MySQL InnoDB 的 REPEATABLE-READ（可重读）并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是 Next-Key Locks。即，最终效果为解决了幻读的问题。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

🌈 拓展一下(以下内容摘自《MySQL 技术内幕：InnoDB 存储引擎(第 2 版)》7.7 章)：

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

设置事务的隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
#其中，隔离级别格式：
> READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别';
#其中，隔离级别格式：
> READ-UNCOMMITTED
> READ-COMMITTED
> REPEATABLE-READ
> SERIALIZABLE

如何避免长事务对业务的影响

首先，从应用开发端来看：

  1. 确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
  2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）

其次，从数据库端来看：

  1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警/或者 kill；
  2. Percona 的 pt-kill 这个工具不错，推荐使用；
  3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
  4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

反向链接