Tenormis's Blog

Appearance

MySQL 事务 ​

1. MySQL 的事务隔离级别 ​

首先，我们需要理解在并发数据库环境中可能出现的几种异常情况：

  1. 脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据；
  2. 不可重复读（Non-Repeatable Read）：同一个事务内，多次读取同一数据行，得到的结果不一致；
  3. 幻读（Phantom Read）：一个事务多次执行相同的查询，但返回的结果集不同；

事务的四种隔离级别：

  • 读未提交（READ UNCOMMITTED）
    • 最低的隔离级别
    • 允许读取未提交的数据
    • 可能导致脏读、不可重复读和幻读
    • 性能最高，但数据一致性最差
  • 读已提交（READ COMMITTED）
    • 仅允许读取已提交的数据
    • 解决了脏读问题
    • 可能出现不可重复读和幻读
    • 大多数数据库的默认隔离级别
  • 可重复读（REPEATABLE READ）
    • MySQL 的默认隔离级别
    • 解决了脏读和不可重复读问题
    • 仍可能出现幻读
    • 使用记录锁（Record Lock）和间隙锁（Gap Lock）机制
    • 通过 Next-Key Lock（Record Lock + Gap Lock）解决了部分幻读问题
  • 串行化（SERIALIZABLE）
    • 最高的隔离级别
    • 完全串行执行事务，避免所有并发问题
    • 通过锁定整个范围的记录来防止幻读
    • 性能最低，实际应用较少
隔离级别脏读不可重复读幻读锁机制
READ UNCOMMITTED√√√无行锁，仅使用共享读（可能会读到未提交数据）
READ COMMITTED×√√行级锁（Record Lock），每次读取获取新快照
REPEATABLE READ（默认）××√间隙锁（Gap Lock）+ 记录锁（Record Lock）
SERIALIZABLE×××表级锁（使用 Range Lock，防止并发插入）

设置事务隔离级别：

EBNF
SET_ISOLATION_LEVEL = "SET TRANSACTION ISOLATION LEVEL" ISOLATION_LEVEL ";"
ISOLATION_LEVEL = "READ UNCOMMITTED" | "READ COMMITTED" | "REPEATABLE READ" | "SERIALIZABLE"

你可以使用以下 SQL 语句查看当前会话和全局的事务隔离级别：

SQL
SELECT @@global.transaction_isolation; -- 查看全局隔离级别
SELECT @@transaction_isolation;        -- 查看当前会话隔离级别

2. 事务语法 ​

2.1. 基本语法 ​

SQL
START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

查看自动提交状态：

SQL
-- 会话
SELECT @@autocommit;
SHOW VARIABLES LIKE 'autocommit';

-- 全局
SELECT @@GLOBAL.autocommit;
SHOW GLOBAL VARIABLES LIKE 'autocommit';

查看运行中事务：

SQL
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

监控锁状态：

SQL
SHOW ENGINE INNODB STATUS;

2.2. 异常处理 ​

含异常处理的事务写法模板：

SQL
START TRANSACTION;

-- 使用条件语句处理可能的错误
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  RESIGNAL;
END;

-- 事务操作
-- ...

COMMIT;

DECLARE ... HANDLER Statement 指定一个或多个条件的处理程序，除了 DECLARE EXIT HANDLER ... 还有 DECLARE CONTINUE HANDLER ...：

  • EXIT：处理完错误后，立即终止当前代码块的执行；
  • CONTINUE：处理完错误后，继续执行后续代码；

2.3. 事务保存点 ​

SQL
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

需要注意的是，事务保存点要求当前会话有活跃的事务。

3. 嵌套事务 ​

MySQL 原生不支持嵌套事务，但可通过保存点模拟部分回滚。

隐式提交问题：如果在已开启的事务中执行新的 START TRANSACTION 或 BEGIN，MySQL 会隐式提交当前事务，然后开启一个新事务。这意味着 “嵌套事务” 实际上被拆分为多个独立的事务，破坏了事务的原子性。

SQL
START TRANSACTION;  -- 外层事务
INSERT INTO table1 VALUES (1);

START TRANSACTION;  -- 隐式提交外层事务，开启新事务
INSERT INTO table2 VALUES (2);
COMMIT;            -- 提交第二个事务

ROLLBACK;          -- 无效，外层事务已被提交

通过 SAVEPOINT 可以在事务内设置标记点，允许回滚到某个标记点而不影响整个事务。这可用于模拟 “嵌套事务” 中的部分回滚。

SQL
START TRANSACTION;
INSERT INTO table1 VALUES (1);

SAVEPOINT sp1;   -- 设置保存点
INSERT INTO table2 VALUES (2);

ROLLBACK TO sp1; -- 回滚到 sp1（仅撤销 INSERT INTO table2）
COMMIT;          -- 最终提交 table1 的插入

建议永远不主动通过 SQL 开启事务，而是由应用层统一控制。

Note：Spring 事务的传播机制

在 Spring 框架中，利用 Spring 的传播机制（如 PROPAGATION_NESTED）实现逻辑嵌套事务，底层基于保存点。

Java
@Transactional(propagation = Propagation.NESTED)
public void innerMethod() {
  // 方法失败时回滚到保存点，不影响外层事务
}

3.1. 事务的隐式提交 ​

本节列出的语句（以及它们的任何同义词）在当前会话中会隐式结束任何活动事务，就像在执行该语句之前执行了 COMMIT 一样。

大多数这些语句在执行后还会导致隐式提交。其目的是将每个这样的语句作为单独的特殊事务来处理。事务控制和锁定语句是例外：如果在执行之前发生隐式提交，则在执行之后不会再次发生隐式提交。

  • 定义或修改数据库对象的数据定义语言（DDL）语句

    ALTER EVENT、ALTER FUNCTION、ALTER PROCEDURE ……

    CREATE TABLE 和 DROP TABLE 语句在使用 TEMPORARY 关键字时不会提交事务（此规则不适用于 ALTER TABLE 和 CREATE INDEX 等其他针对临时表的操作，这些操作仍会导致提交）。然而，尽管不会发生隐式提交，但该语句也无法回滚，这意味着使用这些语句会导致事务的原子性被破坏。例如，如果你使用 CREATE TEMPORARY TABLE，然后回滚事务，该表仍然会存在。

    在 InnoDB 中，CREATE TABLE 语句被作为一个单独的事务处理。这意味着用户执行 ROLLBACK 并不会撤销在该事务中执行的 CREATE TABLE 语句。

    CREATE TABLE ... SELECT 在创建非临时表时，会在语句执行前后都进行隐式提交（对于 CREATE TEMPORARY TABLE ... SELECT 不会发生提交）。

  • 隐式使用或修改 mysql 数据库中表的语句

    ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。

  • 事务控制和锁定语句

    BEGIN、LOCK TABLES、SET autocommit = 1（如果当前值不是 1）、START TRANSACTION、UNLOCK TABLES。

    只有在 LOCK TABLES 获取了非事务性表的锁后，UNLOCK TABLES 才会提交事务。对于 FLUSH TABLES WITH READ LOCK 之后的 UNLOCK TABLES，不会发生提交，因为前者不会获取表级锁。

    事务不能嵌套。这是因为当执行 START TRANSACTION 语句或其同义词时，会对当前事务执行隐式提交。

    处于 ACTIVE 状态的 XA 事务中，不能使用会导致隐式提交的语句。

    BEGIN 语句与 BEGIN ... END 复合语句中的 BEGIN 关键字不同。后者不会导致隐式提交。

  • 数据加载语句

    LOAD DATA。仅当目标表使用 NDB 存储引擎时，LOAD DATA 才会导致隐式提交。

  • 管理语句

    ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET（但不包括 RESET PERSIST）。

  • 复制控制语句

    START REPLICA、STOP REPLICA、RESET REPLICA、CHANGE REPLICATION SOURCE TO。

4. 事务原理 ​

MySQL 事务的：

  • Atomicity（原子性）：是由 Undo Log 保证的；
  • Consistency（一致性）：是由 Undo Log + Redo Log 保证的；
  • Isolation（隔离性）：是由锁 + MVCC 保证的；
  • Durability（持久性）：是由 Redo Log 保证的；

4.1. Redo Log ​

4.1.1. 概念 ​

Redo Log（重做日志）是 MySQL InnoDB 存储引擎特有的日志，用于崩溃恢复（Crash Recovery），保证事务的持久性（Durability，ACID 的 "D"）。它能够在数据库发生崩溃时，确保已经提交的事务不会丢失。

  • 崩溃恢复：当数据库崩溃重启后，Redo Log 记录的已提交事务可以被重做，从而恢复数据一致性；
  • 提高性能：减少磁盘 I/O，因为不需要频繁地将数据写入磁盘，而是先写入日志，之后再批量刷新到磁盘；

4.1.2. 工作原理 ​

InnoDB 存储引擎使用 WAL（Write-Ahead Logging，预写式日志）机制，即：

  1. 事务执行时，先将变更记录写入 Redo Log（先写日志）；
  2. 数据缓存在 Buffer Pool（内存），不立即写入磁盘，而是等到合适的时机（如刷脏页）再落盘；
  3. 事务提交时，Redo Log 先持久化（fsync 到磁盘），然后 MySQL 认为事务提交成功；
  4. 发生崩溃后，MySQL 在重启时，会从 Redo Log 读取日志并重做已提交但尚未落盘的操作，确保数据不丢失；

4.1.3. Redo Log 结构 ​

Redo Log 由两部分组成：

  • Redo Log Buffer（内存中的重做日志缓冲区）
  • Redo Log File（磁盘上的重做日志文件）

Redo Log 采用循环写（环形队列）的方式存储，分为：

  • write pos：当前写入的位置；
  • checkpoint：代表已持久化到磁盘的数据位置；
  • 当 write pos 追上 checkpoint 时，需要将一部分日志刷新到磁盘，腾出空间；

4.1.4. Redo Log 刷盘策略 ​

MySQL 提供了三种刷盘策略（由 innodb_flush_log_at_trx_commit 参数控制）：

  • 0：事务提交时，不写入磁盘（仅保留在内存）；
  • 1（默认）：事务提交时，立即写入磁盘（最安全）；
  • 2：事务提交时，写入操作系统缓存（但未 fsync 到磁盘）；

4.2. Undo Log ​

4.2.1. 概念 ​

Undo Log（回滚日志）记录的是数据的逻辑操作的反向信息，主要用于事务回滚（Rollback）和 MVCC（多版本并发控制），保证事务的原子性（Atomicity，ACID 的 "A"）和一致性。

  • 事务回滚：当事务失败或执行 ROLLBACK，可以利用 Undo Log 还原数据到事务开始前的状态；
  • MVCC：支持快照读，实现非锁定读，提高并发能力；

4.2.2. 工作原理 ​

  1. 事务开始前，先将旧值记录到 Undo Log；
  2. 事务执行过程中，如果有 DELETE、UPDATE 等操作，会生成 Undo Log；
  3. 事务提交：
    • Undo Log 仍然存在（因为 MVCC 可能需要它）；
    • 回滚段（Rollback Segment）负责管理 Undo Log，后续垃圾回收（Purge）；
  4. 事务回滚：
    • MySQL 读取 Undo Log，按照反向逻辑操作还原数据；

Note：垃圾回收（Purge 机制）

  • MySQL 会定期清理 Undo Log 中不再被事务访问的数据（Purge 线程）；
  • 过多的 Undo Log 会影响性能，尤其是长事务会导致 Undo Log 不能回收；

4.2.3. Undo Log 类型 ​

  1. 插入（Insert Undo）

    • 记录 INSERT 语句的日志；
    • 仅在事务未提交时才需要，事务提交后立即丢弃；

  2. 修改（Update Undo）

    • 记录 UPDATE、DELETE 操作的反向操作信息；
    • 需要支持 MVCC，因此不能立即删除，需等待垃圾回收；

4.2.4. Undo Log 的清理 ​

  • 事务提交后不会立即删除，而是由 Purge 线程进行清理；
  • 由于 Undo Log 影响 MVCC 版本管理，在长事务场景下，过多的 Undo Log 可能导致性能下降；

4.3. Bin Log ​

4.3.1. 概念 ​

Binlog（二进制日志）是 MySQL 服务器层维护的日志，主要用于主从复制（Replication）和数据恢复，它不属于 InnoDB，而是 MySQL 所有存储引擎共享的日志。

  • 数据恢复：利用 Binlog 进行 Point-in-Time Recovery（时间点恢复）；
  • 主从复制：MySQL Replication 依赖 Binlog 进行数据同步，主库记录 Binlog，从库重放 Binlog，实现数据一致性；
  • 增量备份：可结合 mysqldump --single-transaction 和 Binlog 进行备份；

4.3.2. 工作原理 ​

  1. 事务执行时，先写入 Redo Log，再写入 Binlog（保证事务提交的一致性）；
  2. Binlog 采用追加写（Append-Only）的方式；
  3. 事务提交时，Binlog 先写入磁盘（不同于 Redo Log 的循环写）；
  4. 从库通过 I/O 线程拉取 Binlog，并通过 SQL 线程执行重放，达到主从一致；

4.3.3. Binlog 记录格式 ​

MySQL 提供了三种 Binlog 格式：

  1. STATEMENT（基于 SQL 语句）：记录 SQL 语句，较节省空间，但存在非确定性问题（如 NOW()）；
  2. ROW（基于行）（推荐）：记录行变更，可完美复制，但日志量较大；
  3. MIXED（混合模式）：一般使用 STATEMENT，在某些情况下切换到 ROW；

4.4. MVCC ​

MySQL 的多版本并发控制（MVCC，Multi-Version Concurrency Control）是一种用于提高数据库并发性能的机制。它主要用于实现事务的隔离性，确保在并发事务中读取数据的一致性。

在 MySQL 的可重复读（REPEATABLE READ）和读已提交（READ COMMITTED）隔离级别下，MVCC 使得事务可以执行非阻塞的快照读（Snapshot Read），避免加锁操作，从而提升查询性能。

以下是 MySQL MVCC 的实现原理的简要概述：

  1. 事务 ID：每个事务在启动时都会分配一个唯一的事务 ID，这个 ID 是递增的。

  2. 版本链：每行数据都有一个版本链，版本链中包含了该行数据的多个版本。每个版本都有一个唯一的版本号，通常是事务 ID。

  3. 隐藏列：在每行数据中，MySQL 会维护一些隐藏列，用于实现 MVCC。这些列包括：

    • DB_TRX_ID：创建该版本的事务 ID。
    • DB_ROLL_PTR：指向该行上一个版本的指针，形成一个版本链。
    • DB_ROW_ID：如果没有主键，MySQL 会自动生成一个行 ID。

  4. Read View：决定在当前事务中哪些数据版本是可见的，从而实现不同隔离级别下的读一致性。

    组成部分：

    • m_ids：在创建 Read View 时，当前活跃（尚未提交）的事务 ID 列表；
    • min_trx_id：在创建 Read View 时，系统中最小的活跃事务 ID；
    • max_trx_id：在创建 Read View 时，系统分配的最大事务 ID，即下一个将被分配的事务 ID；
    • creator_trx_id：创建 Read View 的事务 ID；

    创建时机：

    • 在读已提交（Read Committed）隔离级别下，每个语句执行时都会创建一个新的 Read View。
    • 在可重复读（Repeatable Read）隔离级别下，Read View 在事务启动时创建，并在整个事务期间保持不变。

    可见性规则。对于一行数据的某个版本，如果其事务 ID：

    • 事务 ID < min_trx_id：可见 — 该版本是在 Read View 创建之前已经提交的
    • 事务 ID ≥ max_trx_id：不可见 — 该版本是在 Read View 创建之后才创建的
    • min_trx_id ≤ 事务 ID < max_trx_id：
      • 事务 ID 在 m_ids 中：不可见 — 该事务在 Read View 创建时仍然活跃
      • 事务 ID 不在 m_ids 中：可见 — 该事务在 Read View 创建时已经提交
表 4.1 - MVCC 在不同隔离级别下的表现
隔离级别MVCC 机制读取规则
READ UNCOMMITTED不使用 MVCC读取未提交的数据（脏读）
READ COMMITTED使用 MVCC每次查询生成新的快照（不可重复读）
REPEATABLE READ（默认）使用 MVCC事务创建快照，保证可重复读
SERIALIZABLE不使用 MVCC读写加锁，串行化执行

转载请注明作者和出处

Copyright © 2025-至今 Tenormis