MySQL 事务 - YHT's Note Repo

%% # 纲要 > 主干纲要、Hint/线索/路标 # Q&A #### 已明确 #### 待明确 > 当下仍存有的疑惑 **❓ 有什么问题？** # Buffer ## 闪念 > sudden idea ## 候选资料 > Read it later %% # 事务 Transaction > ❓什么是事务？ 数据库中的 "**==事务==**" 是保证 "**==数据一致性==**" 的机制，用于管理**一组 SQL 操作**的执行，使其**具有 "ACID" 特性**。使用场景： - **银行转账操作**：必须确保**转账的两步操作（从账户 A 扣款、向账户 B 存款）要么全部成功，要么全部回滚**。 - **订单处理**：处理订单时，**必须同时更新库存和订单状态**（新增一笔订单记录，同时库存量减一） > [!NOTE] MySQL 中，InnoDB 引擎支持事务处理（支持完整 ACID 特性），MyISAM 引擎不支持事务处理。 # 事务的四大特性（ACID） | | 定义说明 | | -------------------- | -------------------------------------------------------------------------------------------------------------------------- | | **原子性**（Atomicity） | 事务中的所有操作**要么全部执行成功，要么全部回滚**，不可分割。 | | **一致性**（Consistency） | 事务执行前后，**数据库必须保持一致的状态**，**满足所有完整性约束**。即**所有数据库==约束和规则==都必须得到满足**。 （例如主键约束、外键约束、`NOT NULL` 约束等，事务中包含的处理必须满足这些约束，否则会出错回滚） | | **隔离性**（Isolation） | 事务的**执行是相互隔离的**，**并发事务间不会相互干扰**时，**每个事务的中间状态（在该事务结束前）对其他事务是不可见的**。 （保证事物之间不会互相嵌套、影响） | | **持久性**（Durability） | 事务**一旦提交**，所做的修改就会**永久保存到数据库中**，即使数据库崩溃也不会丢失。 | ### MySQL 事务的实现 MySQL 中主要通过下列 4 个机制实现 **对事务的一致性支持**： - **锁**：利用**表锁**、**行锁**，**实现对数据并发修改的控制**，支持事务的==**隔离性**==。 - **Redo log**：记录**事务对数据库的所有修改**，当 MySQL 宕机崩溃时，可通过该日志恢复数据，实现 "**==持久性==**" - **Undo log**：记录**事务的反向操作**（保留数据历史版本），用于**事务回滚**，恢复事务执行前状态，实现 "**==原子性==**"。 - **MVCC**：提供无锁的 "**快照读**"，实现 "**读已提交**" 与 "**可重复读**" 两种隔离级别，提供 "**==隔离性==**"。 > [!NOTE] MySQL 数据库中对 "事务" 的实现 > > - ==**原子性**==：通过 **Undo log** 实现； > - **==隔离性==**：通过 **MVCC** 和 **行级锁**（包括记录锁、间隙锁、临键锁）实现，MySQL 中提供了 4 种事务隔离级别； > - **==持久性==**：通过 **Redo Log** 实现——InnoDB 通过 **WAL （Write-Ahead Logging）** **在事务提交前先写到 Redo log**，确保数据不丢失。 > - "**一致性**" 是**通过前三个特性来共同实现的**。 > # 事务的隔离级别 SQL 提供了四种隔离级别（从低到高），用于控制并发事务的影响 - （1）**==读未提交== （READ UNCOMMITTED）** - 最低的隔离级别 - 事务 a **可以读取另一个事务 b 尚未提交的数据修改** => 可能会造成 **脏读** 、**不可重复读**、**幻读**。 - （2）**==读已提交==（READ COMMITTED）** - 事务 a **可以读取另一个事务 b ==已提交==的数据** => 可防止 "脏读"，但可能会出现 "**不可重复读**"、"**幻读**"。 - （3）**==可重复读==**（**REPEATABLE READ**） - MySQL **默认级别**； - 保证在**一个事务内**，**多个查询返回的结果始终一致** => 可防止不可重复读，但可能导致 "**幻读**"（对 MySQL ） - （4）**==串行化==**（**SERIALIZABLE**） - 该隔离级别下，保证并发 SQL 事务的执行相当于 "**按特定==顺序串行执行==**"——即每个 SQL 事务**在上一事务结束后才会开始**。 - **完全隔离**，**所有事务按顺序执行**，防止幻读，但性能大幅下降。 > [!NOTE] SQL 通过 "表级锁" 实现 "串行化" 隔离级别，保证数据一致性。 ### 不同隔离级别下可能出现的问题 - **脏读**（Dirty Read） - 事务 A **读取了另一个事务 B ==未提交==的数据**，而这些数据可能会被事务 B 回滚，导致读取到的数据不可靠。 - **不可重复读**（Non-Repeatable Read） - 同一事务中**多次读取同一数据**，但先后**读取得到的==数据不一致==**（由于另一事务 B 的提交所导致） - **幻读**（Phantom Read） - 同一事务**在多次相同的查询中，返回不同数量的行**——另一事务 B 对查询范围内的**数据发生了增删**。 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 备注 | | ---- | --- | ----- | --- | -------------- | | 读未提交 | ✔️ | ✔️ | ✔️ | 最低的隔离级别 | | 读已提交 | ❌ | ✔️ | ✔️ | | | 可重复读 | ❌ | ❌ | ✔️ | MySQL **默认级别** | | 串行化 | ❌ | ❌ | ❌ | | > [!info] 关于 "可重复读" 隔离级别下 "幻读" 的说明 > > - 在标准 SQL 定义下，**`REPEATABLE READ` 允许发生幻读**，在**其他数据库（如 Oracle、PostgreSQL）中均是如此**。 > - 在 MySQL 的 InnoDB 引擎中，**`REPEATABLE READ`** 下支持引入 "**==临键锁==**（Next-Key Lock）"来 **==避免幻读==**： > > > [!info] MySQL InnoDB 中在 `REPEATABLE READ` 下防止幻读的方式 > > `REPEATABLE READ` 级别下： > > - 针对 "**快照读**"，即在事务里使用**普通 `SELECTE` 语句**时， **==MVCC 机制==** 保证**数据与事务启动时一致**，避免幻读问题。 > - 针对 "**当前读**"，即在事务里**使用==加锁==的 `SELECT ... FOR UPDATE` 或 `SELECT ... LOCK IN SHARE MODE` 语句**时， > InnoDB 底层将基于 "**==临键锁==**" （包含**间隙锁**）提供支持，锁住查询范围，**阻塞其他事务在此范围内插入/删除数据**，从而防止幻读。 > > ![[_attachment/02-开发笔记/08-数据库/MySQL 相关/MySQL 事务.assets/IMG-MySQL 事务-61CC26105AEBB76C679F3D91B2644EC0.png|681]] > ### 设置不同隔离级别 ```SQL -- 设置读未提交 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED; -- 设置读已提交 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置可重复读 (MySQL默认级别) SET TRANSACTION ISOLATION LEVEL REAPEATABLE READ; -- 设置串行化 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; ``` # MySQL 事务并发控制 MySQL 提供的**事务并发控制**机制包括： 1. **锁**，参见 [[02-开发笔记/08-数据库/MySQL 相关/MySQL 锁|MySQL 锁]]。 2. InnoDB 提供的 **MVCC 机制**。 ## 「快照读」与「当前读」 MySQL InnoDB 引擎中，区分 "**当前读**" 与 "**快照读**" 两种**不同读取方式**，涉及**事务隔离级别**和**一致性试图**。 - **==当前读==**（Current Read）：**读取==最新==数据**，保证数据一致性。 - 执行 "**当前读**" 的操作： - （1）**==加锁==的 `SELECT` 语句**； - （2）**读未提交 & 串行化** 级别下：**普通的==无锁== `select` 语句**； - （3）**数据的增删改语句**（`INSERT`，`UPDATE`，`DELETE`）（**自动加锁**） - **==快照读==**（Snapshot Read）：**基于 ==MVCC== 机制**，**无锁读取==快照==数据**（某一快照版本的数据） - 执行 "**快照读**" 的操作： - `READ COMMITTED` 与 `REPEATABLE READ` 级别下：**==未加锁==的 `SELECET` 语句** | 隔离级别 | 当前读 | 快照读 | 备注 | | ---- | ------- | ------------- | ----------------------------------------------------- | | 读未提交 | ✔️ | ❌ | 无 MVCC 机制，不存在 "**快照读**"，所有查询都是 "**当前读**"，故可能存在**脏读**。 | | 读已提交 | ✔️（需加锁） | ✔️（每次查询创建新快照） | 基于 MVCC 机制，无锁 `select` 执行 "**快照读**"； | | 可重复读 | ✔️（需加锁） | ✔️（事务期间快照一致） | 基于 MVCC 机制，无锁 `select` 执行 "**快照读**"； | | 串行化 | ✔️ | ❌（所有查询加锁） | 无 MVCC 机制，**所有查询自动加锁**，无锁 `select` 也执行 "**当前读**"。 | > [!summary] > > - "**==加锁==**" 一定是 "**当前读**" > - "**读未提交 or 串行化**" 级别下，"**==无锁==**" 也是 "当前读"！ > > [!example] "当前读" 操作示例 > > ```SQL > -- 共享锁（S 锁）：当前读，阻止其他事务修改该行 > SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE; > > -- 排他锁（X 锁）：当前读，阻止其他事务读取或修改该行 > SELECT * FROM users WHERE id = 1 FOR UPDATE; > > -- 更新操作（会进行当前读） > UPDATE users SET age = 30 WHERE id = 1; > > -- 删除操作（会进行当前读） > DELETE FROM users WHERE id = 1; > > -- 插入操作（隐式当前读） > INSERT INTO users(name, age) VALUES ('Alice', 25); > ``` > > > [!example] "快照读" 示意 > > ![[_attachment/02-开发笔记/08-数据库/MySQL 相关/MySQL 事务.assets/IMG-MySQL 事务-F3CB6A247CB3A7E34D0C15D5F3B41A71.png|586]] > > # MVCC 机制 > MVCC：Multi-Version Concurrency Control，**多版本并发控制**。 MVCC 机制用于在 **`READ COMMITED` 和 `REPEATABLE READ` 隔离级别**下实现 "**==无锁==的事务并发控制**"，提供 "**==快照读==**"。其基本思想是[^1]： 1. 数据更新时会将 "**旧版本**" 写入 **==Undo Log==**； 2. 每行数据中的 "**隐藏字段**" 结合 Undo Log 形成了 "**==版本链==**"； 3. `READ COMMITED` 和 `REPEATABLE READ` 两隔离级别下，将**创建 "ReadView"**（不同时机），**维护一组事务 ID 记录**。 4. 在执行 "**快照读**" 时，MVCC 将沿着 "**版本链**" 查找返回 **"==匹配该 ReadView==" 的数据版本**（**当前表中数据** or **Undo Log 中历史版本**）。 > [!NOTE] MVCC 正是通过控制 ReadView 的 "**==创建时机==**" 来实现两个不同隔离级别 > > - `READ COMMITTED`：**==每次查询时==都会生成新的 Read View**； > - `REPEATABLE READ`（默认）：**==事务开始时==生成 Read View**，在整个事务期不变； > ## （1）三个基本概念 - **隐藏字段** - **Undo ==版本链==** - **Read View ==一致性视图==** ### 隐藏字段 MySQL 数据库的每行记录中，除自定义字段外，包含一些**隐藏字段**： - `db_row_id`（行 id）：当未给表创建主键时，将按该字段创建 "**聚簇索引**"； - `db_trx_id`（事务 ID）：**最后==修改得到==该行记录的==事务的 ID==**。 - `db_roll_ptr`（回滚指针）：指向该行记录的上一个版本，即 **Undo Log 中上一个版本的快照地址**。 ### Undo 版本链 - 事务通过 `UPDATE` 修改数据时，**旧数据将被写入 Undo Log**，用于回滚和 MVCC 版本管理。 - ""Undo Log 中记录了一行数据的 "**历史版本**"； - "**表中最新数据**" 以及 "**Undo Log 中历史版本**" 里的隐藏字段 `db_roll_ptr` 构成了 "**==版本链==**"。 > [!example] > > ![[_attachment/02-开发笔记/08-数据库/MySQL 相关/MySQL 事务.assets/IMG-MySQL 事务-EF2CB363CFE7E81DB2A70F92748758B1.png|534]] > > [!NOTE] Undo Log 中实际存储的是 "**写操作的==反向操作==**" > ![[_attachment/02-开发笔记/08-数据库/MySQL 相关/MySQL 事务.assets/IMG-MySQL 事务-1255ABFEC8E113B4C8DB8295F8281330.png|652]] ### Read View 一致性视图 ReadView 本质上是**维护了一个集合**，创建时记录下列字段： - `trx_ids`：生成 ReadView 时，系统中**活跃的读写事务的事务 ID 列表**（即还未提交的） - `low_limit_id`：生成 ReadView 时，**应当分配给下一个事务的 ID**； - `up_limit_id`：生成 ReadView 时，**未提交**的事务中的**最小事务 ID**； - `creator_trx_id`：创建该 Read View 的事务 ID。 ## （2）ReadView 快照版本查找过程 MVCC 查找 "**合适的快照版本**"时的过程，即是沿着 **==版本链==**，用 "**表中记录" or "Undo Log 历史记录**" 中的 **`db_trx_id`字段** 依次与 **==Read View 中多个字段==** 进行比较： | | 修改该版本数据的 "事务" | 结果 | | ----------------------------------------------------------------------------------- | --------------------------- | --------------------- | | `db_trx_id == creator_trx_id` | 即为 **当前事务** | 可返回该版本 | | `db_trx_id < up_limit_id` | 该事务**已提交** | 可返回该版本 | | `db_trx_id > low_limit_id` | 该事务是 **在该 ReadView 创建后生成的** | 不可返回，沿着**版本链**查找上一旧版本 | | `up_limit_id < db_trx_id < low_limit_id` && **`db_trx_id` 在 `trx_ids` 中** | 该事务 "**尚未提交**" | 不可返回，沿着**版本链**查找上一旧版本 | MVCC 正是通过控制 ReadView 的 "**==创建时机==**" 来实现 `READ COMMITED` 和 `REPEATABLE READ` 两个隔离级别： - `READ COMMITTED`：**==每次查询时==都会生成新的 Read View**； - 故只要相较于 "**查询时**" 已提交，就会返回该数据版本。 - `REPEATABLE READ`（默认）：**==事务开始时==生成 Read View**，在整个事务期不变； - 在**事务 A 开始后才提交**的其他事务 B，相较于 **Read View 创建时**而言均视为 "**==未提交==**"的，因此只会沿着版本链找到 "**==事务开始时==的最新数据版本**" 。 > [!NOTE] 数据版本查找过程示意 > ![[_attachment/02-开发笔记/08-数据库/MySQL 相关/MySQL 事务.assets/IMG-MySQL 事务-1573F2194BF58A11B14CBA8A593FCD74.png|733]] # 事务管理命令在 MySQL 中，事务的管理主要通过以下语句进行： | | 语句 | 说明 | | ------------- | ------------------------------- | --------------------------------- | | **开始事务** | `START TRANSACTION;` 或 `BEGIN;` | | | **==提交==事务** | `COMMIT;` | **将所有操作持久化到数据库中** | | **==回滚==事务** | `ROLLBACK;` | **取消所有操作并将数据库恢复到事务开始时的状态**。 | | **设置==保留点==** | `SAVEPOINT <保留点占位符>;` | 在事务处理块中**创建占位符**，以便实现 "**部分回滚**"。 | | **回滚至某个保留点** | `ROLLBACK TO <保留点占位符>;` | 实现部分回滚，仅撤销保留点之后的操作。 | | **查看事务状态** | `SHOW ENGINE INNODB STATUS` | 查看事务锁、死锁等信息。 | 自 `START TRANSACTION` 语句之后，直至 `COMMIT` 或 `ROLLBACK` 语句之间的部分，称为 "**==事务处理块==**"。 **执行 `COMMIT` 或 `ROLLBACK` 语句后，"事务" 会==自动关闭==**。 > [!NOTE] MySQL 默认开启"==**自动提交**=="，即"单条 SQL 语句"都会作为事务并自动提交（立即生效）。 > > - 关闭自动提交： `SET AUTOCOMMIT=0;` > - 恢复自动提交：`SET AUTOCOMMIT=1;` > [!NOTE] 保留点会在事务处理完成后自动释放，也可通过 `RELEASE SAVEPOINT` 手动释放。 #### 示例示例一：事务提交 ```sql -- 下例中, 只有当两个 `UPDATE` 操作都成功时，事务才会提交，否则可以通过 `ROLLBACK` 撤销这两个操作。 -- 标记开始事务 START TRANSACTION; -- 或者 `BEGIN;` -- 执行一系列SQL语句 UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1; UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2; -- 提交事务 COMMIT; ``` 示例二：事务回滚 ```sql START TRANSACTION; DELETE FROM ordertotals; -- 清空该表 SELECT * FROM ordertotals; -- SELECT 检查确实清空了表 ROLLBACK; -- 回滚, 回退START TRANSACTION之后的所有语句, 撤销DELETE操作. ``` 示例三：回滚部分事务 ```sql START TRANSACTION; -- 执行第一步操作 UPDATE account SET balance = balance - 500 WHERE account_id = 1; -- 创建一个保存点 SAVEPOINT savepoint_one; -- 执行第二步、第三步操作 UPDATE account SET balance = balance + 500 WHERE account_id = 2; UPDATE account SET balance = balance + 100 WHERE account_id = 3; -- 若发现第三部操作有问题, 可回滚到保存点, 撤销第二步、第三步的操作. ROLLBACK TO savepoint_one; -- 提交事务（仅保留第一步操作） COMMIT; ``` # ♾️参考资料 # Footnotes [^1]: [MySQL 中的 MVCC 是什么？ - MySQL 面试题 - 面试鸭 - 程序员求职面试刷题神器](https://www.mianshiya.com/bank/1791003439968264194/question/1780933295484203009#heading-8)