SQL 与查询优化（PostgreSQL 篇）· 第六期

锁与并发控制 – 查询优化的另一维度

前五期我们专注 SQL 本身：执行计划、统计信息、连接算法、高级 SQL、分区表，以及优化器调参。
但在高并发系统中，锁与事务往往成为性能瓶颈。本期深入 PostgreSQL 的锁机制、MVCC、Vacuum 原理，学会诊断锁冲突、优化高并发写入，让数据库在多用户环境下依然流畅运行。

一、PostgreSQL 的并发控制基石：MVCC

PostgreSQL 没有使用传统的两阶段锁（2PL）来避免读写冲突，而是采用了 多版本并发控制（MVCC）。

读操作不会阻塞写操作，写操作也不会阻塞读操作。
每个 SQL 语句（或事务）看到的是某个时间点的数据库快照。
更新操作会产生新的元组版本，旧版本仍然保留，直到不再需要（被所有活跃事务可见）。

关键系统列：

列	含义
`xmin`	插入该元组的事务 ID
`xmax`	删除或更新该元组的事务 ID（0 表示未删除）
`cmin` / `cmax`	事务内命令序列号

查询时，PostgreSQL 结合当前事务的快照（xmin、xmax 与当前事务 ID、pg_snapshot）判断可见性。

MVCC 带来的好处

读不阻塞写，写不阻塞读。
SELECT 可以拿到一致性快照，无需加锁。

MVCC 的代价

旧版本元组会一直留在表中，导致表膨胀。
需要后台进程 autovacuum 清理死元组。
长事务会阻止旧版本被清理，加速膨胀。

二、PostgreSQL 锁体系概览

PostgreSQL 的锁分为表级锁和行级锁，还有一个轻量级的 自旋锁（SpinLock） 用于保护共享内存。

2.1 表级锁模式（从低到高冲突程度）

锁模式	关键词	冲突对象	典型场景
`ACCESS SHARE`	`SELECT`	`ACCESS EXCLUSIVE`	读取表
`ROW SHARE`	`SELECT FOR UPDATE/SHARE`	`EXCLUSIVE`, `ACCESS EXCLUSIVE`	准备修改行
`ROW EXCLUSIVE`	`INSERT`, `UPDATE`, `DELETE`	`SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`	修改数据
`SHARE UPDATE EXCLUSIVE`	`VACUUM`, `CREATE INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`	保护模式变更
`SHARE`	`CREATE INDEX` (非并发)	`ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`	读但阻止写入
`SHARE ROW EXCLUSIVE`	较少使用	`ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`	并发读但禁止并发 DML
`EXCLUSIVE`	`REFRESH MATERIALIZED VIEW` (非并发)	`ROW SHARE` 及以上	防止并发读写
`ACCESS EXCLUSIVE`	`DROP`, `TRUNCATE`, `REINDEX`, `CLUSTER`, `VACUUM FULL`	所有其他锁	完全独占

查看当前表级锁：

SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE locktype = 'relation' AND relation = 'orders'::regclass;

2.2 行级锁

行级锁记录在内存的锁表中（不是存储元组中），通过 xmax 标记哪个事务持有锁。

FOR UPDATE：排他行锁。
FOR SHARE：共享行锁。
FOR NO KEY UPDATE、FOR KEY SHARE：用于外键约束的细化锁。

注意：行级锁与表级锁 ROW EXCLUSIVE / ROW SHARE 配合使用。

2.3 锁监视视图

pg_locks：当前所有锁的信息。
pg_stat_activity：会话状态，wait_event 列指示是否等待锁。
组合查询定位阻塞源：

SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked.query AS blocked_query,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.page = blocking_locks.page
    AND blocked_locks.tuple = blocking_locks.tuple
    AND blocked_locks.virtualxid = blocking_locks.virtualxid
    AND blocked_locks.transactionid = blocking_locks.transactionid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

更简洁视图：pg_blocking_pids(pid) 函数。

SELECT pid, pg_blocking_pids(pid), query
FROM pg_stat_activity
WHERE array_length(pg_blocking_pids(pid), 1) > 0;

三、死锁 – 检测与预防

3.1 死锁示例

事务1：

UPDATE orders SET amount = 100 WHERE id = 1;
UPDATE orders SET amount = 200 WHERE id = 2;

事务2：

UPDATE orders SET amount = 300 WHERE id = 2;
UPDATE orders SET amount = 400 WHERE id = 1;

两个事务相互等待对方释放行锁，形成死锁。

3.2 死锁检测

PostgreSQL 有死锁检测器（deadlock_timeout 参数，默认 1 秒）。当等待超过该时间，检测器会主动检查死锁，并回滚其中一个事务（代价最小的那个），另一个继续执行。

查看死锁日志（需要设置 log_lock_waits = on）：

LOG:  process 12345 detected deadlock while waiting for ShareLock on transaction 9876
DETAIL:  Process 12345 waits for ShareLock on transaction 9876; blocked by process 12346.
Process 12346 waits for ShareLock on transaction 9875; blocked by process 12345.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "orders"
STATEMENT:  UPDATE orders SET amount = 100 WHERE id = 1;

3.3 死锁预防

固定访问顺序：应用层约定按相同顺序更新多行（例如按 id 升序）。
尽早提交：减少锁持有时间。
使用 FOR UPDATE 谨慎：避免死锁。
设置 lock_timeout：防止无限等待（例如 SET lock_timeout = '5s'）。
使用 SKIP LOCKED：非阻塞队列模式（下文详述）。

四、长事务与表膨胀 – Vacuum 的重要性

4.1 表膨胀原理

当更新或删除行时，旧版本保留在堆中，直到没有事务再需要看到它（即最旧活跃事务之前的版本）。

如果有一个长事务一直不提交，最旧活跃事务 ID 停滞不前，autovacuum 无法清理死元组。
表会持续膨胀，索引也会膨胀，导致查询性能下降（扫描更多块，缓存效率降低）。

4.2 查看膨胀情况

使用 pg_stat_user_tables：

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count
FROM pg_stat_user_tables WHERE n_dead_tup > 1000;

更精确的膨胀率查询（使用 pgstattuple 扩展）：

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');

dead_tuple_percent 超过 10% 就需要关注。

4.3 autovacuum 调优

默认 autovacuum 参数：

参数	默认值	含义
`autovacuum_vacuum_threshold`	50	死元组数超过此值 + 表的扫描行数比例时才触发
`autovacuum_vacuum_scale_factor`	0.2	死元组比例（相对于表大小）
`autovacuum_analyze_threshold`	50	同上，用于 analyze
`autovacuum_analyze_scale_factor`	0.1
`autovacuum_naptime`	1min	轮询间隔
`autovacuum_max_workers`	3	并发 vacuum 进程数

对于大表（数亿行），默认 scale factor 0.2 意味着要累积 20% 死元组才触发 vacuum，这在频繁更新的表上会导致严重膨胀。建议为这些表单独设置存储参数：

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 10000);
ALTER TABLE orders SET (autovacuum_vacuum_cost_delay = 2);  -- 毫秒，降低对正常负载的影响

4.4 手动干预

立即清理特定表（不重组，只标记可复用空间）：
VACUUM (VERBOSE, ANALYZE) orders;
回收空间并重组表（会锁表，需要独占锁）：
VACUUM FULL orders;
或者使用 pg_repack 扩展实现在线重组。

4.5 监控长事务

SELECT pid, age(backend_xid) AS xid_age, backend_xmin, query_start, state, query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(backend_xid) DESC;

age(backend_xid) 超过 2 亿（接近事务 ID 回卷）时需要紧急处理。通常及时提交或终止长事务。

五、高并发写入热点优化

5.1 热点行更新 – 从排队到批处理

问题：多个会话同时更新同一行（例如秒杀场景减库存）。

默认行为：第一个会话获得行锁，后续会话排队，导致大量锁等待，TPS 急剧下降。

解决方案：

使用乐观锁 + 重试：应用层读取当前版本号，更新时检查版本，冲突则重试。
把热点拆分为多行：例如库存拆成 10 份，随机选择更新（最终一致性）。
使用 advisory lock 进行限流：应用层只允许有限并发。

5.2 `SELECT ... FOR UPDATE SKIP LOCKED` – 无锁队列

场景：多个消费者从任务表中取任务，希望每个任务只被一个消费者处理。传统做法是 SELECT ... FOR UPDATE，但会阻塞其他消费者。
使用 SKIP LOCKED 跳过已被锁定的行：

BEGIN;
SELECT id, payload FROM task_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- 处理任务
UPDATE task_queue SET status = 'done' WHERE id IN (...);
COMMIT;

每个事务跳过已被其他消费者锁定的行，实现无竞争的任务分发。
配合 NOWAIT 可立即返回而不是等待。

执行计划注意：SKIP LOCKED 会导致锁检查和跳过，通常使用索引扫描。确保 WHERE status = 'pending' 有索引，并且排序列有索引，避免文件排序。

5.3 减少锁冲突的 DDL 技巧

使用 CREATE INDEX CONCURRENTLY（不阻塞写入，但耗时更长）。
使用 REINDEX CONCURRENTLY（PG 12+）。
使用 ALTER TABLE ... ADD COLUMN ... DEFAULT ...：PG 11 之后，带默认值的加列不再需要重写全表（但 DEFAULT 值仍会写入所有行，大表操作需谨慎）。
使用 TRUNCATE 而不是 DELETE 全表（TRUNCATE 需要 ACCESS EXCLUSIVE 锁，但极快）。

六、实战案例：行锁冲突导致吞吐量下降 90%

场景

电商系统优惠券领取功能，表 coupons 存储每张券的使用状态。每张券有一行，user_id 默认 NULL，领取时更新。

高并发下（1000 QPS），执行：

UPDATE coupons SET user_id = 123, used_at = now()
WHERE code = 'XXXXXX' AND user_id IS NULL;

同一张券的并发请求会在 code 唯一索引上等待行锁。高峰期大量会话处于 waiting 状态，TPS 从 1000 降到 100。

诊断

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

显示大量 Lock 等待，wait_event 是 transactionid。

查看锁树：

SELECT pid, pg_blocking_pids(pid), query
FROM pg_stat_activity
WHERE array_length(pg_blocking_pids(pid), 1) > 0;

发现一个更新语句长时间未提交，持有了 code 行锁，后续请求排队。

根本原因：应用层事务未及时提交（应用代码中开启了事务却在之后做了远程调用）。导致行锁持有时间过长（秒级）。在高并发下，队列越长，响应越慢，形成雪崩。

解决方案

紧急修复：杀死阻塞源头（谨慎），并联系应用负责人提交或回滚。
```
SELECT pg_terminate_backend(pid);
```
代码重构：将领取操作设计为单条语句 + 立即提交，避免在事务中夹杂外部调用。
- 使用 SET lock_timeout = '200ms' 防止长时间等待。
- 使用 UPDATE ... WHERE ... RETURNING 判断是否成功。
乐观锁改进：不依赖行锁排队，而是先尝试更新，如果更新行数为 0 表示已被领走，应用层立即返回“已领完”。这样不会阻塞其他会话。

UPDATE coupons SET user_id = 123, used_at = now()
WHERE code = 'XXXXXX' AND user_id IS NULL
RETURNING *;

如果返回行数 = 1，成功；否则失败。

效果：优化后，即使有并发冲突，大部分请求立即失败（业务上可接受，如提示“手慢了”），不会阻塞，TPS 恢复到 900+。

七、隔离级别对查询的影响

隔离级别	脏读	不可重复读	幻读	并发性能影响
`READ UNCOMMITTED`	可能（PG 同 READ COMMITTED）	可能	可能	最高，但不安全
`READ COMMITTED`（默认）	不会	可能	可能	高，普通场景推荐
`REPEATABLE READ`	不会	不会	不会（PG 中通过快照避免）	中等，长事务影响
`SERIALIZABLE`	不会	不会	不会（使用 SSI 检测）	低，频繁冲突重试

READ COMMITTED：每个语句看到语句开始时的快照。适合大多数 OLTP。
REPEATABLE READ：事务开始时的快照。适合需要一致性读的报告（但要注意长事务导致膨胀）。
SERIALIZABLE：可串行化，通过谓词锁或 SSI 检测。适合严格一致性场景，但可能有大量 40001 错误，需要应用层重试。

参数调优

default_transaction_isolation：默认隔离级别。
transaction_isolation：当前事务的。

案例：将长报告查询从默认 READ COMMITTED 改为 REPEATABLE READ 可以避免多次查询之间数据不一致，但需要监控膨胀。

八、常用监控与急救脚本

8.1 查看当前锁等待树

WITH RECURSIVE lock_tree AS (
  SELECT pid, pg_blocking_pids(pid) as blockers, query, state, wait_event
  FROM pg_stat_activity
  WHERE state != 'idle'
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid), a.query, a.state, a.wait_event
  FROM pg_stat_activity a
  JOIN lock_tree l ON a.pid = ANY(l.blockers)
)
SELECT * FROM lock_tree;

8.2 杀掉长时间阻塞的查询（谨慎）

-- 杀掉超过 5 分钟未提交的事务
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND age(now(), state_change) > interval '5 minutes';

8.3 查看 autovacuum 运行状态

SELECT pid, query, state, wait_event, now() - xact_start AS duration
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%' AND state = 'active';

如果 autovacuum 长期运行且阻塞了 DDL，可以设置 log_autovacuum_min_duration = 0 记录详细日志。

九、总结与下期预告

本期要点

理解 MVCC 带来的读写不冲突优势和表膨胀代价。
掌握锁类型（表级锁、行级锁）和监控视图 pg_locks、pg_stat_activity。
学会死锁检测与预防策略（固定顺序、lock_timeout）。
深入 autovacuum 调优，避免因长事务导致的膨胀。
高并发写入优化：热点行拆分、SKIP LOCKED、乐观锁。
实战案例展示了行锁排队导致的雪崩及其解决方案。

下期预告

第七期：查询缓存、连接池与中间件优化

PostgreSQL 查询缓存方案：pgpool-II 的语句缓存、物化视图持续刷新。
连接池原理：PgBouncer 的三种模式（会话、事务、语句）。
读写分离架构：同步/异步流复制，负载均衡策略。
分库分表中间件：citus 扩展（分布式 PostgreSQL）。
实战：使用事务级连接池解决频繁连接断开的性能抖动。

欢迎分享你在生产环境遇到的锁问题或高并发写入瓶颈，我们将在后续内容中选取典型案例。

并发控制的本质是权衡一致性、隔离性和性能，PostgreSQL 给了你所有工具，但如何使用取决于你对业务的理解。 我们第七期见。

posted on 2026-04-24 10:29 绩隐金阅读(4) 评论(0) 收藏举报

刷新页面返回顶部

SQL 与查询优化（PostgreSQL 篇）· 第六期

SQL 与查询优化（PostgreSQL 篇）· 第六期

锁与并发控制 – 查询优化的另一维度

一、PostgreSQL 的并发控制基石：MVCC

MVCC 带来的好处

MVCC 的代价

二、PostgreSQL 锁体系概览

2.1 表级锁模式（从低到高冲突程度）

2.2 行级锁

2.3 锁监视视图

三、死锁 – 检测与预防

3.1 死锁示例

3.2 死锁检测

3.3 死锁预防

四、长事务与表膨胀 – Vacuum 的重要性

4.1 表膨胀原理

4.2 查看膨胀情况

4.3 autovacuum 调优

4.4 手动干预

4.5 监控长事务

五、高并发写入热点优化

5.1 热点行更新 – 从排队到批处理

5.2 `SELECT ... FOR UPDATE SKIP LOCKED` – 无锁队列

5.3 减少锁冲突的 DDL 技巧

六、实战案例：行锁冲突导致吞吐量下降 90%

场景

诊断

解决方案

七、隔离级别对查询的影响

参数调优

八、常用监控与急救脚本

8.1 查看当前锁等待树

8.2 杀掉长时间阻塞的查询（谨慎）

8.3 查看 autovacuum 运行状态

九、总结与下期预告

本期要点

下期预告

导航

公告

SQL 与查询优化（PostgreSQL 篇）· 第六期

SQL 与查询优化（PostgreSQL 篇）· 第六期

锁与并发控制 – 查询优化的另一维度

一、PostgreSQL 的并发控制基石：MVCC

MVCC 带来的好处

MVCC 的代价

二、PostgreSQL 锁体系概览

2.1 表级锁模式（从低到高冲突程度）

2.2 行级锁

2.3 锁监视视图

三、死锁 – 检测与预防

3.1 死锁示例

3.2 死锁检测

3.3 死锁预防

四、长事务与表膨胀 – Vacuum 的重要性

4.1 表膨胀原理

4.2 查看膨胀情况

4.3 autovacuum 调优

4.4 手动干预

4.5 监控长事务

五、高并发写入热点优化

5.1 热点行更新 – 从排队到批处理

5.2 SELECT ... FOR UPDATE SKIP LOCKED – 无锁队列

5.3 减少锁冲突的 DDL 技巧

六、实战案例：行锁冲突导致吞吐量下降 90%

场景

诊断

解决方案

七、隔离级别对查询的影响

参数调优

八、常用监控与急救脚本

8.1 查看当前锁等待树

8.2 杀掉长时间阻塞的查询（谨慎）

8.3 查看 autovacuum 运行状态

九、总结与下期预告

本期要点

下期预告

导航

公告

5.2 `SELECT ... FOR UPDATE SKIP LOCKED` – 无锁队列