MySQL 事务与并发控制完全指南

用户307459698207
85 阅读12分钟

MySQL 事务与并发控制完全指南

1. 事务的 ACID 特性

事务是数据库中的一组操作序列，这些操作要么全部成功，要么全部失败，是一个不可分割的工作单元。它必须具备以下四个特性，简称 ACID：

特性通俗说明核心实现机制
原子性 (Atomicity)事务是一个不可分割的整体，要么所有操作都成功提交，要么在发生错误时全部回滚，就像 “要么全做，要么全不做”。Undo Log (回滚日志) ：记录事务执行前的数据状态，用于失败时撤销操作。
一致性 (Consistency)事务执行前后，数据库的总状态保持合法，即数据始终满足预设的业务规则和约束（如主键唯一、外键引用有效等）。由数据库约束（主键、外键、CHECK）、事务的原子性、以及应用层逻辑共同保证。它是事务追求的最终目标。
隔离性 (Isolation)多个事务并发执行时，彼此的操作互不干扰，每个事务都感觉不到其他事务的存在。MVCC (多版本并发控制)  和 锁机制 是实现隔离性的两大基石。
持久性 (Durability)一旦事务提交成功，其对数据的修改就是永久性的，即使数据库发生崩溃（如断电），修改也不会丢失。Redo Log (重做日志)  + WAL (Write-Ahead Logging) 策略：事务提交前，先将修改记录写入 Redo Log，确保崩溃后可以恢复。

💡 核心思想：一致性是最终目标，原子性、隔离性、持久性是保障一致性的手段。

2. 并发事务带来的问题与隔离级别

当多个事务同时操作同一批数据时，如果隔离性不足，会引发以下并发问题：

并发问题通俗说明
脏读 (Dirty Read)一个事务读取了另一个事务尚未提交的修改。这些修改可能之后被回滚，导致读取到的数据是 “脏” 的、无效的。
不可重复读 (Non-Repeatable Read)同一个事务内，多次读取同一行数据，在读取过程中，其他事务修改并提交了该行数据，导致前后读取结果不一致。
幻读 (Phantom Read)同一个事务内，多次执行同一个范围查询（如 WHERE id > 10），在查询过程中，其他事务插入或删除了符合该范围条件的数据并提交，导致前后查询返回的行数不一致。

为了解决这些问题，SQL 标准定义了四个隔离级别，不同级别对应不同的并发性能和数据一致性：

隔离级别英文脏读不可重复读幻读并发性能InnoDB 实现特点
读未提交Read Uncommitted✅ 可能✅ 可能✅ 可能最高最低的隔离级别，几乎不用。
读已提交Read Committed❌ 不可能✅ 可能✅ 可能较高每次 SELECT 都会生成新的快照。解决了脏读。
可重复读 (默认)Repeatable Read (RR)❌ 不可能❌ 不可能⚠️ 理论可能InnoDB 已解决中等事务启动后首次 SELECT 生成一个快照，全程复用。InnoDB 通过 Next-Key Lock 机制解决了幻读。
串行化Serializable❌ 不可能❌ 不可能❌ 不可能最低最高隔离级别，所有事务串行执行。读取加共享锁，写入加排他锁，完全避免并发问题，但性能极差。

💡 隔离级别选择建议：

  • 绝大多数场景下，使用 InnoDB 默认的 REPEATABLE READ 级别即可，它在保证强一致性的同时，兼顾了良好的并发性能。
  • 如果你的应用对 “不可重复读” 不敏感，且追求更高的并发写入性能（如某些日志系统），可以考虑使用 READ COMMITTED。

3. 快照读 (Snapshot Read) vs 当前读 (Current Read)

在 InnoDB 中，读取数据主要有两种方式，这是理解其并发控制的关键：

读取方式SQL 示例是否加锁？读取的内容核心机制
快照读SELECT * FROM table WHERE ...; (普通查询)❌ 不加锁基于 MVCC，读取的是一个一致性快照。MVCC
当前读SELECT ... FOR UPDATE;``SELECT ... LOCK IN SHARE MODE;``UPDATE ...;``DELETE ...;``INSERT ...;✅ 加锁读取的是数据库中最新的、已提交的数据。锁机制

💡 关键区别在不同隔离级别下的表现：

  • 在 RR (可重复读) 级别下：

    • 一个事务内的快照读，始终读取的是事务启动时（或首次查询时）创建的那个快照，保证了 “可重复读”。
    • 而当前读则会读取到最新的数据，并对其加锁，防止其他事务同时修改。

  • 在 RC (读已提交) 级别下：

    • 每次执行快照读，都会生成一个新的快照，因此能看到其他事务已提交的最新修改，但无法保证 “可重复读”。

4. MVCC (多版本并发控制)：无锁并发的秘密

MVCC 是 InnoDB 实现高并发、读不加锁的核心技术。它通过保存数据的 “历史版本”，让不同事务可以看到同一数据在不同时间点的状态。

MVCC 的核心组成：

  1. 隐藏字段：InnoDB 会为表中的每一行数据增加三个隐藏字段：

    • DB_TRX_ID：记录最后一次修改该行数据的事务 ID。
    • DB_ROLL_PTR：指向一个 undo log 记录的指针，通过它可以找到该行数据的上一个版本。
    • DB_ROW_ID：隐藏的自增主键（如果表没有主键或唯一非空索引时自动生成）。

  2. Undo Log (回滚日志) ：

    • 当事务修改数据时，InnoDB 会先将修改前的数据备份到 undo log 中。
    • 如果事务需要回滚（ROLLBACK），InnoDB 就可以利用 undo log 中的信息将数据恢复到修改前的状态。
    • 这些 undo log 记录串联起来，就形成了一条数据版本链。

  3. Read View (读视图) ：

    • 它是一个数据结构，用于定义一个事务能够看到哪些版本的数据。
    • 它主要包含了当前活跃的（未提交的）事务 ID 列表。
    • 核心思想：通过比较数据行的 DB_TRX_ID 和 Read View 中的事务 ID 列表，来判断该版本的数据对当前事务是否 “可见”。

Read View 可见性判断规则 (简化版)：

  • 如果数据行的 DB_TRX_ID 等于当前事务自己的 ID → 可见（自己修改的当然能看见）。
  • 如果数据行的 DB_TRX_ID 小于 Read View 中最小的活跃事务 ID → 可见（该修改在当前事务开始前已提交）。
  • 如果数据行的 DB_TRX_ID 在 Read View 的活跃事务 ID 列表中 → 不可见（该修改由其他未提交的事务做出）。
  • 如果数据行的 DB_TRX_ID 大于 Read View 中最大的活跃事务 ID → 可见（该修改由一个已提交的事务做出）。
  • 如果以上都不满足，则需要通过 DB_ROLL_PTR 顺着版本链往上找，直到找到一个满足可见性条件的版本，或者到达版本链的顶端（则认为该行不存在）。

隔离级别如何影响 MVCC？

  • RC (读已提交) ：每次执行 SELECT 语句时，都会创建一个新的 Read View。这意味着，你每次查询都能看到在此之前所有已提交事务的修改，因此解决了脏读，但无法避免不可重复读和幻读。
  • RR (可重复读) ：在事务启动后，第一次执行 SELECT 语句时创建一个 Read View，之后在整个事务期间，都复用这个 Read View**。** 这就保证了在同一个事务内，多次读取同一数据，看到的都是同一个版本，从而实现了 “可重复读”。

5. InnoDB 锁机制：并发修改的仲裁者

虽然 MVCC 实现了高效的读并发，但对于写操作（UPDATE, DELETE），为了保证数据一致性，仍然需要锁机制来协调。

5.1 锁的粒度

锁的粒度越小，并发性能越好，但锁的管理开销越大。

  • 行级锁 (Row-level Lock) ：

    • 粒度最细，只锁定被修改的具体行。
    • InnoDB 特有，是实现高并发的基础。
    • 依赖索引实现。如果 SQL 语句没有使用索引，InnoDB 会退化为表级锁，这是性能优化的大忌！

  • 表级锁 (Table-level Lock) ：

    • 粒度最粗，锁定整个表。
    • MyISAM 存储引擎的默认锁机制。InnoDB 在执行 ALTER TABLE 等 DDL 操作时也会使用。
    • 并发性能差，写操作会阻塞所有其他读写操作。

  • 页级锁 (Page-level Lock) ：

    • 粒度介于行级和表级之间，锁定一个数据页（通常为 16KB）。
    • 一些其他数据库引擎使用，InnoDB 主要使用行级锁。

5.2 锁的类型 (按兼容性划分)

锁类型英文描述兼容性 (自己 / 他人)典型 SQL 场景
共享锁Shared Lock (S-Lock)读锁。多个事务可以同时对同一资源加共享锁（读 - 读兼容）。S-S: 兼容S-X: 互斥SELECT ... LOCK IN SHARE MODE;
排他锁Exclusive Lock (X-Lock)写锁。一旦一个事务对资源加了排他锁，其他任何事务都不能再对该资源加任何类型的锁（写 - 读、写 - 写都互斥）。X-S: 互斥X-X: 互斥SELECT ... FOR UPDATE;``UPDATE ...;``DELETE ...;

💡 兼容性总结：读共享，写排他。

5.3 意向锁 (Intention Lock)

为了提高 “表锁” 和 “行锁” 之间的协调效率，InnoDB 引入了意向锁。

  • 意向共享锁 (IS) ：在对某行记录加 S 锁之前，先对该表加一个 IS 锁。
  • 意向排他锁 (IX) ：在对某行记录加 X 锁之前，先对该表加一个 IX 锁。

作用：意向锁是表级锁，它表明 “我（一个事务）打算对表中的某些行进行加锁操作”。这样，当其他事务想要对整个表加表锁时，只需要检查该表是否有意向锁即可，而无需逐行检查行锁，极大地提升了性能。

5.4 InnoDB 的行锁算法 (重点)

InnoDB 的行锁是基于索引实现的，具体的锁定范围由以下三种算法决定：

  1. Record Lock (记录锁) ：

    • 锁定索引中的具体某一行。
    • 例如，WHERE id = 10; (id 是唯一索引)。
    • 不防止幻读。

  2. Gap Lock (间隙锁) ：

    • 锁定索引记录之间的间隙，防止其他事务在这个间隙中插入新的数据。
    • 它是 InnoDB 在 RR 级别下防止幻读的关键技术之一。
    • 例如，WHERE id BETWEEN 10 AND 20; 会锁定 id 为 10 到 20 之间的记录以及它们前后的间隙。

  3. Next-Key Lock (临键锁) ：

    • InnoDB 在 RR 隔离级别下的默认行锁算法。
    • 它是 Record Lock + Gap Lock 的组合。
    • 它锁定一个左开右闭的索引区间。例如，对于索引值 10, 20, 30，Next-Key Lock 可能锁定 (10, 20], (20, 30] 等区间。
    • 通过锁定记录本身及其之前的间隙，Next-Key Lock 完美地解决了幻读问题。

⚠️ 优化点：当使用唯一索引进行等值查询时，InnoDB 会将 Next-Key Lock 优化为仅锁定具体的索引记录（Record Lock），而不会锁定其前面的间隙，从而减少锁的范围，提高并发度。

6. 实战宝典：如何安全地实现扣库存 / 转账？

这是事务和锁机制最经典的应用场景，也是最容易出错的地方。

❌ 错误的做法 (丢失更新 / 超卖风险)

sql

-- 事务A
START TRANSACTION;
SELECT stock FROM products WHERE id = 1; -- 假设查询出 stock = 10 (快照读)
-- 此时事务B也执行了同样的SELECT，也得到 stock = 10

-- 网络延迟或业务逻辑处理...

UPDATE products SET stock = 10 - 1 WHERE id = 1; -- 事务A扣减1，stock变为9
COMMIT;

-- 事务B
START TRANSACTION;
SELECT stock FROM products WHERE id = 1; -- 快照读，可能仍读到10
UPDATE products SET stock = 10 - 1 WHERE id = 1; -- 事务B也扣减1，stock变为9 (覆盖了事务A的修改)
COMMIT;
-- 最终库存为9，但实际上扣减了2次，发生了超卖！

问题根源：SELECT 是快照读，无法感知其他事务的并发修改。两个事务基于同一个旧的库存值进行更新，后提交的事务会覆盖先提交的事务的修改。

✅ 正确的做法 (使用当前读 + 排他锁)

sql

-- 事务A
START TRANSACTION;
-- 使用 SELECT ... FOR UPDATE 进行当前读，并对行加排他锁
SELECT stock FROM products WHERE id = 1 FOR UPDATE; -- 锁定该行，其他事务无法读取或修改
-- 假设查询出 stock = 10

-- 业务逻辑判断
if (stock > 0) {
    UPDATE products SET stock = stock - 1 WHERE id = 1; -- 安全扣减
}
COMMIT; -- 提交后释放锁

-- 事务B
START TRANSACTION;
-- 事务A未提交前，事务B执行此语句会被阻塞，直到事务A提交或回滚
SELECT stock FROM products WHERE id = 1 FOR UPDATE; -- 事务A提交后，这里会读到最新的库存 9

if (stock > 0) {
    UPDATE products SET stock = stock - 1 WHERE id = 1; -- 扣减后变为 8
}
COMMIT;
-- 最终库存为8，正确。

核心思想： “先锁定，再修改” 。通过 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE (根据需求选择)，在读取数据的同时就对其加锁，阻塞其他事务的并发修改，从而保证数据一致性。

💡 结论：

  • 在处理并发更新（如扣库存、转账）时，必须使用当前读（加锁读） ，而不能依赖普通的快照读。
  • 在这个场景下，RC 和 RR 隔离级别效果类似，因为核心在于通过 FOR UPDATE 显式加锁。但 RR 提供了更强的一致性保障。

7. 存储引擎对比：InnoDB vs MyISAM

选择正确的存储引擎至关重要。

特性InnoDB (现代首选)MyISAM ( legacy )
事务支持✅ 是❌ 否
行级锁✅ 是 (基于索引)❌ 否 (仅表级锁)
MVCC✅ 是 (支持高并发读)❌ 否
外键约束✅ 是❌ 否
崩溃恢复✅ 是 (通过 Redo/Undo Log)❌ 否 (需修复表)
全文索引 / 地理索引✅ 支持 (5.6+ 版本)✅ 支持 (传统优势)
表空间管理✅ 支持 (独立表空间等)❌ 不灵活
锁粒度与并发细粒度，高并发粗粒度，低并发

📌 现代开发铁律：请始终优先选择 InnoDB 作为你的存储引擎！它提供的事务、行锁和崩溃恢复功能是构建健壮、高并发应用的基石。MyISAM 已基本被淘汰。

avatar
文章
阅读
粉丝