PostgreSQL 事务
事务是数据库操作的基本单元，保证一组操作要么全部成功，要么全部失败。本文档涵盖事务基础、保存点、隔离级别、MVCC 机制与锁。
1. 事务基本介绍与特点
什么是事务
事务（Transaction）是一组 SQL 语句的逻辑单元，这些语句作为一个整体执行——要么全部提交成功，要么全部回滚撤销。
sql
-- 经典场景：银行转账
-- Alice 向 Bob 转账 500 元，必须保证两步都成功
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 500 WHERE name = 'Bob';
COMMIT;
-- 如果中间任何一步出错，所有操作都会被撤销ACID 特性
| 特性 | 英文 | 说明 |
|---|---|---|
| 原子性 | Atomicity | 事务中的操作要么全部完成，要么全部不做 |
| 一致性 | Consistency | 事务前后数据满足所有约束和规则 |
| 隔离性 | Isolation | 并发事务之间互不干扰 |
| 持久性 | Durability | 事务提交后数据永久保存，即使系统崩溃也不丢失 |
sql
-- 原子性示例：约束违反导致整个事务回滚
BEGIN;
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@test.com');
INSERT INTO users (id, name, email) VALUES (2, 'Bob', NULL); -- 假设 email 是 NOT NULL
-- 第二条失败，第一条也被撤销
COMMIT; -- 实际会变成 ROLLBACK
-- 一致性示例：余额不能为负
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice';
-- 如果有 CHECK (balance >= 0)，余额不足时违反约束
-- 事务回滚，数据保持一致
COMMIT;
-- 持久性：提交后即使断电也不丢失
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);
COMMIT; -- WAL 日志已写入磁盘，数据安全隐式事务
PostgreSQL 中每条 SQL 语句都在一个隐式事务中执行：
sql
-- 这条语句自动被包装在一个事务中
INSERT INTO users (name) VALUES ('Alice');
-- 等价于：
-- BEGIN;
-- INSERT INTO users (name) VALUES ('Alice');
-- COMMIT;
-- autocommit 默认开启
SHOW autocommit; -- on2. 事务的操作
BEGIN / COMMIT / ROLLBACK
sql
-- 开始事务（以下三种写法等价）
BEGIN;
BEGIN WORK;
BEGIN TRANSACTION;
START TRANSACTION;
-- 提交事务
COMMIT;
COMMIT WORK;
COMMIT TRANSACTION;
END; -- END 是 COMMIT 的别名
-- 回滚事务
ROLLBACK;
ROLLBACK WORK;
ROLLBACK TRANSACTION;
ABORT; -- ABORT 是 ROLLBACK 的别名完整事务流程
sql
-- 示例：创建订单
BEGIN;
-- 1. 检查库存
SELECT stock FROM products WHERE id = 100 FOR UPDATE; -- 加行锁
-- 2. 减少库存
UPDATE products SET stock = stock - 1 WHERE id = 100;
-- 3. 创建订单
INSERT INTO orders (user_id, product_id, amount)
VALUES (1, 100, 29.99)
RETURNING id;
-- 4. 创建支付记录
INSERT INTO payments (order_id, amount, status)
VALUES (currval('orders_id_seq'), 29.99, 'pending');
COMMIT;事务中的错误处理
PostgreSQL 事务中一旦出错，事务进入 aborted 状态，后续所有语句都会被拒绝：
sql
BEGIN;
INSERT INTO users (name, email) VALUES ('Alice', 'alice@test.com');
INSERT INTO users (name, email) VALUES ('Bob', 'invalid'); -- 假设失败
-- 此时事务已 aborted
SELECT * FROM users; -- ERROR: current transaction is aborted
-- 只能 ROLLBACK
ROLLBACK;只读事务
sql
-- 只读事务不能执行写操作
BEGIN READ ONLY;
SELECT * FROM users; -- OK
-- INSERT INTO users VALUES (...); -- ERROR: cannot execute INSERT in a read-only transaction
COMMIT;
-- 设置事务属性
BEGIN;
SET TRANSACTION READ ONLY;
-- ...
COMMIT;
-- 设置会话级别
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;事务超时
sql
-- 语句超时（毫秒）
SET statement_timeout = '5s'; -- 单条语句最多执行5秒
-- 空闲事务超时（PG 14+）
SET idle_in_transaction_session_timeout = '60s'; -- 空闲超过60秒自动终止
-- 锁等待超时
SET lock_timeout = '3s'; -- 等锁超过3秒就放弃查看活跃事务
sql
-- 查看当前运行中的事务
SELECT pid, state, xact_start, query_start,
now() - xact_start AS duration,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;
-- 查看长事务
SELECT pid, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > INTERVAL '5 minutes';
-- 终止有问题的事务
SELECT pg_cancel_backend(pid); -- 取消查询（温和）
SELECT pg_terminate_backend(pid); -- 终止连接（强制）3. 事务 — 保存点
保存点（SAVEPOINT）允许在事务内设置标记，可以部分回滚到某个标记而不回滚整个事务。
基本用法
sql
BEGIN;
INSERT INTO users (name, email) VALUES ('Alice', 'alice@test.com');
SAVEPOINT sp1; -- 设置保存点
INSERT INTO users (name, email) VALUES ('Bob', 'bob@test.com');
SAVEPOINT sp2; -- 第二个保存点
INSERT INTO users (name, email) VALUES ('Charlie', 'invalid_email');
-- 假设失败或需要撤销
ROLLBACK TO SAVEPOINT sp2; -- 只回滚 Charlie 的插入，Alice 和 Bob 保留
-- 继续操作
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@test.com');
COMMIT; -- Alice、Bob、Charlie 全部提交保存点嵌套
sql
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
SAVEPOINT sp_order;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 10, 2);
SAVEPOINT sp_item1;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 20, 1);
SAVEPOINT sp_item2;
-- 尝试扣减库存
UPDATE products SET stock = stock - 1 WHERE id = 20;
-- 假设库存不足，回滚这一步
ROLLBACK TO sp_item2;
-- item2 的操作被撤销，但 item1 和 order 仍然有效
RELEASE SAVEPOINT sp_item1; -- 释放保存点（不再需要回滚到此点）
-- order 和 item1 保留
COMMIT;释放保存点
sql
BEGIN;
SAVEPOINT sp1;
-- ... 一些操作 ...
RELEASE SAVEPOINT sp1; -- 释放保存点，不再可以回滚到 sp1
-- 释放不等于提交，只是释放这个标记
-- ROLLBACK TO sp1; -- ERROR: 保存点已释放
COMMIT;实际应用：批量导入容错
sql
-- 批量导入数据，跳过有问题的行
BEGIN;
SAVEPOINT before_row;
INSERT INTO users (name, email) VALUES ('Alice', 'alice@test.com');
RELEASE SAVEPOINT before_row;
SAVEPOINT before_row;
INSERT INTO users (name, email) VALUES ('Bob', 'duplicate@test.com'); -- 假设重复
-- 如果出错，回滚这一行
ROLLBACK TO SAVEPOINT before_row;
SAVEPOINT before_row;
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@test.com');
RELEASE SAVEPOINT before_row;
COMMIT; -- Alice 和 Charlie 成功，Bob 被跳过PL/pgSQL 中的异常处理（等效保存点）
sql
-- PL/pgSQL 的 EXCEPTION 块内部使用保存点实现
CREATE OR REPLACE FUNCTION safe_insert_user(p_name TEXT, p_email TEXT)
RETURNS BOOLEAN AS $$
BEGIN
INSERT INTO users (name, email) VALUES (p_name, p_email);
RETURN TRUE;
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE '邮箱 % 已存在，跳过', p_email;
RETURN FALSE;
WHEN check_violation THEN
RAISE NOTICE '数据校验失败: %', SQLERRM;
RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
-- 在事务中使用
BEGIN;
PERFORM safe_insert_user('Alice', 'alice@test.com'); -- 成功
PERFORM safe_insert_user('Bob', 'alice@test.com'); -- 重复，被捕获跳过
PERFORM safe_insert_user('Charlie', 'charlie@test.com'); -- 成功
COMMIT;4. 并发 — 事务的隔离级别
当多个事务并发执行时，可能出现各种数据一致性问题。隔离级别决定了事务之间的可见性。
并发问题
| 问题 | 说明 | 示例 |
|---|---|---|
| 脏读 | 读到其他事务未提交的数据 | 事务A修改但未提交，事务B读到了修改后的值 |
| 不可重复读 | 同一事务中两次读取同一行结果不同 | 事务A第一次读 age=25，事务B更新为30并提交，事务A第二次读 age=30 |
| 幻读 | 同一事务中两次查询返回不同的行集合 | 事务A查询有3行，事务B插入1行并提交，事务A再查询有4行 |
| 序列化异常 | 并发事务的结果与任何串行执行顺序都不同 | 两个事务互相依赖对方的写入结果 |
四种隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 序列化异常 |
|---|---|---|---|---|
| Read Uncommitted | ✗ | ✓ | ✓ | ✓ |
| Read Committed（默认） | ✗ | ✓ | ✓ | ✓ |
| Repeatable Read | ✗ | ✗ | ✗* | ✓ |
| Serializable | ✗ | ✗ | ✗ | ✗ |
✗ = 不会发生 ✓ = 可能发生 PostgreSQL 的 Read Uncommitted 实际等同于 Read Committed（不允许脏读）PostgreSQL 的 Repeatable Read 也防止幻读（标记 ✗*）
设置隔离级别
sql
-- 事务级别设置
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 也可以在 BEGIN 后设置
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 会话级别设置
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 全局默认（postgresql.conf）
-- default_transaction_isolation = 'read committed'
-- 查看当前隔离级别
SHOW transaction_isolation;Read Committed（默认）
每条 SQL 语句看到的是该语句开始执行时已提交的数据快照：
sql
-- 会话 A -- 会话 B
BEGIN; BEGIN;
SELECT age FROM users WHERE id = 1;
-- 结果: 25
UPDATE users SET age = 30 WHERE id = 1;
COMMIT;
SELECT age FROM users WHERE id = 1;
-- 结果: 30（看到了 B 提交的修改，不可重复读）
COMMIT;sql
-- Read Committed 下 UPDATE 的行为
-- 会话 A -- 会话 B
BEGIN; BEGIN;
UPDATE users SET score = score + 10
WHERE age > 25;
-- 正在执行中...
UPDATE users SET age = 30 WHERE id = 1;
-- 原来 age=20，现在改成30
COMMIT;
-- 会话 A 的 UPDATE 重新评估条件
-- id=1 现在 age=30 > 25，也会被更新！
COMMIT;Repeatable Read
事务看到的是事务开始时已提交的数据快照，整个事务期间快照不变：
sql
-- 会话 A -- 会话 B
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN;
SELECT age FROM users WHERE id = 1;
-- 结果: 25
UPDATE users SET age = 30 WHERE id = 1;
COMMIT;
SELECT age FROM users WHERE id = 1;
-- 结果: 仍然 25（可重复读！）
COMMIT;sql
-- Repeatable Read 下的写冲突
-- 会话 A -- 会话 B
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE users SET name = 'Alice2'
WHERE id = 1;
UPDATE users SET name = 'Alice3'
WHERE id = 1;
-- 等待会话 A...
COMMIT;
-- ERROR: could not serialize access
-- due to concurrent update
-- 必须重试事务！
ROLLBACK;Serializable
最严格的隔离级别，保证并发结果等同于某种串行执行顺序：
sql
-- 经典问题：黑白球计数
-- 表中有黑球和白球，两个事务分别统计
CREATE TABLE balls (color TEXT, count INTEGER);
INSERT INTO balls VALUES ('black', 10), ('white', 20);
-- 会话 A -- 会话 B
BEGIN ISOLATION LEVEL SERIALIZABLE; BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count FROM balls SELECT count FROM balls
WHERE color = 'black'; WHERE color = 'white';
-- 结果: 10 -- 结果: 20
UPDATE balls SET count = 20 UPDATE balls SET count = 10
WHERE color = 'white'; WHERE color = 'black';
COMMIT; COMMIT;
-- ERROR: could not serialize access
-- 检测到序列化冲突！隔离级别选择建议
sql
-- Read Committed（默认）：大多数 OLTP 场景
-- 优点：冲突少，并发高
-- 缺点：可能不可重复读
-- Repeatable Read：需要事务内一致性快照的场景
-- 优点：事务内数据一致
-- 缺点：写冲突需要重试
-- Serializable：对正确性要求极高的场景（金融、计费）
-- 优点：完全隔离，保证正确
-- 缺点：冲突率高，需要完善的重试机制
-- 重试模板
DO $$
DECLARE
retries INTEGER := 0;
max_retries CONSTANT INTEGER := 3;
BEGIN
LOOP
BEGIN
-- 你的事务逻辑
PERFORM transfer_money(1, 2, 100.00);
EXIT; -- 成功则退出循环
EXCEPTION
WHEN serialization_failure OR deadlock_detected THEN
retries := retries + 1;
IF retries >= max_retries THEN
RAISE EXCEPTION '重试 % 次后仍失败', max_retries;
END IF;
RAISE NOTICE '序列化冲突，第 % 次重试...', retries;
PERFORM pg_sleep(random() * 0.1); -- 随机退避
END;
END LOOP;
END;
$$;5. 并发 — MVCC
MVCC（Multi-Version Concurrency Control，多版本并发控制）是 PostgreSQL 实现事务隔离的核心机制。
MVCC 基本原理
PostgreSQL 不会直接覆盖旧数据，而是创建数据的新版本：
行的生命周期：
INSERT → 创建一个新版本 (xmin=100, xmax=0)
UPDATE → 旧版本标记删除 (xmax=200)，创建新版本 (xmin=200, xmax=0)
DELETE → 旧版本标记删除 (xmax=300)
┌──────────────────────────────────┐
│ Tuple (行版本) │
│ xmin = 创建该版本的事务ID │
│ xmax = 删除/更新该版本的事务ID │
│ xmin=0 或 xmax=0 表示未设置 │
└──────────────────────────────────┘查看行的隐藏系统列
sql
-- 每行都有隐藏的系统列
SELECT xmin, xmax, ctid, * FROM users LIMIT 5;
-- xmin：创建该行版本的事务 ID
-- xmax：删除/更新该行版本的事务 ID（0 表示活跃）
-- ctid：行的物理位置 (页号, 行号)sql
-- 观察 MVCC 行为
CREATE TABLE mvcc_demo (id INT, val TEXT);
INSERT INTO mvcc_demo VALUES (1, 'original');
SELECT xmin, xmax, ctid, * FROM mvcc_demo;
-- xmin=100, xmax=0, ctid=(0,1), id=1, val='original'
UPDATE mvcc_demo SET val = 'updated' WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM mvcc_demo;
-- xmin=101, xmax=0, ctid=(0,2), id=1, val='updated'
-- 注意 ctid 变了，说明是新行！旧行 (0,1) 被标记删除但仍在磁盘上事务快照
sql
-- 查看当前事务 ID
SELECT txid_current();
-- 查看当前快照
SELECT txid_current_snapshot();
-- 返回格式：xmin:xmax:xip_list
-- 例如：100:105:102,104
-- 含义：
-- xmin=100：事务ID < 100 的都已提交（可见）
-- xmax=105：事务ID >= 105 的都未开始（不可见）
-- xip_list=102,104：这两个事务正在进行中（不可见）
-- 判断某个事务 ID 对当前快照是否可见
SELECT txid_visible_in_snapshot(99, txid_current_snapshot()); -- true
SELECT txid_visible_in_snapshot(102, txid_current_snapshot()); -- false（进行中）
SELECT txid_visible_in_snapshot(110, txid_current_snapshot()); -- false（未来）可见性判断规则
对于一个行版本，当前事务能看到它，需要满足：
1. xmin 对应的事务已提交 AND xmin 在当前快照之前
2. xmax = 0（未被删除）
OR xmax 对应的事务未提交
OR xmax 在当前快照之后
简化理解：
- 行是"在我开始之前创建的" AND "还没被删除（或删除它的事务还没提交）"sql
-- 可见性实例演示
-- 会话 A (txid=100) -- 会话 B (txid=101)
BEGIN; BEGIN;
INSERT INTO t VALUES (1, 'hello');
-- 行: xmin=100, xmax=0
SELECT * FROM t WHERE id = 1;
-- Read Committed: 看不到（100未提交）
COMMIT;
SELECT * FROM t WHERE id = 1;
-- Read Committed: 看到了（100已提交）
-- Repeatable Read: 看不到（快照在100提交之前创建）
COMMIT;HOT Update（Heap Only Tuple）
当更新的列没有索引时，PostgreSQL 使用 HOT 优化，避免索引更新：
sql
-- HOT 更新条件：
-- 1. 更新的列不在任何索引中
-- 2. 新行版本在同一页中有空间
CREATE TABLE hot_demo (
id INTEGER PRIMARY KEY,
data TEXT, -- 无索引
counter INTEGER -- 无索引
);
-- 这个 UPDATE 可以使用 HOT
UPDATE hot_demo SET counter = counter + 1 WHERE id = 1;
-- 旧版本和新版本在同一个页中，通过指针链接
-- 索引仍然指向旧版本位置，通过 HOT 链找到新版本
-- 查看 HOT 更新统计
SELECT n_tup_hot_upd, n_tup_upd
FROM pg_stat_user_tables
WHERE relname = 'hot_demo';VACUUM 与 MVCC
MVCC 产生的旧版本需要 VACUUM 清理：
sql
-- 旧版本为什么不能立即删除？
-- 因为可能还有事务需要看到它（Repeatable Read / Serializable）
-- VACUUM 清理条件：
-- 没有任何活跃事务需要看到该旧版本
-- 查看最老的活跃事务（阻止 VACUUM 的罪魁祸首）
SELECT pid, backend_xmin, xact_start, state, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin
LIMIT 5;
-- 查看表的死元组数量
SELECT relname, n_live_tup, n_dead_tup,
last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;事务 ID 回卷问题
sql
-- PostgreSQL 事务 ID 是 32 位，约 42 亿，会回卷
-- 到达上限前必须 VACUUM FREEZE 将旧事务标记为「冻结」
-- 查看各数据库的事务 ID 年龄
SELECT datname, age(datfrozenxid) AS xid_age,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- 查看各表的年龄
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;
-- 手动冻结
VACUUM FREEZE users;
-- autovacuum 会在接近 autovacuum_freeze_max_age 时自动触发
SHOW autovacuum_freeze_max_age; -- 默认 2 亿6. 锁（表锁、行锁）
锁是并发控制的另一重要机制，与 MVCC 配合使用。
锁的层次
PostgreSQL 锁层次：
表级锁 (Table-level)
├── ACCESS SHARE 最弱，SELECT 获取
├── ROW SHARE SELECT FOR UPDATE/SHARE
├── ROW EXCLUSIVE INSERT/UPDATE/DELETE
├── SHARE UPDATE EXCLUSIVE VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY
├── SHARE CREATE INDEX（非 CONCURRENTLY）
├── SHARE ROW EXCLUSIVE CREATE TRIGGER, 某些 ALTER TABLE
├── EXCLUSIVE REFRESH MATERIALIZED VIEW CONCURRENTLY
└── ACCESS EXCLUSIVE 最强，DROP TABLE, ALTER TABLE, VACUUM FULL
行级锁 (Row-level)
├── FOR KEY SHARE 最弱
├── FOR SHARE
├── FOR NO KEY UPDATE
└── FOR UPDATE 最强表级锁
sql
-- 显式加表锁
BEGIN;
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;
-- ... 独占操作 ...
COMMIT; -- 释放锁
-- 各种模式
LOCK TABLE t IN ACCESS SHARE MODE; -- 允许读，阻止 DDL
LOCK TABLE t IN ROW SHARE MODE; -- 允许读和行锁
LOCK TABLE t IN ROW EXCLUSIVE MODE; -- 允许并发读写
LOCK TABLE t IN SHARE MODE; -- 允许读，阻止写
LOCK TABLE t IN ACCESS EXCLUSIVE MODE; -- 完全独占
-- NOWAIT：获取不到锁立即报错
LOCK TABLE users IN SHARE MODE NOWAIT;表锁兼容矩阵
| 请求 \ 持有 | ACCESS SHARE | ROW SHARE | ROW EXCL | SHARE UPDATE EXCL | SHARE | SHARE ROW EXCL | EXCLUSIVE | ACCESS EXCL |
|---|---|---|---|---|---|---|---|---|
| ACCESS SHARE | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ |
| ROW SHARE | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ |
| ROW EXCL | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ |
| SHARE UPDATE EXCL | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ |
| SHARE | ✓ | ✓ | ✗ | ✗ | ✓ | ✗ | ✗ | ✗ |
| SHARE ROW EXCL | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| EXCLUSIVE | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| ACCESS EXCL | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
✓ = 兼容（可同时持有） ✗ = 冲突（需等待）
行级锁
sql
-- FOR UPDATE：最强行锁，阻止其他事务修改或加锁
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 其他事务的 UPDATE/DELETE/FOR UPDATE 会等待
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- FOR NO KEY UPDATE：类似 FOR UPDATE，但允许 FOR KEY SHARE
-- UPDATE 语句不修改主键/唯一键时默认使用此锁
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;
COMMIT;
-- FOR SHARE：共享锁，允许其他事务读但不允许修改
BEGIN;
SELECT * FROM products WHERE id = 100 FOR SHARE;
-- 确保商品信息在事务期间不被修改
INSERT INTO order_items (product_id, price)
SELECT id, price FROM products WHERE id = 100;
COMMIT;
-- FOR KEY SHARE：最弱行锁，只阻止删除和修改主键
-- 外键检查时自动使用
BEGIN;
SELECT * FROM departments WHERE id = 5 FOR KEY SHARE;
-- 允许修改部门名称，但不允许删除该部门
COMMIT;行锁选项
sql
-- NOWAIT：获取不到锁立即报错
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- SKIP LOCKED：跳过已被锁定的行（队列处理场景）
-- 非常适合任务队列模式
SELECT * FROM task_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- 任务队列完整示例
BEGIN;
-- 获取一个未被其他 worker 处理的任务
WITH task AS (
SELECT id FROM task_queue
WHERE status = 'pending'
ORDER BY priority DESC, created_at
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE task_queue SET status = 'processing', worker_id = 'worker-1'
FROM task
WHERE task_queue.id = task.id
RETURNING task_queue.*;
COMMIT;建议锁（Advisory Lock）
建议锁是应用层面的锁，不与任何数据库对象关联：
sql
-- 会话级建议锁（持续到会话结束或显式释放）
SELECT pg_advisory_lock(12345); -- 获取排他锁
SELECT pg_advisory_lock_shared(12345); -- 获取共享锁
SELECT pg_advisory_unlock(12345); -- 释放
-- 事务级建议锁（事务结束自动释放）
SELECT pg_advisory_xact_lock(12345);
SELECT pg_advisory_xact_lock_shared(12345);
-- 非阻塞尝试
SELECT pg_try_advisory_lock(12345); -- 返回 true/false
-- 两参数版本（更灵活的命名空间）
SELECT pg_advisory_lock(classid, objid); -- 两个整数组合
-- 应用场景：防止重复执行定时任务
DO $$
BEGIN
IF pg_try_advisory_lock(hashtext('daily_report')) THEN
-- 执行日报生成逻辑
RAISE NOTICE '开始生成日报...';
PERFORM generate_daily_report();
PERFORM pg_advisory_unlock(hashtext('daily_report'));
ELSE
RAISE NOTICE '日报正在被其他进程生成，跳过';
END IF;
END;
$$;死锁
sql
-- 死锁示例
-- 会话 A -- 会话 B
BEGIN; BEGIN;
UPDATE accounts SET balance = 0
WHERE id = 1; UPDATE accounts SET balance = 0
WHERE id = 2;
UPDATE accounts SET balance = 0 UPDATE accounts SET balance = 0
WHERE id = 2; WHERE id = 1;
-- 等待 B 释放 id=2 的锁 -- 等待 A 释放 id=1 的锁
-- PostgreSQL 检测到死锁，终止其中一个事务
-- ERROR: deadlock detected避免死锁的策略
sql
-- 策略一：固定加锁顺序
-- 始终按 id 升序加锁
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- 先锁 id=1，再锁 id=2，所有事务都这样做就不会死锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 策略二：使用 NOWAIT 或 lock_timeout
BEGIN;
SET LOCAL lock_timeout = '3s';
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 如果3秒内拿不到锁就放弃
COMMIT;
-- 策略三：减少事务持有锁的时间
-- 把不需要锁保护的计算移到事务外锁监控
sql
-- 查看当前所有锁
SELECT l.locktype, l.relation::regclass, l.mode, l.granted,
a.pid, a.usename, a.query, a.state
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
ORDER BY l.relation, l.mode;
-- 查看锁等待关系（谁在等谁）
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid AND NOT blocked_locks.granted
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
AND blocked_locks.relation = blocking_locks.relation
AND blocking_locks.granted
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE blocked.pid != blocking.pid;
-- PG 14+ 更简洁的等待查询
SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
-- 查看死锁日志
-- 在 postgresql.conf 中设置：
-- log_lock_waits = on -- 记录等锁超过 deadlock_timeout 的情况
-- deadlock_timeout = 1s -- 死锁检测间隔（默认1秒）小结
- 事务保证原子性，出错即回滚，PostgreSQL 不允许脏读
- 保存点实现部分回滚，适合批量处理容错
- 隔离级别按需选择：Read Committed 够用就不要升级，Serializable 需要重试机制
- MVCC是 PostgreSQL 的核心，通过多版本实现读写不阻塞，代价是需要 VACUUM 清理旧版本
- 锁分表锁和行锁，
FOR UPDATE SKIP LOCKED是任务队列利器，建议锁适合应用层协调- 死锁通过固定加锁顺序和超时机制预防，PostgreSQL 会自动检测并终止一方