Appearance
事务处理
事务概述
PostgreSQL 是完全符合 ACID 的数据库，提供完善的事务支持。
ACID 特性
| 特性 | 说明 |
|---|---|
| 原子性 (Atomicity) | 事务是不可分割的工作单位 |
| 一致性 (Consistency) | 事务前后数据保持一致状态 |
| 隔离性 (Isolation) | 多个事务互不干扰 |
| 持久性 (Durability) | 事务提交后永久保存 |
事务控制
基本语法
sql
BEGIN;
START TRANSACTION;
COMMIT;
ROLLBACK;事务示例
sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;保存点
sql
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
SAVEPOINT sp2;
ROLLBACK TO sp1;
COMMIT;事务模式
sql
BEGIN READ ONLY;
BEGIN READ WRITE;
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL SERIALIZABLE;隔离级别
四种隔离级别
| 级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | - | - | - |
| READ COMMITTED | ✗ | ✓ | ✓ |
| REPEATABLE READ | ✗ | ✗ | ✓ |
| SERIALIZABLE | ✗ | ✗ | ✗ |
READ COMMITTED（默认）
sql
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;每次查询看到事务开始时已提交的数据。
REPEATABLE READ
sql
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;事务中所有查询看到同一快照。
SERIALIZABLE
sql
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;最高隔离级别，完全串行化执行。
MVCC 多版本并发控制
实现原理
- 每行数据包含 xmin、xmax 隐藏列
- xmin: 创建该行的事务 ID
- xmax: 删除/更新该行的事务 ID
- 使用快照确定可见性
查看隐藏列
sql
SELECT xmin, xmax, * FROM users;可见性判断
如果 xmin 已提交且对当前快照可见，且 xmax 为空或未提交或对当前快照不可见：
该行可见
否则：
该行不可见锁机制
表级锁
| 锁模式 | 说明 |
|---|---|
| ACCESS SHARE | SELECT |
| ROW SHARE | SELECT FOR UPDATE/SHARE |
| ROW EXCLUSIVE | INSERT/UPDATE/DELETE |
| SHARE UPDATE EXCLUSIVE | VACUUM, ANALYZE |
| SHARE | 创建外键 |
| SHARE ROW EXCLUSIVE | 排他共享 |
| EXCLUSIVE | 阻止并发更新 |
| ACCESS EXCLUSIVE | ALTER TABLE, DROP |
sql
LOCK TABLE users IN SHARE MODE;
LOCK TABLE users IN EXCLUSIVE MODE;行级锁
| 锁模式 | 说明 |
|---|---|
| FOR UPDATE | 排他行锁 |
| FOR NO KEY UPDATE | 非键排他锁 |
| FOR SHARE | 共享行锁 |
| FOR KEY SHARE | 键共享锁 |
sql
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 1 FOR SHARE;
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM users WHERE id = 1 FOR UPDATE SKIP LOCKED;查看锁
sql
SELECT * FROM pg_locks;
SELECT * FROM pg_locks WHERE relation = 'users'::regclass;查看阻塞
sql
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.relation = blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;死锁
死锁示例
事务A: UPDATE users SET age = 1 WHERE id = 1;
事务B: UPDATE users SET age = 2 WHERE id = 2;
事务A: UPDATE users SET age = 3 WHERE id = 2; -- 等待
事务B: UPDATE users SET age = 4 WHERE id = 1; -- 死锁死锁检测
PostgreSQL 自动检测死锁并回滚其中一个事务。
避免死锁
- 按相同顺序访问表和行
- 避免长事务
- 使用较低的隔离级别
- 合理设计索引
事务日志
WAL（Write-Ahead Logging）
所有修改先写入 WAL，再修改数据文件。
sql
SHOW wal_level;
SHOW max_wal_size;
SHOW wal_keep_size;WAL 配置
ini
wal_level = replica
max_wal_size = 2GB
min_wal_size = 1GB
wal_keep_size = 1GB
checkpoint_completion_target = 0.9并发问题
脏读
PostgreSQL 不会发生脏读。
不可重复读
READ COMMITTED 级别可能发生：
事务A: SELECT age FROM users WHERE id = 1; -- 25
事务B: UPDATE users SET age = 30 WHERE id = 1; COMMIT;
事务A: SELECT age FROM users WHERE id = 1; -- 30幻读
REPEATABLE READ 级别可能发生：
事务A: SELECT * FROM users WHERE age > 20; -- 5条
事务B: INSERT INTO users (age) VALUES (25); COMMIT;
事务A: SELECT * FROM users WHERE age > 20; -- 5条（快照）序列化异常
SERIALIZABLE 级别可能检测到：
事务A: SELECT COUNT(*) FROM users WHERE age > 20; -- 5
事务B: INSERT INTO users (age) VALUES (25); COMMIT;
事务A: INSERT INTO users (age) VALUES (26); -- 可能失败事务监控
查看活跃事务
sql
SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT * FROM pg_stat_activity WHERE query NOT LIKE '%pg_stat_activity%';查看长事务
sql
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';终止事务
sql
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);事务最佳实践
1. 保持事务简短
sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;2. 避免长事务
sql
SET statement_timeout = '30s';
SET lock_timeout = '10s';
SET idle_in_transaction_session_timeout = '60s';3. 合理选择隔离级别
sql
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;4. 使用适当的锁
sql
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 1 FOR UPDATE SKIP LOCKED;