WEEK 05

公司立规矩：建模、约束与事务

技术债集中爆发：脏数据、重复注册、改价没记录、并发下单差点把库存扣成负数。公司招了后端，你们决定把库重新立规矩，支付模块（payments 表）也在这周上线--你从「查数据的人」变成「能设计表的人」。
目标：从「查数据的人」变成「能设计表的人」。事务与隔离级别是八股必考区。
0 / 7 天
剧情 复盘一个月攒下的债：status 靠裸字符串、有人问你金额能不能用 float、id 会不会溢出。今天重整 DDL，把类型的坑一次踩明白。
练 · 60 min
  1. 跑 select 0.1::float8 + 0.2::float8 与 numeric 版对比，记下结果
  2. 用 IDENTITY 重建一张表，对比 serial 的差异
  3. 给 order_items 加生成列 line_total = qty * unit_price
  4. 在 12 万行的 order_items 上加一个带 DEFAULT 的列，记录耗时；再把一列 varchar(20) 改成 varchar(50)、改成 int，对比两次耗时
  5. 用 pg_dump -s 导出整库结构存档
过关能说出 uuid 做主键的两个具体代价、金额不能用 float 的原因、timestamptz 磁盘上存的是什么。
专注视图 ->
剧情 支付模块今天上线，payments 表由你来建。后端说：「别靠应用代码保证正确，数据库要能挡住脏数据。」
练 · 65 min
  1. 跑 seed.sql「W5」段灌入支付流水；按页面底部结构建 payments，PK / FK / NOT NULL 一次到位
  2. 给前几周建的 7 张表补齐全部 PK 和 FK（还债日）
  3. 加 CHECK (amount > 0)，插一条负数验证报错
  4. 用唯一约束防止「同一用户同一秒重复下单」
  5. 做 ON DELETE CASCADE 实验：删一个用户，看订单是否连带消失
  6. 写下「生产用外键」的支持理由 3 条、反对理由 3 条
过关能给出你自己的外键取舍结论，并说明在什么规模下会改变结论。
专注视图 ->
剧情 后端提议把用户名、商品名直接冗余进订单表「查得快」。你想起了范式，决定跟他掰扯清楚。
练 · 55 min
  1. 拿一张「订单宽表」（含用户名、商品名、类目名）逐步拆到 3NF，写出每一步消除了什么异常
  2. 论证 order_items.unit_price 的冗余是合理的（价格快照语义）
  3. 给 orders 加冗余列 item_count，写出保持它同步的两种方案
  4. 举一个你认为必须反范式的实际场景并说明理由
  5. 画出练习库的完整 ER 图
过关能用订单表这一个例子，从头讲完 1NF 到 3NF 的演进。
专注视图 ->
剧情 支付网关的回调会重试：同一笔支付可能回调多次。后端问你：「怎么保证不重复入账？」--幂等写入专场。
练 · 70 min
  1. 写一条 upsert：商品存在则更新库存，不存在则插入
  2. 用唯一键 + ON CONFLICT DO NOTHING 实现支付回调的幂等入账，重复执行验证结果不变
  3. 插入订单并用 RETURNING 拿回自增 id
  4. 用 UPDATE ... FROM 把某类目下所有商品调价 10%
  5. 用 COPY 导入 10 万行 CSV，与逐条 INSERT 对比耗时
过关写出一条真正幂等的 upsert（重复执行结果不变）。
专注视图 ->
剧情 线上事故复盘：扣款成功、订单却没创建。你意识到「两条写入要么都成、要么都不成」需要事务。
练 · 60 min
  1. 手写一个下单事务（建订单 + 扣库存），中途故意报错验证全部回滚
  2. 用 SAVEPOINT 实现部分回滚
  3. 开一个 5 分钟不提交的事务，在另一窗口查 pg_stat_activity 观察状态
  4. 验证未提交的修改在另一个会话中不可见
  5. 写下长事务的三个具体危害
过关能说出 ACID 每个字母分别由数据库的什么机制保证。
专注视图 ->
剧情 两个运营同时改一条库存，后提交的把先提交的覆盖了。你复现并搞懂了并发异常的全家桶。
练 · 55 min · 开两个 psql 窗口逐个复现
  1. 读已提交下复现不可重复读
  2. 切到 REPEATABLE READ，验证不可重复读消失
  3. 在 RR 下尝试复现幻读，记录 PG 的实际表现
  4. 复现丢失更新，再用 SELECT FOR UPDATE 消除它
  5. SERIALIZABLE 下制造串行化冲突，记录报错信息
过关产出一张自己实测的「隔离级别 × 异常」表格，能对照讲。
专注视图 ->
剧情 大促前的秒杀预演：多人同时抢最后一件库存。你需要 SELECT FOR UPDATE、SKIP LOCKED 这些真家伙。
练 · 50 min
  1. 两个会话对同一行 FOR UPDATE，观察阻塞
  2. 用 SKIP LOCKED 实现一个多消费者任务队列
  3. 故意制造死锁，读 PG 报出的死锁日志
  4. 查 pg_locks 找出阻塞链条
  5. 用 NOWAIT 实现快速失败而不是排队
过关能画出死锁那两个事务的时序图，并说出三种避免死锁的做法。
专注视图 ->