不同事务隔离级别间的性能/可扩展性差异探究

1. 锁分析相关工具 #

PostgreSQL没有专门生成“锁执行计划”的工具，但有几个内置组件和扩展可以帮你分析锁行为：

• pg_locks系统视图：实时查询当前数据库中所有锁的状态，包括锁类型、持有/等待的进程ID、关联的表或行对象。结合pg_stat_activity可以定位到触发锁的具体事务和查询语句。
• pg_stat_statements扩展：统计所有查询的执行时长、等待时间等指标，其中blk_read_time、blk_write_time能间接反映锁导致的IO等待延迟，wal_write_time可以体现事务提交阶段的锁竞争影响。
• auto_explain扩展：虽然核心是输出查询执行计划，但可以配置记录锁等待时间，将锁开销纳入查询执行日志，方便排查慢查询中的锁因素。
• pg_stat_user_tables：查看表级的锁等待统计，部分版本中n_locks相关指标能反映表上的锁竞争频率。

2. 无专门工具时的核心关注点 #

如果需要对比Repeatable Read和Serializable的性能差异，重点关注以下内容：

• 事务只读属性的利用：PostgreSQL对只读可延迟事务在Serializable隔离级别下有特殊优化，会跳过SSI谓词锁检查，此时性能与Repeatable Read接近。如果你的查询组是纯只读的，务必设置SET TRANSACTION READ ONLY DEFERRABLE;，再对比性能。
• 锁等待与冲突情况：通过pg_locks过滤granted = false的记录，统计锁等待的次数和平均时长。Serializable下如果锁等待次数显著高于Repeatable Read，说明谓词锁带来了实际竞争。
• 事务吞吐量与回滚率：在相同负载下，对比两种隔离级别下每秒完成的事务数。Serializable会检测写偏斜等异常并回滚事务，若回滚率较高，会直接拉低吞吐量。
• 查询执行时间的稳定性：即使平均执行时间相近，Serializable可能因偶发的锁等待或事务回滚，导致执行时间的方差远大于Repeatable Read，这也是性能影响的关键体现。
• 查询类型的锁触发差异：Serializable在范围扫描（如WHERE col > 100）时会加谓词锁，而Repeatable Read不会。如果你的查询以点查询（如WHERE id = 123）为主，两者性能差异极小；若大量涉及范围查询，Serializable的锁开销会更明显。
• 系统级负载指标：关注CPU使用率（锁竞争会导致进程空转等待）、WAL写入量（事务回滚会增加额外WAL开销）、以及lock_wait_timeout的触发次数，这些指标能侧面反映锁对系统的影响。

内容的提问来源于stack exchange，提问作者Erick T