PostgreSQL事务与并发控制：MVCC、锁和隔离级别实战解析

后端开发和数据库运维干久了几乎都会碰上一类诡异的线上问题两个服务同时改同一条用户数据后提交的反而把先提交的覆盖了库存明明查出来还有10件真正减的时候却提示不足压测一上去数据库日志刷出一屏 deadlock detected。代码逻辑翻来覆去都没错改什么都一样最后才发现真正的根子出在事务并发控制上。手头这份笔记日期标着0313是我最近复盘一个线上库存并发更新问题时整理的把 PostgreSQL 的隔离级别、锁、MVCC 从头到尾过了一遍。想到这块内容问的人确实不少干脆梳成一篇长文分享出来。内容照顾两类读者刚接触 PostgreSQL 的人可以顺着这篇把事务和并发控制的框架搭起来有经验的开发或 DBA可以重点看锁排查、参数调优和应用层改写的部分。1. 事务 ACID 在 PostgreSQL 里到底是怎么落地的很多人把 ACID 背得很熟但不知道 PostgreSQL 是靠什么机制实现的。原子性和持久性这两条核心功臣是 WALWrite-Ahead Logging预写日志。在 PG 里事务改数据不是直接改磁盘上的数据文件而是先把变更记录追加到 WAL 日志等 WAL 安全落地了数据页的修改才在内存里慢慢应用。这就像店里记账先记流水账把“顾客赊账300块”写在账本上之后才去改总账目。如果营业中途停电老板只要翻出流水账就知道哪些账要补、哪些账没生效而不是两眼一抹黑。事务提交的时候PG 会强制把 WAL 刷到磁盘这一步完成后才返回客户端“COMMIT”成功。崩溃重启时PG 根据 WAL 重放变更已经提交的事务会被恢复没提交的事务则被回滚。数据库要么停在事务开始前的状态要么停在事务完整提交后的状态不会停在中间。写库时不要为了所谓的性能去关 fsync 或者把 synchronous_commit 调到 off——除非你明确知道丢了几个事务也能接受。这个我在测试环境试过极端情况下数据会出现不一致真正回溯起来比丢几个事务麻烦得多。1.1 隔离性并发场景下“看见什么”的本质隔离性在 PG 里主要靠快照Snapshot机制实现。每个事务开始后系统会给它一个可见性快照之后这个事务读数据实际上是在读快照对应的版本集合。别的事务修改并提交了也不会立刻改变你手里快照能看到的内容。这也是 PG 读操作不阻塞写、写操作不阻塞读的最根本原因。理解这一点有个很管用的类比当你在编辑线上文档的时候别人也在改同一份文档但你本地看到的是自己打开那一刻或者你手动刷新那一刻的版本不会因为他改了十几个字屏幕就跟着跳。MVCC 就是让每个人握着各自的版本提交的时候再合并。PG 的每行数据都带着 xmin/xmax 这类版本信息用来判断“这行对哪个事务可见”。这个概念刚开始接触会觉得抽象但它其实就是系统在问这行的出生时间是不是在我这个事务开始之前死亡时间删改标记是不是在我这个事务开始之后。两个条件都满足我才看得见。1.2 MVCC 与锁PostgreSQL 并发控制的两条腿很多人以为“并发控制”就是加锁实际上 PG 用的是两条腿走路MVCC 解决读写冲突锁解决写写冲突。前者让读写并行不打架后者确保同一行数据不会被两个人同时改坏。两个事务同时 UPDATE 同一行靠的是行锁。先拿到锁的事务把数据改了另一个事务就得等。等前面的提交或回滚之后后面的事务基于最新版本重新判断自己的 UPDATE 条件再继续。这就是为什么“先查后改”存在风险你查的时候数据是好的改的时候可能已经被别人改过一轮而你看到的条件已经过期。MVCC 带来的副产品是旧版本数据不会立即消失这些被称为 dead tuple 的行会被 VACUUM 在后台清理。如果你的库里长事务特别多垃圾版本清不掉表会越来越大查询越来越慢。这块后面会专门讲。2. 隔离级别与那些会让人抓狂的并发异常2.1 四个隔离级别一张表看懂行为差异SQL 标准定义了四种隔离级别PG 都支持但有一个特例需要先说清楚PG 里的 READ UNCOMMITTED 实际表现等同于 READ COMMITTED也就是说它没有真正实现脏读。我用一张表总结 PG 四个级别的行为和典型误用场景这是基于 PG 实际行为不是标准定义的理想情况隔离级别脏读不可重复读幻读写偏斜典型适用场景READ UNCOMMITTED实际等价 READ COMMITTED不可能可能可能可能不建议单独使用READ COMMITTED不可能可能可能可能绝大多数 OLTPREPEATABLE READ不可能不可能不可能PG快照可能统计、报表类SERIALIZABLE不可能不可能不可能SSI 可检测资金账等强约束场景这里有个细节很多人忽略PG 的 REPEATABLE READ 因为用快照实现连幻读都被消灭了比标准定义里“允许幻读”要严格。但写偏斜仍然存在只有 SERIALIZABLE 级别的 SSI 机制能检测出来。2.2 READ COMMITTED默认级别并不等于省心级别PG 默认隔离级别是 READ COMMITTED大多数人也没改过。它的行为是“每条语句开始前拿一个新快照”也就是说同一个事务里第一条 SELECT 和第二条 SELECT 可能看到完全不同的数据。我在一个订单金额统计需求上被真实坑过。当时想在一个事务里先算今日总金额再算本月总金额期间运营刚好改了一笔大单的状态两条 SQL 统计口径不一样出来的数字对不上运营拿着报表来问怎么同一个时间段两个数差这么多。实际演示这句话-- 会话A BEGIN ISOLATION LEVEL READ COMMITTED; SELECT balance FROM accounts WHERE id 1; -- 假设此时返回 100 -- 会话B UPDATE accounts SET balance 50 WHERE id 1; COMMIT; -- 会话A 再次查询同一事务内 SELECT balance FROM accounts WHERE id 1; -- 返回 50看到的是新快照 COMMIT;这在默认级别下是正常行为。如果业务要求一条事务里所有查询必须基于同一次快照就要用 REPEATABLE READ。还有一点READ COMMITTED 下“先查后改”很容易丢更新。两个会话都查到 stock10然后各自执行 stock stock - 1最终库存可能是9而不是8。解决办法在第四章讲。提示默认级别下同一事务两次查询看到不同数据是正常行为不是 bug。判断隔离级别是否满足业务要以“这个事务需要一致的快照吗”为标准。2.3 REPEATABLE READ 与 SERIALIZABLE写偏斜那道坎值班表场景是理解写偏斜最好的例子。假设 on_call 表有 8 个人在值班其中 A 和 B 两条记录 statustrue业务约束是至少要有一个人在场用于值班防止没人值守。事务1把 A 的值班状态改为 false事务2同时把 B 改为 false。因为两个事务各改各的行在 REPEATABLE READ 下互不阻塞。它们基于同一份快照判断都看到“有两人在值班”于是都认为自己可以修改。最后 A、B 都变成不在岗约束被打破了系统却没有报任何错误。这叫写偏斜两个写操作单独看都没问题合起来破坏了业务约束。要拦住这种情况只能上 SERIALIZABLE。PG 的 SERIALIZABLE 采用 SSI可串行化快照隔离机制能够检测到这类读写依赖冲突在事务提交时返回 serialization_failureSQLSTATE 是 40001。收到这个错误应用层需要做有限次重试把这个事务再跑一遍。补充一个 REPEATABLE READ 下的并发写行为两个事务 RR 模式下并发 UPDATE 同一行后到者会等待前一个事务结束如果前面提交了等待方可能直接报出“could not serialize access due to concurrent update”同样类似串行化失败。所以不要以为 RR 只有“不会不可重复读”这个好处它在重写同一行时也会更敏感。2.4 版本选择想学并发控制该装哪个 PG既然聊到隔离级别顺带回答一个经常被私信问的问题想学 PostgreSQL该下载哪个版本我的建议很直接学习、实验用最新稳定版或直接下便携版解压就能跑不用折腾系统服务适合开两个窗口调事务行为。我本地就放着一个 PG16 便携版需要验证某种锁行为时直接 initdb 一个临时集群测完就删。生产环境则要保守一些选社区支持期内的稳定版。写本篇笔记时 PG17 已在社区活跃我见多数生产库还稳在 PG16因为它的 VACUUM 效率、逻辑复制都有明显改进并发场景下更不容易因为清理跟不上而膨胀。版本越新特性越多但升级前要把兼容性测试做透。Windows 上用安装包部署的朋友服务起不来见过太多次了优先看数据目录下的日志目录默认是 log 或 pg_log常见原因是端口被其他程序占用、安装时选的 locale 与系统不一致、服务账户权限不足。本地开发调试便携版省心很多但正式环境还是建议用标准安装并注册成服务。3. 锁与阻塞会话卡住时怎么快速定位3.1 行锁与表锁PG 加锁是分层次的MVCC 能解决读写冲突但解决不了写写冲突这部分交给锁。PG 的锁分两个层级行锁和表锁。行锁最常出现在 UPDATE/DELETE 和 SELECT ... FOR UPDATE 中。普通 UPDATE 会给目标行加行锁事务提交或回滚才释放。两个事务同时改同一行必须排队。表锁则更重。比如 ALTER TABLE 这类 DDL 会拿 ACCESS EXCLUSIVE 锁它会阻塞一切读写包括最普通的 SELECT。为什么生产环境凌晨做表结构变更经常把业务打死就是这个原因。哪怕只是加一列也可能让整个应用停在那里等锁。表级锁里还有 ACCESS SHARESELECT 持有、ROW EXCLUSIVEUPDATE/DELETE 持有等它们之间存在兼容矩阵。日常管理你不需要背完整矩阵但脑子里要有这个概念锁不是一个开关而是一个分层体系DBA 和管理员的很多工作就是在跟这个体系打交道。3.2 pg_stat_activity 与 pg_locks先找到那个等着拿锁的会话遇到数据库“卡死”最怕的是不知道谁卡了谁。排查的思路分两步先找等待锁的会话再顺着它找持有锁的会话。一条很实用的查询能直接列出正在等待锁的会话SELECT pid, state, wait_event_type, wait_event, query, xact_start FROM pg_stat_activity WHERE state idle AND wait_event_type Lock;wait_event_type 等于 Lock 时说明这个会话正在等锁。接下来用 pg_blocking_pids 函数找到底是谁在挡住它SELECT pid, query, pg_blocking_pids(pid) AS blocking_pids FROM pg_stat_activity WHERE pid 1234;blocking_pids 返回的是阻碍当前会话的进程 ID 列表拿到这些 PID 再去 pg_stat_activity 里看它们在跑什么 SQL、什么时候开始的基本就能定位问题。还可以结合 pg_locks 看锁的具体模式SELECT l.pid, l.mode, l.granted, c.relname, a.query FROM pg_locks l LEFT JOIN pg_class c ON l.relation c.oid LEFT JOIN pg_stat_activity a ON a.pid l.pid WHERE l.locktype relation;granted 为 false 的行就是在等待锁同表 granted 为 true 的行就是当前占有者。确认是某个会话长期持锁后该终止就终止但终止前先和业务确认会话在跑什么。我见过有人直接 pg_terminate_backend 把正在执行大查询的会话杀掉结果业务方处理到一半的数据全部回滚影响面比等锁还大。3.3 死锁是怎么产生的以及 deadlock_timeout 怎么工作死锁在 PG 里是家常便饭。最常见的是两个事务按不同顺序更新同一批记录比如事务A先更新 id1 再更新 id2事务B先更新 id2 再更新 id1两边各拿到一把锁又都在等对方手里的另一把谁也走不下去。PG 不是时时刻刻都在扫死锁那样太费资源。它有一个 deadlock_timeout 参数默认 1 秒也就是说当一个会话等锁超过 1 秒后台才会触发死锁检测。检测到死锁后PG 会选一个事务回滚报错信息类似“deadlock detected”SQLSTATE 是 40P01。对应用来说遇到 40P01 和 40001 都应该做重试。对设计来说最好的办法是让所有事务按同样的顺序访问资源比如统一先更新 user 表再更新 order 表交叉场景自然就消失了。另外可以在连接层设置 lock_timeout让单个锁等待不要无限挂起。业务 SQL 卡在锁等待上比报错更难受因为它没有任何返回DBA 排查起来也很被动。给锁等待设个上限最多等 3 秒超时就返回错误配合监控报警效果比一直挂在那里好得多。4. 事务并发控制的实践参数与常见坑4.1 超时类参数别让一个坏事务拖死整个库先放一张我经常拿来检查生产库的参数表这些参数直接关系并发控制参数默认值作用我的建议max_connections100最大连接数连接越多锁竞争越激烈配合连接池控制真实并发statement_timeout0不限制单条语句最大执行时间报表类执行会很久的库可以放宽OLTP建议设置lock_timeout0不限制等锁的最大时间OLTP 建议 1~3秒避免无限挂起idle_in_transaction_session_timeout0不限制事务内空闲的最长时间强烈建议设置比如 60 秒deadlock_timeout1s死锁检测触发间隔一般保持默认为什么特别强调 idle_in_transaction_session_timeout因为很多线上事故都是这样来的业务代码里开了事务查了数据然后因为各种原因卡在业务逻辑上一直没提交连接就挂在那。这个事务持有的快照会让 VACUUM 清理不了对应的旧版本连带拖累整个库的查询性能。设置事务内空闲超时后超时会自动断开连接回滚事务比 DBA 半夜爬起来手动杀会话好太多。max_connections 也不要盲目调大。连接数一高锁等待和上下文切换随之增加很多时候数据库变慢不是 CPU 不够而是锁竞争太激烈。正确的做法是前端用连接池把真正并发的事务数控制在合理范围。4.2 长事务、膨胀与 VACUUM并发控制的隐藏成本长事务是 PostgreSQL 里最隐蔽的杀手。MVCC 要求一个事务在快照内看到的数据版本必须保留任何人不能提前清掉。如果一个事务跑了很长时间它启动之后产生的所有旧版本数据都清不掉表不停膨胀索引效率下降全表扫描和更新都变慢。查询长事务用这个SELECT pid, state, xact_start, now() - xact_start AS duration, query FROM pg_stat_activity WHERE state idle AND xact_start IS NOT NULL ORDER BY xact_start;duration 超过几分钟就应该注意超过几十分钟基本可以定位为事故。VACUUM 是 PG 回收 dead tuple 的核心手段。它不能与长事务并存——长事务持有旧快照时VACUUM 会主动跳过它启动时间点之前产生的垃圾版本。这也是为什么 PG 版本越新VACUUM 效率优化越多对高并发 OLTP 越友好。给运维同学一个实用习惯每周看一次 pg_stat_user_tables 里的 n_dead_tup 和 last_autovacuum如果 n_dead_tup 长期高且表很大说明 autovacuum 可能没跟上或者有长事务在顶着。提前处理比事后膨胀到查询超时才去救要容易太多。注意不要轻易手动执行 VACUUM FULL它会拿到 ACCESS EXCLUSIVE 锁并阻塞业务生产环境尽量靠自动化 autovacuum。4.3 应用层改写从“先查后改”到“条件更新”事务并发控制最终还是落到 SQL 写法上。最容易踩的坑是“先查后改”SELECT stock FROM products WHERE id 10; -- 返回 10 UPDATE products SET stock stock - 1 WHERE id 10; -- 并发时丢更新两个事务都查到 10都执行减一最终可能是 9而不是 8。要修很简单把条件更新直接用起来UPDATE products SET stock stock - 1 WHERE id 10 AND stock 1 RETURNING stock;这条 SQL 本身带行锁并发时只有一个事务能成功返回值可以判断是否扣减成功。库存扣减这类操作根本不需要先 SELECT。如果业务确实需要先锁定一行再处理后续逻辑再用 SELECT ... FOR UPDATE。比如订单支付流程先锁订单行再计算优惠、调用外部接口确保整个期间订单状态不被别的事务改掉。FOR SHARE 则是多个事务可以一起读但不能改适合“多人同时查看但都不能编辑”的场景。做任务队列消费时FOR UPDATE 配合 SKIP LOCKED 很好用。多个 worker 同时捞任务SKIP LOCKED 让它们跳过已被别人锁住的行各拿各的SELECT task_id FROM task_queue WHERE status pending ORDER BY task_id FOR UPDATE SKIP LOCKED LIMIT 10;没有 SKIP LOCKED 的话所有 worker 都会堵在同一批任务上排队拿锁效率极低。应用层还要有重试意识。serialization_failure40001和 deadlock40P01不是代码写错了是并发调度导致的正常回滚业务上应该允许重试。我一般这样写伪逻辑from psycopg2.extensions import TransactionRollbackError max_retries 3 for attempt in range(max_retries): try: with conn.transaction(): do_work() break except TransactionRollbackError: if attempt max_retries - 1: raise把可重试错误和真正的业务错误分开重试几次后还是失败再报警比一次性失败让用户重来体验好很多。4.4 常见并发场景选型速查这一节用一个速查表总结典型的业务场景应该怎么写业务场景常见错误推荐做法说明库存/余额扣减先 SELECT 再 UPDATEUPDATE 条件更新 RETURNING原子操作避免丢更新支付流程不加锁直接改状态先 SELECT FOR UPDATE 再更新保证整段逻辑期间状态稳定防止重复提交靠应用层 Redis 判断唯一索引兜底 条件插入Redis 会过期数据库约束更可靠任务队列并发消费所有 worker SELECT 同一批FOR UPDATE SKIP LOCKED跳过已锁行各行其是资金对账强约束用 REPEATABLE READ 就以为安全SERIALIZABLE 重试只有 SSI 能检测写偏斜大批量数据更新一条 UPDATE 扫全表分批多次小事务控制每批锁持有时间降低阻塞这张表不是教条是我实际处理过的场景归纳。核心思路是能原子更新就原子更新需要锁定就明确加锁期待并发安全就把重试写好。事务隔离级别负责兜底应用层写法才是第一道防线。最后再分享一个个人习惯。事务并发控制这件事参数和视图都是工具真正决定系统稳不稳的是你有没有预警机制。我每套生产环境都会留一条定时任务定期查询 pg_stat_activity 里的事务时长和等待锁的会话任何超过阈值的都推给值班群。很多问题在用户感知之前就被提前拦下来了省掉的是一整夜的数据库排查时间。另外也提醒一句不要盲目把隔离级别升到 SERIALIZABLE。我曾经见过一个团队把所有事务都改成最高级别结果每天都在处理 serialization_failure业务吞吐掉了一大截。先想清楚业务能容忍哪些异常再决定用哪个级别。多数时候条件更新加行锁比调高隔离级别可靠得多也省心得多。