DAY 34 W5 · 公司立规矩：建模、约束与事务高频

并发下的怪事：隔离级别与 MVCC

两个运营同时改一条库存，后提交的把先提交的覆盖了。你复现并搞懂了并发异常的全家桶。

学 50 min

练 55 min

盘 15 min

共 120 分钟

完成这一天

学 · 50 min

01四种异常：脏读、不可重复读、幻读、丢失更新
场景两个会话同时操作一份数据，能出什么幺蛾子？全家桶一共四种。
① 脏读：读到别人还没提交的修改（他一回滚，你读的就是从没存在过的数据）；② 不可重复读：同一事务里两次读同一行，值变了（别人提交了 update）；③ 幻读：两次执行同一条件查询，行集变了（别人提交了 insert/delete）；④ 丢失更新：两个事务都「读-改-写」同一行，后提交的覆盖先提交的。
```
-- 丢失更新现场（两个窗口都执行）：
-- 窗口A                          -- 窗口B
begin;                            begin;
select stock from products
  where id = 1;      -- 100
                                  select stock from products where id = 1;  -- 100
update products set stock = 99
  where id = 1;
commit;                           update products set stock = 0 where id = 1;
                                  commit;   -- A 的 99 被 B 的 0 覆盖
```
易错脏读和不可重复读的区别就一个字：「没提交的」和「提交了的」。幻读盯的是行集不是单行值。
02四个隔离级别各自防住哪些异常
场景隔离级别就是「愿意容忍哪种怪事」的档位旋钮。
从松到紧：读未提交（啥都不防）/ 读已提交（防脏读）/ 可重复读（再防不可重复读和丢失更新）/ 串行化（全防，并发最低）。档位越紧，并发性能越差--隔离级别本质是正确性和并发度的交易。
```
show transaction_isolation;    -- PG 默认 read committed
set transaction isolation level repeatable read;   -- 当前事务用 RR
set default_transaction_isolation = 'repeatable read';  -- 会话级默认
```
易错矩阵别死背：先记住四种异常是什么，级别能防谁自然推导出来。
03PG 实际只实现三级（读未提交等价于读已提交）
场景你在 PG 里设 read uncommitted，结果脏读复现不了。
PG 的 MVCC 架构下不存在脏读：未提交的行版本对别人天然不可见，读未提交这个档位在 PG 里被静默升级成读已提交。所以「PG 支持四种隔离级别」的说法不严谨--能设四个名字，行为只有三档。
```
set transaction isolation level read uncommitted;
show transaction isolation;   -- 显示 read uncommitted
-- 但脏读照样发生不了：MVCC 保证了未提交不可见
```
易错面试说「PG 支持脏读」直接露馅；标准矩阵是理论，PG 的实现是现实，两边都要会讲。
04MVCC 原理：xmin / xmax 与快照可见性
场景为什么 PG 读不加锁也不脏读？答案写在每一行里。
每行藏着两个系统列：xmin（创建它的事务 id）、xmax（删除/更新它的事务 id）。修改不改原行，而是插入新版本。读时拿一个「快照」（记着哪些事务已提交），按规则判断每个版本可见：xmin 已提交且在快照内、且 xmax 未提交或不存在 -> 可见。读不阻塞写、写不阻塞读，就是这么来的。
```
select xmin, xmax, id, stock from products where id = 1;
-- update 后再看：旧行 xmax 被填上，新行是新的 xmin
-- pg 把旧版本留给并发读者，稍后 vacuum 清理
```
易错MVCC 的账单是死元组：更新越多、垃圾越多，vacuum 是系统的清道夫（W5 D33 长事务拖累的就是它）。
05PG 的 REPEATABLE READ 已能防幻读，与 MySQL 的差异
场景面试最爱：「PG 和 MySQL 的可重复读有什么区别？」
PG 的 RR 靠快照：整个事务用同一份快照，别人提交的插入和删除都不可见，幻读天然不存在。MySQL InnoDB 的 RR 靠锁（间隙锁挡住区间内的插入）防幻读，但两个事务都「读-改-写」同一行时仍可能互相覆盖，要靠 CAS 式更新防丢失。同名 RR，两套世界。
```
-- PG RR 下复现不了幻读：
-- 窗口A（RR）                     -- 窗口B
begin isolation level repeatable read;
select count(*) from t;  -- 10
                                  insert into t values (...); commit;
select count(*) from t;  -- 还是 10：快照没变，看不见 B 的插入
```
易错标准矩阵说「RR 防不了幻读」--PG 是例外。答这题的正确姿势：「标准这么说，但 PG 的实现是快照隔离，实际防住了」。

练 · 55 min · 开两个 psql 窗口逐个复现

读已提交下复现不可重复读

参考答案

PG 默认就是 read committed：每条语句拿一份新快照，B 提交后 A 的第二次读立刻看到新值--不可重复读复现。

-- 基线
update products set price = 100 where name = '商品1';

-- 窗口A                                        -- 窗口B
begin;
select price from products
where name = '商品1';   -- 100.00
                                              update products set price = 110
                                              where name = '商品1';
                                              commit;
select price from products
where name = '商品1';   -- 110.00：同一事务两次读，值变了
rollback;

update products set price = 100 where name = '商品1';   -- 还原

切到 REPEATABLE READ，验证不可重复读消失

参考答案

RR 在事务第一条语句执行时定下快照，此后别人提交的修改一律不可见--不可重复读消失。代价：RR 下并发改同一行，后到者在 update 处直接报 could not serialize access due to concurrent update。

-- 基线
update products set price = 100 where name = '商品1';

-- 窗口A
begin isolation level repeatable read;
select price from products where name = '商品1';   -- 100.00

-- 窗口B
update products set price = 110 where name = '商品1';
commit;

-- 窗口A 再读
select price from products where name = '商品1';   -- 还是 100.00：快照没变
commit;   -- A 提交之后再读才是 110

update products set price = 100 where name = '商品1';   -- 还原

在 RR 下尝试复现幻读，记录 PG 的实际表现

参考答案

复现不了：两次 count 完全一致。标准矩阵说 RR 防不住幻读，PG 是例外--它的 RR 是快照隔离，幻读天然不存在。面试答法：先说标准怎么说，再补「PG 的实现实际防住了」。

-- 窗口A
begin isolation level repeatable read;
select count(*) as 大额待支付单
from orders
where status = 1 and total_amount > 1500;   -- 记下这个数

-- 窗口B
insert into orders(user_id, status, total_amount, created_at)
values ((select id from users limit 1), 1, 1999.00, now())
returning id;   -- 记下 id，结束后删掉这行
commit;

-- 窗口A 再数
select count(*) as 大额待支付单
from orders
where status = 1 and total_amount > 1500;   -- 还是第一次的数
commit;

-- 窗口B 清理
delete from orders where id = '<刚才 returning 返回的 id>';

复现丢失更新，再用 SELECT FOR UPDATE 消除它

参考答案

for update 的价值不只是锁：后到者恢复执行时读到的是已提交的最新值，再基于它动手。防丢失更新还有个免显式锁的写法：单语句 CAS 式 update，set stock = stock - 1 配 where stock 大于等于 1。

-- 基线
update products set stock = 100 where name = '商品1';

-- ① 复现丢失更新（两窗口交错执行）
-- 窗口A                                        -- 窗口B
begin;
select stock from products
where name = '商品1';   -- 100
                                              begin;
                                              select stock from products
                                              where name = '商品1';   -- 100
update products set stock = 99
where name = '商品1';
commit;
                                              update products set stock = 99
                                              where name = '商品1';   -- 基于 B 读到的 100
                                              commit;
-- 结果 stock = 99：扣了两次却只少 1，A 的写入被 B 覆盖

-- ② FOR UPDATE 消除
update products set stock = 100 where name = '商品1';
-- 窗口A                                        -- 窗口B
begin;
select stock from products
where name = '商品1'
for update;   -- 100，行锁到手
                                              begin;
                                              select stock from products
                                              where name = '商品1' for update;
                                              -- B 卡住：等 A 释放锁
update products set stock = stock - 1
where name = '商品1';
commit;   -- A 提交，B 立刻恢复
                                              -- B 这次读到的是 99
                                              update products set stock = stock - 1
                                              where name = '商品1';
                                              commit;
-- 结果 stock = 98：两次扣减都生效

SERIALIZABLE 下制造串行化冲突，记录报错信息

参考答案

SQLSTATE 都是 40001（冲突是谓词级时，也可能拖到 commit 才报 read/write dependencies 版本）。串行化靠 SSI 主动杀掉「可能不可串行化」的事务换正确性--被杀方必须重试，这是正确性和并发度的交易。

-- 基线
update products set stock = 100 where name = '商品1';

-- 窗口A                                        -- 窗口B
begin isolation level serializable;
select stock from products
where name = '商品1';   -- 100
                                              begin isolation level serializable;
                                              select stock from products
                                              where name = '商品1';   -- 100
update products set stock = stock - 1
where name = '商品1';
                                              update products set stock = stock - 1
                                              where name = '商品1';
                                              -- B 在这条语句上排队等 A 的行锁
commit;
                                              -- A 一提交，B 的 update 立刻报错：
                                              -- ERROR: could not serialize access
                                              -- due to concurrent update
                                              rollback;   -- 只能回滚后重试

update products set stock = 100 where name = '商品1';   -- 还原

过关标准 产出一张自己实测的「隔离级别 × 异常」表格，能对照讲。

学 · 50 min

01四种异常：脏读、不可重复读、幻读、丢失更新

02四个隔离级别各自防住哪些异常

03PG 实际只实现三级（读未提交等价于读已提交）

04MVCC 原理：xmin / xmax 与快照可见性

05PG 的 REPEATABLE READ 已能防幻读，与 MySQL 的差异

练 · 55 min · 开两个 psql 窗口逐个复现

出题端点设置