LumantoBlog

Appearance

MySQL 第八章：锁机制与并发控制 ​

本章目标：理解 MySQL 锁的分类与作用，掌握常见加锁 SQL、死锁处理策略和并发控制方案，能够在高并发场景下保证数据正确性。

1. 为什么需要锁 ​

并发场景中，多个事务可能同时读写同一批数据。如果没有锁，就可能出现脏写、超卖、余额错误等问题。

锁的核心作用：

  • 保证并发操作下的数据一致性
  • 在“性能”和“正确性”之间做权衡
flowchart LR
    A[并发请求] --> B{是否冲突同一数据}
    B -- 否 --> C[并行执行]
    B -- 是 --> D[加锁等待/串行化]
    C --> E[结果正确]
    D --> E

2. 锁的分类（按粒度与模式） ​

2.1 按锁粒度 ​

  • 全局锁：锁整个实例（如备份场景）
  • 表锁：锁整张表（MyISAM 常见，InnoDB 也可显式使用）
  • 行锁：锁某一行（InnoDB 高并发核心）

2.2 按锁模式 ​

  • 共享锁（S Lock）：读锁，可并发读，不可写
  • 排他锁（X Lock）：写锁，其他事务不能读写该行（当前读场景）

2.3 InnoDB 特有锁 ​

  • Record Lock：记录锁（锁某一行）
  • Gap Lock：间隙锁（锁索引区间，不锁具体行）
  • Next-Key Lock：记录锁 + 间隙锁（防幻读常见）

3. 常用加锁语句 ​

3.1 当前读 + 行锁 ​

sql
START TRANSACTION;

SELECT id, stock
FROM products
WHERE id = 1001
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 1001 AND stock > 0;

COMMIT;

FOR UPDATE 会对命中行加排他锁，适合“先查后改”。

3.2 共享锁读取 ​

sql
START TRANSACTION;

SELECT id, stock
FROM products
WHERE id = 1001
LOCK IN SHARE MODE;

COMMIT;

MySQL 8.0 也支持 FOR SHARE，语义更直观。

4. 并发控制常见模式 ​

4.1 悲观锁（Pessimistic Lock） ​

先加锁再操作，假设冲突概率高。

适用场景：

  • 库存扣减
  • 账户余额变更
  • 强一致业务流程

4.2 乐观锁（Optimistic Lock） ​

先读后改，提交时校验版本号（或时间戳），冲突则重试。

示例（版本号）：

sql
UPDATE products
SET stock = stock - 1,
    version = version + 1
WHERE id = 1001
  AND version = 12
  AND stock > 0;

5. 死锁（Deadlock） ​

死锁是指两个或多个事务互相持有对方需要的锁，形成循环等待。

sequenceDiagram
    participant T1 as 事务T1
    participant T2 as 事务T2
    T1->>DB: 锁住行A
    T2->>DB: 锁住行B
    T1->>DB: 请求行B(等待)
    T2->>DB: 请求行A(等待)
    Note over T1,T2: 形成死锁，InnoDB会回滚一个事务

5.1 降低死锁概率的策略 ​

  1. 固定加锁顺序（先锁小 ID，再锁大 ID）
  2. 事务尽量短，减少锁持有时间
  3. 命中索引，避免“锁范围扩大”
  4. 在应用层增加失败重试机制

6. 锁与索引的关系（高频坑） ​

行锁是“基于索引项加锁”的：

  • 命中索引：更精确，锁行少
  • 未命中索引：可能退化成更大范围锁，甚至接近表锁效果

示例：

sql
-- 若 user_id 无索引，这条更新可能扫描并锁大量行
UPDATE orders
SET status = 2
WHERE user_id = 10086;

结论：高并发更新条件列，一定要有合适索引。

7. 实战案例：防止库存超卖 ​

7.1 表结构 ​

sql
CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  stock INT NOT NULL,
  version INT NOT NULL DEFAULT 0
);

7.2 方案 A：悲观锁 ​

sql
START TRANSACTION;

SELECT stock
FROM products
WHERE id = 1
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock > 0;

COMMIT;

7.3 方案 B：乐观锁 ​

sql
UPDATE products
SET stock = stock - 1,
    version = version + 1
WHERE id = 1
  AND stock > 0
  AND version = ?;

8. 并发问题排查思路 ​

  1. 用 SHOW PROCESSLIST 看是否有长事务、锁等待
  2. 用 SHOW ENGINE INNODB STATUS 查看死锁信息
  3. 检查慢 SQL 与未命中索引 SQL
  4. 确认事务边界是否过大（是否把外部调用放进事务）
  5. 加入监控：锁等待时间、死锁次数、事务耗时

9. 面试高频点 ​

9.1 FOR UPDATE 一定会锁行吗 ​

不一定。要看是否命中索引与执行计划；否则锁范围可能扩大。

9.2 行锁和表锁的本质区别 ​

  • 行锁并发度高、实现复杂
  • 表锁并发度低、实现简单

9.3 乐观锁和悲观锁怎么选 ​

  • 冲突少：乐观锁（吞吐更高）
  • 冲突多：悲观锁（逻辑更稳）

9.4 死锁是坏事吗 ​

死锁是并发系统中的正常现象，关键是要有可观测性与自动重试机制。

10. 练习题 ​

  1. 用 FOR UPDATE 写一个“转账扣款”事务示例。
  2. 写一个基于 version 的乐观锁更新 SQL。
  3. 举例说明“无索引更新”为什么会导致锁冲突扩大。
  4. 描述一套你自己的死锁排查流程。
  5. 比较悲观锁和乐观锁在秒杀场景下的优缺点。

11. 本章小结 ​

  • 锁是并发控制的核心手段，索引是锁精度的关键前提。
  • 并发能力不是“完全无锁”，而是“合理加锁 + 缩小锁范围 + 快速释放”。
  • 真正的高可用方案通常是：SQL 优化、事务设计、锁策略、重试机制一起配合。