怎样在PostgreSQL 15中处理SQL物化视图死锁问题-SQL-PHP中文网

物化视图刷新卡住主因是ACCESS EXCLUSIVE锁阻塞所有读写操作；并发刷新需唯一索引且列非空，否则报错；死锁多源于刷新与业务DML争抢源表行锁，须通过pg_blocking_pids定位阻塞链。

怎样在postgresql 15中处理sql物化视图死锁问题

物化视图本身不会直接导致死锁，但它的创建、刷新和并发访问会触发底层锁冲突——尤其是当与其他DML事务交叉执行时。

REFRESH MATERIALIZED VIEW 为什么会卡住其他查询

不带 CONCURRENTLY 的刷新会拿 ACCESS EXCLUSIVE 锁，整个物化视图在刷新期间对所有 SELECT 不可见。你看到的不是慢，是连接卡在 wait_event_type = 'Lock'，pg_stat_activity 里状态还是 'active'。哪怕只刷 10 万行，只要原始查询含 JOIN 或聚合，几秒就能让应用超时。

它阻塞所有读（SELECT）、写（INSERT/UPDATE/DELETE）和结构变更（DROP/ALTER）
如果刷新语句本身被另一个长事务阻塞（比如某人正在更新源表且未提交），那它也会变成“等待者”
常见诱因：脚本中没 COMMIT 就直接跑 REFRESH；或在事务块里创建 MV 后立刻刷新

加 CONCURRENTLY 后还是报错：ERROR: cannot refresh materialized view "xxx" concurrently, because it does not have a unique index

这个报错不是警告，是硬性拒绝执行。必须同时满足三个条件：

物化视图上必须有显式创建的 UNIQUE 或 PRIMARY KEY 索引（基表主键不会自动继承到 MV 上）
索引列全部为 NOT NULL；如果源字段允许 NULL，得用 COALESCE(col, 0) 包裹后建索引
若 MV 定义含 GROUP BY a, b，索引必须覆盖全部分组列；只建 (a) 会导致比对逻辑错乱

示例修复：

CREATE UNIQUE INDEX idx_mv_patients_id ON mimiciii.mv_patients (subject_id) WHERE subject_id IS NOT NULL;

死锁实际发生在哪？怎么定位

真正死锁通常不出现在 REFRESH 自身，而是它与业务 DML 争抢源表或 MV 行锁时发生。比如：

事务 A 正在刷新 MV（需读取 patients 表），同时事务 B 在更新同一张 patients 表的某行并持有行锁
事务 B 又试图读 MV（需等刷新完成），而事务 A 刷新又卡在等 B 释放行锁 → 形成环

查法（PostgreSQL 15+）：

SELECT blocked_pid, blocking_pid, blocked_query, blocking_query
FROM pg_blocking_pids(NULL) AS blocked_pid
JOIN pg_stat_activity ON pid = blocked_pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_pid;

注意看 blocked_query 是否含 REFRESH，blocking_query 是否是 UPDATE/DELETE 源表操作。