MySQL并发事务问题与事务隔离级别揭秘

本文为《MySQL归纳学习》专栏的第十四篇文章，同时也是关于《MySQL事务》知识点的第二篇文章。

相关文章：

MySQL探秘：解码事务、分布式事务与长事务的奥秘

欢迎阅读《MySQL并发事务问题与事务隔离级别揭秘》。并发事务是MySQL中一个至关重要的部分，然而它往往带来一些棘手的问题。那么，如何解决这些问题呢？本篇文章将引导你了解到事务隔离级别的重要性，并详细介绍事务隔离的实现方式。同时，为了避免长事务给数据库带来的负担，我们还将深入探讨事务的启动方式。

事务机制

关系型数据库是需要遵循ACID规则的，分别介绍如下。

A( Atomic ）原子性：即事务要么全部做完，要么全部都不做。只要其中一个操作失败，就认为事务失败，需要回滚。
C ( Consistency ）一致性：数据库要一直处于一致的状态。
I ( Isolation ）独立性：并发的事务之间不会互相影响。
D ( Durability ）持久性：一旦事务提交后，它所做的修改将会永久地保存在数据库中。

在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

并发事务存在的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

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

隔离级别

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
串行：我的事务尚未提交，别人就别想改数据。

这4种隔离级别，并行性能依次降低，安全性依次提高。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在第一个 select 执行时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

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

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

“可重复读”的应用场景？

我们来看一个数据校对逻辑的案例。假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。

视图

在 MySQL 里，有两个“视图”的概念：

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

一致性视图何时会被创建呢？

事务有三种启动方式，如果使用 start transaction with consistent snapshot 这个命令，则一致性视图是在执行该命令时创建。另外两种方式下，一致性视图是在执行第一个快照读语句时创建的。

事务隔离的实现

理解了事务的隔离级别，我们再来看看事务隔离具体是怎么实现的。这里我们展开说明“可重复读”。

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。这里涉及到一个概念——undo log（回滚日志）。

例如

(1)当你 delete 一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2)当你 update 一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
(3)当年 insert 一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。即当系统里没有比这个回滚日志更早的 read-view 的时候，认为该回滚日志不需要。（InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。简单来说就是对比 row trx_id 来判断的，后续内容会详细讲解。）

在学习连接器知识点的时候，我们知道连接分为长连接和短连接。事务同样也存在长事务，是我们不提倡使用的。

MySQL5.6之前，回滚日志是跟数据字典一起放在共享表空间 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。甚至出现过数据只有 20GB，而回滚段有 200GB 的库。

为什么回滚段被清理，但文件大小不会变小？

文件的存放分为两个部分：数据部分和指针部分，数据部分包含了实际的数据记录，而指针部分包含了指向数据记录的指针。

当事务回滚时，回滚段中的指针部分被删除，但数据部分仍然占用磁盘空间。这些空间不会立即被重用，而是会保留为已分配状态，以便在以后的写入操作中使用。

当新的数据需要被写入时，MySQL会寻找未使用的空闲空间来存储新数据，而不是分配新的空间。这样做是为了避免频繁的磁盘分配操作，提高性能。

然而，回滚日志文件的大小并不会因为回滚段中的数据被清理而减小。这是因为MySQL引擎在设计上选择了延迟空间重用的策略，即保留已分配空间，不会立即释放磁盘空间。

如果您需要缩小回滚日志文件的大小，目前的解决方法是重建表空间。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数。

查看 undolog 的命令

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.01 sec)

事务的启动方式

在上文《MySQL探秘：解码事务、分布式事务与长事务的奥秘》介绍长事务时，提到了其存在潜在的风险，实际应用时当然建议你避免使用，为此你有必要了解 MySQL 的启动方式，避免误用。

MySQL 的事务启动方式有以下几种：

显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

很多语言的第三方库都是使用连接池维持可复用的长连接来保持与 MySQL 的链接，需注意，长连接并不意味着长事务，需要判断是否将 autocommit 设置成了0。建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

你可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

关于事务的启动，还有一个必须了解的知识点——事务 ID（transaction id，缩写为 trx_id）。

基于已知的事务启动方式，何时分配事务ID呢？

下面这段内容是我一开始以为的，后来发现不对，希望大家不要犯这个错误。

我们在命令行中输入 begin 开始事务时，MySQL 并不会立马给此事务分配一个事务 ID，直到开始事务后发出第一个 SQL 语句后，事务 ID 才会出现。如果我们在命令行输入 start transaction with consistent snapshot 这个命令，MySQL 则立马会给此事务分配一个事务 ID。

真正的答案是这样的：启动事务不代表就会分配事务 ID。从MySQL5.7开始，所有的事务都以只读模式开启。只有当在事务中执行到DML语句才会分配trx_id，即只有update / insert / for update 等修改类语句才会分配trx_id。