MySQL 知识系列（九）：CASE 语句、乐观锁与悲观锁实战

本文是 MySQL 知识系列的第九篇——实战收尾。前面八篇讲了架构、索引、锁、日志、事务、调优……这一篇把”业务侧最常用”的 CASE 表达式与三种并发方案（悲观锁/乐观锁/复杂 SQL）整合到一起，并给出锁选型决策树。

CASE WHEN：单条 SQL 实现多分支更新

场景

更新 VIP 会员的 start_at 和 end_at：

如果 end_at < NOW()（已过期）：从 NOW() 开始 + 续费时长
如果 end_at >= NOW()（未过期）：在原 end_at 基础上 + 续费时长

用一条 SQL 解决

UPDATE vip_member
SET
   start_at = CASE
              WHEN end_at < NOW()
                 THEN NOW()
              ELSE start_at
              END,
   end_at = CASE
            WHEN end_at < NOW()
               THEN DATE_ADD(NOW(), INTERVAL #duration:INTEGER# MONTH)
            ELSE DATE_ADD(end_at, INTERVAL #duration:INTEGER# MONTH)
            END,
   active_status = 1,
   updated_at = NOW()
WHERE uid = #uid:BIGINT#
LIMIT 1;

优势

一次交互完成，不需要应用层”先查后改”
WHERE 条件 + LIMIT 1 锁住单行，避免并发问题
业务逻辑下沉到 SQL，减少应用层代码

劣势

可维护性差：业务规则揉在 SQL 里，下次业务变了要改 SQL
复杂度高：分支多时 SQL 会非常长
数据库移植性差：MySQL 方言，迁到 PG/Oracle 要改

💡 适用场景：分支简单、调用频次高（如订单状态流转）的核心业务。

悲观锁：数据库层显式加锁

共享锁（读锁 / S 锁）

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

其他事务：可以读（LOCK IN SHARE MODE），不能写。

⚠️ 仅靠共享锁不能解决并发问题——多个事务可以同时拿到共享锁，仍然可能写入冲突。

排他锁（写锁 / X 锁）

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

其他事务：不能读（FOR UPDATE 锁住了对应行），也不能写。

💡 FOR UPDATE 的关键：

必须在事务内（BEGIN ... COMMIT）

默认锁定扫描过的所有行（可能锁范围超出预期）

必须有索引——否则会锁整张表

乐观锁：应用层版本号 / 时间戳

核心思想

先读出来一个时间戳或版本号，再更新——如果更新影响条数为 0，说明版本已过期，重试或报错。

模板 SQL

-- 第一步：读取当前版本
SELECT update_time FROM `kill_product` WHERE id = 1;

-- 第二步：携带版本号更新
UPDATE `kill_product` k
SET k.`num` = num - 1,
    k.`update_time` = NOW()
WHERE k.`update_time` = ?    -- 版本号
  AND id = 1;

Java 代码示例（MyBatis）

public boolean deductStock(Long productId) {
    // 1. 读取当前更新时间
    Product p = productMapper.selectById(productId);

    // 2. 携带更新时间尝试更新
    int rows = productMapper.deductStock(productId, p.getUpdateTime());

    // 3. 影响条数为 0 说明版本已过期，重试
    if (rows == 0) {
        return deductStock(productId);   // 重试
    }
    return true;
}

优势

不加数据库锁，并发性能好
不依赖数据库特性（PostgreSQL / Oracle 同样适用）
天然适合”读多写少” 场景

劣势

每次更新需要 2 次 SQL（先读再写）
业务需要重试逻辑（重试风暴要注意限流）
不保证最终一致性（重试次数用尽后数据可能仍不一致）

实战对比：库存扣减 3 种方案

假设业务：秒杀场景，扣减商品 num 字段。

方案一：复杂 SQL（CASE WHEN 思路）

UPDATE kill_product
SET num = CASE WHEN num > 0 THEN num - 1 ELSE num END
WHERE id = 1 AND num > 0;

问题：

把业务逻辑糅在 SQL 里
维护成本高，下次业务规则调整要改 SQL
MySQL 8.0 之前 CASE WHEN 的可读性较差

方案二：悲观锁（SELECT FOR UPDATE）

@Transactional
public boolean deductStock(Long id) {
    Product p = productMapper.selectForUpdate(id);  // SELECT ... FOR UPDATE
    if (p.getNum() <= 0) return false;
    p.setNum(p.getNum() - 1);
    productMapper.updateById(p);
    return true;
}

问题：

锁粒度依赖 WHERE 条件
并发性能差，所有写请求串行
容易锁等待 / 死锁

方案三：乐观锁（推荐）

public boolean deductStock(Long id) {
    int maxRetry = 3;
    for (int i = 0; i < maxRetry; i++) {
        Product p = productMapper.selectById(id);
        if (p.getNum() <= 0) return false;
        int rows = productMapper.deductStockWithVersion(id, p.getVersion());
        if (rows == 1) return true;       // 成功
        // rows == 0 说明版本过期，重试
    }
    return false;
}

优势：

不加数据库锁
业务逻辑清晰（应用层重试）
可以不加事务（因为版本号本身就是一致性约束）

三种方案对比

维度	复杂 SQL	悲观锁	乐观锁
维护性	差	中	好
并发性能	良	差	好
实现复杂度	低	中	中
数据库依赖	高	高	低
死锁风险	低	高	低
适用场景	简单业务	冲突率高	通用推荐

选型建议

简单业务 + 高频调用 → 复杂 SQL
冲突率极高 + 写多读少 → 悲观锁
其他场景 → 乐观锁（推荐默认选择）

锁选型决策树

乐观锁和悲观锁的选择，不是非此即彼，要结合业务特征。

决策依据

读的响应度要求：
- 高（如证券交易系统）→ 乐观锁（悲观锁会阻塞读）
- 低 → 都可以
读写比：
- 读远多于写 → 乐观锁（避免大量读被少量写阻塞）
- 写多于读 → 都可以
写冲突率：
- 高（大量并发写同一行） → 悲观锁
- 低（写不集中） → 乐观锁

一图决策

                读响应度要求高？
                  /         \
                Yes          No
                /             \
        乐观锁         写冲突率高？
                          /        \
                        Yes         No
                        /            \
                  悲观锁         乐观锁（推荐）

实战清单

判断要不要加锁、加什么锁时，按这个顺序思考：

✅ 能不锁就不锁——用数据库自身的约束（唯一索引、版本号）
✅ 能乐观锁就不悲观锁——性能更好
✅ 必须悲观锁时，缩小锁粒度——索引覆盖 + LIMIT 1
✅ 事务要短——锁持有时间越短越好
✅ 注意死锁——多表/多行加锁顺序一致
✅ 重试要有上限——避免重试风暴

MySQL 系列全文索引

到这一篇，整个 MySQL 知识系列 9 篇就全部完成了。回顾整个体系：

篇	主题	重点关键词
一	架构与存储引擎	连接器/优化器/执行器、InnoDB vs MyISAM
二	索引结构	B+ 树、聚簇 vs 非聚簇、覆盖索引
三	锁机制	FTWRL/MDL、行锁、MVCC、间隙锁
四	日志系统	redo / undo / binlog、两阶段提交
五	隔离级别与 Spring 事务	4 种隔离级别、传播机制、多数据源事务
六	SQL 注入与版本特性	PreparedStatement、MySQL 8.0、Percona/MariaDB
七	SQL 调优（一）	EXPLAIN、type 7 档、Extra 关键字、ORDER BY 索引排序
八	SQL 调优（二）	最左前缀、索引设计、慢查询、pt-query-digest、COUNT
九	CASE 与锁实战	CASE WHEN、悲观锁、乐观锁、锁选型决策树

这套体系覆盖了 MySQL 知识的核心 90%——从架构到实战调优，从原理到业务落地。后续遇到具体的 MySQL 问题，可以按图索骥回到对应章节复习。