数据库教程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避开业务高峰，金融串行化业务做好异常捕获重试逻辑。