PostgreSQL 事务与锁 #
事务概述 #
事务是一组数据库操作的逻辑单元，要么全部成功，要么全部失败。
text
┌─────────────────────────────────────────────────────────────┐
│ ACID 特性 │
├─────────────────────────────────────────────────────────────┤
│ │
│ A - Atomicity（原子性） │
│ 事务中的操作要么全部执行，要么全部不执行 │
│ │
│ C - Consistency（一致性） │
│ 事务执行前后数据库状态保持一致 │
│ │
│ I - Isolation（隔离性） │
│ 并发事务之间互不干扰 │
│ │
│ D - Durability（持久性） │
│ 事务提交后数据永久保存 │
│ │
└─────────────────────────────────────────────────────────────┘
事务控制 #
基本事务 #
sql
-- 开始事务
BEGIN;
-- 或
BEGIN TRANSACTION;
-- 或
START TRANSACTION;
-- 提交事务
COMMIT;
-- 或
COMMIT TRANSACTION;
-- 回滚事务
ROLLBACK;
-- 或
ROLLBACK TRANSACTION;
-- 示例
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 如果出错则回滚
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 发现错误
ROLLBACK;
保存点 #
sql
-- 创建保存点
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT order_created;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (currval('orders_id_seq'), 1, 5);
-- 如果订单项插入失败，回滚到保存点
-- ROLLBACK TO order_created;
COMMIT;
-- 释放保存点
RELEASE SAVEPOINT order_created;
事务模式 #
sql
-- 读写事务（默认）
BEGIN READ WRITE;
-- 只读事务
BEGIN READ ONLY;
-- 隔离级别
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 组合使用
BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE;
隔离级别 #
隔离级别对比 #
text
┌─────────────────┬─────────────────────────────────────────────┐
│ 隔离级别 │ 可能的问题 │
├─────────────────┼─────────────────────────────────────────────┤
│ READ UNCOMMITTED│ 脏读、不可重复读、幻读 │
│ READ COMMITTED │ 不可重复读、幻读（PostgreSQL 默认） │
│ REPEATABLE READ │ 幻读 │
│ SERIALIZABLE │ 无（最高隔离级别） │
└─────────────────┴─────────────────────────────────────────────┘
PostgreSQL 实现说明：
- READ UNCOMMITTED 实际等同于 READ COMMITTED
- REPEATABLE READ 实际上也防止了幻读
READ COMMITTED（默认） #
sql
-- 会话 1
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- balance: 1000
-- 会话 2（在会话 1 事务未提交时）
BEGIN;
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;
-- 会话 1 继续查询
SELECT * FROM accounts WHERE id = 1;
-- balance: 2000（可以看到其他事务已提交的修改）
COMMIT;
REPEATABLE READ #
sql
-- 会话 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;
-- balance: 1000
-- 会话 2
BEGIN;
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;
-- 会话 1 继续查询
SELECT * FROM accounts WHERE id = 1;
-- balance: 1000（看不到其他事务的修改）
COMMIT;
-- 会话 1 新事务
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;
-- balance: 2000（新事务可以看到已提交的修改）
COMMIT;
SERIALIZABLE #
sql
-- 最高隔离级别，完全隔离并发事务
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 如果检测到序列化冲突，会抛出错误
-- ERROR: could not serialize access due to concurrent update
-- 应用需要处理这个错误并重试
MVCC 多版本并发控制 #
MVCC 原理 #
text
┌─────────────────────────────────────────────────────────────┐
│ MVCC 工作原理 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 每行数据包含： │
│ ├── xmin - 插入该行的事务 ID │
│ └── xmax - 删除/更新该行的事务 ID │
│ │
│ 事务可见性规则： │
│ 1. xmin 已提交且 xmin < 当前事务 ID │
│ 2. xmax 为空或 xmax 未提交或 xmax >= 当前事务 ID │
│ │
│ 示例： │
│ ┌───────────────────────────────────────────────────────┐ │
│ │ id | name | balance | xmin | xmax │ │
│ ├────┼───────┼─────────┼──────┼────── │ │
│ │ 1 | Alice | 1000 | 100 | │ │
│ │ 1 | Alice | 1500 | 150 | (更新后新版本) │ │
│ └───────────────────────────────────────────────────────┘ │
│ │
│ 事务 120 看到：balance = 1000（旧版本） │
│ 事务 160 看到：balance = 1500（新版本） │
│ │
└─────────────────────────────────────────────────────────────┘
查看事务信息 #
sql
-- 查看当前事务 ID
SELECT txid_current();
-- 查看行的版本信息
SELECT
id,
name,
xmin,
xmax,
ctid
FROM employees;
-- ctid 是行的物理位置 (page_number, item_number)
锁机制 #
锁类型 #
text
┌─────────────────────┬─────────────────────────────────────────┐
│ 锁类型 │ 说明 │
├─────────────────────┼─────────────────────────────────────────┤
│ ACCESS SHARE │ SELECT 请求 │
│ ROW SHARE │ SELECT FOR UPDATE/SHARE │
│ ROW EXCLUSIVE │ UPDATE, DELETE, INSERT │
│ SHARE UPDATE EXCLUSIVE│ VACUUM, ALTER TABLE │
│ SHARE │ 创建外键时获取 │
│ SHARE ROW EXCLUSIVE │ 并发更新 │
│ EXCLUSIVE │ 阻塞 ROW SHARE │
│ ACCESS EXCLUSIVE │ ALTER TABLE, DROP TABLE, VACUUM FULL │
└─────────────────────┴─────────────────────────────────────────┘
表级锁 #
sql
-- 显式获取表锁
LOCK TABLE employees IN ACCESS SHARE MODE;
LOCK TABLE employees IN ROW SHARE MODE;
LOCK TABLE employees IN ROW EXCLUSIVE MODE;
LOCK TABLE employees IN SHARE MODE;
LOCK TABLE employees IN EXCLUSIVE MODE;
LOCK TABLE employees IN ACCESS EXCLUSIVE MODE;
-- NOWAIT 选项（如果无法获取锁则立即报错）
LOCK TABLE employees IN ACCESS EXCLUSIVE MODE NOWAIT;
行级锁 #
sql
-- FOR UPDATE：排他行锁
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
-- FOR NO KEY UPDATE：非键排他锁
SELECT * FROM employees WHERE id = 1 FOR NO KEY UPDATE;
-- FOR SHARE：共享行锁
SELECT * FROM employees WHERE id = 1 FOR SHARE;
-- FOR KEY SHARE：键共享锁
SELECT * FROM employees WHERE id = 1 FOR KEY SHARE;
-- NOWAIT 选项
SELECT * FROM employees WHERE id = 1 FOR UPDATE NOWAIT;
-- SKIP LOCKED：跳过已锁定的行
SELECT * FROM employees WHERE status = 'pending'
FOR UPDATE SKIP LOCKED LIMIT 10;
查看锁信息 #
sql
-- 查看当前锁
SELECT
pid,
locktype,
relation::regclass AS table_name,
mode,
granted
FROM pg_locks
WHERE relation IS NOT NULL;
-- 查看阻塞的查询
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 current_statement_in_blocking_process
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;
-- 终止阻塞的查询
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = <blocking_pid>;
死锁 #
死锁示例 #
sql
-- 会话 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 会话 2（同时执行）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- 会话 1 继续执行
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 等待...
-- 会话 2 继续执行
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 死锁！其中一个会话会收到错误：
-- ERROR: deadlock detected
-- PostgreSQL 会自动检测并解决死锁
避免死锁 #
sql
-- 1. 按相同顺序访问资源
-- 总是先锁定 id 小的记录
-- 2. 使用 NOWAIT 检测
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- 如果无法获取锁，立即报错
-- 3. 设置锁超时
SET lock_timeout = '5s';
-- 4. 减少事务持有锁的时间
-- 尽量缩短事务时间
并发问题示例 #
丢失更新 #
sql
-- 问题：两个事务同时更新同一行
-- 会话 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- balance: 1000
-- 计算新余额...
UPDATE accounts SET balance = 1050 WHERE id = 1;
COMMIT;
-- 会话 2（同时执行）
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- balance: 1000
-- 计算新余额...
UPDATE accounts SET balance = 1100 WHERE id = 1;
COMMIT;
-- 最终 balance = 1100，会话 1 的更新丢失
-- 解决方案：使用 FOR UPDATE
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 加锁
-- 计算新余额...
UPDATE accounts SET balance = 1050 WHERE id = 1;
COMMIT;
队列处理 #
sql
-- 使用 SKIP LOCKED 实现任务队列
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
data JSONB,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 消费者获取任务
BEGIN;
UPDATE tasks
SET status = 'processing'
WHERE id = (
SELECT id FROM tasks
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;
COMMIT;
-- 多个消费者可以并发处理，不会获取到相同的任务
事务最佳实践 #
事务设计原则 #
text
┌─────────────────────────────────────────────────────────────┐
│ 事务最佳实践 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 保持事务简短 │
│ - 减少持有锁的时间 │
│ - 避免在事务中执行耗时操作 │
│ │
│ 2. 合理选择隔离级别 │
│ - 大多数场景 READ COMMITTED 足够 │
│ - 需要一致性读时使用 REPEATABLE READ │
│ │
│ 3. 避免长事务 │
│ - 长事务会阻塞 VACUUM │
│ - 导致表膨胀 │
│ │
│ 4. 处理序列化失败 │
│ - SERIALIZABLE 隔离级别可能失败 │
│ - 应用需要重试逻辑 │
│ │
│ 5. 使用适当的锁 │
│ - 不要过度使用显式锁 │
│ - 让数据库自动管理锁 │
│ │
└─────────────────────────────────────────────────────────────┘
监控事务 #
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';
-- 查看空闲事务
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
-- 设置空闲事务超时
SET idle_in_transaction_session_timeout = '10min';
学习路径 #
text
高级阶段
├── 索引
├── 视图
├── 存储过程
├── 触发器
└── 事务与锁（本文）
运维阶段
├── 用户权限管理
├── 备份与恢复
└── 性能优化
下一步 #
掌握了事务与锁后，接下来学习 用户权限管理，了解如何管理数据库安全！
最后更新：2026-03-29