5 MYSQL 事务

事务的隔离性和回滚日志

1.事务的隔离性

事务的隔离级别包括:

1. 读未提交: read uncommitted，一个事务还没提交时，它做的变更就能被别的事务看到
2. 读提交: read committed，一个事务提交之后，它做的变更才会被其他事务看到
3. 可重复读: repeatable read，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
4. 串行化: 对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行(锁是在事务提交之后才释放的)。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

1. “可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图
2. 在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
3. “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念
4. “串行化”隔离级别下直接用加锁的方式来避免并行访问

MySQL 中数据的隔离级别由参数 transaction-isolation 配置

1.1 MVCC 与回滚日志

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

不同时刻启动的事务会有不同的 read-view。不同的 read-view 之间是不会相互影响的。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

在 mysql 的实现中，页头，以及每条记录的数据头如下:

 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

// storage/innobase/include/rem0rec.h
struct rec_header_t {
    uint32_t heap_no:13;      // 页内记录序号（0-8191）
    uint32_t status:3;        // 记录状态（REC_STATUS_ORDINARY等）
    uint32_t next_record:16;  // 下条记录相对偏移量
    uint32_t n_owned:4;       // 页目录槽拥有记录数
    uint32_t info_bits:4;     // 信息位（删除标志等）
    uint32_t min_rec_flag:1;  // 是否为最小记录（B+树非叶层）
    // ... 其他位域
};

// 伪代码表示记录布局
struct rec_t {
    rec_header_t header;      // 5字节头信息
    
    // 系统列（仅聚簇索引）
    trx_id_t trx_id;          // 6字节事务ID（MVCC）
    roll_ptr_t roll_ptr;      // 7字节回滚指针
    
    // 用户数据列
    col1_data_t col1;         // 列1数据
    col2_data_t col2;         // 列2数据
    // ...
    
    // 变长列偏移数组（可选）
    var_offset_t var_offsets[]; 
};

每个记录头都有一个 roll_ptr 指向这个事务对应的 undo_log。每个 undo_log 也有一个 roll_ptr 指向更早的 undo_log。

1
2
3
4
5
6
7
8

struct undo_record {
    undo_no_t  undo_no;          // undo log的序号，同一事务中递增
    trx_id_t   trx_id;           // 产生该undo记录的事务ID
    roll_ptr_t  prev_roll_ptr;   // 指向前一个版本的undo记录指针（构建版本链）
    table_id_t  table_id;        // 表ID
    uint32     type_cmpl;        // 操作类型及标志位（如插入、更新、删除等）
    undo_rec_data_t data[1];     // 被修改前的数据（列值）
};

为什么不要使用长事务

1. 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
2. 在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。
3. 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

1.2 事务提交与管理

autocommit: 是否自动提交事务

• =0: 关闭事务的自动提交，意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。如果是长连接，就可能导致意外的长事务。
• =1: 开始事务自动提交，事务启动需要显示使用 begin 或 start transaction配套的提交语句是 commit，回滚语句是 rollback。对于频繁使用事务的业务，可以使用 commit work and chain 语法，在事务提交时，自动开启一个新的事务，以减少 begin 语句的交互次数

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如

1

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

事务启动的时机

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

• 第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；
• 第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

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

从应用开发端来看：

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

2.事务的一致性读视图

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

1. 一个是 view，它是一个用查询语句定义的虚拟表
2. 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现

2.1 MVCC 和一致性视图

MVCC

1. InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id
2. 每行数据也都是有多个版本的，每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的row trx_id
3. 旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它

也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。下面是一个记录被多个事务连续更新后的状态

• 图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4
• 三个虚线箭头，就是 undo log；
• V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的

一致性视图

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。这个视图数组把所有的 row trx_id 分成了几种不同的情况。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况a.
   • 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   • 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见

上面的规则翻译一下: 一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

1. 版本未提交，不可见；
2. 版本已提交，但是是在视图创建后提交的，不可见；
3. 版本已提交，而且是在视图创建前提交的，可见。

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

视图的更新

读数据时是按照上述规则的一致性读，但是更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。因为更新时如果根据数据的历史版本去更新，就会导致当前最新版本出现数据丢失。

除了 update 语句外，select 语句如果加锁，也是当前读。

1. select + lock in share mode: 加读锁(S 锁，共享锁）
2. select + for update: 加写锁（X 锁，排他锁）

而 update 更新语句的当前读也会给当前最新版本的数据加上读锁。

更新与两阶段锁

假设有上面一组更新事务:

1. 虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。
2. 事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放
3. 而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。

到这里，我们把一致性读、当前读和行锁就串起来了。

RR 与 RC

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

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

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

3. 索引上多版本数据的实现

我们以表结构 (id INT PRIMARY KEY, key INT, INDEX idx_key(key)) 为例，分析事务中执行 DELETE id=1, key=1 后立即 INSERT id=1, key=1 对 B+ 树结构的影响，即主键索引与二级索引在删除插入操作中的 B+ 树结构变化

3.1 初始状态（事务开始前）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

graph TB
  subgraph 主键索引 PK
    pk_root[根节点]
    pk_root --> |id=1| pk_leaf1[叶子节点1]
    pk_leaf1 --> id1[(id=1, key=1, data...)]
  end

  subgraph 二级索引 idx_key
    sk_root[根节点]
    sk_root --> |key=1| sk_leaf1[叶子节点1]
    sk_leaf1 --> key1_1[(key=1, id=1)]
  end

3.2 删除操作 DELETE id=1 后的变化

主键索引变化（聚簇索引）

1
2
3
4
5
6
7
8
9

graph TB
  subgraph PK
    pk_root[根节点]
    pk_root --> pk_leaf1[叶子节点1]
    pk_leaf1 --> id1_delete[⚠ id=1 
          ▸ 标记删除标志 
          ▸ DB_TRX_ID=当前事务ID 
          ▸ DB_ROLL_PTR→UNDO]
  end

二级索引变化

1
2
3
4
5
6
7
8

graph TB
  subgraph idx_key
    sk_root[根节点]
    sk_root --> sk_leaf1[叶子节点1]
    sk_leaf1 --> key1_del[[key=1, id=1
          ▸ 标记删除标志
          ▸ 物理位置保留]]
  end

核心机制：

• 所有索引都标记删除但保留物理位置
• 主键索引记录 DB_ROLL_PTR 指向 Undo Log
• 二级索引不存储事务信息

3.3 插入操作 INSERT id=1, key=1 后的变化

主键索引变化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

graph TB
  subgraph PK
    pk_root[根节点]
    pk_root --> pk_leaf1[叶子节点1]
    pk_leaf1 --> 
        id1_delete[⚠ id=1（标记删除）] & 
        id1_new[✅ id=1（新记录）
          ▸ DB_TRX_ID=新事务ID
          ▸ DB_ROLL_PTR→新UNDO]
  end

二级索引变化

1
2
3
4
5
6
7
8

graph TB
  subgraph idx_key
    sk_root[根节点]
    sk_root --> sk_leaf1[叶子节点1]
    sk_leaf1 --> 
        key1_del[[key=1, id=1（删除标记）]] & 
        key1_new[[✅ key=1, id=1（新记录）]]
  end