锁机制：行锁、表锁与乐观悲观锁

S3-005必学高级约 60 分钟JDK 21企业开发基础 · MySQL 与数据建模

1. 本节定位

《事务与隔离级别》讲的是"别人改的数据我能看到吗"（隔离级别），本节讲"别人能不能同时改我要改的数据"（锁）。

这两个问题经常被混在一起，但它们是两套机制：

text

可见性问题 → 隔离级别（MVCC 快照）
互斥问题   → 锁

本节用真实数据库把"锁等待""死锁"都跑出来——包括数据库自己打印的循环等待链路。

2. 学习目标

完成本节后，你应该能够：

说出行锁与表锁、共享锁与排他锁的区别；
说出"写-写冲突会阻塞、读不会被阻塞"，并知道这是 MVCC 带来的；
会用 SELECT ... FOR UPDATE 实现悲观锁；
会用版本号实现乐观锁，并用"影响行数"判断冲突；
说出死锁的产生条件，以及"固定加锁顺序"为什么能避免它；
说出"带条件的 UPDATE"为什么比"先查再改"更安全；
知道生产环境必须配锁等待超时。

3. 前置知识检查

知道事务与隔离级别吗？（《事务与隔离级别》）
会看 UPDATE 的影响行数吗？（《SQL 基础》）

4. 为什么需要这个知识

看一段"看起来很对"的库存扣减：

java

int stock = queryInt("SELECT stock FROM product WHERE id = 1");     // 查
if (stock < quantity) {                                             // 判断
    return false;
}
update("UPDATE product SET stock = stock - " + quantity + " WHERE id = 1");   // 改

单线程下完美工作，并发下会超卖：

text

时刻 1   事务 A：查到库存 1
时刻 2   事务 B：查到库存 1          ← 两个事务看到同一个值
时刻 3   事务 A：判断 1 >= 1，通过，扣减 → 库存 0
时刻 4   事务 B：判断 1 >= 1，通过，扣减 → 库存 -1   ← 超卖！

"先检查后执行"在并发下是经典的竞态条件——检查时成立，执行时已经变了。

本节要解决的正是这类问题。

5. 核心概念

5.1 锁的分类

维度	类型	说明
粒度	行锁（Row Lock）	锁住一行（MySQL 的 InnoDB 默认）
	表锁（Table Lock）	锁整张表（DDL、`LOCK TABLES`）
	间隙锁（Gap Lock）	锁住"区间"，防止区间内插入（MySQL RR 级别用它防幻读）
模式	共享锁（S 锁，读锁）	多个事务可同时持有；`SELECT ... FOR SHARE`
	排他锁（X 锁，写锁）	只能一个事务持有；`SELECT ... FOR UPDATE`

兼容关系（谁挡谁）：

已有锁 \ 请求锁	共享锁	排他锁
共享锁	✅ 兼容	❌ 等待
排他锁	❌ 等待	❌ 等待

一句话：读读兼容，其他都互斥。

5.2 写-写会阻塞，读不会被阻塞

实测（连接 A 更新但未提交，连接 B 也想更新同一行）：

text

【连接 A】更新 id=1 成功（影响 1 行），但**没有提交**
【连接 B】被阻塞了 2044 ms 后失败：错误: 由于锁超时，取消语句操作
【连接 A】回滚后锁释放，B 才能继续。

但普通的 SELECT 不会被阻塞——它会读到旧版本（MVCC 的快照）。

这个不对称非常重要：

操作	会被写锁阻塞吗	原因
普通 `SELECT`	❌ 不会	MVCC：读旧版本快照
`SELECT ... FOR UPDATE`	✅ 会	主动请求排他锁
`UPDATE` / `DELETE`	✅ 会	需要排他锁
`INSERT`	视情况	可能与间隙锁冲突

带来的实际好处：读多写少的系统里，读操作不会因为写事务而卡住——这正是 MVCC 的价值。

带来的坑：SELECT 读到的是快照，不是最新值——如果拿这个值去做判断，就可能踩到 4.1 节的超卖问题。

5.3 悲观锁：`SELECT ... FOR UPDATE`

思路：假设冲突一定会发生，所以在读取时就把行锁住。

sql

BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;    -- 加排他锁
-- 此时其他事务改这一行会阻塞
UPDATE account SET balance = balance - 100 WHERE id = 1;
COMMIT;                                                  -- 提交后释放锁

实测（连接 A 锁住后，连接 B 也请求 FOR UPDATE）：

text

【连接 A】锁定 id=1 后读到余额：1000
【连接 B】FOR UPDATE 被阻塞并超时：错误: 由于锁超时，取消语句操作
【连接 A】扣款 100 并提交，余额变为 900

适用场景：

场景	说明
冲突频繁（如秒杀、热点账户）	悲观锁能避免"重试风暴"
临界区逻辑复杂（多步操作）	锁住后可以放心做多步
需要"读到的值一定是最新的"	`FOR UPDATE` 绕过 MVCC 快照

三个注意点：

必须锁定"所有会用到的行"，且顺序要一致（否则死锁，见 5.5）；
锁的范围要尽量小（WHERE 条件要能用索引，否则可能锁很多行甚至全表）；
必须配锁等待超时（见 5.6）。

5.4 乐观锁：版本号

思路：假设冲突很少发生，所以不锁，在提交时检查"我读到的数据有没有被别人改过"。

sql

-- 读的时候把版本号也读出来
SELECT id, balance, version FROM account WHERE id = 2;      -- version = 0

-- 更新时带上版本号条件
UPDATE account SET balance = balance - 50, version = version + 1
WHERE id = 2 AND version = 0;                                -- 版本不匹配则影响 0 行

实测（两条连接读到同一版本，A 先更新成功）：

text

【A】【B】读到的版本号都是 0
【连接 A】提交更新（影响 1 行）
【连接 B】用旧版本更新（影响 0 行）  ← 影响 0 行 = 检测到冲突
乐观锁不阻塞：两条语句都没等待，冲突靠「影响行数」发现。

应用层的处理：

java

int updated = update("UPDATE ... WHERE id = ? AND version = ?", ...);
if (updated == 0) {
    // 冲突：重试（重新读取最新值再试）或返回"请刷新后重试"
}

适用场景：

场景	说明
冲突较少（如用户改自己的资料）	乐观锁的"无锁"优势体现出来
跨请求的操作（如"编辑页面 → 提交"）	事务跨越用户思考时间，悲观锁不可行
读多写少	不加锁能显著提升并发

5.5 悲观锁 vs 乐观锁：怎么选

维度	悲观锁	乐观锁
加锁时机	读取时	更新时（靠条件判断）
阻塞	会（其他事务等锁）	不会（失败重试）
适用冲突率	高	低
实现位置	数据库（`FOR UPDATE`）	应用 + 数据库（版本号）
跨请求	❌ 不适合（锁不能跨请求持有）	✅ 适合
失败表现	等待 → 超时	影响 0 行 → 重试

一条经验判断：

冲突率高用悲观锁，冲突率低用乐观锁。
秒杀（1000 人抢 10 件）→ 悲观锁（乐观锁会让 990 个人重试，系统被重试压垮）
用户改资料（自己改自己的）→ 乐观锁（几乎没有冲突）

5.6 死锁：产生与避免（本节重点）

死锁 = 两个事务互相等待对方持有的锁。

实测（连接 A 先锁 id=1，连接 B 先锁 id=2，然后各自尝试锁对方那行）：

text

【A】持有 id=1 的行锁，【B】持有 id=2 的行锁
  【A】被数据库中止：错误: 检测到死锁 详细：进程47156等待在事务 880上的ShareLock;
       由进程48540阻塞. 进程48540等待在事务 879上的ShareLock; 由进程47156阻塞.
  【B】拿到了 id=1 的锁，顺利完成

注意数据库的做法：它主动检测循环等待并中止其中一个事务（而不是无限等待）——被中止的事务就是那个"死锁受害者"。

而且报错信息里包含了完整的等待链路：

text

进程 A 等待在事务 880 上的锁; 由进程 B 阻塞.
进程 B 等待在事务 879 上的锁; 由进程 A 阻塞.

这条信息足以定位是"哪两条 SQL 互相等"——生产排查死锁时，第一件事就是找这个日志。

死锁的四个必要条件（与《线程死锁》一致）：

条件	含义	能否破坏
互斥	锁同时只能被一个事务持有	❌ 不能（锁的本质）
持有并等待	拿着一个锁，再等另一个	✅ 一次锁定所有需要的行
不可剥夺	已持有的锁不能被抢走	✅ 用超时放弃
循环等待	A 等 B、B 等 A	✅ 固定加锁顺序 ✓ 最实用

实践中最有效的两招：

招式	做法
固定加锁顺序	所有事务都按"主键升序"加锁（如转账时先锁 `id` 小的账户）
一次锁定全部	用 `WHERE id IN (1, 2) ORDER BY id FOR UPDATE` 一次锁完

sql

-- 转账：一次锁两行，且按 id 排序 → 不会死锁
SELECT * FROM account WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

5.7 生产必备：锁等待超时

实测输出里那个 2044 ms 就是超时——因为示例代码先执行了：

sql

SET lock_timeout = '2s';

为什么必须配：

不配的后果	说明
请求无限等待	用户界面一直转圈，最后可能等来超时
连接池被耗尽	等待的连接不释放 → 其他请求拿不到连接 → 整个服务雪崩
故障难定位	只知道"慢"，不知道"在等锁"

配置建议：

层面	配置
数据库会话	`SET lock_timeout = '3s'`（超时后该语句失败，事务可回滚）
JDBC	`connection.setNetworkTimeout(...)`
连接池	HikariCP 的 `connectionTimeout`（默认 30 秒）
业务层	给整个请求设超时（比数据库超时更早触发，能给出友好提示）

排查锁等待的 SQL（PostgreSQL）：

sql

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

看到这个结果就说明"有语句在等锁" ✓

5.8 最实用的写法：把"判断"放进 `UPDATE`

回到 4.1 节的超卖问题——最简单的解法不是加锁，而是把判断和扣减合并成一条语句：

sql

UPDATE product SET stock = stock - 100 WHERE id = 1 AND stock >= 100;

它的好处：

好处	说明
原子	"判断 + 扣减"在一条语句内完成，中间不会被插入
不需要显式锁	数据库内部会对这一行加行锁
用影响行数判断结果	返回 0 就是"条件不满足"（库存不足）

实测：

text

带条件的 UPDATE 影响行数：1（余额不足时为 0，不需要先查再判断）

这条技巧适用于：

sql

-- 库存扣减
UPDATE product SET stock = stock - ? WHERE id = ? AND stock >= ?;

-- 余额扣减（不能透支）
UPDATE account SET balance = balance - ? WHERE id = ? AND balance >= ?;

-- 状态流转（防止重复处理）
UPDATE order SET status = 'PAID' WHERE id = ? AND status = 'CREATED';

-- 抢票/抢券
UPDATE ticket SET owner_id = ? WHERE id = ? AND owner_id IS NULL;

最后一条尤其重要："状态流转"用带条件的 UPDATE，能天然防止"并发下同一个订单被处理两次"——这比"先查状态再更新"可靠得多。

6. 递进示例

6.1 最小示例：带条件的 UPDATE

sql

UPDATE account SET balance = balance - 100 WHERE id = 1 AND balance >= 100;

返回 1 = 扣款成功，返回 0 = 余额不足 ✓ 一行语句解决了"先查再判断再改"的全部并发问题。

6.2 业务示例：悲观锁做转账

sql

BEGIN;
SELECT * FROM account WHERE id IN (1, 2) ORDER BY id FOR UPDATE;    -- 一次锁两行，按序
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

6.3 反例：先查再改

java

int stock = query("SELECT stock FROM product WHERE id = 1");    // 查
if (stock < quantity) return false;                             // 判断
update("UPDATE product SET stock = stock - " + quantity + " WHERE id = 1");   // 改

并发下必然超卖（见第 4 节）。修复：用 5.8 节的带条件 UPDATE，或加 FOR UPDATE。

7. 完整可运行代码

文件：labs/sql-advanced/src/main/java/cn/kb/learn/sql/adv/LockLesson.java（节选）

java

public class LockLesson {
    public static void main(String[] args) throws InterruptedException {
        try (PgRunner a = PgRunner.open(); PgRunner b = PgRunner.open()) {
            prepare(a);
            rowLockBlocks(a, b);
            pessimisticLock(a, b);
            optimisticLock(a, b);
            deadlock(a, b);
        }
    }

    /** 行锁：一个事务锁住某行时，另一个事务改它会一直等 */
    private static void rowLockBlocks(PgRunner a, PgRunner b) {
        a.begin();
        a.execute("UPDATE account SET balance = balance - 10 WHERE id = 1");
        System.out.println("【连接 A】更新 id=1 成功，但**没有提交**");

        b.execute("SET lock_timeout = '2s'");        // 避免无限等待
        long start = System.currentTimeMillis();
        try {
            b.execute("UPDATE account SET balance = balance - 10 WHERE id = 1");
        } catch (RuntimeException exception) {
            System.out.println("【连接 B】被阻塞了 " + (System.currentTimeMillis() - start)
                    + " ms 后失败：" + firstLine(messageOf(exception)));
        }
        a.rollback();
    }

    /** 乐观锁：用版本号检测冲突 */
    private static void optimisticLock(PgRunner a, PgRunner b) {
        long versionA = Long.parseLong(a.scalar("SELECT version FROM account WHERE id = 2"));
        long versionB = Long.parseLong(b.scalar("SELECT version FROM account WHERE id = 2"));

        int rowsA = a.execute("UPDATE account SET balance = balance - 50, version = version + 1 "
                + "WHERE id = 2 AND version = " + versionA);
        int rowsB = b.execute("UPDATE account SET balance = balance - 50, version = version + 1 "
                + "WHERE id = 2 AND version = " + versionB);
        // rowsA = 1，rowsB = 0
    }

    /** 死锁：两条连接以相反顺序加锁，必须用两个线程才能形成 */
    private static void deadlock(PgRunner a, PgRunner b) throws InterruptedException {
        a.begin();
        b.begin();
        a.execute("UPDATE account SET balance = balance - 1 WHERE id = 1");    // A 持 id=1
        b.execute("UPDATE account SET balance = balance - 1 WHERE id = 2");    // B 持 id=2

        Thread threadA = new Thread(() ->
                a.execute("UPDATE account SET balance = balance - 1 WHERE id = 2"));   // A 等 B
        Thread threadB = new Thread(() ->
                b.execute("UPDATE account SET balance = balance - 1 WHERE id = 1"));   // B 等 A
        threadA.start();
        threadB.start();
        threadA.join();
        threadB.join();
    }
}

运行命令：

powershell

cd labs
$env:PGPASSWORD = "你的密码"
./mvnw -pl sql-advanced compile exec:exec -Dexec.mainClass=cn.kb.learn.sql.adv.LockLesson

预期输出：

text

=== 1. 行锁：写操作会被未提交的锁挡住 ===
【连接 A】更新 id=1 成功（影响 1 行），但**没有提交**
【连接 B】被阻塞了 2044 ms 后失败：错误: 由于锁超时，取消语句操作 在位置：当更新关系"account"的元组(0, 1)时
【连接 A】回滚后锁释放，B 才能继续。
注意：这是**写-写冲突**；普通的 SELECT 不会被阻塞（MVCC 读旧版本）。

=== 2. 悲观锁：SELECT ... FOR UPDATE ===
【连接 A】锁定 id=1 后读到余额：1000
【连接 B】FOR UPDATE 被阻塞并超时：错误: 由于锁超时，取消语句操作
【连接 A】扣款 100 并提交，余额变为 900

=== 3. 乐观锁：UPDATE ... WHERE version = ? ===
【A】【B】读到的版本号都是 0
【连接 A】提交更新（影响 1 行）
【连接 B】用旧版本更新（影响 0 行）  ← 影响 0 行 = 检测到冲突，应用层据此重试或报错
乐观锁不阻塞：两条语句都没等待，冲突靠「影响行数」发现。

=== 4. 死锁：相反的加锁顺序 ===
【A】持有 id=1 的行锁，【B】持有 id=2 的行锁
  【A】被数据库中止：错误: 检测到死锁 详细：进程47156等待在事务 880上的ShareLock;
       由进程48540阻塞. 进程48540等待在事务 879上的ShareLock; 由进程47156阻塞.
  【B】拿到了 id=1 的锁，顺利完成
数据库会检测到循环等待并**主动中止其中一个事务**（另一个得以继续）。

=== 5. 正确的转账：固定顺序 + 一行 SQL 完成判断与扣减 ===
带条件的 UPDATE 影响行数：1（余额不足时为 0，不需要先查再判断）

四处最值得记住的：

输出	含义
`被阻塞了 2044 ms 后失败`	锁等待是真的会阻塞，且会持续到超时
`影响 0 行 = 检测到冲突`	乐观锁靠"影响行数"判断冲突，不需要额外的锁
`检测到死锁 ... 由进程48540阻塞`	数据库主动检测死锁，并打印完整的循环等待链路
`带条件的 UPDATE 影响行数：1`	一行 SQL 替代"查-判-改"三步

8. Debug 观察点

观察"写阻塞、读不阻塞"：在连接 A 未提交时，用另一条连接执行普通 SELECT（不阻塞、读到旧值）与 SELECT ... FOR UPDATE（阻塞）——对比这两种读的差别；
在死锁实验的 threadA.join() 处断点，观察两个线程的状态：一个在等锁、一个在等锁——"循环等待"在调试器里是可见的；

查锁等待现场：在阻塞发生时，用另一个客户端执行：

sql

SELECT pid, state, wait_event_type, wait_event, left(query, 60)
FROM pg_stat_activity WHERE wait_event_type = 'Lock';

能看到"谁在等锁、等的是什么"；

观察 lock_timeout 的作用：把 '2s' 改成 '10s'（或 0 表示不限制），重跑第 1 步——体会"不设超时的后果"（示例会卡住很久）。

9. 常见错误

错误 1：先查再改（并发超卖）

见第 4 节与 6.3 节。修复：用带条件的 UPDATE，或 FOR UPDATE。

错误 2：`FOR UPDATE` 没有索引，锁了全表

sql

SELECT * FROM orders WHERE status = 'CREATED' FOR UPDATE;    -- status 没索引 → 锁大量行

后果：本来想锁几行，结果锁住了大量行（甚至全表），并发放并发急剧下降。

修复：FOR UPDATE 的 WHERE 条件必须能用索引；如果锁范围大，考虑分批处理。

错误 3：用 `FOR UPDATE` 做长事务

java

connection.setAutoCommit(false);
lockRow();                          // 锁住
String result = remoteService.call();    // ✗ 远程调用（可能几秒）
updateAndCommit();

后果：锁被持有几秒，其他事务全部排队——这是"线上突然变慢"的典型成因。

修复：远程调用放事务外（《事务与隔离级别》陷阱一）。

错误 4：不设锁等待超时

见 5.7 节。最坏的情况是连接池被耗尽，导致服务整体不可用（不只是"慢"）。

错误 5：以为乐观锁"不用处理冲突"

java

int updated = update("UPDATE ... WHERE id = ? AND version = ?", ...);
// 忘了检查 updated == 0

修复：必须检查影响行数，并决定是"重试"还是"提示用户"。

一个实践细节：重试要有次数上限（如 3 次）与退避策略（随机等待），否则冲突激烈时会形成"重试风暴"。

错误 6：加锁顺序不一致导致死锁

java

// 服务 A：先锁账户 1，再锁账户 2
// 服务 B：先锁账户 2，再锁账户 1        ← 死锁

修复：全局统一加锁顺序（如按主键升序），或一次锁完（WHERE id IN (...) ORDER BY id FOR UPDATE）。

这条规则要写进团队规范——因为"换个顺序"在代码审查时不明显，但线上会周期性出现死锁。

10. 动手实验

目标：把五种锁的场景全部跑一遍，并亲手制造一次死锁。

步骤：

运行第 7 节的命令，核对五组输出；
验证"读不阻塞"：在第 1 步连接 A 未提交时，插入一行普通 SELECT balance FROM account WHERE id = 1——确认它立即返回旧值；
把 lock_timeout 去掉（改成 '0'）重跑第 1 步，观察程序卡住；再改回 2s；
制造死锁并修复：把死锁实验的两条语句改成"都按 id 升序加锁"，重跑——确认不再出现死锁；
写一个并发扣库存的测试：
- 用带条件的 UPDATE 实现扣减；
- 起 10 个线程并发扣同一个商品的库存（初始 100，每个线程扣 1）；
- 断言最终库存 = 90（如果没有并发保护，结果会大于 90）；
对比实验：把第 5 步改成"先查再改"，重跑，观察最终库存的错误。

验收方式：能说出第 5、6 步的结果差异，以及第 4 步修复前后的变化。

扩展任务：实现一个"乐观锁重试"helper：更新失败时自动重读版本并重试（上限 3 次，每次随机退避 10-50 ms），并说明"为什么重试要加随机等待"。

11. 练习题

基础题

判断：普通 SELECT 会被其他事务的写锁阻塞。（对 / 错）
填空：悲观锁用 ______ 实现；乐观锁用 ______ 实现，靠 ______ 判断冲突。
简答：为什么"固定加锁顺序"能避免死锁？

代码阅读题

sql

-- 事务 A
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- （事务 B 此时执行：UPDATE account SET balance = balance + 100 WHERE id = 1;）
COMMIT;

事务 B 会发生什么？为什么？
如果事务 A 一直不提交，事务 B 最终会怎样？（提示：取决于配置）

编程题

实现"安全扣库存"并做并发验证：
- 表：product(id BIGINT PRIMARY KEY, name VARCHAR(50), stock INT NOT NULL)，初始 stock = 100；
- 方法 boolean decrease(long productId, int quantity)：
  - 用带条件的 UPDATE 实现（不要先查再改）；
  - 库存不足返回 false；
- 写一个并发测试：10 个线程各扣 1 件，断言最终 stock = 90 且成功次数 = 10；
- 对比实验：改成"先查再改"的版本，重跑，记录最终 stock（大概率 > 90）；
- 说明：为什么"影响行数"比"先查询"更可靠？

综合任务

为一个"秒杀"场景设计并发控制方案：
- 需求：1000 个用户抢 10 件商品，不能超卖；
- 分析：为什么"乐观锁"在这个场景下不合适？（提示：想想 990 个失败请求会做什么）
- 设计：给出你的方案（至少包含三层：数据库层、应用层、流量层），说明每层解决什么问题；
- 写出关键的数据库语句与 Java 代码；
- 说明：如果只允许改一行 SQL，你会改哪一行？ 为什么？
- 额外要求：列出这个方案的 3 个"仍然可能出问题的地方"（如超时、重复下单、黄牛）。

分级提示

提示 1（方向）：第 4 题的关键是"写-写冲突"——UPDATE 需要排他锁。

提示 2（关键点）：第 6 题的"影响行数"是数据库在加锁状态下判断条件后返回的，所以它能反映"当时的真实状态"。

提示 3（接近答案）：第 7 题的"只改一行 SQL"指的是把扣减改成 UPDATE ... WHERE stock >= ?——它用最少的改动解决了"不超卖"这个核心问题。

12. 参考答案

基础题

错。普通 SELECT 不会被写锁阻塞——MVCC 让它读旧版本快照。

这正是"写-写冲突会阻塞、读不会被阻塞"的机制来源，也是很多"读到的值不是最新"的困惑的根源。需要读最新值时，必须用 SELECT ... FOR UPDATE（但那就变成会被阻塞了）。

SELECT ... FOR UPDATE；版本号（或时间戳）；影响行数（UPDATE 返回的 getUpdateCount()）。
因为死锁的必要条件之一是"循环等待"——固定顺序让所有事务的等待关系变成单向的（只能"向后"等），不可能形成环。

text

不固定顺序：A 持 1 等 2，B 持 2 等 1     → 环 → 死锁
固定顺序：  A 持 1 等 2，B 只能先要 1     → 无环 → B 等待，A 完成后继续

代码阅读题（第 4、5 题）

第 4 题：事务 B 会被阻塞。

因为事务 A 的 UPDATE 已经对 id = 1 这一行加了排他锁（且未提交），而事务 B 的 UPDATE 也需要同一行的排他锁——两者互斥，B 只能等待。

注意这与你可能预期的不一样：B 不是"失败"，而是等待（在 PG 里是阻塞在 wait_event_type = 'Lock'）。如果 A 最终提交，B 会立刻成功执行。

第 5 题：取决于配置：

配置	结果
设置了 `lock_timeout`	B 在超时后失败（报"锁超时"）
设置了 `statement_timeout`	同样会超时失败，但语义是"语句执行太久"
两个都没设	B 会一直等下去（直到 A 提交/回滚，或连接被网络层断开）

这就是为什么生产必须配超时（5.7 节）：不设超时的话，一个忘记提交的事务可能挂住一批请求，最终耗尽连接池。

编程题（第 6 题）

java

package cn.kb.learn.sql.adv;

import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicInteger;

public class StockDecrease {
    /** 正确写法：把「判断」与「扣减」合并成一条带条件的 UPDATE */
    static boolean decrease(PgRunner db, long productId, int quantity) {
        int updated = db.execute(
                "UPDATE product SET stock = stock - " + quantity
                        + " WHERE id = " + productId + " AND stock >= " + quantity);
        return updated > 0;
    }

    /** 错误写法：先查再改（并发下会超卖） */
    static boolean decreaseWrong(PgRunner db, long productId, int quantity) {
        int stock = Integer.parseInt(db.scalar("SELECT stock FROM product WHERE id = " + productId));
        if (stock < quantity) {
            return false;
        }
        db.execute("UPDATE product SET stock = stock - " + quantity + " WHERE id = " + productId);
        return true;
    }

    public static void main(String[] args) throws InterruptedException {
        try (PgRunner db = PgRunner.open()) {
            db.execute("DROP TABLE IF EXISTS product");
            db.execute("CREATE TABLE product (id BIGINT PRIMARY KEY, name VARCHAR(50), stock INT NOT NULL)");

            System.out.println("=== 正确写法：带条件的 UPDATE ===");
            runConcurrent(db, StockDecrease::decrease);

            System.out.println("=== 错误写法：先查再改 ===");
            runConcurrent(db, StockDecrease::decreaseWrong);
        }
    }

    private static void runConcurrent(PgRunner db,
                                      java.util.function.BiFunction<PgRunner, Long, Boolean> single)
            throws InterruptedException {
        db.execute("DELETE FROM product");
        db.execute("INSERT INTO product VALUES (1, '秒杀商品', 100)");

        int threads = 10;
        CountDownLatch start = new CountDownLatch(1);
        CountDownLatch done = new CountDownLatch(threads);
        AtomicInteger success = new AtomicInteger();

        for (int index = 0; index < threads; index++) {
            new Thread(() -> {
                try (PgRunner worker = PgRunner.open()) {          // 每个线程一条连接
                    start.await();
                    if (single.apply(worker, 1L)) {
                        success.incrementAndGet();
                    }
                } catch (Exception exception) {
                    // 简化处理
                } finally {
                    done.countDown();
                }
            }).start();
        }

        start.countDown();
        done.await();

        System.out.println("  成功次数：" + success.get());
        System.out.println("  最终库存：" + db.scalar("SELECT stock FROM product WHERE id = 1")
                + "（初始 100，期望 90）");
    }
}

预期结果：

写法	成功次数	最终库存
带条件的 UPDATE	10	90 ✓
先查再改	可能 > 10?（不可能，但库存会错）	> 90（丢更新）

"先查再改"为什么会错：多个线程查到相同的库存值（如 100），各自判断"够扣"，然后各自执行 stock = stock - 1——但它们读到的是同一个快照，最后的写入会互相覆盖（虽然 stock = stock - 1 本身是原子的，但"判断"用的是一个可能过期的值）。

为什么"影响行数"比"先查询"更可靠：

	先查询	影响行数
判断依据	查询时的快照（可能已过期）	加锁后、执行时的真实状态
是否原子	❌ 判断与修改分离	✅ 判断与修改在同一条语句内
并发安全	❌ 需要额外加锁	✅ 不需要

一句话："影响行数"是数据库在"已经拿到锁"的情况下给你的答案——它比你先前的查询结果更新、更可靠。

综合任务（第 7 题）

为什么乐观锁不适合秒杀：

text

1000 个请求同时到达：
  每个请求：读库存（都是 10）→ 尝试 UPDATE ... WHERE version = ?
  结果：1 个成功，999 个"影响 0 行"
  这 999 个会做什么？→ 重试（再读、再更新）→ 又 998 个失败 → 再重试...

"重试风暴"的代价：

问题	说明
数据库压力 ×N	每个请求重试 3 次 = 3000 次 SQL
响应时间爆炸	用户等待重试完成，体验极差
缓存/连接池被打满	高并发下的连锁反应
成功率并不会提高	库存就 10 件，怎么重试都只有 10 个成功

正确方案（三层）：

markdown

## 第一层：流量层（把请求挡在外面）

- **前端限流**：按钮点击后置灰、倒计时
- **网关限流**：按用户/接口限速（如令牌桶）
- **答题/验证码**：过滤机器请求
- **提前售罄**：库存为 0 时直接拒绝（不查数据库）

## 第二层：应用层（减少数据库压力）

- **缓存预热**：库存放到 Redis，用 `DECR` 原子扣减
- **扣减成功再落库**：Redis 扣成功 → 发消息 → 异步创建订单
- **同一用户去重**：防止一个人抢多次（Redis SET + 过期）

## 第三层：数据库层（最后一道防线）

- **带条件的 UPDATE**：
  UPDATE product SET stock = stock - 1 WHERE id = ? AND stock >= 1;
- **影响行数 = 0 → 库存不足**（这是最终的、权威的判断）
- 数据库是"最终一致性"的保证者：即使前面两层被绕过，这里也不会超卖

"只改一行 SQL"的答案：

sql

UPDATE product SET stock = stock - 1 WHERE id = ? AND stock >= 1;

为什么：它用最小的改动解决了"不超卖"这个核心问题——把"判断"交给数据库在加锁状态下执行，避免了"先查后改"的竞态。其他优化（限流、缓存）是性能问题，"不超卖"是正确性问题——正确性优先。

这个方案的 3 个仍可能出问题的地方：

问题	说明	缓解
重复下单	网络重试时同一个用户可能下两单	请求幂等（唯一约束 + 业务单号）
Redis 与数据库不一致	Redis 扣成功但数据库写失败	补偿任务对账；以数据库为准
黄牛	抢到了但集中在少数账号	风控、限购、实名
（第四个）超时与体验	用户等待很久最后失败	前端明确的等待与失败提示；预热库存比例

最后一条经验：

秒杀的核心不是"抢得快"，而是"在极端流量下不崩且不超卖"。
前者靠缓存与限流，后者靠数据库的最后一层带条件更新——两层缺一不可。

13. 掌握标准

[ ] 能说出锁的粒度与模式，以及"读读兼容、其他互斥"；
[ ] 知道写-写会阻塞、普通读不会，并能解释原因（MVCC）；
[ ] 会用 FOR UPDATE（且知道它的 WHERE 必须有索引）；
[ ] 会用版本号实现乐观锁，并一定检查影响行数；
[ ] 能说出死锁的四个必要条件，并知道"固定加锁顺序"是最实用的解法；
[ ] **会用"带条件的 UPDATE"**替代"先查再改"，并用影响行数判断结果；
[ ] 知道生产必须配锁等待超时。

14. 扩展资源

15. 下一步学习

数据库基础部分到此完成（《MySQL 搭建与客户端使用》 → 《SQL 基础》 → 《表设计与约束》 → 《数据库范式与表关联设计》 → 《索引》 → 《事务与隔离级别》 → 《锁机制：行锁、表锁与乐观悲观锁》）。

接下来可以：

S3-017 JDBC 与数据库连接：把本节的知识用到 Java 代码里
《Spring：IOC 与依赖注入》：进入框架的世界
《执行计划与慢查询定位》：把索引知识用到排查实践

前置知识

S3-004 事务与隔离级别

后续知识

暂无

进度只保存在本机浏览器，可随时清除

外部学习资源

面试题库索引

项目与练手索引

工具与软件索引

锁机制：行锁、表锁与乐观悲观锁

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 锁的分类

5.2 写-写会阻塞，读不会被阻塞

5.3 悲观锁：`SELECT ... FOR UPDATE`

5.4 乐观锁：版本号

5.5 悲观锁 vs 乐观锁：怎么选

5.6 死锁：产生与避免（本节重点）

5.7 生产必备：锁等待超时

5.8 最实用的写法：把"判断"放进 `UPDATE`

6. 递进示例

6.1 最小示例：带条件的 UPDATE

6.2 业务示例：悲观锁做转账

6.3 反例：先查再改

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：先查再改（并发超卖）

错误 2：`FOR UPDATE` 没有索引，锁了全表

错误 3：用 `FOR UPDATE` 做长事务

错误 4：不设锁等待超时

错误 5：以为乐观锁"不用处理冲突"

错误 6：加锁顺序不一致导致死锁

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习

前置知识

后续知识

锁机制：行锁、表锁与乐观悲观锁 ​

1. 本节定位 ​

2. 学习目标 ​

3. 前置知识检查 ​

4. 为什么需要这个知识 ​

5. 核心概念 ​

5.1 锁的分类 ​

5.2 写-写会阻塞，读不会被阻塞 ​

5.3 悲观锁：SELECT ... FOR UPDATE ​

5.4 乐观锁：版本号 ​

5.5 悲观锁 vs 乐观锁：怎么选 ​

5.6 死锁：产生与避免（本节重点） ​

5.7 生产必备：锁等待超时 ​

5.8 最实用的写法：把"判断"放进 UPDATE ​

6. 递进示例 ​

6.1 最小示例：带条件的 UPDATE ​

6.2 业务示例：悲观锁做转账 ​

6.3 反例：先查再改 ​

7. 完整可运行代码 ​

8. Debug 观察点 ​

9. 常见错误 ​

错误 1：先查再改（并发超卖） ​

错误 2：FOR UPDATE 没有索引，锁了全表 ​

错误 3：用 FOR UPDATE 做长事务 ​

错误 4：不设锁等待超时 ​

错误 5：以为乐观锁"不用处理冲突" ​

错误 6：加锁顺序不一致导致死锁 ​

10. 动手实验 ​

11. 练习题 ​

12. 参考答案 ​

13. 掌握标准 ​

14. 扩展资源 ​

15. 下一步学习 ​

前置知识

后续知识

锁机制：行锁、表锁与乐观悲观锁

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 锁的分类

5.2 写-写会阻塞，读不会被阻塞

5.3 悲观锁：`SELECT ... FOR UPDATE`

5.4 乐观锁：版本号

5.5 悲观锁 vs 乐观锁：怎么选

5.6 死锁：产生与避免（本节重点）

5.7 生产必备：锁等待超时

5.8 最实用的写法：把"判断"放进 `UPDATE`

6. 递进示例

6.1 最小示例：带条件的 UPDATE

6.2 业务示例：悲观锁做转账

6.3 反例：先查再改

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：先查再改（并发超卖）

错误 2：`FOR UPDATE` 没有索引，锁了全表

错误 3：用 `FOR UPDATE` 做长事务

错误 4：不设锁等待超时

错误 5：以为乐观锁"不用处理冲突"

错误 6：加锁顺序不一致导致死锁

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习