数据库教程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相关参数模板
“`ini
#内存
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`。
前置环境已经初始化实例，业务库与测试表：
“`bash
su – postgres
/fgedudb/pgsql/bin/psql -d fgedudb
“`
“`sql
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：
“`sql
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM t_account WHERE id=1;
“`
会话B执行更新并且提交：
“`sql
BEGIN;
UPDATE t_account SET balance=1500 WHERE id=1;
COMMIT;
“`
会话A再次执行查询，可以读到B已经提交的新数据，RC每条SQL拿新快照。

#### REPEATABLE READ（RR可重复读）
会话A：
“`sql
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：
“`sql
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，不会读取脏数据。
“`sql
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
“`
风哥数据库教程 itpux‑com

### 实战2：MVCC快照机制，长事务阻碍vacuum故障模拟
1. 会话A开启事务，不提交，制造长事务持有旧快照
“`sql
BEGIN;
SELECT * FROM t_account;
–事务保持打开，不commit，不rollback
“`
2. 会话B大量更新产生dead tuple死元组
“`sql
UPDATE t_account SET balance=balance+100 WHERE id=1;
UPDATE t_account SET balance=balance+100 WHERE id=2;
COMMIT;
“`
3. 执行vacuum，因为会话A持有旧快照，旧元组**不能被清理**
“`sql
VACUUM VERBOSE t_account;
“`
4. 查询表统计信息，观察死元组数量
“`sql
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年龄，监控回卷风险
“`sql
–查看各个数据库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强制冻结测试：
“`sql
VACUUM FREEZE VERBOSE t_account;
“`
>风险模拟：如果人为关闭autovacuum，持续大量写入，age持续上涨，到达阈值实例会拒绝分配新事务ID，业务完全停机，该故障只能依靠备份恢复，无法简单修复。

### 实战4：WAL日志体系实操，pg_waldump解析WAL日志
查看WAL目录与当前LSN
“`bash
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日志切换
“`sql
SELECT pg_switch_wal();
“`
使用pg_waldump工具解析WAL段文件，查看WAL内部事务记录
“`bash
/fgedudb/pgsql/bin/pg_waldump /fgedudb/pgdata/pg_wal/000000010000000000000012
“`
解析输出可以看到INSERT、UPDATE、COMMIT各类WAL记录。

### 实战5：CheckPoint检查点观测实操
查看bgwriter与checkpoint统计视图
“`sql
SELECT * FROM pg_stat_bgwriter;
“`
关键字段：
– `checkpoints_timed`：定时触发的检查点；
– `checkpoints_req`：手动或者压力触发的请求式检查点；生产环境希望`checkpoints_req`数值尽量小。

手动触发一次检查点
“`sql
CHECKPOINT;
“`
调整postgresql.conf，`checkpoint_timeout=15min`，`checkpoint_completion_target=0.8`，平滑刷脏页，避免IO瞬间打满。
修改参数后执行pg_ctl reload生效。

### 实战6：WAL归档配置完整实操
修改postgresql.conf增加归档参数
“`ini
wal_level = replica
archive_mode = on
archive_command = ‘test ! -f /fgedudb/pg_arch/%f && cp %p /fgedudb/pg_arch/%f’
“`
创建归档目录
“`bash
mkdir -p /fgedudb/pg_arch
chown postgres:postgres /fgedudb/pg_arch
chmod 700 /fgedudb/pg_arch
“`
>archive_mode属于postmaster参数，**修改必须重启实例**。
“`bash
/fgedudb/pgsql/bin/pg_ctl restart -D /fgedudb/pgdata
“`
触发WAL切换，观察归档目录生成WAL段文件
“`sql
SELECT pg_switch_wal();
“`
“`bash
ls -lh /fgedudb/pg_arch
“`
故障模拟：归档目录磁盘100%占满，cp命令返回非0，归档失败，pg_wal目录WAL文件不断堆积，实例写入逐步阻塞；清理磁盘空间之后归档自动继续。

### 实战7：锁管理实操，行锁、表锁、咨询锁advisory lock
#### 7.1 行锁SELECT FOR UPDATE
会话A：
“`sql
BEGIN;
SELECT * FROM t_account WHERE id=1 FOR UPDATE;
“`
会话B执行更新同一行，会话B会被阻塞等待行锁
“`sql
BEGIN;
UPDATE t_account SET balance=balance+100 WHERE id=1;
“`
新开psql，查询锁与会话等待
“`sql
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 表级排他锁演示
“`sql
BEGIN;
ALTER TABLE t_account ADD COLUMN remark text;
–不提交，持有ACCESS EXCLUSIVE锁，所有DML全部阻塞
“`
>业务高峰期禁止直接执行DDL，会造成业务大规模阻塞。

#### 7.3 咨询锁advisory lock，业务自定义互斥锁
“`sql
–获取数字key=100排他咨询锁
SELECT pg_try_advisory_lock(100);
–释放咨询锁
SELECT pg_advisory_unlock(100);
“`
咨询锁不绑定数据表，适合业务层面分布式互斥逻辑。上51CTO搜索风哥可以学习全套数据库教程

### 实战8：死锁故障模拟，死锁日志查看与处理
开启死锁日志记录，postgresql.conf参数`log_deadlocks=on`，reload生效。
>会话A、会话B，更新行顺序相反，制造循环等待死锁。
会话A：
“`sql
BEGIN;
UPDATE t_account SET balance=balance+10 WHERE id=1;
UPDATE t_account SET balance=balance‑10 WHERE id=2;
“`
会话B：
“`sql
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`。
“`sql
BEGIN;
UPDATE t_account SET balance=balance+10 WHERE id=1;
–停在这里，空闲不提交
“`
2. 其他会话持续DML产生大量dead tuple，vacuum无法清理，同时阻止事务ID冻结，带来XID回卷风险。
3. postgresql.conf配置自动超时断开空闲事务
“`ini
idle_in_transaction_session_timeout = 60000
“`
单位毫秒，60秒空闲事务自动断开会话，回滚未提交事务；执行pg_ctl reload生效。
“`sql
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避开业务高峰，金融串行化业务做好异常捕获重试逻辑。