MySQL 事务深度解析

一、事务 ACID

1.1 简单介绍

原子性（Atomicity）

  • 也就是说这一串指令要么全部成功 要么全部失败
  • 这点通过 begin 事务 commit 提交 rollback 回滚实现
  • 其实就是 undo log 实现的

一致性（Consistency）

  • 事务操作前后，数据满足一致性的约束，不会出现中间数据的情况
  • 通过锁来实现，是在其他三个性质之上保证的

隔离性（Isolation）

  • 多个事务执行时不会互相干扰
  • 通过 MVCC 机制实现的

持久性（Durability）

  • 事务处理之后，是要落盘的，而且即使系统故障数据也不会丢失
  • 这点通过 log 日志实现 而且还是 redo log

1.2 总结

  • 原子性通过 Undo Log 实现，保证事务要么全部成功要么全部回滚
  • 隔离性通过 MVCC 和锁机制实现，保证并发事务互不干扰
  • 持久性通过 Redo Log 和 WAL 机制保证，确保事务提交后即使宕机也不会丢失
  • 一致性是事务执行前后数据库满足业务和约束规则的最终状态，它依赖于原子性、隔离性、持久性以及应用层的正确逻辑共同保证

二、SQL 的隔离级别

2.1 读未提交（RU - Read Uncommitted）

特性

一个事务还没提交时，它做的变更就能被别的事务看到（可以读到别人没有提交的事务，等同于考试的时候可以直接看到别人试卷的答案）

可以读到别人没有提交的事务（脏读）

2.2 读提交（RC - Read Committed）

特性

一个事务提交之后，它做的变更才会被其他事务看到（你提交试卷之后我才能看到你的答案）

只能读到已经提交的数据

实现原理

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

  1. 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图
  2. 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图

2.3 可重复读（RR - Repeatable Read）

特性

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的（始终相信自己最开始见到的数据是不会变化的，可以重复读取到最开始的值。这个其实底层是靠 undo log 的回滚指针不停往前找的）

一个事务执行过程中看到的数据，总是和事务开始时看到的一致，即使其他事务提交了修改，当前事务仍然看到旧值，底层依赖 undo log 的版本链

实现原理

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值（这就是 undo log 的作用，可以用于回滚。undo log（撤销日志）在数据库管理系统中用于实现回滚操作，确保数据的完整性和一致性）。

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

2.4 串行化（S - Serializable）

特性

顾名思义是对于同一行记录，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行（串行就是排队等待，排成一行）

所有事务串行执行，读加读锁，写加写锁，后来的事务必须等待前一个事务完成

三、并发问题

3.1 脏读

就是一个事务读到了另外一个事务没有提交的数据。当然，这不是问题，主要是可能你另外的事务读到你修改之后的数据之后你又回滚了。那么我们的事务读到的不就是过期的数据吗? 这种现象被称为脏读。

简单的说，就是我们的事务可以读取到其他事务没有提交的结果

总结：读到了其他事务未提交的数据

3.2 不可重复读

也就是说，一个事务内多次读取同一个数据，如果前后两次读取到的数据不是一样的，那么这就是不可重复读现象。

简单的说，就是我们的事务中可以读取到其他事务提交之后的结果

总结：同一事务多次读取同一行结果不一致

3.3 幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

总结：同一事务多次范围查询，行数发生变化

四、MySQL 特殊处理

4.1 如何解决幻读

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的”间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁(Gap Lock)。

顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁

4.2 ⭐间隙锁和临键锁

现在你知道了，数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

跟间隙锁存在冲突关系的，是”往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

如果有其他的事务想要在这块区域的记录上插入就会被阻塞住，同时生成一个意向锁挂在表上，表示之后的事务不用尝试插入了，直接进入等待队列中就完事了，直到该事务提交事务之后才会唤醒这些等待阻塞的事务。

临键锁（Next-Key Lock）

间隙锁和行锁合称 next-key lock，也就是临键锁，每个 next-key lock 是前开后闭区间。

也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的(容易造成死锁，其实间隙锁和写锁，读锁和写锁的关系是一样的，都是互斥的

核心要点

  • 临键锁本质上是 InnoDB 实现 FOR UPDATE（或 DELETE / UPDATE）时的索引级锁保护机制
  • 临键锁就是 for update 当前读

4.3 间隙锁的生效时机

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这也是现在不少公司使用的配置组合。

如果读提交隔离级别够用，也就是说，业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁）

4.4 ⭐到底解决幻读了没有

只能说很大程度上解决了幻读的问题，但是没有完全解决，要不然要串行化干嘛。

第一个幻读场景

第一个发生幻读现象的场景，主要是 update 这个已经插入了的数据，然后你再 select，就会发现原来 select 不到的数据查询到了。

虽然你看不到这条记录，但是可以修改 update，而且在修改完之后再次查询就查得到了。因为 update 首先会加临键锁查询到最新的数据，然后再修改，将该记录的 trx_id 改为自己的，所以之后再使用普通的 select 语句也可以查询的到了，于是就发生了幻读。

第二个幻读场景

第二个发生幻读现象的场景，之前出现过，其实就是 select 一遍之后，另外一个事务插入了一条数据，然后我们再 select ... for update 就会出现问题。

因为虽然是有临键锁的保护，但是如果在保护之前就进行插入并且提交了，那也无法解决幻读问题。

建议

建议是开启事务之后立刻执行 select ... for update 这类当前读的语句，充分利用到临键锁解决幻读问题的优势

总结

InnoDB 的默认机制（RR + 临键锁 / for update）：

  • 能防止事务开始后修改或删除已有行导致的幻读
  • 能防止事务开始后新行插入到索引范围内导致的幻读
  • 但事务开始前已提交的新行，仍可能造成幻读
  • 完全防幻读 → 只能用 SERIALIZABLE

对比表格

场景 操作 结果
修改/删除已存在行 SELECT … FOR UPDATE + UPDATE 防止被修改 → 阻塞，幻读消失
插入新行到索引范围 SELECT … FOR UPDATE + next-key lock 防止插入 → 阻塞，幻读消失
事务开始前已提交新行 SELECT … FOR UPDATE 无法锁住 → 幻读可能发生
完全防幻读 SERIALIZABLE + SELECT 所有幻读消失

实验验证

RR 但是必须要使用 for update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

mysql> select * from mvcc for update;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
+----+------+
7 rows in set (0.00 sec)

-- 另一个事务尝试删除
mysql> delete from mvcc where id = 7;
waiting...

S 不需要使用 for update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
mysql> begin;
mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
+----+------+

-- 另一个事务尝试删除
mysql> delete from mvcc where id = 6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

RR 但是没有使用 for update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
mysql> begin;
mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
+----+------+

-- 另一个事务插入并提交
mysql> insert into mvcc values(7, 7);
Query OK, 1 row affected (0.00 sec)

-- 当前事务 update 后可以看到新行
mysql> update mvcc set c = 999 where id = 7;
Query OK, 1 row affected (0.00 sec)

mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |  999 |
+----+------+

五、Read View（读视图）

5.1 MySQL 中视图 View 的概念

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

  1. view - 它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样

  2. InnoDB 在实现 MVCC 时用到的一致性读视图 - 即 consistent read view。用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现

这里一定要注意是一致性读，而不是一致性写。因为它这个只适合快照读，当前读（也就是 select ... for update）或者写（也就是 update），还是会读取最新的，也就是当前读，会读取到当前 m_ids 之外的数据

5.2 ⭐视图数组的概念

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在”活跃”的所有事务 ID。”活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）（所以其实一致性视图 read view 是一个数组

5.3 ⭐row trx_id

而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

这个视图数组把所有的 row trx_id 分成了几种不同的情况。对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

  1. 如果落在绿色部分 - 表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的

  2. 如果落在红色部分 - 表示这个版本是由将来启动的事务生成的，是肯定不可见的

  3. 如果落在黄色部分 - 那就包括两种情况

    • a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见
    • b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见

5.4 ⭐ReadView 在 MySQL 中底层的数据结构

这里只介绍重要的四个字段，如果我们看源码就会发现还有很多辅助用的字段，比如 m_n_ids 就是表示当前活跃事务的个数。

重要的字段如下：

  • creator_trx_id - 指的是创建该 Read View 的事务的 ID
  • m_ids - 指的是创建 Read View 时，当前数据库中还活跃的事务的 ID 列表（活跃是未提交）
  • min_trx_id - 指的是在创建 Read View 时，当前活跃事务的最小 ID 值
  • max_trx_id - 指的是全局事务中最大的事务 ID 加 1 (也就是当前数据库应该给下一个事务的 ID 值)

其实这个最大最小的范围，可以理解为是左闭右开的。

六、MVCC（多版本并发控制）

6.1 快照读

SQL 读取的数据是快照版本【可见版本】，也就是历史版本，不用加锁，普通的 SELECT 就是快照读

6.2 当前读

SQL 读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改

UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE 都是当前读

6.3 ⭐事务的 ID

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的（有主键那味了）。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。（这个更新版本的操作和 ES 挺像的）

也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的 row trx_id。

图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。

（Transaction ID: trx_id 的全称是 Transaction Identifier，即事务标识符。通过追踪每行数据的 trx_id，数据库能够确保在读取数据时只返回对应于某个事务快照的数据，从而保持数据的一致性和隔离性。）

undo log 的位置

你可能会问，前面的文章不是说，语句更新会生成 undo log（回滚日志）吗？那么，undo log 在哪呢？

实际上，图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。(所以实际上不会存储那么多的数据)

总结

  • 每个事务的 trx_id 唯一递增
  • 每条记录有多个版本，每个版本的 row trx_id = 生成它的事务 ID
  • 更新操作生成新版本，旧版本由 undo log 支持快照读
  • 数据可见性由 MVCC + Read View 决定
  • 旧版本是逻辑存在，物理上通过 undo log 计算得到

6.4 ⭐MVCC 具体怎么实现的

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种用于数据库管理系统的并发控制机制，旨在允许多个事务同时读取和写入数据，而不互相阻塞。

在 MySQL 数据库 InnoDB 存储引擎中，用 undo Log 来实现多版本并发控制(MVCC)，当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据【快照读】

实验示例 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

mysql> begin;
mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    5 |
|  5 |    5 |
+----+------+

mysql> update mvcc set c = 0 where id = c;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    5 |
|  5 |    5 |
+----+------+

-- 使用当前读
mysql> SELECT * FROM mvcc WHERE id=1 FOR UPDATE;
+----+------+
| id | c    |
+----+------+
|  1 |    2 |
+----+------+

mysql> commit;

另一个事务

1
2
mysql> update mvcc set c = c + 1;
Query OK, 5 rows affected (0.00 sec)

实验示例 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> begin;
mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+

mysql> update mvcc set c = c + 1;
Query OK, 5 rows affected (0.00 sec)

mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    2 |
|  2 |    3 |
|  3 |    4 |
|  4 |    5 |
|  5 |    6 |
+----+------+

七、长事务

7.1 事务的两种启动方式

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

  1. 显式启动事务语句 - begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback

  2. set autocommit=0 - 这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

建议

因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务

commit work and chain 语法

有的开发同学会纠结多一次交互的问题。对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 begin，减少了语句的交互次数。

如果你也有这个顾虑，我建议你使用 commit work and chain 语法。

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

示例

1
2
3
4
5
6
// 使用 COMMIT WORK AND CHAIN 之后，即便 autocommit 为 1，数据库也会自动开启新的事务，不再需要你手动执行 BEGIN
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT WORK AND CHAIN;
UPDATE users SET balance = balance + 100 WHERE id = 2;
COMMIT;

7.2 事务启动的时机

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。

如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。（怪不得之前会加上一个后缀 with consistent snapshot）

begin/start transaction 开始的时候 read view 还没有创建

事务 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
+----+------+

mysql> select * from mvcc;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
+----+------+

事务 2

1
2
3
4
5
mysql> insert into mvcc values(6, 6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mvcc values(7, 7);
Query OK, 1 row affected (0.00 sec)

7.3 危害

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库

7.4 ⭐怎么查询长事务

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

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

7.5 ⭐怎么解决长事务

这个问题，我们可以从应用开发端和数据库端来看。

从应用开发端来看

  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（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便