罗罗编程学习记录
本页内容 展开查看

05.3.1.5 WAL、崩溃恢复与并发锁机制

所属:全栈开发面试题库 / 05 数据库与存储 / 05.3 PostgreSQL / 05.3.1 架构、存储与 MVCC

覆盖范围

  • WAL 记录、checkpoint 类型与脏页写出
  • 崩溃恢复流程与 full_page_writes
  • PG 锁分类、行锁存储与等待队列
  • FOR UPDATE / FOR SHARE / SKIP LOCKED / NOWAIT
  • 死锁检测、锁等待定位、advisory lock 与并发写实践

高频题速览

编号 题目 难度 频率
05.3.1.5-001 WAL 与 checkpoint 作用 高级 ★★★★☆
05.3.1.5-006 PG 锁类型与行锁实现 高级 ★★★★★
05.3.1.5-007 FOR UPDATE / SKIP LOCKED 高级 ★★★★★
05.3.1.5-008 死锁检测与排查 高级 ★★★★★
05.3.1.5-013 库存扣减防超卖方案 高级 ★★★★★

主线

持久化:先写 WAL(日志先行)→ 数据页延迟刷盘 → checkpoint 定重做点
崩溃恢复:最近 checkpoint → 重放 WAL 到一致状态
并发:MVCC 快照读不加锁;当前读按行锁强度阻塞冲突操作
排查:pg_locks + pg_stat_activity → 阻塞链 → 持锁事务

05.3.1.5-001 WAL 与 checkpoint 的作用?

频率 ★★★★☆ | 难度 高级

一句话结论

WAL(Write-Ahead Log)先记日志再改数据页,保证崩溃后可恢复已提交事务(持久化 D);checkpoint 把脏页刷盘并记录重做起点,崩溃后从最近 checkpoint 开始重放 WAL,缩短恢复时间。

概念解释

  • WAL:页级变更日志,存 pg_wal,段文件默认 16MB(待确认以版本为准)。
  • checkpoint:刷脏页并记录一致点,作为重放起点。
  • full_page_writes:开启后检查点后首次修改整页写入,防页断裂。

原理流程

  1. 变更先写 WAL buffer,提交时刷 WAL。
  2. 脏页留在 shared_buffers 延迟刷盘。
  3. checkpoint 周期/容量触发,刷脏页并记录重做起点。

代码 / 命令

SELECT * FROM pg_stat_bgwriter;   -- checkpoints_timed / checkpoints_req
SHOW checkpoint_timeout;
SHOW max_wal_size;

常见误区

  • 误区:提交时数据页必须落盘。正解:持久化靠 WAL 落盘,数据页可延迟刷盘。
  • 误区:checkpoint 越频繁越安全。正解:频繁 checkpoint 造成 IO 抖动,需按 WAL 容量平衡。

追问

  • 问:checkpoints_req 过多说明什么?答:max_wal_size 太小,WAL 频繁触发检查点,影响性能。
  • 问:WAL 与 MySQL redo/binlog 对照?答:WAL 是页级日志;InnoDB redo 重做、binlog 逻辑复制,见 05.2.1.3。

05.3.1.5-002 WAL 记录结构与日志段?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

WAL 由段文件组成,每条记录描述页修改(页号、偏移、变更内容);崩溃恢复按日志顺序重放已提交事务的变更;段文件在确认不再需要后回收或归档。

概念解释

  • 日志段:固定大小文件,编号顺序递增。
  • 记录内容:定位到数据页的物理/半物理变更。
  • 归档:WAL 段可归档用于 PITR 与流复制。

原理流程

  1. 变更生成 WAL 记录追加到当前段。
  2. 提交按持久化级别刷 WAL。
  3. 恢复时从 checkpoint 顺序重放记录。

代码 / 命令

SELECT name, size FROM pg_ls_waldir();  -- 函数名随版本以手册为准

常见误区

  • 误区:WAL 记录 SQL 语句。正解:它记录页变更,逻辑复制靠 WAL 解码得到逻辑变更。
  • 误区:WAL 段可随意删除。正解:未归档/未复制的段不能删,否则破坏恢复与复制。

追问

  • 问:WAL 为什么不能直接当 binlog 用?答:物理页记录需解码才成逻辑事件,逻辑复制走解码插件。
  • 问:WAL 占满磁盘怎么处理?答:检查复制槽/归档积压,不能手工删段。

05.3.1.5-003 checkpoint 类型与脏页写出?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

checkpoint 分时间触发(timeout)与 WAL 容量触发(req);执行时把脏页刷盘、更新控制文件记录重做起点;bgwriter 提前平滑写出部分脏页,减少 checkpoint 集中刷盘压力。

概念解释

  • timed checkpoint:按 checkpoint_timeout 周期触发。
  • requested checkpoint:WAL 达 max_wal_size 触发。
  • bgwriter:后台预写脏页,平滑 IO。

原理流程

  1. 周期或容量条件满足触发 checkpoint。
  2. 刷脏页、写检查点记录。
  3. 旧 WAL 在确认可回收后清理。

代码 / 命令

SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean
FROM pg_stat_bgwriter;

常见误区

  • 误区:bgwriter 替代 checkpoint。正解:前者平滑预写,后者负责完整一致点。
  • 误区:checkpoint 只刷索引不刷堆。正解:脏数据页与相关页都需刷盘。

追问

  • 问:checkpoint 期间 IO 抖动怎么缓解?答:调整 checkpoint 间隔与 WAL 容量,分散脏页写出。
  • 问:脏页什么时候必须刷?答:checkpoint 回收旧 WAL 前相关脏页需落盘。

05.3.1.5-004 崩溃恢复流程?

频率 ★★★☆☆ | 难度 进阶

一句话结论

实例启动先读控制文件找最近 checkpoint,重放其后 WAL 把数据页恢复到一致状态,回滚未提交事务;已提交事务靠 WAL 持久化不丢,未提交事务撤销。

概念解释

  • 重做(redo):重放已提交变更到数据页。
  • 回滚(undo 语义):终止未提交事务的修改。
  • 一致状态:恢复后所有已提交变更生效,未提交被撤销。

原理流程

  1. 启动读控制文件与检查点位置。
  2. 从检查点重放 WAL 记录。
  3. 回滚未提交事务后接受连接。

代码 / 命令

恢复:控制文件 → 最近 checkpoint → 重放 WAL → 回滚未提交 → 就绪

常见误区

  • 误区:崩溃后未提交事务也保留。正解:未提交事务在恢复时回滚。
  • 误区:恢复需要人工执行 SQL 撤销。正解:实例启动自动完成崩溃恢复。

追问

  • 问:full_page_writes 防什么?答:防止页在写入中途崩溃导致页断裂,重放无法修复半写页。
  • 问:恢复时间由什么决定?答:checkpoint 间隔与待重放 WAL 量。

05.3.1.5-005 行锁存储在 xmax 与等待队列?

频率 ★★★☆☆ | 难度 高级

一句话结论

PG 行锁标记在元组头 xmax 等字段,不建独立行锁对象;等待关系记录在后端进程锁表中。冲突事务排队等待持锁事务结束,MVCC 快照读不受行锁阻塞。

概念解释

  • 行头标记:xmax 记录锁定/删除事务信息。
  • 等待队列:后端进程等待行锁的关系在锁视图可见。
  • 快照读豁免:普通 SELECT 走 MVCC,不等行锁。

原理流程

  1. 当前读锁定行,行头标记持锁事务。
  2. 冲突事务进入等待,锁表记录等待关系。
  3. 持锁事务结束,等待者按规则获得锁。

代码 / 命令

SELECT locktype, relation::regclass, mode, granted, transactionid
FROM pg_locks WHERE locktype = 'tuple';

常见误区

  • 误区:PG 行锁存在独立内存锁对象表。正解:标记在行头,锁表只记等待/持有概况。
  • 误区:普通 SELECT 会等行锁。正解:快照读不阻塞,当前读才加锁等待。

追问

  • 问:行锁什么时候释放?答:事务提交/回滚释放,不随语句结束释放。
  • 问:与 InnoDB 行锁存储对照?答:InnoDB 锁基于索引记录与锁结构,见 05.2.2.4。

05.3.1.5-006 PG 有哪些锁?表级与行级如何分级?

频率 ★★★★★ | 难度 高级

一句话结论

表级锁按强度从 ACCESS SHARE 到 ACCESS EXCLUSIVE 分级:普通 SELECT 加 ACCESS SHARE,写操作加 ROW EXCLUSIVE,DDL/VACUUM FULL 等需 ACCESS EXCLUSIVE;行级锁分 FOR UPDATE / NO KEY UPDATE / FOR SHARE / KEY SHARE,只阻塞冲突行操作。

概念解释

  • 表级锁:保护表结构与并发访问,按模式兼容矩阵判断冲突。
  • 行级锁:锁定具体元组,粒度细。
  • 兼容矩阵:不同锁模式间可兼容或互斥。

原理流程

  1. 语句按类型自动加对应表级锁。
  2. 当前读按强度加行级锁。
  3. 冲突模式排队等待。

代码 / 命令

SELECT mode, granted, count(*) FROM pg_locks GROUP BY mode, granted;

常见误区

  • 误区:ALTER TABLE 只阻塞 DDL。正解:它要 ACCESS EXCLUSIVE,阻塞所有读写直到完成。
  • 误区:行锁会阻塞普通快照读。正解:MVCC 读不等行锁。

追问

  • 问:哪些操作拿 ACCESS EXCLUSIVE?答:DROP、大部分 ALTER、VACUUM FULL 等,需避开长事务。
  • 问:ROW EXCLUSIVE 之间互斥吗?答:多个写事务的 ROW EXCLUSIVE 兼容,冲突在具体行锁。

05.3.1.5-007 FOR UPDATE / FOR SHARE / SKIP LOCKED 怎么用?

频率 ★★★★★ | 难度 高级

一句话结论

FOR UPDATE 排他锁定行供后续更新;FOR SHARE 共享锁允许其他共享锁、阻塞排他;SKIP LOCKED 跳过被锁行做并发队列消费;NOWAIT 拿不到锁立即报错。锁随事务结束释放。

概念解释

  • FOR UPDATE:排他行锁,阻塞其他更新/排他锁。
  • FOR SHARE:共享行锁,防他人修改该行。
  • SKIP LOCKED:不等待,跳过当前被锁行。
  • NOWAIT:冲突立即报错而非排队。

原理流程

  1. 查询带锁定子句当前读。
  2. 命中行按强度加行锁。
  3. 冲突事务等待、跳过或快速失败。

代码 / 命令

SELECT * FROM orders WHERE id = 1 FOR UPDATE;
SELECT * FROM orders WHERE id = 1 FOR SHARE;
SELECT * FROM jobs WHERE status='NEW' ORDER BY id LIMIT 10
  FOR UPDATE SKIP LOCKED;
SELECT * FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;

常见误区

  • 误区:SKIP LOCKED 会漏任务。正解:只跳过当前被锁行,锁释放后其他 worker 可消费。
  • 误区:FOR SHARE 能阻止他人读。正解:快照读不受影响,它阻塞的是写/排他锁。

追问

  • 问:队列消费怎么防重复领取?答:SKIP LOCKED 领取后更新状态并 RETURNING,事务内完成。
  • 问:FOR NO KEY UPDATE 用于什么?答:UPDATE 默认强度,不阻塞外键相关的 KEY SHARE 锁。

05.3.1.5-008 PG 死锁怎么检测与排查?

频率 ★★★★★ | 难度 高级

一句话结论

PG 死锁检测器按 deadlock_timeout 周期检查等待环,检测到循环等待即中止一个事务报 deadlock detected;排查从日志阻塞链路与 pg_locks/pg_stat_activity 关联入手,治理靠统一加锁顺序、缩短事务与重试。

概念解释

  • 等待环:A 等 B 持锁,B 等 A 持锁。
  • 死锁检测:超时后检查等待图成环。
  • 中止一方:回滚代价较小事务解除环。

原理流程

会话1: 锁行1 → 请求行2 等待
会话2: 锁行2 → 请求行1 等待 → 成环 → 检测回滚一方

代码 / 命令

SELECT a.pid, a.query, l.locktype, l.mode, l.granted, l.relation::regclass
FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted;

常见误区

  • 误区:死锁要等锁超时才解除。正解:死锁检测器主动发现成环即回滚一方。
  • 误区:deadlock_timeout 越短越好。正解:检测有成本,过小增加开销,默认约 1s(待确认)。

追问

  • 问:死锁能完全避免吗?答:不能,但统一加锁顺序、短事务可显著降低。
  • 问:业务层遇到死锁怎么办?答:捕获死锁错误后有限次重试事务。

05.3.1.5-009 锁等待怎么定位?如何减少?

频率 ★★★★★ | 难度 高级

一句话结论

用 pg_locks 找未授予锁,pg_blocking_pids 关联阻塞源会话与 SQL;减少等待靠短事务、固定加锁顺序、SKIP LOCKED 队列化、避免长 ACCESS EXCLUSIVE 操作与热点行拆分。

概念解释

  • 未授予锁:granted=false 的等待项。
  • 阻塞链:等待 pid → 阻塞 pid → 阻塞源 SQL。
  • 治理手段:缩短持锁时间、收窄锁范围、并行跳过。

原理流程

  1. 查未授予锁定位等待会话。
  2. 反查阻塞 pid 与其持锁 SQL。
  3. 按根因治理事务、顺序或热点。

代码 / 命令

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocked.query AS blocked_query, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL (SELECT unnest(pg_blocking_pids(blocked.pid)) AS pid) b ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid;

常见误区

  • 误区:锁等待只看慢查询。正解:计划正常但会话处于锁等待,需查阻塞链。
  • 误区:终止等待事务即可。正解:根因是持锁长事务,需治理阻塞源。

追问

  • 问:锁等待超时怎么设?答:lock_timeout 限制单锁等待,避免无限排队。
  • 问:什么操作容易造成大范围等待?答:长 ACCESS EXCLUSIVE 的 DDL 被长事务卡住后阻塞后续读写。

05.3.1.5-010 长事务与 idle in transaction 的危害?

频率 ★★★★★ | 难度 高级

一句话结论

长事务持快照阻碍 VACUUM 回收与 freeze,导致表/索引膨胀;同时持有行/表锁阻塞并发写。idle in transaction 是事务已开始但客户端未提交,危害相同且最隐蔽,需超时自动断开并在应用层避免事务内等待。

概念解释

  • 持快照:旧版本不能回收。
  • 持锁:行/表锁阻塞冲突事务。
  • idle in transaction:会话空闲但事务未提交。

原理流程

  1. 事务开始后长时间不提交。
  2. 快照与锁持续持有。
  3. 回收受阻、并发阻塞,age 与膨胀上升。

代码 / 命令

SELECT pid, state, now()-xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now()-xact_start > interval '1 minute';
SHOW idle_in_transaction_session_timeout;

常见误区

  • 误区:idle 连接都没事。正解:idle in transaction 持快照与锁,必须区分普通 idle。
  • 误区:长事务只影响性能不影响正确性。正解:阻碍 freeze 逼近回卷,是可用性风险。

追问

  • 问:怎么强制终止?答:pg_terminate_backend(pid),先评估业务影响。
  • 问:应用层怎么防?答:事务内不做 RPC/IO/等用户输入,设置事务与空闲超时。

05.3.1.5-011 Advisory Lock 是什么?用在哪里?

频率 ★★★★☆ | 难度 进阶

一句话结论

咨询锁是应用级互斥锁,不绑定表/行,按键值协调任务;会话级 pg_advisory_lock 连接断开释放,事务级 pg_advisory_xact_lock 事务结束自动释放。用于定时任务防重、初始化互斥;只在单库有效,跨库需分布式锁。

概念解释

  • 会话级:显式加解锁,绑定连接。
  • 事务级:事务结束自动释放,适配连接池。
  • 用途:单库内任务互斥、幂等初始化。

原理流程

  1. 任务开始申请咨询锁键。
  2. 获得锁才执行临界区。
  3. 会话/事务结束按级别释放。

代码 / 命令

SELECT pg_advisory_xact_lock(12345);
-- 事务内执行任务,结束自动释放

常见误区

  • 误区:advisory lock 能替代 Redis/ZK 分布式锁。正解:只在单库实例内有效。
  • 误区:会话级锁在连接池下安全。正解:连接归还后锁可能未释放,泄漏给复用连接,优先事务级。

追问

  • 问:事务级锁优点?答:自动释放,避免连接池泄漏。
  • 问:锁键怎么选?答:用稳定整数键,避免冲突,按业务域划分。

05.3.1.5-012 唯一约束下并发插入会怎样?

频率 ★★★★☆ | 难度 高级

一句话结论

两事务插同一唯一键,一方成功另一方提交时遇唯一冲突报错;并发多行按不同顺序插入唯一索引可能死锁。幂等场景用 ON CONFLICT DO NOTHING,需要合并用 DO UPDATE(upsert),或捕获冲突异常重试。

概念解释

  • 唯一冲突:同键并发插入,后提交者违反约束。
  • 等待与死锁:唯一索引检查时等待对方事务,顺序交叉可成环。
  • upsert:冲突时转更新,需唯一索引支撑。

原理流程

  1. 插入走唯一索引定位键值。
  2. 冲突时等待对方提交/回滚。
  3. 对方提交则报唯一冲突,回滚则可插入。

代码 / 命令

INSERT INTO users(email, name) VALUES ('a@x.com','Ann')
ON CONFLICT (email) DO NOTHING;

INSERT INTO counters(name, value) VALUES ('pv', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;

常见误区

  • 误区:先 SELECT 再 INSERT 能防重复。正解:检查与插入间有竞态,唯一索引 + 捕获冲突才可靠。
  • 误区:upsert 高并发无争用。正解:同行冲突会锁等待,热点键需评估。

追问

  • 问:ON CONFLICT 需要什么前提?答:冲突列有唯一索引或主键。
  • 问:upsert 更新自己被锁的行有什么注意?答:语句内引用被更新行需用表名限定。

05.3.1.5-013 库存扣减怎么防超卖?PG 方案。

频率 ★★★★★ | 难度 高级

一句话结论

推荐条件原子更新:UPDATE stock SET qty=qty-1 WHERE sku=? AND qty>=1,行锁内判断余量,影响行数 0 即售罄;也可 FOR UPDATE 悲观锁、版本号乐观锁,高并发热点行分桶库存;扣减与订单创建放同一事务。

概念解释

  • 条件更新:锁内判断 qty>=1,原子防超卖。
  • 悲观锁:先 FOR UPDATE 再更新,临界区长。
  • 乐观锁:版本号条件更新,冲突重试。
  • 分桶库存:单行拆多行分散热点。

原理流程

  1. 订单事务内执行条件扣减。
  2. 影响行数 1 → 创建订单;0 → 售罄回滚/提示。
  3. 热点高并发评估分桶或缓存预扣 + 对账。

代码 / 命令

UPDATE stock SET qty = qty - 1
WHERE sku = 'A' AND qty >= 1;
-- 影响行数 1 成功;0 表示库存不足

常见误区

  • 误区:先 SELECT 读库存再判断扣减安全。正解:读与扣减间有并发窗口,必须锁内条件判断。
  • 误区:乐观锁在高冲突库存场景更优。正解:冲突频繁时重试率高,条件更新/悲观锁更稳。

追问

  • 问:分桶库存怎么保证总量?答:各桶扣减,汇总核对总量,必要异步校准。
  • 问:Redis 预扣方案风险?答:缓存与库之间需对账补偿,崩溃可能丢预扣。

05.3.1.5-014 乐观锁与悲观锁如何实现?

频率 ★★★★☆ | 难度 进阶

一句话结论

悲观锁:SELECT ... FOR UPDATE 锁定行后更新,冲突频繁、临界区短适用;乐观锁:版本号条件更新,影响 0 行即冲突重试,读多写少适用。条件更新语句本身行锁 + 校验原子完成。

概念解释

  • 悲观锁:假设会冲突,提前持锁。
  • 乐观锁:假设少冲突,提交时校验版本。
  • 原子性:WHERE 版本/条件在行锁内判定。

原理流程

  1. 悲观:事务内锁定 → 读取 → 更新 → 提交释放。
  2. 乐观:读版本 → 提交带版本条件更新 → 0 行重试。
  3. 按冲突频率选型。

代码 / 命令

-- 悲观
BEGIN;
SELECT * FROM stock WHERE sku='A' FOR UPDATE;
UPDATE stock SET qty=qty-1 WHERE sku='A';
COMMIT;

-- 乐观
UPDATE stock SET qty=qty-1, version=version+1
WHERE sku='A' AND version=3;

常见误区

  • 误区:乐观锁不加任何锁。正解:更新语句仍加行锁,只是不提前长持锁。
  • 误区:乐观锁可以无限重试。正解:需有限次数与退避,持续冲突应改悲观/拆分。

追问

  • 问:乐观锁重试上限怎么定?答:按冲突率设定次数与退避,避免活锁。
  • 问:悲观锁会死锁吗?答:会,多事务加锁顺序不一致时,需统一顺序。

05.3.1.5-015 写偏斜与 SSI 处理?

频率 ★★★★☆ | 难度 高级

一句话结论

写偏斜是两事务基于各自快照读取不同数据后写入,组合结果违反约束,快照隔离不检测;Serializable 下 SSI 识别读写依赖冲突并中止一方,应用必须重试。

概念解释

  • 写偏斜:跨行读写依赖交叉,非同行更新冲突。
  • 快照隔离盲区:只保证快照一致,不检测依赖环。
  • SSI 中止:检测危险依赖结构回滚冲突事务。

原理流程

A 读"还有人值班" → 请假;B 读同样快照 → 请假
快照隔离都提交 → 无人值班(违反约束)
SSI:检测读写依赖冲突 → 中止一方

代码 / 命令

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;
UPDATE doctors SET on_call=false WHERE id=1;
COMMIT;  -- 可能报序列化失败,需重试

常见误区

  • 误区:行锁能防写偏斜。正解:双方不写同一行,普通行锁拦不住。
  • 误区:开 Serializable 后不用写重试。正解:冲突中止必须应用捕获重试。

追问

  • 问:除 SSI 外怎么防写偏斜?答:显式锁范围、唯一/排他约束、重读校验。
  • 问:SSI 报错信息?答:could not serialize access due to read/write dependencies。

05.3.1.5-016 批量更新如何避免锁冲突?

频率 ★★★★☆ | 难度 高级

一句话结论

按固定顺序(主键升序)更新避免交叉等待;分批提交缩短事务与持锁时间;用 SKIP LOCKED 并行处理不同行;避免单事务锁大量行,批大小按锁等待与时长压测调整。

概念解释

  • 统一顺序:多事务按相同键序加锁,防成环。
  • 分批小事务:每批限定行数,锁随批释放。
  • 并行跳过:多 worker SKIP LOCKED 领批。

原理流程

  1. 按主键顺序圈定一批待处理行。
  2. FOR UPDATE SKIP LOCKED 领取并更新提交。
  3. 循环推进,监控锁等待与复制延迟。

代码 / 命令

WITH batch AS (
  SELECT id FROM tasks WHERE status='PENDING'
  ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED
)
UPDATE tasks SET status='RUNNING'
FROM batch WHERE tasks.id = batch.id;

常见误区

  • 误区:批量一条语句跑完锁最少。正解:长事务持大量行锁,交叉顺序易死锁。
  • 误区:分批后不会有锁冲突。正解:批内仍加锁,需保证顺序与范围不重叠。

追问

  • 问:批量更新为什么容易死锁?答:不同事务加锁顺序不一致形成等待环。
  • 问:批大小怎么定?答:按事务时长、锁等待与 WAL 压力压测调整。

05.3.1.5-017 事务使用最佳实践?

频率 ★★★★☆ | 难度 进阶

一句话结论

短事务、事务内不做 RPC/文件 IO;按需求选隔离级别,默认 RC;统一加锁顺序,热点用条件更新/分桶;设置 lock_timeout、statement_timeout、idle_in_transaction_session_timeout;大批量写拆批,监控长事务、锁等待与 dead tuple。

概念解释

  • 短事务:持锁与快照时间最小。
  • 超时保护:语句/锁/空闲事务超时兜底。
  • 监控闭环:长事务、锁等待、回收状态联动。

原理流程

  1. 设计事务边界,排除事务外慢操作。
  2. 配置各级超时与隔离级别。
  3. 线上监控并复盘锁冲突与长事务。

代码 / 命令

SHOW lock_timeout;
SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;

常见误区

  • 误区:事务越长越能保证一致。正解:长事务阻碍回收、扩大锁范围,反增风险。
  • 误区:连接池下不用管会话状态。正解:transaction 模式需重置会话,注意临时表/锁生命周期。

追问

  • 问:statement_timeout 设多少?答:按业务 SLA,常见数秒级,避免慢查询拖垮。
  • 问:为什么默认 RC?答:性能与兼容兼顾,多数 OLTP 够用,强一致场景再升级别。

05.3.1.5-018 与 InnoDB redo / 行锁 / 间隙锁对照?

频率 ★★★☆☆ | 难度 进阶

一句话结论

持久化:PG WAL 页级日志 + checkpoint;InnoDB redo 重做 + doublewrite + binlog 复制。锁:PG 行锁基于行头标记,无 InnoDB 式间隙/临键锁;InnoDB RR 当前读加临键锁防幻读,PG 靠快照隔离。

概念解释

  • 日志对照:WAL ↔ redo(页级),PG 无独立逻辑 binlog。
  • 锁对照:行头标记 vs 索引记录锁结构。
  • 幻读对照:快照固定 vs 间隙封锁。

原理流程

  1. 两边都先写日志保持久化。
  2. 当前读按各自模型加行锁。
  3. 防幻读与冲突检测机制不同。

代码 / 命令

对照:WAL/checkpoint ↔ redo/doublewrite;PG 行头锁 ↔ InnoDB 记录/间隙/临键锁

常见误区

  • 误区:PG 也有间隙锁。正解:PG 不采用 InnoDB 临键锁模型,幻读靠快照隔离。
  • 误区:两边锁语法语义完全对应。正解:锁定读相似,锁范围与冲突模型不同。

追问

  • 问:InnoDB 间隙锁细节见哪?答:05.2.2.4。
  • 问:哪边复制日志是逻辑的?答:MySQL binlog 逻辑事件;PG 逻辑复制由 WAL 解码产生,见 05.3.2.4。