PostgreSQL 锁机制与并发控制实战详解

✅ PostgreSQL 锁机制与并发控制实战详解

在高并发场景下，锁机制是保证数据一致性、避免脏读/幻读/丢失更新的核心。本篇将带你深入 PostgreSQL 的 MVCC、锁类型、隔离级别、死锁处理等实战内容，助你构建高并发、高可靠的应用系统。

---

🧭 一、核心概念：MVCC 与锁的关系

PostgreSQL 采用 MVCC（多版本并发控制） + 锁机制 的混合模型：

• MVCC：解决读写冲突（读不阻塞写，写不阻塞读）
• 锁机制：解决写写冲突（避免数据竞争）

💡 简单理解：

• 读操作 → 靠 MVCC（快照隔离）
• 写操作 → 靠锁（行锁、表锁等）

---

一、MVCC（多版本并发控制）详解

---

✅ 1. MVCC 原理

每个事务看到的是事务开始时的数据快照，而非最新数据。

• 每行数据包含：xmin（插入事务ID）、xmax（删除/更新事务ID）
• 事务根据自身 txid 和行的 xmin/xmax 判断数据可见性

-- 查看系统列（需在表定义时启用）
SELECT xmin, xmax, cmin, cmax, * FROM users;

---

✅ 2. 事务快照（Snapshot）

事务开始时获取快照，决定哪些数据版本可见：

-- 查看当前事务快照
SELECT txid_current(), txid_current_snapshot();
-- 输出：1234 | 1200:1234:1205,1208  (活跃事务列表)

快照格式：xmin:xmax:xip_list

• xmin：最早仍活跃的事务 ID
• xmax：下一个将分配的事务 ID
• xip_list：当前活跃事务 ID 列表

---

✅ 3. MVCC 优势

优势	说明
读不阻塞写	SELECT 不加锁，不影响 UPDATE/DELETE
写不阻塞读	UPDATE/DELETE 不阻塞 SELECT
避免脏读	事务只能看到已提交的数据
支持高并发	多个事务可同时读取不同版本

---

二、锁机制详解

---

✅ 1. 锁类型（Lock Modes）

PostgreSQL 支持多种锁模式，按强度从弱到强：

锁模式	缩写	作用对象	说明
ACCESS SHARE	AS	表	SELECT 语句持有
ROW SHARE	RS	表	SELECT FOR UPDATE/SHARE 持有
ROW EXCLUSIVE	RX	表	INSERT/UPDATE/DELETE 持有
SHARE UPDATE EXCLUSIVE	SUE	表	VACUUM (非FULL), ANALYZE, CREATE INDEX CONCURRENTLY
SHARE	S	表	CREATE INDEX (非并发)
SHARE ROW EXCLUSIVE	SRX	表	CREATE TRIGGER, ALTER TABLE 部分操作
EXCLUSIVE	X	表	REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE	AX	表	DROP TABLE, TRUNCATE, VACUUM FULL, ALTER TABLE

⚠️ 关键冲突规则：

• ACCESS EXCLUSIVE 与所有锁冲突
• ROW EXCLUSIVE 与 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 冲突
• ACCESS SHARE 只与 ACCESS EXCLUSIVE 冲突

---

✅ 2. 行级锁（Row-Level Locks）

通过 SELECT FOR UPDATE/SHARE 显式加锁：

-- FOR UPDATE：加排他锁（其他事务不能 UPDATE/DELETE/SELECT FOR UPDATE）
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 此时其他事务对该行的 UPDATE/DELETE 会被阻塞
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE：加共享锁（其他事务可读，但不能 UPDATE/DELETE）
BEGIN;
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- 其他事务可 SELECT，但不能 UPDATE/DELETE
COMMIT;

---

✅ 3. 死锁检测与处理

▶ 死锁示例：

-- 事务1
BEGIN;
UPDATE users SET name = 'A' WHERE id = 1; -- 持有 user1 锁
UPDATE users SET name = 'B' WHERE id = 2; -- 等待 user2 锁

-- 事务2（同时执行）
BEGIN;
UPDATE users SET name = 'C' WHERE id = 2; -- 持有 user2 锁
UPDATE users SET name = 'D' WHERE id = 1; -- 等待 user1 锁 → 死锁！

▶ PostgreSQL 自动处理：

• 检测到死锁 → 终止其中一个事务（报错）
• 客户端需捕获异常并重试

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.

▶ 避免死锁策略：

1. 按固定顺序访问资源（如按 ID 升序）
2. 设置锁超时：SET lock_timeout = '5s';
3. 使用 NOWAIT：立即失败而非等待

SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;
-- 如果锁被占用，立即报错：ERROR: could not obtain lock on row

---

✅ 4. 查看锁信息

-- 查看当前所有锁
SELECT 
    locktype,
    relation::regclass,
    mode,
    transactionid,
    virtualtransaction,
    pid,
    granted
FROM pg_locks
WHERE NOT granted; -- 查看等待中的锁

-- 查看阻塞关系
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
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.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    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;

---

三、事务隔离级别实战

PostgreSQL 支持四种隔离级别：

隔离级别	脏读	不可重复读	幻读	PostgreSQL 实现方式
READ UNCOMMITTED	❌	❌	❌	实际等同于 READ COMMITTED
READ COMMITTED	❌	✅	✅	默认级别，MVCC + 行锁
REPEATABLE READ	❌	❌	✅	快照隔离（SSI 之前）
SERIALIZABLE	❌	❌	❌	SSI（可串行化快照隔离）

💡 PostgreSQL 9.1+ 使用 SSI（Serializable Snapshot Isolation）实现真正的可串行化

---

✅ 1. READ COMMITTED（默认）

-- 事务1
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 返回 1000
-- 此时事务2更新了 balance = 2000 并提交
SELECT balance FROM accounts WHERE id = 1; -- 返回 2000（不可重复读）
COMMIT;

---

✅ 2. REPEATABLE READ

-- 事务1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 返回 1000
-- 事务2更新 balance = 2000 并提交
SELECT balance FROM accounts WHERE id = 1; -- 仍返回 1000（可重复读）
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 如果事务2已修改，会报错：could not serialize access due to concurrent update
COMMIT;

---

✅ 3. SERIALIZABLE（推荐金融场景）

-- 事务1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts; -- 返回 3000
-- 事务2插入新账户 balance = 500 并提交
SELECT SUM(balance) FROM accounts; -- 仍返回 3000
INSERT INTO transfer_log VALUES ('总余额: 3000');
COMMIT; -- 可能报错：serialization failure

-- 客户端需捕获异常并重试

✅ SERIALIZABLE 级别下，PostgreSQL 会检测写偏斜（Write Skew）等并发异常

---

四、并发控制实战案例

---

🎯 案例1：账户转账（避免丢失更新）

❌ 错误做法（无锁）：

-- 事务1和事务2同时执行
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 原余额1000
-- 结果：余额900（丢失一次扣款）

✅ 正确做法（行锁）：

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 加锁
-- 检查余额是否足够
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

✅ 更佳做法（原子操作）：

-- 单条语句，无需显式锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
-- 检查 affected rows = 1 表示成功

---

🎯 案例2：库存扣减（高并发场景）

❌ 悲观锁（性能差）：

BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 库存检查
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

✅ 乐观锁（推荐）：

-- 方法1：版本号
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND stock >= 1 AND version = 5; -- 客户端传入旧版本号
-- 检查 affected rows > 0

-- 方法2：直接条件
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1;
-- 检查 affected rows = 1

---

🎯 案例3：防止重复插入（唯一约束 vs 锁）

✅ 推荐：唯一约束（最高效）

ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

-- 插入时捕获异常
INSERT INTO users (email) VALUES ('a@example.com');
-- 如果重复：ERROR: duplicate key value violates unique constraint

⚠️ 备选：显式锁（性能差）

BEGIN;
SELECT 1 FROM users WHERE email = 'a@example.com' FOR UPDATE;
-- 如果不存在则插入
INSERT INTO users (email) VALUES ('a@example.com');
COMMIT;

---

五、性能优化与监控

---

✅ 1. 锁超时设置

-- 会话级
SET lock_timeout = '5s';

-- 事务级
BEGIN;
SET LOCAL lock_timeout = '3s';
SELECT * FROM users WHERE id = 1 FOR UPDATE;
COMMIT;

---

✅ 2. 监控长事务

-- 查看运行超过5分钟的事务
SELECT 
    pid,
    now() - xact_start as duration,
    query
FROM pg_stat_activity
WHERE state = 'active' 
  AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;

---

✅ 3. 监控阻塞

-- 创建视图方便监控
CREATE VIEW blocking_locks AS
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query,
    now() - blocked_activity.query_start AS blocked_duration
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.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    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;

---

✅ 4. 减少锁冲突策略

1. 缩短事务时间：尽快 COMMIT
2. 按固定顺序访问数据：避免死锁
3. 使用乐观锁：减少行锁使用
4. 避免长事务：特别是写操作
5. 合理使用隔离级别：READ COMMITTED 通常足够

---

🎯 六、实践任务

请完成以下操作：

1. 创建 accounts(id, balance) 表，插入测试数据
2. 开启两个 psql 会话，模拟转账死锁场景
3. 使用 pg_locks 观察锁状态
4. 修改代码按固定顺序访问避免死锁
5. 测试 SERIALIZABLE 隔离级别下的写偏斜检测
6. 实现库存扣减的乐观锁方案

---

📊 隔离级别选择指南

场景	推荐隔离级别	说明
普通 Web 应用	READ COMMITTED	默认级别，性能好
报表统计	REPEATABLE READ	保证统计过程中数据一致
金融交易、库存管理	SERIALIZABLE	防止所有并发异常
数据迁移、批量操作	READ COMMITTED	避免长事务锁表