数据库教程FGMT53‑PostgreSQL事务处理与并发控制

数据库教程FGMT53‑PostgreSQL事务处理与并发控制

前言

PostgreSQL数据库依靠MVCC多版本并发控制机制实现事务隔离，WAL预写日志保障崩溃安全，事务ID冻结、回卷机制保障32位事务ID循环复用，锁子系统管理并发访问冲突。大量生产故障来源于对事务隔离级别理解偏差、长事务阻塞vacuum、WAL参数配置不合理、死锁未及时发现、事务ID回卷风险没有提前预警。风哥教程本文以实验主机fgedu‑net‑cn1开展全套实操，硬件规格64G内存，8颗CPU；数据库实例名fgedudb，业务测试用户名fgedu，软件与数据根目录统一使用/fgedudb。风哥 itpux‑com

本套风哥教程面向PostgreSQL DBA、运维工程师、后端开发、数据库架构师；覆盖ANSI事务隔离级别、MVCC多版本并发控制原理、事务提交机制、事务ID回卷与冻结、WAL预写日志体系、CheckPoint检查点、WAL归档、锁管理、死锁定位与处理；包含大量可复现故障模拟案例。风哥教程本文分为前言与大纲、核心理论知识、实战操作演练、风哥针对本文总结四大模块；实战包含大量可直接复制Shell、SQL脚本，读者可以在测试主机完整复现事务、锁、WAL全套实验。网上搜索风哥教程可以学习全套数据库教程

内容大纲

1. PostgreSQL事务并发控制整体综述，实验主机硬件环境规划，事务相关风险总览
2. 核心理论：ANSI四种事务隔离级别；MVCC多版本并发控制原理；快照机制；事务提交流程；事务ID回卷wraparound与事务冻结freeze；WAL预写日志工作机制；CheckPoint检查点；WAL文件结构、日志切换、归档；锁体系分类；死锁产生条件与检测机制；适配64G内存8CPU生产参数模板；各类风险点汇总
3. 实战1：四种事务隔离级别实操复现，RC、RR、Serializable行为对比
4. 实战2：MVCC快照机制实操，长事务阻碍vacuum故障模拟
5. 实战3：事务ID冻结、事务回卷风险检测实操，监控XID年龄
6. 实战4：WAL日志体系实操，pg_waldump解析WAL日志，WAL切换观测
7. 实战5：CheckPoint检查点观测，调整checkpoint相关参数
8. 实战6：WAL归档配置完整实操，归档失败故障模拟
9. 实战7：锁管理实操，行锁、表锁、advisory咨询锁，pg_locks、pg_stat_activity监控
10. 实战8：死锁故障模拟，死锁日志查看，死锁报错处理
11. 实战9：空闲事务idle_in_transaction危害模拟与自动回收配置
12. 事务并发上线验收检查清单，生产最佳实践，高频故障排查

一、核心理论知识

本章节为本套风哥教程理论基础，理解MVCC、事务冻结、WAL、锁与死锁底层逻辑，识别长事务、XID回卷、WAL暴涨、死锁等生产风险，避免业务并发故障。风哥教程 113257174

1.1 ANSI事务隔离级别

PostgreSQL实现标准ANSI隔离级别，其中READ UNCOMMITTED在PG内部等价于READ COMMITTED，不会读到脏数据。

1. READ COMMITTED（读已提交RC）：每条SQL语句获取新快照，可以读到其他事务已经提交的数据；允许不可重复读、幻读；OLTP业务最常用。
2. REPEATABLE READ（可重复读RR）：事务启动时获取一次快照，整个事务内快照保持不变；避免不可重复读；更新幻影行会报序列化失败。
3. SERIALIZABLE（串行化）：基于SSI快照隔离，真正杜绝幻读；冲突事务直接报40001序列化失败，业务需要增加重试逻辑，适合金融强一致性场景。

报错码：死锁40P01；序列化冲突40001，应用程序需要捕获异常做事务重试。

1.2 MVCC多版本并发控制原理

MVCC不依靠读锁实现读不阻塞写、写不阻塞读。数据行存在多个元组版本，元组头部保存xmin（创建该行版本的事务ID）、xmax（删除/更新该行版本的事务ID）。

• xmin：事务ID，该元组由哪个事务插入；
• xmax：事务ID，该元组被哪个事务删除或者更新；0代表未删除。
  每个会话根据自身快照可见性规则，判断元组版本是否对当前事务可见。旧版本元组产生死元组dead tuple，交由VACUUM清理；长事务持有旧快照会阻止vacuum清理旧元组，造成表膨胀。

1.3 事务ID回卷Wraparound与事务冻结Freeze

PostgreSQL事务ID是32位无符号整数，最大值约42亿，会循环复用，也就是事务ID回卷wraparound。为防止ID循环之后数据不可见，VACUUM会执行冻结freeze，把很老的元组xmin设置为特殊Frozen事务ID，永远对所有会话可见。
关键参数：

• autovacuum_freeze_max_age：事务ID年龄到达该阈值，强制执行激进vacuum冻结，哪怕autovacuum关闭；
• vacuum_freeze_table_age、vacuum_freeze_min_age控制冻结触发时机。

风险：禁止关闭autovacuum；长事务会阻止冻结操作；XID接近回卷点实例会拒绝分配新事务ID，业务完全不可用。

1.4 WAL预写日志工作原理

WAL（Write‑Ahead‑Log）预写日志，修改数据页之前，先把变更写入WAL日志，再刷磁盘数据页。

1. 事务提交，先把事务变更写入WAL缓冲区；
2. fsync刷WAL到磁盘文件之后，向客户端返回提交成功；数据脏页可以后续后台bgwriter/checkpoint慢慢落盘；
3. 实例崩溃重启，回放WAL完成崩溃恢复；同时WAL也是物理流复制的数据源。

关键概念：

• CheckPoint检查点：触发时把内存所有脏数据页刷入数据文件，推进redo恢复点；检查点过于频繁会造成IO抖动；
• LSN：日志序列号，WAL内全局唯一偏移位置；
• WAL段文件：默认单文件大小16MB，pg_wal目录存放；到达大小自动切换新段；
• WAL归档：把已经完成的WAL段拷贝到归档存储，用于PITR时间点恢复。

1.5 锁体系分类

1. 表级锁：ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE；ALTER、DROP获取ACCESS EXCLUSIVE排他锁，阻塞全部读写。
2. 行级锁：SELECT FOR UPDATE / FOR SHARE，锁定行元组，阻止其他会话修改该行；PG不存在InnoDB的next‑key间隙锁。
3. 咨询锁Advisory lock：业务自定义数字key锁，不绑定数据表，用于分布式业务互斥控制。
4. 监控视图：pg_locks锁详情；pg_stat_activity会话与等待事件。

1.6 死锁原理

两个或者多个会话互相持有对方需要的锁，循环等待。PostgreSQL后台死锁检测器自动检测，随机回滚其中一个事务，抛出40P01 deadlock_detected错误。死锁根本原因：更新行顺序不一致；应用层必须保证更新资源顺序统一，从源头降低死锁概率。

1.7 64G内存8CPU硬件生产postgresql.conf相关参数模板

#内存
shared_buffers = 16GB
work_mem = 64MB
maintenance_work_mem = 4GB
effective_cache_size = 48GB
max_connections = 800
#WAL配置
wal_level = replica
max_wal_size = 16GB
min_wal_size = 4GB
wal_buffers = 16MB
wal_compression = on
checkpoint_timeout = 15min
checkpoint_completion_target = 0.8
synchronous_commit = on
#vacuum冻结与事务ID
autovacuum = on
autovacuum_max_workers = 6
autovacuum_freeze_max_age = 200000000
vacuum_freeze_table_age = 150000000
vacuum_freeze_min_age = 50000000
#空闲事务自动断开，避免长事务
idle_in_transaction_session_timeout = 60000
#日志
logging_collector = on
log_directory = '/fgedudb/pg_log'
log_filename = 'postgresql‑%Y%m%d.log'
log_min_duration_statement = 1000
log_deadlocks = on

1.8 生产高频风险汇总

1. 长事务、idle_in_transaction空闲事务，阻止vacuum，表疯狂膨胀，同时阻止事务ID冻结，带来XID回卷致命风险；
2. synchronous_commit=off提升性能，但是实例断电丢失最近事务；
3. 关闭autovacuum，元组无法冻结，数据库到达回卷阈值业务停机；
4. DDL获取ACCESS EXCLUSIVE锁，业务高峰期执行DDL，全部业务会话阻塞；
5. 业务更新行顺序随机，大量死锁；
6. checkpoint_completion_target过小，检查点瞬间大量刷脏页，IO打满业务抖动；
7. WAL归档目录磁盘满，WAL段无法归档，实例停止所有写入。

网上搜索风哥教程可以学习全套数据库教程

二、实战操作演练

环境说明：
实验主机fgedu‑net‑cn1；硬件规格64G内存8CPU；PostgreSQL软件根目录/fgedudb/pgsql；数据目录/fgedudb/pgdata；日志目录/fgedudb/pg_log；操作系统用户postgres；端口5432；业务库fgedudb，业务用户fgedu。
前置环境已经初始化实例，业务库与测试表：

su - postgres
/fgedudb/pgsql/bin/psql -d fgedudb

CREATE DATABASE fgedudb;
CREATE USER fgedu WITH PASSWORD 'fgedudb';
GRANT ALL ON DATABASE fgedudb TO fgedu;
\c fgedudb fgedu
CREATE TABLE t_account(id int primary key,balance numeric(12,2),user_name text);
INSERT INTO t_account VALUES(1,1000.00,'user01'),(2,2000.00,'user02');

实战1：四种事务隔离级别实操复现

开启两个独立psql会话，会话A、会话B。

READ COMMITTED（RC读已提交）

会话A：

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM t_account WHERE id=1;

会话B执行更新并且提交：

BEGIN;
UPDATE t_account SET balance=1500 WHERE id=1;
COMMIT;

会话A再次执行查询，可以读到B已经提交的新数据，RC每条SQL拿新快照。

REPEATABLE READ（RR可重复读）

会话A：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM t_account WHERE id=1;

会话B更新提交；会话A再次执行SELECT * FROM t_account WHERE id=1;，读到的还是事务启动那一刻的旧快照，看不到B提交的新值。

SERIALIZABLE串行化

会话A：

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t_account WHERE id=1;

会话B修改提交；会话A尝试更新同一行，直接报ERROR: could not serialize access due to concurrent update，SQLSTATE=40001，应用需要捕获异常重试。

READ UNCOMMITTED在PG行为等价READ COMMITTED，不会读取脏数据。

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

风哥数据库教程 itpux‑com

实战2：MVCC快照机制，长事务阻碍vacuum故障模拟

1. 会话A开启事务，不提交，制造长事务持有旧快照

BEGIN;
SELECT * FROM t_account;
--事务保持打开，不commit，不rollback

2. 会话B大量更新产生dead tuple死元组

UPDATE t_account SET balance=balance+100 WHERE id=1;
UPDATE t_account SET balance=balance+100 WHERE id=2;
COMMIT;

3. 执行vacuum，因为会话A持有旧快照，旧元组不能被清理

VACUUM VERBOSE t_account;

4. 查询表统计信息，观察死元组数量

SELECT n_live_tup,n_dead_tup FROM pg_stat_user_tables WHERE relname='t_account';

5. 会话A执行commit结束长事务；再次执行vacuum，死元组被清理。

生产经验：空闲长事务idle_in_transaction是表膨胀头号诱因，配置idle_in_transaction_session_timeout自动断开空闲事务。网上搜索风哥教程可以学习全套数据库教程

实战3：事务ID冻结、XID回卷风险检测实操

查询当前数据库事务ID年龄，监控回卷风险

--查看各个数据库XID年龄
SELECT datname,age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;
--查看表层面冻结年龄
SELECT relname,age(relfrozenxid) FROM pg_class WHERE relkind='r' ORDER BY age(relfrozenxid) DESC LIMIT 20;

age数值越接近200000000，说明接近强制冻结阈值，必须保证autovacuum正常运行。
手工执行激进vacuum强制冻结测试：

VACUUM FREEZE VERBOSE t_account;

风险模拟：如果人为关闭autovacuum，持续大量写入，age持续上涨，到达阈值实例会拒绝分配新事务ID，业务完全停机，该故障只能依靠备份恢复，无法简单修复。

实战4：WAL日志体系实操，pg_waldump解析WAL日志

查看WAL目录与当前LSN

su - postgres
cd /fgedudb/pgdata/pg_wal
ls -lh
#查看当前WAL LSN
/fgedudb/pgsql/bin/pg_controldata -D /fgedudb/pgdata | grep "Latest checkpoint location"

执行SQL触发WAL日志切换

SELECT pg_switch_wal();

使用pg_waldump工具解析WAL段文件，查看WAL内部事务记录

/fgedudb/pgsql/bin/pg_waldump /fgedudb/pgdata/pg_wal/000000010000000000000012

解析输出可以看到INSERT、UPDATE、COMMIT各类WAL记录。

实战5：CheckPoint检查点观测实操

查看bgwriter与checkpoint统计视图

SELECT * FROM pg_stat_bgwriter;

关键字段：

• checkpoints_timed：定时触发的检查点；
• checkpoints_req：手动或者压力触发的请求式检查点；生产环境希望checkpoints_req数值尽量小。

手动触发一次检查点

CHECKPOINT;

调整postgresql.conf，checkpoint_timeout=15min，checkpoint_completion_target=0.8，平滑刷脏页，避免IO瞬间打满。
修改参数后执行pg_ctl reload生效。

实战6：WAL归档配置完整实操

修改postgresql.conf增加归档参数

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /fgedudb/pg_arch/%f && cp %p /fgedudb/pg_arch/%f'

创建归档目录

mkdir -p /fgedudb/pg_arch
chown postgres:postgres /fgedudb/pg_arch
chmod 700 /fgedudb/pg_arch

archive_mode属于postmaster参数，修改必须重启实例。

/fgedudb/pgsql/bin/pg_ctl restart -D /fgedudb/pgdata

触发WAL切换，观察归档目录生成WAL段文件

SELECT pg_switch_wal();

ls -lh /fgedudb/pg_arch

故障模拟：归档目录磁盘100%占满，cp命令返回非0，归档失败，pg_wal目录WAL文件不断堆积，实例写入逐步阻塞；清理磁盘空间之后归档自动继续。

实战7：锁管理实操，行锁、表锁、咨询锁advisory lock

7.1 行锁SELECT FOR UPDATE

会话A：

BEGIN;
SELECT * FROM t_account WHERE id=1 FOR UPDATE;

会话B执行更新同一行，会话B会被阻塞等待行锁

BEGIN;
UPDATE t_account SET balance=balance+100 WHERE id=1;

新开psql，查询锁与会话等待

SELECT pid,usename,query,state,wait_event_type,wait_event FROM pg_stat_activity;
SELECT locktype,relation,page,tuple,pid,mode FROM pg_locks;

会话A执行commit，锁释放，会话B继续执行。

7.2 表级排他锁演示

BEGIN;
ALTER TABLE t_account ADD COLUMN remark text;
--不提交，持有ACCESS EXCLUSIVE锁，所有DML全部阻塞

业务高峰期禁止直接执行DDL，会造成业务大规模阻塞。

7.3 咨询锁advisory lock，业务自定义互斥锁

--获取数字key=100排他咨询锁
SELECT pg_try_advisory_lock(100);
--释放咨询锁
SELECT pg_advisory_unlock(100);

咨询锁不绑定数据表，适合业务层面分布式互斥逻辑。上51CTO搜索风哥可以学习全套数据库教程

实战8：死锁故障模拟，死锁日志查看与处理

开启死锁日志记录，postgresql.conf参数log_deadlocks=on，reload生效。

会话A、会话B，更新行顺序相反，制造循环等待死锁。
会话A：

BEGIN;
UPDATE t_account SET balance=balance+10 WHERE id=1;
UPDATE t_account SET balance=balance‑10 WHERE id=2;

会话B：

BEGIN;
UPDATE t_account SET balance=balance+10 WHERE id=2;
UPDATE t_account SET balance=balance‑10 WHERE id=1;
COMMIT;

两个会话执行，瞬间触发死锁，其中一个会话自动被PG回滚，报40P01 deadlock_detected。
查看数据库日志目录/fgedudb/pg_log，日志完整记录死锁两个会话SQL、锁资源。

业务解决手段：统一更新资源顺序，永远按照id从小到大更新，从根源消除死锁循环等待条件；应用捕获40P01错误增加事务重试逻辑。

实战9：空闲事务idle_in_transaction危害模拟与自动回收配置

1. 会话开启事务，执行一条SQL之后，保持空闲，既不commit也不rollback，状态idle in transaction。

BEGIN;
UPDATE t_account SET balance=balance+10 WHERE id=1;
--停在这里，空闲不提交

2. 其他会话持续DML产生大量dead tuple，vacuum无法清理，同时阻止事务ID冻结，带来XID回卷风险。
3. postgresql.conf配置自动超时断开空闲事务

idle_in_transaction_session_timeout = 60000

单位毫秒，60秒空闲事务自动断开会话，回滚未提交事务；执行pg_ctl reload生效。

show idle_in_transaction_session_timeout;

2.10 事务并发上线验收检查清单

1. 参数配置：wal_level=replica；max_wal_size、checkpoint参数适配64G‑8CPU硬件；autovacuum完全开启；idle_in_transaction_session_timeout配置空闲事务超时；log_deadlocks=on开启死锁日志；
2. 风险监控：定期监控pg_database.age(datfrozenxid)事务ID年龄，防止XID回卷；监控pg_stat_user_tables死元组n_dead_tup，及时发现表膨胀；
3. 隔离级别：业务评估RC/RR/SERIALIZABLE；金融串行化业务，应用做好40001序列化失败捕获重试逻辑；
4. 归档：WAL归档目录磁盘充足，归档脚本测试可用，模拟归档失败场景验证告警；
5. 业务规范：禁止业务长事务；禁止业务高峰期执行ALTER、DROP等获取ACCESS EXCLUSIVE锁DDL；业务更新资源固定顺序，降低死锁概率；
6. 监控项：长空闲事务、锁等待会话、死锁计数、checkpoints_req、WAL生成速率；
7. 演练：模拟长事务故障、死锁故障、归档磁盘满故障，确认告警与处理流程；
8. 运维文档：事务隔离级别说明，死锁处理手册，XID回卷风险应急处置。

2.11 高频故障排查

1. 表持续膨胀：排查是否存在idle in transaction长事务，长快照阻止vacuum清理死元组；配置空闲事务超时，结束长事务后执行vacuum。
2. 大量死锁40P01：查看pg_log死锁日志，调整业务更新行顺序，应用捕获死锁异常增加重试。
3. checkpoint_req持续上涨IO抖动：max_wal_size过小；调大max_wal_size，调大checkpoint_completion_target，平滑刷脏页。
4. WAL文件大量堆积：归档失败，归档目录磁盘满，修复磁盘，归档自动继续。
5. XID年龄持续上涨：autovacuum被关闭或者被长事务阻塞；禁止关闭autovacuum，消除长事务。
6. SERIALIZABLE频繁报40001：业务冲突大，应用增加事务重试逻辑。
7. DDL执行之后业务全部卡住：DDL获取ACCESS EXCLUSIVE排他锁，会话存在活跃事务持有表快照，DDL排队，新业务全部阻塞，优先kill掉老会话。

三、风哥针对本文总结

本套风哥教程完整讲解PostgreSQL事务与并发控制整套知识，包含ANSI事务隔离级别、MVCC多版本并发控制快照机制、事务ID冻结与XID回卷风险、WAL预写日志、CheckPoint检查点、WAL归档、表锁行锁咨询锁、死锁模拟处理、空闲事务危害；适配64G内存8CPU硬件参数模板，配套全套可复现SQL与Shell实战脚本。

1. PostgreSQL MVCC依靠元组xmin/xmax多版本实现读不阻塞写；RC隔离级别每条SQL拿新快照，RR事务启动拿一次快照；SERIALIZABLE串行化提供真正防幻读，业务需要捕获40001序列化失败做事务重试；READ UNCOMMITTED在PG内部等价RC，读不到脏数据。
2. 32位事务ID会发生wraparound回卷；VACUUM FREEZE冻结老旧元组避免回卷风险；autovacuum绝对不能关闭；长事务、idle_in_transaction空闲事务会阻止vacuum与冻结，带来表膨胀和XID回卷停机风险；生产务必配置idle_in_transaction_session_timeout自动回收空闲事务。
3. WAL预写日志保证崩溃安全，提交先刷WAL再返回成功；max_wal_size、checkpoint_completion_target参数控制检查点IO抖动；WAL归档用于PITR时间点恢复，归档目录磁盘耗尽会阻塞实例全部写入，需要监控归档状态。pg_waldump工具可以解析原始WAL二进制日志排查变更。
4. 锁分为表级锁、行级锁、咨询锁；ALTER、DROP会获取ACCESS EXCLUSIVE排他锁，高峰期执行会造成业务大规模阻塞；pg_locks、pg_stat_activity用于锁等待监控；咨询锁不绑定数据表，适合业务自定义互斥逻辑。
5. 死锁由会话循环等待锁资源产生，PG后台自动检测并回滚其中一个事务；从业务层统一更新资源顺序可以从根源降低死锁概率；开启log_deadlocks把死锁信息写入日志，便于故障排查。
6. 生产上线前要做风险模拟：长事务膨胀模拟、死锁模拟、归档磁盘占满模拟；监控XID事务年龄、死锁计数、长空闲事务、checkpoint请求次数；DDL避开业务高峰，金融串行化业务做好异常捕获重试逻辑。